Why Analytical Queries Crawl on Postgres

Part 1 of a three-part series on what's actually under your dashboard. Next: You Might Not Need a New Database, then Why AI Analytics Needs Fast Scans.

The same query is 1 millisecond on one engine and 37 seconds on another. That is not a missing index. It's the difference between storing data in rows and storing it in columns — and it's the single most important thing to understand before you put a dashboard in front of a large table.

Postgres is a superb transactional database. It's the right default for almost every application backend, and nothing here is an argument against it for that job. But the moment you point a dashboard at it — COUNTs, GROUP BYs, and distinct counts over tens of millions of rows — it falls off a cliff that has nothing to do with configuration.

To see exactly where and why, we built a workload that looks like a real product. Arcstream is an open-source CDP (Customer Data Platform): it ingests raw behavioural events through Kafka, runs identity resolution and sessionization in Flink, and serves a live dashboard of 15 analytical queries — time-series charts, dimensional breakdowns, distinct-user counts, tenant rollups. We fed the same 134M-event dataset (90 simulated days, 5 tenants) to three engines — Apache Pinot, ClickHouse, and PostgreSQL — through the same Kafka topics on the same box, and ran the dashboard's exact SQL against each.

The evidence: query latency ​

Warm cache, average of 6 runs, all timings in milliseconds. Each engine uses its best available optimisation per query: Pinot reads from star-tree pre-aggregated nodes, ClickHouse from projections (falling back to a columnar scan), Postgres scans heap pages with btree filtering and parallel workers.

Events table · 134M rows

The 7-day hourly query (49 ms on Pinot) filters on event_hour, which isn't a star-tree dimension, so it falls through to a columnar scan — a preview of a theme that returns in Part 3.

Now the smaller tables. At 2–2.5M rows, Postgres is fine:

Tens to low hundreds of milliseconds. The wheels only come off on the 134M-row events table. That's the tell. The problem isn't the engine's competence; it's what happens when the working set no longer fits the assumptions of row storage. The rest of this post is that mechanism, layer by layer.

How data is stored ​

The performance difference traces back to one design choice: how rows and columns are laid out on disk. Both layouts are correct engineering — for different workloads.

Postgres: the row-oriented heap ​

Postgres stores each row as a contiguous tuple on a heap page, all columns packed together. Insert an event and the whole row lands in one place. Reading a full row by primary key is a single page fetch — exactly what a transactional workload wants.

The tradeoff shows on analytical queries. SELECT device_type, COUNT(*) GROUP BY device_type needs exactly one column, but Postgres reads every page and pulls all 17 columns from each tuple to extract the one it needs. For 134M rows, that's ~39 GB of heap I/O to answer a question about a 3-value column.

ClickHouse / Pinot: columnar storage ​

Columnar engines store each column as an independent file, sorted by the table's primary key. Low-cardinality columns — like device_type with 3 distinct values — compress to a fraction of their size because identical values sit adjacent. The same GROUP BY device_type reads only the device_type file; the other 16 columns are never touched. For 134M rows, that one column compresses to ~50 MB.

Column pruning: the 780× factor ​

A 17-column table where the query touches 1 column means ~6% of the data is read. This is the single largest factor in the whole benchmark. Postgres cannot do it, because its storage format interleaves all columns in every heap page — there is no way to read one column without reading the tuple it lives in.

Compression ​

Compression works on any storage format, but columnar layout makes it dramatically more effective. Compressing a sequence of 134M same-type values finds patterns that don't exist when those values are scattered across mixed-type row tuples (UUID + string + timestamp + …).

Dictionary encoding ​

The device_type column has 134M values but only 3 distinct strings: "desktop", "mobile", "tablet". A columnar engine builds a dictionary mapping each to an integer (0, 1, 2) and stores the column as 134M integers instead of 134M variable-length strings. At 2 bits per value, that's ~32 MB for the raw encoded column. In Postgres, each of those 134M strings is stored inline in the heap tuple, repeated in full.

Compression effectiveness varies enormously by column cardinality. Per-column storage from the 134M-row ClickHouse events table:

The columns dashboards actually GROUP BY — tenant, event type, device, country — compress 220×. But event_id, a UUID with 134M unique values, is 4.6 GB at 1:1 and accounts for 51% of total storage. The overall 3.85× table-level ratio blends these extremes, and that blend matters for the next layer.

Cache efficiency ​

With the working set compressed, more of it fits in memory. ClickHouse caches its 9 GB events dataset comfortably in 24 GB of RAM; Postgres can cache maybe 60% of the 39 GB heap. Higher hit rates mean fewer disk reads and steadier latency under concurrent load.

In practice the effective ratio is even better than the table numbers suggest. Dashboard queries predominantly hit the low-cardinality columns that compress 220×. The high-cardinality columns that dominate total storage (event_id, canonical_id) are rarely read. The columns a typical GROUP BY touches fit in a few hundred megabytes — not the 9 GB total.

Vectorised execution ​

Column pruning and compression reduce how much data is read. Vectorised execution changes how that data is processed once it reaches the CPU.

Postgres processes one row at a time. For each of 134M rows it calls a function to evaluate the filter, another to update the aggregate, with type checking and branching at every step. A vectorised engine (ClickHouse, DuckDB) processes a batch of 1,024–8,192 values through each operation as a single step, on contiguous arrays of same-type values — exactly what columnar storage hands it.

Three mechanisms make it faster: SIMD instructions (AVX2/AVX-512) processing 4–16 values per CPU instruction; cache-line utilisation, where contiguous column arrays fill the full 64-byte line instead of wasting it on unrelated columns; and branchless loops, where filters compute a bitmask for the whole batch before applying it, eliminating per-row branch misprediction. ClickHouse is built around this — every operator processes column vectors. Postgres and TimescaleDB use row-at-a-time execution even on compressed columnar chunks: the data is decompressed back to rows before processing.

The memory boundary ​

Postgres analytical queries are fast when the table fits in the buffer cache. Our sessions table (2.5M rows, 475 MB) returns aggregations in 33–388 ms. The events table (134M rows, 39 GB) doesn't fit, and query times jump to 12–73 seconds. That's the whole story of the latency tables above, restated as a single boundary.

Columnar storage with compression pushes that boundary further out. The same 134M events occupy 9 GB in ClickHouse instead of 39 GB, so they fit in memory on hardware where the Postgres representation doesn't. Column pruning pushes it further still: a query touching 2 of 17 columns needs ~1 GB resident. And when the data outgrows one node, sharding distributes segments across machines, each with its own memory — the point where queries go from fast to slow keeps moving out.

None of this is a Postgres flaw. Row storage is the correct choice for transactional work, and every layer here — column files, dictionary encoding, vectorised execution — is a bet against the very thing that makes Postgres good at OLTP. The question is never "is Postgres slow" but "does this workload match row storage." For a dashboard over 100M+ rows, it doesn't.

Part 1 established why columnar engines win at analytics. But adopting one means operating a second database. Part 2 asks whether you can get most of the win without leaving Postgres at all: You Might Not Need a New Database.