When to Move Analytics Off Postgres (And When Not To)

Your product database is Postgres. It runs your transactional workload fine. But your analytics queries — the ones powering internal dashboards, customer-facing reports, BI tools — take 30+ seconds. Engineers want to move analytics to ClickHouse, Snowflake, or BigQuery.

Should you?

Maybe. The honest answer depends on numbers most teams don't compute.

The boundary: OLTP vs OLAP

OLTP (online transaction processing): lots of small reads/writes. Update one row, fetch one user, insert one order. Postgres is excellent at this. So is MySQL.

OLAP (online analytical processing): few large queries. Aggregate a million rows, group by dimensions, compute time-series rollups. Postgres can do this, but it's not what it's optimized for.

For small data, the boundary doesn't matter — Postgres handles both. The question is: where's the cliff?

The Postgres analytics cliff

Postgres analytics is fine until:

Data volume: ~100M rows in your largest analytics table
Query patterns: dashboards re-aggregating from raw data on every load
Concurrency: multiple expensive queries running simultaneously
Latency requirement: sub-second response time for interactive dashboards

You can extend the runway by:

Materialized views for expensive aggregations
BRIN indexes on time-series columns
Table partitioning by date
Read replica dedicated to analytics

These can buy you 1-2 orders of magnitude. If you're at 100M rows and your dashboards take 30s, materialized views can get you to 1B rows / 1s queries.

If those tactics aren't enough, you've hit the cliff.

What ClickHouse / Snowflake actually do differently

These are columnar databases (or column-oriented DWHs). The technical differences:

Columnar storage: queries that touch 3 columns of a 50-column table only read 6% of the data
Vectorized execution: SIMD-style batch processing
Pre-aggregated materialized views baked into the engine
Compression of 5-50x typical, since columns of one type compress well

A query that's 30s on Postgres might be 500ms on ClickHouse on the same hardware.

The trade-offs:

No transactions (or very limited)
Slow point reads (fetching one row by ID is much slower)
Inserts are batched, not real-time
Different SQL dialect with quirks
More moving parts to operate

The decision framework

Step 1: Is your problem actually slow?

Run EXPLAIN ANALYZE on the slowest dashboard queries. Look for:

Sequential scans on large tables → missing indexes
Sort operations using disk → not enough work_mem
Hash joins blowing up → bad query plan, often fixable
High actual time but low rows read → CPU-bound aggregation

Half the time, the dashboards aren't slow because of Postgres. They're slow because of bad queries. Fix those first.

Step 2: Have you tried Postgres extensions?

pg_stat_statements to find which queries are killing you
citus for sharded Postgres (free for self-hosted)
timescaledb for time-series (massive speedup for that workload)
pg_duckdb for embedded analytical queries on Postgres data

Citus and TimescaleDB can extend Postgres analytics to 10B+ rows. If you haven't tried them and you're considering ClickHouse, you're skipping a step.

Step 3: Compute the actual cost.

What does ClickHouse really cost?

Self-hosted: at least 3 nodes, ~$300-1000/month base
ClickHouse Cloud / Altinity: ~$0.30-1/GB-month for storage + compute
Snowflake / BigQuery: charges per query — can be cheap or absurd depending on workload

Plus engineering time:

ETL pipeline from Postgres → analytical store (Debezium, Fivetran, custom)
Maintaining schema drift between systems
Re-tooling dashboards/BI to point at the new store
Operational overhead (especially self-hosted)

Realistic first-year all-in cost: $50k-150k. If your slow dashboards are wasting $20k of engineer time per year, the ROI math is bad.

When to definitely move

Clear signals to migrate:

Postgres queries running into resource limits (CPU pinned, RAM exhausted) and tactical fixes don't help
Customer-facing analytics with sub-second SLA, multi-tenant, growing fast
Multi-billion row tables with full table scans
You're running a separate read replica purely for analytics and it's still slow

These are real reasons. The dashboard team complaining isn't (yet).

When not to move

You haven't tried indexes / partitioning / materialized views
Slow queries are concentrated in a few specific dashboards (just rewrite those)
Your data is under 100M rows total
You're a 5-person engineering team — operational burden of two databases is not worth it

For small teams: stay on Postgres until you can't.

The pragmatic in-between

Two patterns that delay the migration:

1. Read replica + materialized views.

Dedicated Postgres replica for analytics workload
Materialized views refreshed nightly or hourly
Dashboards query the views, not raw data
Costs: extra Postgres instance, ~$50-200/month

This buys you to ~1B rows.

2. DuckDB sidecar.

DuckDB reads Parquet exports of Postgres data
Lambda or scheduled job exports nightly
BI tool queries DuckDB instead of Postgres
Costs: nearly free, just compute time for export

This works well for nightly/non-real-time analytics on data that's already in S3 or similar.

What ETL looks like in practice

If you do migrate, the data pipeline is the actual project. Options:

Debezium → Kafka → ClickHouse — change data capture, near-real-time. Operationally heavy.

Fivetran / Airbyte — managed connectors. $0.50-2 per million rows synced. Easy to set up, expensive at scale.

Custom batch jobs — pg_dump nightly + COPY into target. Cheap, simple, but 24-hour staleness.

Debezium → Estuary / Snowpipe — cloud-managed CDC. Sweet spot for many teams.

The initial migration is 2-4 engineering months. Plan it like a project, not a side task.

The takeaway

The "Postgres can't do analytics" claim is half-true. Postgres can do analytics up to ~100M-1B rows with modern extensions. Moving to ClickHouse / Snowflake is a real win for billion-row tables and sub-second latency requirements — and a $100k mistake for everyone else. Compute your actual numbers before committing.