Why do columns compress better than rows?
Updated · DBShifts
Short answer
A row store keeps each row's values side by side — an id, a price, a status, a timestamp — so neighbouring bytes have little in common. Storing many rows column by column puts similar values together: sorted ids become tiny differences, repeated statuses become a short dictionary, and a general compressor then finds long runs to shrink.
Key facts
- General-purpose compressors such as zlib find repeated byte sequences within a window, so they work best when similar bytes sit close together.
- Delta encoding stores the difference from the previous value, which turns sorted ids and timestamps into small numbers.
- Dictionary encoding replaces repeated strings with short indexes into a list of distinct values.
- XOR encoding of floating-point numbers stores what changed from the previous value's bits, so neighbouring values cost little.
- Random values — UUIDs, hashes, encrypted or already compressed data — do not compress, whichever way they are laid out.
- Reading one row from column-grouped storage means reaching into every column's data, which is why columnar formats favour scans over point lookups.
What a compressor sees in a row store
In PostgreSQL's heap or an InnoDB page, a row's columns are stored one after another, then the next row. An order row might be an 8-byte id, a 4-byte customer id, a numeric amount, a short status string and an 8-byte timestamp. Two adjacent rows share structure but almost no identical bytes: the id moved by one, the timestamp by a few seconds, the amount is different.
A compressor reading that page sees a mix of types and finds only short matches. That is why page-level compression in row stores often saves less than people expect on narrow business tables.
What changes when values are grouped by column
Take a thousand rows and store each column on its own. The ids, if sorted, are a thousand numbers that each differ from the last by one; delta encoding turns them into a thousand ones, which compress to almost nothing. Timestamps from the same period become small, similar differences. A status column with three distinct values becomes a three-word dictionary plus a thousand tiny indexes.
In one 1,000-row block of our test table, the sorted ids took 31 bytes, a three-value status column 415 bytes and timestamps 85 bytes — though that test spaces its timestamps evenly, which real data rarely does. A column of random UUIDs took 16,005 bytes: sixteen bytes each, no saving at all.
Where it does not help
Values with no pattern stay large: random identifiers, cryptographic hashes, encrypted fields, images and files that are already compressed. Free-text columns compress somewhat, mostly from repeated words, and long text compresses better with a stronger algorithm such as lzma.
There is also a cost on the read side. A row is scattered across every column's data, so reading one row means decoding a little from each column. Systems that store old data this way usually keep recent rows in ordinary row storage, where single-row reads and updates are cheapest.
Checklist
- 1
Look at the columns that take the most space and what their values look like.
- 2
Expect sorted ids, timestamps and low-cardinality text to shrink the most.
- 3
Expect random ids, hashes and encrypted or compressed blobs not to shrink.
- 4
Keep rows that are read and updated constantly in row storage.
- 5
Measure on a real sample before deciding; ratios depend entirely on the data.
Common mistakes
Quoting a ratio from someone else's data
A table of events with sorted keys can shrink tenfold; a table of UUIDs and encrypted blobs barely moves. Measure yours.
Compressing data that is already compressed
Images, archives and encrypted values only cost CPU to compress again.
Column storage for constantly updated rows
Updating one row in column-grouped storage means rewriting part of every column; that is what row storage is good at.
How LCS does it
- LCS stores old rows in blocks of about 1,000, each column on its own: integers as offsets or deltas, floats as XOR of their bits, Decimals exactly with their scale, strings plain or as a dictionary — whichever is smaller — then zlib, or lzma for larger text.
- On our 200,000-row test table, compressed rows took about a fifth of their values' size, and the blocks came out about 1.5× smaller than the same rows as CSV compressed with xz.
- Recent rows stay in the original table as ordinary rows; only rows past the age you set are moved into blocks.
- A read by key decodes one value from each column of one block instead of the whole block.
- Measuring a table reports the ratio on a sample of your own rows without changing anything.
Questions people also ask
Is columnar storage always smaller?
For data with patterns — sorted keys, timestamps, repeated text, numbers in a range — it is usually much smaller. For random or already compressed values there is nothing to gain.
Why not store everything in columns?
Single-row reads, inserts and updates are cheaper in row storage. That is why many systems keep recent, busy rows in rows and older rows in columns.
What is the difference between zlib and lzma here?
zlib is fast to compress and decompress; lzma usually compresses text further but is much slower to compress. Using lzma only where it saves enough keeps writes fast.
Keep old rows in the database, at a fraction of the size
LCS for PostgreSQL, MySQL and MariaDB. Measure a table first; nothing changes until you enable it.
Start free4 guides on keeping old rows small. Back to Live Compression Software.