What Are Database Compression Techniques and Why Do They Matter?
Learn how database compression works — dictionary, delta, and run-length encoding — and the CPU-vs-storage trade-off it makes.
Expected Interview Answer
Database compression techniques shrink the physical size of stored data by removing redundancy — through methods like dictionary encoding, run-length encoding, delta encoding, and general-purpose algorithms such as LZ4 or Zstandard — so the same rows occupy fewer disk blocks and fewer bytes move through I/O and network paths.
Row-store engines typically compress at the page or block level, applying a general algorithm across a block of bytes, while column-store engines compress each column separately using type-aware schemes (dictionary encoding for low-cardinality text, delta encoding for sorted numeric sequences) because values in one column are far more similar to each other than to values in a neighboring column. The trade-off is CPU: compressing and decompressing costs processor cycles in exchange for fewer disk reads, smaller buffer-pool footprints, and lower storage and network cost, which is usually a net win because storage and I/O bandwidth are slower than modern CPUs.
- Reduces disk footprint and storage cost
- Shrinks I/O per query, since fewer bytes are read from disk
- Lets more data fit in the buffer pool/cache, raising the hit rate
- Lowers network transfer cost for replication and backups
AI Mentor Explanation
A scorer writing out every ball verbatim — "no run, no run, no run, single, no run" — wastes ink compared to writing "3x dot balls, 1 run" once and noting the repeat count. That shorthand is exactly what run-length encoding does to a column full of repeated values: instead of storing the same value nine times, the engine stores the value once plus a count of nine. The scorebook stays fully readable, it is just far smaller to carry and faster to flip through.
Step-by-Step Explanation
Step 1
Profile the data
Identify column cardinality, sort order, and value distribution to pick a suitable compression scheme.
Step 2
Choose an encoding
Use dictionary encoding for low-cardinality text, run-length encoding for repeated runs, delta encoding for sorted numerics, or general LZ4/Zstandard for the rest.
Step 3
Compress at write time
The engine encodes data into compact blocks or column segments as it is written or during background compaction.
Step 4
Decompress on read
Blocks are decompressed transparently when queried, trading CPU cycles for reduced I/O and storage.
What Interviewer Expects
- Naming at least two concrete compression schemes (dictionary, RLE, delta, LZ4/Zstd)
- Understanding the CPU-vs-I/O trade-off
- Awareness that columnar layouts compress better than row layouts for many workloads
- A real example of when compression helps most (repetitive, low-cardinality, or sorted data)
Common Mistakes
- Assuming compression is always free with no CPU cost
- Not distinguishing row-level from column-level compression
- Ignoring that high-cardinality random data compresses poorly
- Forgetting that compression also reduces buffer pool pressure, not just disk usage
Best Answer (HR Friendly)
“Database compression means storing data more efficiently by spotting patterns and redundancy, so the same information takes up less disk space. It trades a small amount of extra CPU work for major savings in storage cost and disk I/O, which usually makes queries faster overall since less data has to move off disk.”
Code Example
-- Columnar engines expose compression per column/table
CREATE TABLE events (
event_id BIGINT,
event_type TEXT ENCODE ZSTD,
event_ts TIMESTAMP ENCODE DELTA,
country_code TEXT ENCODE BYTEDICT
);
-- Row-store engines typically compress at the table or page level
ALTER TABLE events SET (compression = zstd);Follow-up Questions
- What is the difference between row-level and column-level compression?
- When does dictionary encoding stop being effective?
- How does compression interact with indexing performance?
- What is the CPU-vs-storage trade-off in choosing a compression algorithm?
MCQ Practice
1. Which compression technique is best suited to a sorted, mostly increasing numeric column?
Delta encoding stores the small difference between consecutive sorted values instead of the full value each time.
2. Run-length encoding is most effective on columns that are:
RLE replaces long runs of identical repeated values with a single value-and-count pair, so it shines on streaky, repeated data.
3. What is the primary trade-off of enabling database compression?
Compression spends CPU cycles compressing and decompressing in exchange for smaller data on disk and less I/O per query.
Flash Cards
What does database compression do? — Reduces the physical size of stored data by removing redundancy, cutting disk usage and I/O.
When is dictionary encoding effective? — On low-cardinality columns with many repeated values, like country codes or status flags.
When is delta encoding effective? — On sorted numeric columns where consecutive values are close together, like timestamps.
Main trade-off of compression? — Extra CPU cost for compress/decompress, in exchange for less disk I/O and storage.