PostgreSQL Compression: How to Reduce Database Storage Without Sacrificing Performance

1. Why PostgreSQL Storage Expands Faster Than Expected

To compress data effectively, you must first pinpoint where the bytes actually live. Running SELECT pg_size_pretty(pg_database_size('production_db')); tells you the total disk footprint, but it hides the distribution of physical disk usage.

The Primary Storage Drivers

1.Wide Payloads and Unstructured Data: Columns storing JSONB, long text, raw event payloads, or arrays trigger out-of-line storage. If left unoptimized, small mutations to large records write entirely new tuple versions to disk.

2.Index Proliferation: A table with 10 columns and 6 secondary B-tree indexes often dedicates more disk space to indexes than to the underlying table data. Unlike heap tables, indexes in native PostgreSQL do not support built-in TOAST-style compression.

3.MVCC Dead Tuples: PostgreSQL's Multi-Version Concurrency Control leaves obsolete row versions in place until VACUUM frees them. High-write or high-update tables that lack aggressive vacuum tuning accumulate dead space that directly inflates disk footprint and wastes buffer pool cache.

4.Append-Only Historical Drift: Auditing logs, telemetry, and transactional event streams are rarely updated or queried after 30 to 90 days, yet they continue to consume premium solid-state storage alongside hot transactional data.

To see the exact split between table data, TOAST data, and indexes for your largest relations, run:

---

2. Deep Dive: TOAST and Native Column Compression

PostgreSQL pages have a rigid, fixed size: 8 KB. A single database tuple cannot span multiple pages. To store values larger than an 8 KB page (or values that would cause a row to exceed the page threshold), PostgreSQL relies on TOAST (The Oversized-Attribute Storage Technique).

TOAST Strategies

Every column in a PostgreSQL table has an internal storage strategy that dictates when and how TOAST processes it:

PLAIN: Prevents compression and out-of-line storage. Reserved for fixed-width types (e.g., INTEGER, BOOLEAN, UUID).

EXTENDED (Default for TEXT, VARCHAR, JSONB, BYTEA): Allows both inline compression and out-of-line chunking.

MAIN: Favors inline compression first; moves data out-of-line only as a last resort if the page remains full.

EXTERNAL: Moves data out-of-line without performing compression. Ideal for data that is already compressed (e.g., JPEGs, PNGs, pre-zipped payloads) to avoid burning CPU cycles on fruitless compression passes.

PGLZ vs. LZ4: Why Algorithm Choice Matters

Historically, PostgreSQL used its internal compression algorithm, PGLZ, exclusively. While PGLZ produces reasonable compression ratios, its decompression cycle can be CPU-intensive under heavy read traffic.Modern PostgreSQL releases support LZ4, an algorithm designed specifically for high-throughput streaming scenarios. LZ4 decompresses data substantially faster than PGLZ, allowing the database to pull compressed records from disk, decompress them in memory, and serve the query with negligible latency penalties.

Configuring LZ4 Compression

To take advantage of LZ4, verify that your PostgreSQL build was compiled with LZ4 support, then adjust your default or per-column configuration:

---

3. Storage Bloat: Clear Dead Tuples Before Compressing

Compressing data that shouldn't be on disk in the first place wastes both time and compute resources.When rows are updated or deleted, PostgreSQL's MVCC creates dead versions of those tuples. While regular autovacuum routines track these dead spaces in the Free Space Map (FSM) so future inserts can reuse them, the physical files on disk (base//) do not automatically shrink.

Step 1: Diagnose Table & Index Bloat

To see how many dead tuples are consuming storage space in your relations, run:

Step 2: Prevent Bloat Recurrence with Proactive Autovacuum Settings

If tables run hot with constant write operations, default autovacuum thresholds will lag behind row modifications. Adjust the scale factors directly on bloated relations:

Step 3: Reclaim Allocated Space Online with pg_repack

Running VACUUM FULL rewrites a table to a fresh disk file, stripping away dead tuples and unallocated space. However, it takes an ACCESS EXCLUSIVE lock, completely halting application reads and writes for the duration of the operation.For production systems that cannot tolerate locks, use the open-source pg_repack utility. It builds a duplicate table behind the scenes using triggers to capture live transactional deltas, swaps the underlying files in catalog tables, and drops the old, bloated files:

---

4. Partitioning vs. Compression: When to Use Which

Engineers frequently ask: “Should we partition our large table or compress it?”The answer is rarely one or the other. Partitioning and compression address two different aspects of database scalability:

Practical Guidelines

Use Partitioning When: Your queries consistently filter on a specific boundary key (such as created_at or tenant_id), or when old records need to be purged instantly using DROP TABLE instead of expensive, fragmented DELETE queries.

Use Compression When: Individual records contain repetitive structural text, extensive JSONB structures, or arrays that benefit from algorithmic reduction.

Combine Both When: You handle large event logs, metrics, or transactional histories. Keep the active partition hot and uncompressed for maximum write velocity, while compressing and archiving historical partitions.

---

5. Hot vs. Cold Storage Architecture

Not all records in your database share the same operational value. Storing immutable event logs from three years ago on ultra-fast, provisioned-IOPS enterprise solid-state drives is one of the most common causes of inflated cloud bills.By grouping data into distinct operational tiers, you can allocate expensive resources where they matter most:

Implementing Range-Based Partition Transitions

Define parent tables with native declarative partitioning: When partitions transition to cold status (e.g., older than 90 days), use automated pipelines to convert those tables into open columnar formats like Apache Parquet, upload them to low-cost cloud object storage, and drop the underlying partition from your primary database. This keeps backup cycles short, keeps index trees shallow, and frees up primary storage capacity.

---

6. Why Row-Based Storage Fails at Analytical Scale

While native TOAST compression and table partitioning help manage data sizes, traditional row-oriented storage encounters physical limits when applied to data-intensive analytics.PostgreSQL writes and reads data in complete horizontal rows across standard 8 KB pages: Consider this analytical aggregation: To calculate that sum across 50 million records, PostgreSQL must load every single 8 KB data page containing those records into its buffer pool. That means reading every unneeded column off disk—user IDs, text fields, large metadata JSON documents—just to extract a single amount value.

The Consequences of I/O Amplification

1.Memory Cache Eviction: Massive sequential table scans evict hot, frequently accessed transactional data from shared_buffers, causing latency spikes for unrelated operational workloads.

2.Poor Compression Ratios: Compressing row-oriented pages is inefficient because adjacent bytes represent completely different data types (UUIDs, timestamps, floating-point numbers, JSON strings). Compression algorithms achieve their highest density when processing identical, contiguous data types.

---

7. Columnar Compression: High Density for Analytical Workloads

Unlike row storage, a columnar architecture pivots data layout on disk by 90 degrees. Every column is stored in its own dedicated, contiguous set of storage blocks. Because contiguous blocks contain identical data types, modern columnar formats can apply specialized compression algorithms that far outperform general-purpose compression:

Dictionary Encoding: Replaces repetitive strings with compact integer IDs, storing the lookup table once.

Run-Length Encoding (RLE): Collapses repeated adjacent values into a simple counter (e.g., storing active, active, active, active as active x 4).

Frame-of-Reference (Delta Encoding): Stores sorted integers or timestamps as small numerical differences relative to a baseline value, allowing 64-bit numbers to be stored in just a few bits.

High-Density Block Compression (ZSTD): Applied across pre-encoded data blocks to achieve high compression ratios with fast decompression characteristics.

When an aggregation query runs against columnar storage, the database reads only the blocks corresponding to the queried columns. Disk reads drop significantly, memory bandwidth is preserved, and query execution times improve.

---

8. Where KOLMOS Fits: Combining Columnar Storage with Vectorized Execution

For workloads with large analytical scans, high-frequency metrics, or massive aggregation queries, storage compression alone solves only half the performance challenge.Even if data is heavily compressed on disk, a traditional query engine must still decompress those rows and evaluate them one tuple at a time through its execution loop. Decompressing millions of records row-by-row can shift the performance bottleneck from disk I/O straight to CPU saturation.This is where an execution engine like KOLMOS fits naturally into the architecture.

How KOLMOS Changes the Equation

Instead of forcing teams to manage complex ETL pipelines to offload data into external cloud data warehouses, KOLMOS introduces a specialized performance layer designed to accelerate analytical queries within the PostgreSQL ecosystem:

High-Density Columnar Compression: KOLMOS groups homogeneous column values together on disk, applying modern encoding strategies (including dictionary encoding, delta offsets, and ZSTD compression). This can reduce physical storage requirements significantly compared to standard row tables, driving down persistent cloud block-storage costs.

Vectorized Query Processing: Rather than pulling a single row through an evaluation plan at a time, KOLMOS uses vectorized execution. It leverages hardware-level SIMD (Single Instruction, Multiple Data) CPU instructions to process arrays of column values simultaneously in a single clock cycle.

Drastic I/O Reduction: Because analytical queries read only the specific columns needed to resolve filters and aggregations, your database avoids the I/O amplification caused by full-row sequential scans.

Preserved Operational Simplicity: Teams retain standard PostgreSQL connection protocols, SQL dialect compatibility, and their existing BI integrations—avoiding the overhead, custom tooling, and egress costs that come with maintaining separate data warehouses.

For a deeper look into cost reduction strategies, read our guide on How to Make PostgreSQL Cheaper Without Sacrificing Performance, or explore verified performance metrics in our System Benchmarks.

---

9. Practical PostgreSQL Storage Optimization Checklist

Follow this systematic checklist to audit, compress, and optimize your database storage tier:

Phase 1: Identification & Auditing

[ ] Identify the top 10 largest relations using pg_total_relation_size().

[ ] Measure the ratio of table data, TOAST data, and secondary index footprints.

[ ] Run pg_stat_user_indexes to identify unused indexes with zero scans.

[ ] Audit dead-tuple percentages with pg_stat_user_tables.

Phase 2: Cleanup & Maintenance

[ ] Drop confirmed redundant or unused indexes.

[ ] Tune autovacuum scale factors on high-write relations to prevent dead-tuple accumulation.

[ ] Run pg_repack on tables with over 20% dead space to reclaim unallocated disk blocks online.

Phase 3: Native Compression Tuning

[ ] Change table storage strategies from PGLZ to LZ4 for wide text and JSONB columns.

[ ] Verify that pre-compressed binary attributes (PDFs, JPEGs) are marked as EXTERNAL to prevent wasted compression passes.

Phase 4: Lifecycle Tiering & Architecture

[ ] Implement declarative range partitioning for append-only tables larger than 50 GB.

[ ] Set up automated archival pipelines to move partitions older than 90 days to cloud object storage (e.g., S3 as Parquet).

[ ] Evaluate a specialized engine like KOLMOS for large-scale analytical workloads—pairing columnar compression with vectorized execution to cut storage footprints and accelerate queries without leaving the PostgreSQL ecosystem.

---

10. Frequently Asked Questions (FAQs)

Does enabling LZ4 compression on a PostgreSQL table retroactively compress existing data?

No. Running ALTER TABLE ... ALTER COLUMN ... SET COMPRESSION lz4; applies only to new data inserted or updated after the command is executed. Existing rows retain their original storage format. To compress existing records using LZ4, you must rewrite the table using VACUUM FULL, CLUSTER, or an online utility like pg_repack.

Will TOAST compression slow down my transactional write operations?

PGLZ can add slight CPU overhead during intensive write operations because of how it processes compression blocks. LZ4, by contrast, is designed for extremely fast encoding passes, making its impact on write latency negligible for most workloads. In scenarios where disk write throughput or network storage limits are the primary bottleneck, compressing records with LZ4 can actually improve write performance by reducing the physical byte volume sent to disk.

Can secondary indexes in PostgreSQL be compressed?

Native PostgreSQL B-Tree indexes do not support compression in the traditional algorithmic sense. However, starting with PostgreSQL 13, B-Tree indexes use deduplication by default. Deduplication collapses duplicate key values into a single reference list, which can significantly reduce the disk footprint of indexes on columns with low-to-moderate uniqueness without any query performance penalty.

Why is my database size not shrinking after deleting millions of rows?

PostgreSQL's MVCC design means a DELETE operation simply marks tuples as dead—it does not return the physical disk space to your operating system. Normal autovacuum cycles clean the space so it can be reused by future INSERT queries within that same table, but the allocated file on disk remains the same size. To shrink the file and return space to your storage volume without table locks, run pg_repack.

How is columnar compression different from filesystem-level compression (e.g., ZFS)?

Filesystem-level compression compresses data blocks uniformly, without understanding the structure of the database pages inside them. It cannot optimize for specific database types. Columnar compression, by contrast, operates at the data layer: it stores values of the same type contiguously and applies targeted techniques like dictionary encoding and frame-of-reference deltas. This yields significantly better compression ratios and allows the query engine to read only the columns needed by a query, avoiding unnecessary disk I/O entirely.

---

Connect with the KOLMOS Team

Have questions about optimizing your PostgreSQL storage or evaluating columnar performance engines?

LinkedIn Account: https://www.linkedin.com/company/kolmos

Official Website: https://kolmos.dev/

Systems Architecture: https://kolmos.dev/architecture