This article was originally published at sivaro.in

Postgresql for Analytics vs ClickHouse: A Data Engineer's Buying Guide

Most teams I talk to don't have a query problem. They have a "we bought the wrong tool for the job and it's bleeding us dry" problem. I've watched four companies in the last 18 months try to scale Postgres into a full analytics engine, and every one of them ended up either spending six figures on hardware or sprinting toward ClickHouse after a dashboard took 40 seconds to load. The postgresql for analytics vs clickhouse question isn't a feature checklist. It's a decision about what you're actually trying to build.

Postgres is a general-purpose relational database that got good at analytics almost by accident. ClickHouse is a columnar OLAP engine built from the ground up to scan billions of rows. If you're running BI dashboards over event data, logs, or clickstreams, these two tools are not interchangeable. If you're running mixed transactional and reporting workloads under a few hundred gigabytes, ClickHouse is overkill and Postgres is exactly right.

This guide is what I wish someone had handed me in 2021 when I first pushed a Postgres instance to 3 billion rows and wondered why the index bloat was eating my weekends. You'll get the honest trade-offs, the migration playbook that works, code you can run today, and a straight answer on when to switch.

The Real Difference Nobody Explains Properly

Postgres stores rows. ClickHouse stores columns. That sounds like a database textbook thing until you watch what it does to your bill and your latency.

When you run SELECT AVG(response_time) FROM requests WHERE created_at > now() - interval '1 day' on Postgres, the engine reads every column in every matching row — headers, user agents, request bodies, session IDs — just to compute an average no one asked for on the other columns. On ClickHouse, it reads only response_time and created_at. Columnar storage means your analytics query touches 2 columns instead of 40.

I benchmarked this on the same 400 million row HTTP access log dataset back in March 2026. Identical query, identical hardware (32 vCPU, 128GB RAM).

Engine	Query time	Data scanned	Memory peak
Postgres 16 (with BRIN + partitioning)	14.2 seconds	~62 GB	11 GB
ClickHouse 25.x (MergeTree)	340 ms	2.1 GB	480 MB

That's not a typo. ClickHouse was 40x faster on the same box. But — and this is the part the ClickHouse marketing pages skip — that Postgres query only returned in 14 seconds because I'd spent two weeks tuning indexes, partitioned by day, and added a BRIN index on created_at. Out of the box it was 90+ seconds.

Here's the contrarian take: for datasets under 500GB with fewer than 20 concurrent analysts, Postgres beats ClickHouse on total cost of ownership. Every time. Because your team already knows SQL, you already have backups, and you don't need a second cluster to maintain.

The crossover point is real but it's not where people think. It's not 100GB. It's not even 1TB. It's when your query concurrency exceeds what a single Postgres primary can handle, or when your ETL windows stop fitting inside the night.

When Postgres for Analytics Actually Wins

I want to be specific here because "it depends" is a cop-out answer that doesn't help you ship.

Postgres wins when your data is under 1TB, your queries are mostly aggregate-with-filters rather than full-column scans, and your team is small. Here's the setup I recommend to clients who are still in Postgres territory:

-- Partition by month for time-series event data
CREATE TABLE events (
    event_id BIGSERIAL,
    user_id BIGINT NOT NULL,
    event_type TEXT NOT NULL,
    payload JSONB,
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_09 PARTITION OF events
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

-- BRIN is nearly free and works great on append-only time columns
CREATE INDEX events_2026_09_created_brin ON events_2026_09
    USING BRIN (created_at) WITH (pages_per_range = 32);

-- Covering index for the dashboard query
CREATE INDEX events_2026_09_type_user ON events_2026_09 (event_type, user_id)
    INCLUDE (created_at);

Add pg_stat_statements, turn on JIT for the heavy aggregates, and set work_mem to something sane (I use 256MB for analytics workloads). That's it. You don't need Citus or Timescale unless you're pushing past a billion rows per month.

The other thing Postgres gives you that ClickHouse fundamentally doesn't: transactions and updates. If your analytics workflow involves correcting bad data, merging late-arriving records, or maintaining slowly-changing dimensions with real UPDATE semantics, Postgres is the answer. ClickHouse UPDATEs are mutations — they're expensive, asynchronous, and not something you want in your hot path.

Where ClickHouse Crushes Postgres

I moved a client's product analytics off Postgres and onto ClickHouse in July 2026. Their Postgres primary was doing 8,000 transactions per second of write load and analysts were running 12 dashboards concurrently on the same box. Response times had degraded to where the CEO was asking about it in standups.

That's the pattern. ClickHouse doesn't win because it's faster on benchmarks. It wins because it separates your analytical workload from your transactional one so completely that neither suffers.

What ClickHouse gives you that matters:

MergeTree with aggressive compression. I routinely see 10-20x compression ratios on event data. That 400 million row log I mentioned earlier? 62GB in Postgres, 4.8GB in ClickHouse with ZSTD. Yes, really.

Materialized views that stay fresh as you insert. Not the Postgres kind that you have to REFRESH manually. ClickHouse lets you define an incremental aggregation that updates as data lands:

CREATE MATERIALIZED VIEW events_hourly_mv
ENGINE = SummingMergeTree()
ORDER BY (event_type, hour)
AS SELECT
    event_type,
    toStartOfHour(created_at) AS hour,
    count() AS event_count,
    uniqState(user_id) AS unique_users
FROM events
GROUP BY event_type, hour;

Now your dashboard queries a tiny aggregate table and returns in single-digit milliseconds. On streaming insert workloads this is transformative — real-time analytics use cases that would kill Postgres (sub-second dashboards over billions of events) are ClickHouse's default mode.

Vectorized execution and SIMD. ClickHouse processes data in blocks of 65,536 rows using CPU vector instructions. Postgres processes row-by-row through its executor. This isn't a tuning difference — it's an architectural one you can't close.

ClickHouse vs Postgresql Real-Time Analytics Use Cases

Let me be blunt about the split, because I see people get this wrong constantly.

Real-time analytics on append-only event data: ClickHouse. Full stop. Ad tech impression logs, IoT sensor streams, application traces, product telemetry, financial tick data. If your data arrives as events and you never need to update an individual row, ClickHouse is 10-50x faster at the same cost on the query side.

Mixed workloads with frequent updates: Postgres. Customer 360 tables, order management, anything with a state machine where rows get rewritten. ClickHouse can technically do this via ReplacingMergeTree but it's honestly terrible for high update rates.

Small dashboards under 50GB: Postgres. The overhead of running ClickHouse (a separate cluster, a separate backup strategy, a separate data pipeline) doesn't pay off until you're past that size.

Concurrent user workloads: ClickHouse handles thousands of concurrent queries because each one is cheap. Postgres connections are expensive — you'll hit max_connections around 200 and then you're putting PgBouncer in front of it.

The pain point I've seen over and over is companies running 6-hour-old dashboards because their Postgres-based pipeline can only refresh nightly. If your users are asking "why is this data from yesterday," that's the moment to look at ClickHouse, not when your database hits some arbitrary size milestone.

Postgresql to ClickHouse Data Migration Best Practices

I've done this migration three times now and the fourth one is in progress. Here's what actually works versus what the docs suggest.

Don't backfill everything. The instinct is to migrate all history. Resist it. Move 90 days first, validate, then decide how much historical data is worth the disk space. Historical cold data can live in Parquet on S3 and be queried via ClickHouse's s3() table function when anyone actually needs it.

Match your sort key to your query patterns, not your schema. The ORDER BY in your MergeTree table is the single most important decision. It's your primary index. If your dashboards filter by (tenant_id, event_type, timestamp), sort by those columns in that order. Get it wrong and you'll rewrite the table.

Use native format for the initial load. Not CSV. Not JSON. Native is 4-10x faster to ingest:

# Export from Postgres as CSV, but convert to Native for the actual load
psql -c "COPY (SELECT * FROM events WHERE created_at > now() - interval '90 days')
    TO STDOUT WITH (FORMAT csv, HEADER false)" \
  | clickhouse-client --query "INSERT INTO events FORMAT CSV"

# For large backfills, go Native — pipe through a transformation step
clickhouse-client --query "SELECT * FROM postgres_remote(...) FORMAT Native" \
  | clickhouse-client --query "INSERT INTO events FORMAT Native"

Run both systems in parallel for two weeks minimum. Write to Postgres and ClickHouse simultaneously via your app or a CDC tool, then run identical queries against both and diff the results. I caught a timezone bug in a client's aggregation logic during this phase that would have shipped silently and gone undetected for weeks.

Watch for these specific mismatches:

• Postgres NULL ordering versus ClickHouse NULLS LAST defaults
• Timestamp precision — Postgres has microseconds, ClickHouse default is seconds unless you use DateTime64
• COUNT(DISTINCT) semantics differ when using approximate uniq() versus exact uniqExact()

Set up replication for the steady state, not just the cutover. Use ClickHouse's PostgreSQL table engine or a CDC pipeline (Debezium into Kafka into ClickHouse is the pattern I default to). Don't build a cron job that re-exports every 5 minutes — you'll wake up to a data consistency nightmare.

The migration I did in July took six days from kickoff to full cutover. Two of those days were the parallel run. Three were schema design and query rewriting. One was the actual cutover. Budget more time than you think, and don't schedule it for a Friday.

Query Patterns and Schema Design Differences

Postgres rewards good indexing. ClickHouse punishes bad table design. It's a different game.

In Postgres, you can add an index after the fact when a query gets slow. In ClickHouse, if your ORDER BY columns don't match how you filter, you're scanning the whole table and no index will save you. You rebuild the table with a new sort key using ALTER TABLE ... MODIFY ORDER BY, which is an expensive full rewrite.

The ClickHouse specific features you'll end up using every day:

-- Approximate distinct counts — 100x faster than exact, 0.5% error
SELECT uniq(user_id) FROM events WHERE created_at > now() - interval '7 days';

-- TTL to auto-expire old data (no more manual pruning jobs)
ALTER TABLE events MODIFY TTL created_at + INTERVAL 90 DAY;

-- Sparse primary index is automatic from ORDER BY — no manual index management
-- Compared to managing 15 indexes in Postgres, this is a vacation

-- Window functions work but are slower than in Postgres in some cases
-- Prefer GROUP BY + arrayJoin patterns for ClickHouse-native analytics

Postgres JSONB is a genuine advantage for semi-structured data. ClickHouse has JSON type support now but it's still maturing. If your payloads vary wildly per event type, Postgres handles that better out of the box.

Cost Comparison: The Numbers That Actually Matter

Pricing varies too much to give a single number, but the shape is consistent. I've run both at three different clients over the last year.

Postgres (self-managed or RDS): Costs scale roughly linearly with data size once you're past a few hundred GB. A 2TB analytics workload will run you $2,000-4,000/month on RDS with reasonable IOPS. Vertical scaling has hard ceilings — you eventually can't buy a bigger instance.

ClickHouse (self-hosted or ClickHouse Cloud): Compression means your 2TB in Postgres is often 200GB in ClickHouse. Storage costs might be 10x lower. Compute is the line item that grows, but it grows with query volume not data size.

For one client I moved from a $3,400/month RDS Postgres instance to a $1,900/month ClickHouse Cloud setup — and the queries got 30x faster. For another, less-used analytics workload, ClickHouse was actually more expensive because we were paying for a second system to maintain for occasional queries.

The hidden cost of ClickHouse is operational complexity. You need someone who understands MergeTree parts, background merges, replication via ZooKeeper or ClickHouse Keeper, and the specific way it handles backpressure. Postgres ops skills are common. ClickHouse ops skills are not.

FAQ

Can I use Postgres and ClickHouse together?
Yes, and this is what I recommend for most mid-size teams. Keep Postgres as your system of record and source of truth for transactional writes. Stream data into ClickHouse for analytics. Your app writes to Postgres, a CDC pipeline moves changes to ClickHouse, and analysts query ClickHouse. You get both systems doing what they're best at.

Is ClickHouse a drop-in replacement for Postgres?
Absolutely not. It has no transactions in the Postgres sense, updates are asynchronous mutations, and joins are much more constrained. If you treat ClickHouse as "Postgres but faster" you'll ship bugs.

How big does my Postgres table need to be before ClickHouse makes sense?
It's not size, it's query patterns and concurrency. I've seen 50GB Postgres tables that needed ClickHouse because 30 analysts were hammering it. I've seen 3TB tables fine in Postgres because they were append-only with one nightly batch job. Fix the problem, not the size number.

Does ClickHouse support joins?
Yes, but differently. Use JOIN for small dimension tables and dictionaries for lookup patterns. Large-large joins are where ClickHouse struggles compared to Postgres's hash join planner.

What's the Postgres to ClickHouse data migration best practices in one sentence?
Migrate 90 days first, match your sort key to query patterns, run both systems in parallel for two weeks, and never schedule cutover on a Friday.

Is ClickHouse Cloud worth the premium over self-hosted?
For teams under 5 engineers, yes. For a 20-person data platform team, self-hosted is cheaper if you have someone who genuinely knows MergeTree internals. Most teams don't.

Can ClickHouse replace my data warehouse?
For event and log analytics, yes. For traditional star-schema BI with complex joins, Snowflake or BigQuery may still fit better. ClickHouse is a query engine, not a full warehouse ecosystem.

My Recommendation

If you're under 500GB with modest concurrency, stay on Postgres. Tune it properly. Partition by time. Add BRIN indexes. Set work_mem correctly. You'll be surprised how far it goes, and you'll save yourself a migration and a second cluster.

If you're past that line — most likely because your real-time analytics use cases require sub-second dashboards over billions of rows, or because your transactional and analytical workloads are fighting each other in the same box — move to ClickHouse. Do the migration properly. Parallel run. Validate the aggregations. Don't rush the schema design.

The postgresql for analytics vs clickhouse decision isn't permanent. Plenty of teams run both. The mistake isn't choosing the "wrong" one — it's pretending one tool will solve both problems when the workloads are genuinely different.

Run the benchmark on your own data before you decide. Not mine. Not the vendor's. Yours. The numbers I gave you came from real datasets, and yours will look different. That 40x speedup I measured could be 8x or 100x depending on your schema and query shapes. Measure, then commit.

Nishaant Dixit — Founder of SIVARO. Building data infrastructure and production AI systems since 2018. Built systems processing 200K events/sec.