The moment it clicks

Columnar databases like BigQuery and Redshift are built for analytical questions.

Not "update this one order."

Not "fetch this one user profile."

More like:

select avg(transaction_value)
from orders
where created_at >= date_sub(current_date(), interval 30 day);

That query may touch 50 million orders. But it only really needs a few columns.

Columnar storage wins because analytical queries usually ask for columns, not whole rows.

The row-store trap

Imagine an orders table with 100 columns:

  • order id
  • user id
  • transaction value
  • currency
  • status
  • address
  • device
  • campaign
  • metadata
  • dozens of other fields

Now ask a simple analytics question:

What is the average transaction value across 50 million orders?

A row-oriented database stores each record together. That is great when the application needs one complete row, like "load order 123."

But for analytics, it can be wasteful. The database may read blocks containing every field in the row even though the query only needs transaction_value.

scan shapesame question, different bytes read
row store
column store

This is the painful part: your question is narrow, but the row layout makes the read wide.

What columnar storage changes

Columnar storage flips the layout.

Instead of storing:

row1: order_id, user_id, transaction_value, status, ...
row2: order_id, user_id, transaction_value, status, ...

It stores values by column:

order_id:           ...
user_id:            ...
transaction_value:  ...
status:             ...

Now the same average query can scan the transaction_value column directly. It does not need to pull address, device, campaign, or JSON metadata through memory just to ignore it.

Amazon Redshift's docs give the clean version of this: if a table has 100 columns and a query uses five, columnar storage only needs to read about five percent of the table data. A row-wise database reads blocks that also contain the 95 unneeded columns.

Why compression gets so good

The other trick is compression.

Columns are full of similar values. A country column has repeated country codes. A status column has a small vocabulary. A timestamp column often moves in predictable deltas. A numeric id column may mostly fit into a smaller range.

That is exactly the kind of data compression likes.

compression shapesame type, repeated patterns

In a row store, a block mixes many types of values together. Strings, timestamps, decimals, booleans, and JSON fragments sit next to each other.

In a column store, each block tends to hold one kind of value. That lets the engine choose encodings that match the column: dictionary encoding for repeated strings, run-length encoding for repeated values, delta-like schemes for ordered numbers or timestamps, and other type-aware encodings.

Columnar compression is powerful because the storage layout gives the compressor predictable input.

The BigQuery screenshot lesson

That is why a BigQuery table can show something like 1.53 TB of stored data and still feel surprisingly practical to query.

The exact compression ratio depends on the data, schema, cardinality, partitioning, clustering, and encoding choices. But the principle is consistent: analytical warehouses are built to avoid reading and storing unnecessary bytes.

BigQuery's storage docs say table data is stored in columnar format, and BigQuery uses an encoding format optimized for analytical workloads.

Redshift makes the same idea explicit: columnar blocks reduce I/O for analytical scans, and column-specific compression reduces disk and memory pressure.

When columnar databases are the wrong tool

This does not mean columnar databases replace row databases.

Row stores are still excellent when the workload is transactional:

  • insert one order
  • update one user
  • fetch one account
  • lock one inventory row
  • commit a payment state transition

Those operations usually care about complete records and low-latency point reads/writes.

Columnar systems are optimized for different questions:

  • aggregate over millions of rows
  • scan a few columns from huge tables
  • group by dimensions
  • filter partitions
  • compress repeated values
  • serve dashboards and reports

The mistake is using a row database for warehouse-shaped questions and then being surprised when it reads too much.

The practical takeaway

If your product asks questions like:

average transaction value by day
conversion rate by campaign
revenue by region
failed payments by bank
active users by cohort

you are not asking for rows. You are asking for columns.

And once the data gets large enough, that difference becomes the whole system.

A columnar database is not magically faster. It is faster because its physical layout matches the shape of analytical questions.

Sources