How Storage Layout Shapes Query Performance

PostgreSQL and Citus support two fundamentally different ways of laying out table data on disk: row-oriented storage and column-oriented storage. Choosing between them is not a matter of one being objectively better—it depends entirely on what your queries actually need to do.

The distinction is easiest to understand through a concrete example. Imagine a table where a query selects only id, first_name, and last_name. With row storage, PostgreSQL reads data from disk in fixed 8KB blocks. Each block contains complete rows, plus visibility information and other metadata. That design is ideal for OLTP workloads—bookkeeping, transactional systems, or address lookups—where you typically retrieve the full row and want to pull all its fields from disk in one efficient read.

Analytical workloads tell a different story. If you need the total sales over 20 years and that table has 20 columns, you genuinely only care about one of them. Reading all 20 columns just to discard 19 is pure waste: excessive I/O, plus the overhead of locating the relevant fields inside each row. For large aggregations over billions of rows, a column store is dramatically more efficient because it reads only the columns the query references.

The reverse trade-off is equally clear. In a pure column store, reconstructing a single row means reading and rejoining values from many separate column files—an operation that is far more cumbersome than a single row-store read. Neither format is universally superior; your workload determines the right choice.

Setting Up a Practical Comparison

You can directly compare the two storage formats in Citus by creating two identical tables, differing only in their storage method. The columnar version uses the USING columnar clause in CREATE TABLE, and then both tables are distributed normally across the cluster.

With the tables in place, loading a timeseries dataset of 157 million rows—equivalent to one row per second for five years, or 86400 * 365 * 5 = 157,680,000—reveals an interesting fact: load time is nearly identical for both formats. The reason is that most of the CPU effort goes into generating the data itself, not writing it. On a Mac Mini (M1), the test produced roughly 1.5 million rows per second, so ingestion is not the bottleneck when weighing row store versus column store.

Size: The First Major Difference

Checking Citus’s internal system tables immediately shows a significant disparity in storage footprint. The columnar table is roughly five times smaller than its row-store counterpart. While disk hardware has grown bigger, cheaper, and faster over time, those gains are often offset by cloud storage pricing, virtualization overhead, and network-attached storage. A more compact representation of the same data is therefore meaningful for both cost and performance reasons.

The smaller footprint translates directly into faster scans. A simple counting query over the 157 million rows runs about three times faster on the columnar table than on the row-store table.

Aggregations: Where Columnar Pulls Ahead

That performance advantage extends beyond basic counts. Queries with grouping show major gains too, though the margin narrows compared to a plain count.

That narrowing is instructive: as queries become more CPU-intensive, the relative impact of storage size diminishes. I/O time drops as a share of total query time, so the storage format matters less when the CPU is the dominant cost. The pattern confirms the general principle—the more work the CPU does per byte read, the less important the I/O savings from columnar storage becomes.

Know the Limits Before You Commit

Columnar storage in Citus is not a drop-in replacement for every scenario. Some SQL operations are still restricted. At the time of writing, certain operations only work on local storage and are difficult to replicate in a distributed setting. Expecting everything to keep working after adding more systems can backfire: features will inevitably be lost along the way. Design decisions need to account for these constraints upfront, rather than discovering them after you’ve invested in a particular storage layout.

Key Takeaways

  • Row store fits OLTP patterns like full-row lookups and point queries.
  • Column store fits analytics and BI where queries reference a small subset of columns over large datasets.
  • Compression and compact layout in Citus’s columnar storage shrank the test table by about five times.
  • Columnar scans ran up to three times faster in simple counting queries, with smaller gains on more CPU-heavy aggregations.
  • Not every SQL feature is supported with columnar storage in Citus, particularly in distributed setups.