Columnar Storage: When It Helps and When It Does Not

Columnar storage is the default recommendation for analytics workloads for good reason. The performance characteristics match the query patterns that analytics work produces. But "use Parquet" is not a strategy; it is the start of a design decision that has meaningful tradeoffs depending on how you write, compact, and read your data.

This article covers the mechanics of why columnar storage helps for analytics reads, the specific failure modes where it hurts instead, and the compaction and partitioning decisions that determine whether you get the performance you expect.

Why columnar storage helps for analytics queries

A row-oriented storage format like a traditional PostgreSQL heap stores each row as a contiguous unit on disk. A query that reads ten columns from a 100-column table must read and discard 90 percent of the bytes it touches. For transactional workloads where you commonly retrieve all columns of a small number of rows (give me the complete order record for order ID 12345), this is fine. The I/O profile matches the access pattern.

Analytics queries look different. A revenue aggregation query might read two or three columns from a 50-column fact table across millions of rows. In a row-oriented store, you read all 50 columns of all those rows and discard 47 columns worth of bytes. In a columnar store like Parquet, the query engine reads only the two or three column files that the query references. The rest are not touched.

The compression benefit compounds this. Values within a single column tend to be more similar to each other than values within a single row. The column of country codes in an orders table is mostly a handful of values repeated millions of times. Run-length encoding or dictionary encoding compresses that column to a fraction of its uncompressed size. Compression ratios for well-encoded columnar data can reach 5x to 15x over raw data, depending on column cardinality and data distribution. This means your query reads fewer bytes from storage, which is usually the bottleneck in analytical workloads.

Where columnar storage hurts: write amplification and small files

The read performance characteristics of columnar storage come at a write cost. Writing a single row to a Parquet file requires touching every column's data structure. For a write-heavy workload where individual records arrive one at a time, this overhead makes columnar storage the wrong choice at the write layer.

More commonly, the problem is small files. Parquet and ORC are optimized for large sequential reads. A typical Parquet file should contain millions of rows to amortize the per-file metadata overhead. When data arrives in small batches, each batch written as its own file, you end up with thousands of tiny Parquet files. Queries against this dataset spend most of their time opening files and reading metadata rather than actually scanning data. A table with 100,000 Parquet files of 1,000 rows each will often query more slowly than the same data stored as 100 row-oriented CSV files.

This is the small files problem, and it is endemic in streaming and near-real-time ingestion architectures. Data arrives as frequent small batches. Each batch gets committed as a file. Over time, you accumulate millions of small files. Query performance degrades from the metadata overhead alone.

The solution is compaction. Periodically, a background job reads all the small files in a partition and rewrites them as a smaller number of larger files. Delta Lake, Apache Iceberg, and Apache Hudi all provide compaction operations that handle this. Running compaction frequently enough to keep average file sizes in the reasonable range (128 MB to 1 GB for Parquet, depending on your engine) is an operational responsibility that many teams underestimate when they first adopt columnar storage.

Partitioning strategy matters more than format choice

For most analytics workloads, the partitioning strategy has a larger impact on query performance than the choice between Parquet and ORC, or between ZSTD and Snappy compression.

Partitioning by date is the most common strategy and the right default for time-series data. When your queries filter on a date range, the query engine can skip entire partitions rather than scanning the full dataset. A query for last month's revenue on a year of data touches roughly 1/12 of the partitions. Without partitioning, it scans everything.

The mistake teams make is over-partitioning. Partitioning by date and by customer ID and by product category produces a partition space that can have hundreds of thousands of distinct values. Each partition is a small directory on object storage. Listing all the relevant partitions before scanning them can take longer than the scan itself.

The right partitioning granularity depends on your query patterns. If 80 percent of your queries filter on a single date and a single customer ID, partitioning by date-month and customer ID bucket (where you hash the customer ID into 20 buckets) can make those queries fast. If queries span arbitrary date ranges and customer sets, finer partitioning provides less benefit and more overhead.

Column ordering within row groups and statistics

Parquet files are divided into row groups, typically 128 MB or 256 MB each. Each row group stores per-column min/max statistics that query engines use for predicate pushdown. When your query has a filter like WHERE amount > 10000, the engine checks the min/max statistics for the amount column in each row group before deciding whether to scan that row group. Row groups where the max amount is below 10,000 are skipped entirely.

The effectiveness of this row group skipping depends on how well the data within each row group is sorted by the filter column. If your data is sorted by ingestion timestamp and your filter is on amount, the values of amount within each row group will span the full range of the dataset, and no row groups will be skipped. If you sort your data by amount before writing, row groups will cluster high-amount records together, and the predicate skipping will be highly effective.

Sorting before write has its own cost: the sort operation over large datasets is expensive. The tradeoff depends on how frequently the column is used as a filter. For high-cardinality filter columns that queries frequently predicate on, sorted writes can produce query speedups that justify the write-time sort cost. For columns that are rarely filtered, sorting adds cost with no benefit.

The cases where columnar storage is not the answer

We want to be direct about the scenarios where columnar storage is the wrong choice.

Row-level updates and deletes are expensive in columnar formats. Updating a single row in a Parquet file requires rewriting the entire row group containing that row. Database formats that store data row-by-row, such as PostgreSQL or MySQL, handle updates in place. If your workload involves frequent updates to individual records, columnar storage adds significant overhead.

Point lookups (give me the single record where order_id = 'XYZ123') are slower in columnar formats than in indexed row stores. Reading a single row from a Parquet file requires reading at least one full row group, which may be hundreds of megabytes. An indexed heap table lookup returns the row in a few milliseconds. If point lookups are a significant fraction of your query mix, columnar storage for that table is the wrong choice regardless of query volume.

Real-time queries with latency requirements under a second on recently-written data face the compaction problem. If your streaming ingestion writes small files every few seconds and your SLA requires query results within 500 milliseconds, you need a storage format designed for real-time access, not a compaction cycle that runs hourly.

The practical guidance is: use columnar storage for your analytical tables where queries read a small number of columns across large row ranges. Use row-oriented storage for your operational tables where point lookups and frequent updates are the primary access pattern. Do not let the default recommendation for "analytics" paper over the specific access patterns your workload actually has.