The column-storage ideas that finally clicked for me

Column-oriented databases initially sounded simple: store values by column instead of by row. The details become more interesting once you ask how the database still knows which values belong together, how bitmap indexes work, and why the CPU cares about storage layout.

Column storage keeps related values together

A row store keeps one complete record together.

Sale 1:
date | product | store | customer | quantity | price

Sale 2:
date | product | store | customer | quantity | price

A column store keeps values from the same attribute together.

date:      [Jan 1, Jan 1, Jan 2, ...]
product:   [fruit, candy, fruit, ...]
quantity:  [2, 1, 4, ...]
price:     [10, 4, 12, ...]

This matters when an analytical query reads a lot of sales but only needs three columns. The engine can skip customer details, addresses, payment metadata, and every other unused field.

Position keeps columns connected

One thing confused me at first: if values live in separate column files, how can the database reconstruct a row?

The answer is position.

Logical row:       1      2      3
product:          fruit  candy  fruit
quantity:           2      1      4
store:             PK-1   PK-2   PK-1

The first value in every column belongs to logical row 1.

product[1]  = fruit
quantity[1] = 2
store[1]    = PK-1

Together: one sale event

All columns must preserve the same row order. Sorting each column independently would destroy that relationship.

Low cardinality means values repeat often

Cardinality means the number of distinct values in a column.

country
1,000,000 rows
about 200 possible values

= low cardinality

email
1,000,000 rows
almost 1,000,000 distinct values

= high cardinality

Columns such as country, order status, subscription plan, product category, and boolean flags often have low cardinality. Emails, user IDs, order IDs, and exact timestamps usually have high cardinality.

This distinction matters because low-cardinality columns work well with bitmap indexes.

A bitmap index records which rows match a value

Suppose a sales table has this data:

Row:       1  2  3  4  5  6
country:   PK US PK UK US PK

The database can create one bitmap for each country.

PK:        1  0  1  0  0  1
US:        0  1  0  0  1  0
UK:        0  0  0  1  0  0

A 1 means that the corresponding row has that value.

WHERE country = 'PK'

The PK bitmap immediately shows that rows 1, 3, and 6 match.

The useful part comes when a query combines filters.

product = fruit:  1  0  1  1  0  1
store = PK-1:     1  1  0  1  0  0
                  -----------------
AND result:       1  0  0  1  0  0

Rows 1 and 4 match both conditions.

The database can use fast bitwise operations instead of inspecting and comparing every row one at a time.

Sparse bitmaps compress well

A bitmap can look wasteful at first. A database may hold billions of rows, so every bitmap has billions of bit positions.

But a bitmap for one particular value often has long runs of zeroes.

0 0 0 0 0 0 0 0 0 0 1 1 1 0 0 0 0

Run-length encoding stores repeated values as a value and its count.

10 zeroes
3 ones
4 zeroes

This makes sparse bitmap indexes compact. It is especially useful for values that occur in only a small portion of a very large table.

A column family is a different idea

Cassandra and HBase use the phrase "column family." That does not make them analytical column stores.

A column family groups related fields for one row.

User row:

user_id: 42
name: Ali
email: ali@example.com
country: PK
plan: Pro

Another user has another grouped row.

user_id: 43
name: Sara
email: sara@example.com
country: US
plan: Free

A true column store would keep all countries together, all plans together, and all emails together.

Column family
-> related fields stored around one row

Column store
-> one field stored across many rows

The names are similar, but the workloads are different.

Where Cassandra fits

Cassandra is useful when an application has predictable queries, very high write volume, and needs to spread data across many machines.

For example, an IoT system may record a reading from every device every second.

device_id | timestamp | temperature | battery

A useful query is:

Show latest 100 readings for device_42 today

The data can be organized around that access pattern.

Partition: device_42 + date
Rows inside partition: ordered by timestamp

device_42, 2026-08-12

10:00:01  24.1C  88%
10:00:02  24.2C  88%
10:00:03  24.0C  87%

Cassandra is good for telemetry, event ingestion, messaging history, activity feeds, and similar workloads. It is a poor fit for joins, ad hoc reporting, and questions that do not include the partition key.

Column storage also helps the CPU

The benefit of column storage is not only reduced disk I/O. It also gives the CPU easier data to process.

A row-oriented engine may need to repeatedly decode full records.

Read full sale
-> decode date
-> decode product
-> decode customer
-> decode address
-> decode payment details
-> inspect quantity
-> repeat

A column engine can load only a compact chunk of needed values.

price:    [80, 120, 200, 50, 150]
quantity: [ 1,   2,   1,  4,   3]

Small chunks can fit in L1 cache, the CPU's fastest nearby memory. The engine then runs the same simple operation repeatedly, instead of making many function calls and unpredictable decisions for every full record.

Why it is called vectorized processing

A vector here means a batch of values.

price vector:    [80, 120, 200, 50, 150]
quantity vector: [ 1,   2,   1,  4,   3]

Instead of sending one row through the query engine at a time, it sends a batch.

Row-at-a-time:
one row -> operator -> next row

Vectorized:
one batch -> operator -> next batch

Modern CPUs can often apply one instruction to several values at once. This is called SIMD: Single Instruction, Multiple Data.

Compare one price
vs
compare several prices in one CPU instruction

That is why column storage, compression, bitmap operations, CPU cache usage, and vectorized processing work so well together.

My mental model now

Wide table
+ query needs few columns
-> column store can skip irrelevant data

Repeated values
-> low cardinality
-> bitmap indexes can help

Sparse bitmap
-> run-length encoding saves space

Known high-volume operational queries
-> Cassandra may fit

Batch of same-type values
-> CPU cache + SIMD + vectorized processing

These are my personal learning notes from Designing Data-Intensive Applications by Martin Kleppmann.