SQL & KV Data Compression

1.1 PostgreSQL: TOAST Compression (pglz vs LZ4)

PostgreSQL uses TOAST (The Oversized-Attribute Storage Technique) to compress and out-of-line store values exceeding roughly 2 KB. Two algorithms are available since PostgreSQL 14:

Configuration: ALTER TABLE t ALTER COLUMN c SET COMPRESSION lz4; or globally via default_toast_compression = lz4

Columnar Extensions: Citus and Hydra

• Citus Columnar: 3–10x compression ratios; benchmark data shows 5.4x for a typical mixed-type table vs heap storage.
• Hydra Columnar: Open-source; in ClickBench comparisons, Hydra outperformed Citus, TimescaleDB, Aurora, and standard PostgreSQL on the 42-query benchmark.
• TimescaleDB Hypercore: Achieves up to 95% compression on time-series data using dictionary compression for high-repetition JSONB columns.

1.2 MySQL / InnoDB: Page Compression and Table Compression

Guidance: For new deployments, transparent page compression with LZ4 provides the best latency-to-savings tradeoff. Reserve Barracuda table compression for cold/archival tables where write overhead is acceptable.

1.3 ClickHouse: Per-Column Codec Selection

ClickHouse provides the most granular compression control of any production database. Each column can be assigned a pipeline of pre-compression codecs followed by a general-purpose algorithm.

Real-World Benchmarks (NOAA Weather Dataset)

Moving from naive types to optimal codecs reduced compressed storage by 65% (4.07 GiB to 1.42 GiB). Standout: Date column with DoubleDelta reduced 2.24 GiB to 24.28 MiB (~99% reduction).

1.4 SQLite: Page-Level Compression Options

SQLite has no built-in compression. External strategies fill the gap:

1.5 Cross-Engine Compression Ratio Summary

2.1 RocksDB: Per-Level Compression Strategy

RocksDB (LSM-tree) provides the most sophisticated KV compression architecture, with per-level algorithm selection.

Facebook/Meta Production Configuration

compression_per_level = kNoCompression      # L0
                       kNoCompression      # L1
                       kLZ4Compression     # L2
                       kLZ4Compression     # L3
                       kLZ4Compression     # L4
                       kZSTD               # L5
                       kZSTD               # L6
bottommost_compression = kZSTD

Rationale: L0 and L1 are frequently compacted; compression adds CPU load with minimal space benefit. The bottommost level holds the vast majority of data — ZSTD's higher ratio pays off there.

2.2 LMDB: No Built-In Compression

LMDB deliberately excludes compression from its B+tree engine. External compression strategies:

When LMDB is appropriate: Single-writer/many-reader workloads where zero-copy reads matter more than storage density. If you need built-in compression, use RocksDB instead.

2.3 Redis: Memory-Level Compression Encodings

Redis compresses at the data-structure level rather than the page or block level:

Application-level compression benchmarks (LogicMonitor study):

• LZ4 on values: 60% memory savings (39,739 bytes to 16,208 bytes)
• Float16 + Blosc + ZLib + Base64 pipeline: 85% savings (80,000 bytes to 11,383 bytes)

2.4 Cloudflare Workers KV: Edge Compression

Cloudflare Workers KV is an eventually-consistent global KV store optimized for read-heavy, write-infrequent workloads.

• 2025 redesign: Hybrid storage — distributed database for small values, R2 object storage for large values
• p99 read latency improved from 200 ms to under 5 ms after the 2025 rearchitecture
• Internal compression is opaque; Cloudflare handles storage optimization at the infrastructure level

Best practice: Pre-compress with CompressionStream (Web Streams API) in the Worker itself, using gzip (broadest decompression support) or raw deflate.

2.5 DynamoDB: Client-Side Attribute Compression

DynamoDB provides no server-side compression. All compression is application-layer.

2.6 etcd / bbolt: Compaction, Not Compression

etcd uses bbolt (B+tree store with 4 KB memory-mapped pages). bbolt has no compression capability.

What etcd calls "compaction" is revision pruning: dropping superseded MVCC revisions to reclaim logical space. bbolt does not release freed pages back to the OS — only etcd defrag physically reclaims disk space.

Practical guidance:

• Run etcd compaction regularly to prune old revisions
• Follow with etcd defrag to reclaim disk space
• For storage-constrained environments, reduce --snapshot-count and enable auto-compaction

The fundamental difference: columns contain homogeneous data types, enabling specialized codecs that exploit type-specific patterns. Rows mix types, limiting compression to general-purpose algorithms.

ClickBench Comparison (100M rows)

Why Columnar Compresses Better

1. Run-Length Encoding (RLE): Sorted columns with repeated values compress to (value, count) pairs.
2. Dictionary Encoding: Low-cardinality string columns replace strings with integer indices.
3. Delta Encoding: Monotonic sequences store only differences. If timestamps increment by exactly 1 second, each value is 0 bits after double-delta.
4. Bit-Packing / Frame-of-Reference: Integer values clustered in a narrow range are stored with minimal bits per value.
5. Type-Specific Codecs: Gorilla for floats, DoubleDelta for timestamps — impossible in row stores that see each row as an opaque tuple.

Quantified Advantage

• Row-oriented databases (PostgreSQL, MySQL): 2–4x compression on typical OLTP data
• Columnar databases (ClickHouse, DuckDB, Redshift): 10–50x compression on analytical data
• Hybrid approaches (TimescaleDB Hypercore, Citus Columnar): 5–20x, bridging the gap

The 10x storage difference between ClickHouse and PostgreSQL on the same 100M-row dataset represents real production cost: at S3 pricing ($0.023/GB/month), that is the difference between $0.21/month and $2.30/month per dataset — scaling to thousands of dollars at enterprise data volumes.

Key Principles

1. Smallest type first: Use Int16 instead of Int64 when range permits.
2. Pre-compression codecs before general-purpose: Delta, DoubleDelta, Gorilla, and T64 transform data into more compressible representations before LZ4/ZSTD processes it.
3. Measure per column: Compression effectiveness varies dramatically across columns in the same table. Always benchmark with real data.
4. UUIDs are compression-hostile: Their uniform distribution defeats all algorithms. Store as binary (16 bytes) rather than text (36 bytes) for a guaranteed 2.25x savings without compression.

How ZSTD Dictionary Training Works

ZSTD dictionaries capture recurring byte patterns from a training corpus. During compression, each input block is encoded using references to dictionary fragments rather than raw literals. The dictionary is typically small (32–100 KiB) and is shared across all compressions of similar data.

Dictionary Training Guidelines

• Corpus size: 100x the target dictionary size minimum (e.g., 10 MB of samples for a 100 KB dictionary)
• Dictionary size: 32–100 KiB (larger dictionaries offer diminishing returns)
• Retraining frequency: When data distribution shifts significantly (schema changes, new value patterns)
• Storage overhead: Dictionary must be available at decompression time; embed it alongside compressed data or store in metadata

Production Implementations

• RocksDB: Supports ZSTD dictionary training at the bottommost level during compaction. Enable with CompressionOptions::zstd_max_train_bytes and CompressionOptions::max_dict_bytes.
• Cassandra (CEP-54): Proposed per-table dictionary support for SSTable compression. Expected 3–4x improvement over non-dictionary ZSTD.
• sqlite-zstd: Trains dictionaries per-column at the SQLite table level. Benchmark: 2.0 GB database compressed to 528 MB (75%) with dictionary vs 1.63 GB (23%) without — a 3.2x improvement from dictionary training alone.

In LSM-tree databases (RocksDB, LevelDB, Cassandra, CockroachDB), every logical write is physically written multiple times as data moves through compaction levels. Compression adds CPU cost to each of these writes.

Compression Level vs Write Throughput

Decision Framework

If write_throughput_critical AND ssd_storage:
    L0-L1: None, L2-L4: LZ4, Lmax: ZSTD-1
If storage_cost_critical AND write_moderate:
    L0: None, L1-L4: LZ4, Lmax: ZSTD-3
If archival AND write_infrequent:
    All levels: ZSTD-9 or ZSTD-19

Cost-at-Scale Comparison (500 TB/month, S3 eu-central-1 at $0.0235/GB)

ZSTD-3 delivers the sweet spot: 2x the savings of LZ4 with practical CPU overhead.

Decision Flowchart

START
  |
  v
Is latency the primary concern?
  |-- YES --> Use LZ4 everywhere. Measure. Stop.
  |-- NO
  v
Is this columnar / analytical data?
  |-- YES --> Use type-specific codecs (Delta, DoubleDelta, Gorilla, T64) + ZSTD
  |-- NO
  v
Is this an LSM-tree KV store?
  |-- YES --> No compression L0-L1, LZ4 L2-L4, ZSTD-3 Lmax
  |-- NO
  v
Is this a row-oriented RDBMS?
  |-- YES --> PostgreSQL: TOAST LZ4 | MySQL: Transparent page compression LZ4
  |-- NO
  v
Is this a managed KV service (DynamoDB, Workers KV)?
  |-- YES --> Client-side compression (GZIP for broadest compat, ZSTD for best ratio)
  |-- NO
  v
Default: ZSTD-3 at the application layer. Always measure.

PostgreSQL

• PostgreSQL Performance: pglz vs. LZ4 (TigerData/Timescale)
• What is the new LZ4 TOAST compression in PostgreSQL 14 (Fujitsu)
• Hydra Columnar (GitHub)

ClickHouse

• Optimizing ClickHouse with Schemas and Codecs
• Compression in ClickHouse (Docs)
• New Encodings to Improve ClickHouse Efficiency (Altinity)

SQLite

• sqlite-zstd: Transparent dictionary-based row-level compression (phiresky)

RocksDB

• RocksDB Compression Wiki (GitHub)
• Parallel Compression Revamp (RocksDB Blog, Oct 2025)

Redis

• Redis Memory Optimization (Official Docs)
• Redis Compression Benchmarking: 85% Data Reduction (LogicMonitor)

Cloudflare Workers KV

• Cloudflare Rearchitects Workers KV, Achieves 40x Performance Gain (InfoQ, Aug 2025)

What Changed Recently

1. ZSTD is now the de facto standard. In 2020, Snappy and zlib dominated. By 2026, ZSTD has replaced both in most production systems.
2. Dictionary compression matured. RocksDB's dictionary support is production-ready. sqlite-zstd demonstrates 3x improvement from dictionary training alone.
3. LZ4 won the speed tier. Snappy has no remaining advantage. PostgreSQL 14+ made LZ4 TOAST a first-class option.
4. Columnar compression in row databases. TimescaleDB Hypercore, Citus Columnar, and Hydra bring 10–20x compression to PostgreSQL without leaving the ecosystem.
5. Edge KV stores are opaque. Cloudflare Workers KV and similar edge stores handle compression internally. The optimization lever for users is client-side pre-compression and efficient serialization.

Quick-Reference Decision Table