Why Analytics Databases Use Columnar Storage, Compression, and Vectorized Query Execution

Data Systems

One of the easiest mistakes in data architecture is assuming that a database optimized for product writes should also be the one answering analytical questions.

Usually it should not.

The query that renders an account page and the query that scans a year of purchase history are doing fundamentally different work. One wants a few records quickly. The other wants to aggregate huge slices of history efficiently.

That is why analytical databases look so different under the hood.

This article explains the core ideas behind that difference: column-oriented storage, compression, sort order, batched write paths, vectorized and compiled query execution, and materialized aggregates. These are the techniques that make warehouses and analytical engines behave nothing like a typical OLTP database.

For frontend and JavaScript engineers, the payoff is practical. Once you understand why analytics storage is shaped this way, dashboard latency, metrics freshness, warehouse cost, and product-event modeling all become easier to reason about.

OLTP and Analytics Have Opposite Priorities

Transaction processing and analytics are often discussed as if they are minor variations on the same database problem.

They are not.

An OLTP system is usually optimized for:

  • lots of small requests,
  • point reads and small updates,
  • strict correctness on individual transactions,
  • low-latency responses for user-facing actions.

An analytical system is usually optimized for:

  • scanning many rows at once,
  • computing aggregates across long time ranges,
  • grouping and filtering across dimensions,
  • trading some freshness for efficient heavy reads.

That difference changes everything about the storage design.

If the warehouse is constantly asked questions like these:

  • total revenue by day,
  • conversion rate by campaign,
  • purchases by region and product category,
  • retention by signup month,

then the engine should be optimized for large scans and aggregations, not tiny update transactions.

Row-Oriented Storage Wastes Work for Analytical Queries

Traditional OLTP storage is usually row-oriented. All values from one row sit next to each other.

That is great when the application needs the whole record.

If a checkout page needs order ID, status, customer, total, shipping address, payment state, and line items, reading one row-like or document-like bundle is efficient.

Analytical queries are different. They often care about only a few columns across a huge number of rows.

Suppose the business asks:

SELECT
  dim_date.weekday,
  dim_product.category,
  SUM(fact_sales.quantity) AS quantity_sold
FROM fact_sales
JOIN dim_date ON fact_sales.date_key = dim_date.date_key
JOIN dim_product ON fact_sales.product_sk = dim_product.product_sk
WHERE
  dim_date.year = 2024
  AND dim_product.category IN ('Fresh fruit', 'Candy')
GROUP BY
  dim_date.weekday,
  dim_product.category

That query might need only a handful of columns from a fact table that contains dozens or hundreds. In a row-oriented store, the engine still ends up reading lots of irrelevant fields just to reach the few that matter.

That is wasted I/O, wasted memory bandwidth, and wasted CPU parsing.

Columnar Storage Reads Only the Data the Query Needs

Column-oriented storage flips the layout.

Instead of storing a row as one contiguous record, it stores each column separately. The values for date_key are grouped together, the values for product_sk are grouped together, the values for quantity are grouped together, and so on.

That gives the query engine a huge advantage.

If the query only needs three columns, it can read only those three columns. It does not have to drag the rest of the row through the system.

This is why columnar storage dominates analytical systems such as warehouses, embedded analytical engines, and many modern query engines operating on object storage formats.

It matches the real workload.

The interesting subtlety is that practical engines do not store one infinite column for the whole table. They typically break data into blocks or row groups and store the columns for each block separately. That lets the engine skip some blocks entirely when filters rule them out.

Compression Works Better When Similar Values Sit Together

Columnar storage is not only about reading fewer fields. It also compresses better.

When values from the same column are stored together, they often show patterns such as:

  • repeated categories,
  • sorted timestamps,
  • many nulls,
  • long runs of similar values,
  • low-cardinality codes.

Those patterns are much easier to compress than a row containing many unrelated fields with different distributions.

Compression matters twice.

First, it reduces disk and network I/O. Second, it can reduce memory traffic during query execution, which is often just as important for performance on modern hardware.

This is a big reason analytical engines can scan so much data efficiently: they are not actually dragging the raw uncompressed representation through every stage.

Bitmap Encoding and Run-Length Encoding Fit Warehouse Workloads Well

One technique that works particularly well for low-cardinality columns is bitmap encoding.

If a column has a limited set of distinct values, the engine can represent each value as a bitmap over the rows.

For example, if product_sk takes only a modest number of repeating values within a block, the engine can create a bitmap per value and answer predicates using bitwise operations.

That is useful because analytical filters often look like:

  • category is one of these three values,
  • store is this specific location,
  • weekday is Saturday or Sunday.

Bitmaps can answer those predicates extremely quickly with AND, OR, and NOT operations.

When the bitmaps themselves contain long runs of zeros or ones, run-length encoding compresses them further. This is one reason sorted columnar data can become surprisingly compact.

The design lesson is important: storage format and execution strategy reinforce each other. Compression is not just about saving bytes. It shapes how the engine evaluates filters.

Sort Order Does More Than Help Range Queries

Sorted data matters in warehouses too, but for a somewhat different reason than in B-trees or SSTables.

If a fact table is sorted by a commonly filtered dimension such as date_key, then rows for adjacent dates sit near each other. That gives the engine two big benefits:

  • it can skip irrelevant blocks more easily,
  • repeated values create longer runs that improve compression.

Additional sort keys can cluster related rows further. For example, sorting first by date and then by product groups sales for the same day and product together, which can help some filters and aggregations.

The tradeoff is that only the early sort keys get the strongest locality and compression effect. As you go further down the sort order, the data becomes more mixed.

So sort order is not a cosmetic choice. It is an encoding decision shaped by the most important query patterns.

Cloud Warehouses Split the Problem into Layers

Traditional databases often bundled storage, metadata, and query execution tightly together. Modern analytical systems increasingly separate them.

In cloud-native warehouse designs, it is common to see distinct layers for:

  • the query engine, which parses SQL, optimizes plans, and executes work,
  • the storage format, such as Parquet- or ORC-like columnar files,
  • the table format, which tracks which data files belong to a table and supports inserts or deletes,
  • the catalog, which stores table definitions and metadata.

That separation matters because analytical workloads scale differently from OLTP workloads.

Storage can live in object stores. Compute can scale independently. Metadata can be shared across engines. The system becomes more composable because the workload is scan-heavy and batch-oriented.

This is one reason cloud warehouses feel so different operationally from a transactional database. They are often not one monolithic engine. They are a stack of cooperating layers.

Writes Tend to Arrive in Batches, Not as Arbitrary Row Updates

Columnar storage is great for reads. It is not naturally friendly to single-row updates in the middle of a large sorted dataset.

That is why analytical systems often write in batches.

Instead of rewriting compressed columns for one random row at a time, many systems:

  1. accept new data into a write-friendly structure,
  2. accumulate it in batches,
  3. sort and encode it into columnar files,
  4. merge or compact old and new files later.

This should sound familiar. It is conceptually close to the log-structured pattern from OLTP engines, just applied to analytical data.

The result is that analytical systems can absorb large imports efficiently while still serving fast reads from organized columnar files.

The downside is freshness complexity. Queries may need to examine both the recent write buffer and the older optimized files, then combine the results.

That is one reason analytics dashboards can feel slightly behind real time unless the system is explicitly designed for hybrid serving.

CPU Time Matters Too, Not Just Disk I/O

Once the engine is reading only the necessary columns, another bottleneck appears: CPU efficiency.

Analytical queries can still spend a lot of time evaluating predicates, performing joins, applying expressions, and aggregating results across millions of rows.

Two important strategies help here.

The first is query compilation. Instead of interpreting a generic execution plan row by row, the engine can generate specialized code for the specific query and run it directly on the relevant column data.

The second is vectorized execution. Instead of processing one row at a time, the engine processes a batch of values from a column at once. That improves cache locality, reduces per-row overhead, and often maps well onto SIMD instructions in modern CPUs.

This is why analytical execution plans often feel different from OLTP execution plans. The engine is not trying to serve one small lookup with minimal latency. It is trying to make long sequential scans and column operations extremely efficient.

Materialized Views and Data Cubes Trade Write Cost for Read Speed

Even with good columnar storage, some repeated analytical queries are worth precomputing.

A materialized view stores the result of a query instead of recomputing it from raw data each time.

A data cube goes further by precomputing aggregates across several dimensions, such as date and product, so that repeated rollups become nearly instant.

This is powerful because many analytical questions are repetitive:

  • daily revenue by region,
  • order counts by plan,
  • sales by weekday and category,
  • active users by acquisition source.

If those queries are central to the product or the business, precomputing parts of them can be worth the extra write and maintenance cost.

But the tradeoff is important. Precomputed structures are less flexible than raw data. If someone asks a new question that the cube was not designed for, the system may still need to scan the underlying fact data.

That is why mature warehouses often keep large amounts of raw detail and use materialized aggregates as targeted accelerators rather than total replacements.

What Product and Frontend Engineers Should Take from This

When a dashboard loads quickly, it is often because several storage decisions were made long before the chart code existed.

The query may be fast because:

  • the data is stored by column instead of by row,
  • only a few required columns are read,
  • the files are strongly compressed,
  • the table is sorted to make skipping cheap,
  • execution is vectorized,
  • a materialized aggregate already exists.

This matters for product work.

If stakeholders ask for ad hoc slicing across many dimensions, the underlying event schema and warehouse layout need to support it. If a near-real-time dashboard is required, the write path and compaction model matter. If a product team adds dozens of new metrics fields casually, they are also affecting scan width and storage behavior.

Analytics UX is downstream of storage design.

Conclusion

Analytical databases are optimized for a different job than transactional databases, so they use different internal structures.

Columnar storage reads only the needed fields. Compression turns repeated column values into fewer bytes and less memory traffic. Sort order improves pruning and compression. Batched write paths turn awkward row updates into efficient bulk encoding. Query compilation and vectorized execution reduce CPU overhead during large scans. Materialized views and cubes precompute expensive repeated aggregates.

Once you see that, the warehouse stops looking like a slower copy of the application database. It starts looking like what it really is: a specialized read engine for answering big questions over large histories.