Compressing Cold Partitions for Cheaper Storage

Old partitions are read rarely and stored expensively, which makes them the obvious place to trade CPU for bytes. This guide measures the achievable ratio, applies compression and tiering to partitions that no longer take writes, and avoids the two ways this work goes wrong: rewriting a hot partition, and assuming a ratio nobody verified. It implements the warm and cold stages of Partition Lifecycle & Retention Management inside Partitioning Implementation Patterns & Routing.

Prerequisites

Step 1 — Measure the real ratio on one partition

Never plan a compression project on an assumed ratio. Copy one partition into a scratch table with the new setting and compare:

-- baseline
SELECT pg_size_pretty(pg_total_relation_size('events_2025_06')) AS current_size;

-- a scratch copy with LZ4 on the wide columns
CREATE TABLE events_2025_06_lz4 (LIKE events_2025_06 INCLUDING ALL);
ALTER TABLE events_2025_06_lz4 ALTER COLUMN payload SET COMPRESSION lz4;
INSERT INTO events_2025_06_lz4 SELECT * FROM events_2025_06;

SELECT pg_size_pretty(pg_total_relation_size('events_2025_06_lz4')) AS lz4_size;
 current_size
--------------
 391 GB

   lz4_size
--------------
 148 GB
Where the bytes actually are, and which of them compress Breaking a 391 gigabyte partition down by storage component: the JSON payload column accounts for 268 gigabytes and compresses to 61 with LZ4, indexes account for 74 gigabytes and do not compress at all, and the fixed-width columns account for 49 gigabytes and barely move. The overall ratio is 2.6 to 1, driven entirely by one column. events_2025_06 — 391 GB before, 148 GB after payload (jsonb) indexes fixed-width columns 268 GB before 61 GB after — 4.4:1 74 GB before 74 GB after — no change 49 GB before 46 GB after — 1.06:1 One column carries the entire benefit. That is the normal shape, and it means the decision is per column rather than per table — and that indexes, which never compress, set a hard floor on how small a partition can become. If the floor is close to the current size, drop redundant indexes on cold partitions instead: that is often the larger saving.

Operational note: pg_total_relation_size includes indexes and TOAST. Compare it rather than pg_relation_size, or the measured ratio will flatter the result by ignoring the part that does not compress.

DBA tip: Drop the scratch copy immediately. A duplicated 148 GB table that nobody remembers creating is its own capacity incident.

Step 2 — Set the compression method for future writes

For partitions still receiving writes, the setting applies going forward with no rewrite:

-- new values in this column use LZ4 from now on
ALTER TABLE events ALTER COLUMN payload SET COMPRESSION lz4;

-- confirm what each column will use
SELECT attname, attcompression
FROM   pg_attribute
WHERE  attrelid = 'events'::regclass AND attnum > 0 AND NOT attisdropped;

Setting it on the parent means every partition created afterwards inherits it, which is the cheapest possible rollout: no rewrite, no lock, and the benefit accrues as new partitions fill.

Operational note: attcompression shows l for LZ4, p for pglz and blank for the default. A blank means the column follows default_toast_compression, which is a server-wide setting worth changing to lz4 on any recent version.

SRE tip: LZ4 is faster to compress and decompress than the legacy pglz, so switching it on the hot path is usually a latency improvement as well as a space one. It is one of the few settings that is not a trade.

Step 3 — Rewrite cold partitions to apply it retroactively

Existing rows keep their old compression until the data is rewritten. On a cold partition, the simplest rewrite is VACUUM FULL:

Rewrite in place, or detach first VACUUM FULL rewrites the partition in place, taking an access exclusive lock on that partition for the whole rewrite — about fifty minutes for a 391 gigabyte table. Detaching first, rewriting off the parent, then reattaching keeps the lock on the parent to two brief moments, at the cost of the partition being invisible to queries in between. VACUUM FULL in place DETACH → rewrite → ATTACH ACCESS EXCLUSIVE on the partition — 50 min queries spanning it block rewrite off the parent — no lock on the parent detach / attach: seconds simplest, and acceptable when nothing queries that month during the window the partition is absent from the parent while it is rewritten, so queries silently return no rows for that range Choose by which is worse for your readers: blocking on that range, or silently missing it. For cold data, blocking usually wins. 
-- takes ACCESS EXCLUSIVE on this partition only; other partitions serve normally
ALTER TABLE events_2025_06 ALTER COLUMN payload SET COMPRESSION lz4;
VACUUM FULL VERBOSE events_2025_06;

For a partition large enough that the lock duration matters, detach it first, rewrite it while it is off the parent, then reattach:

ALTER TABLE events DETACH PARTITION events_2025_06 CONCURRENTLY;
ALTER TABLE events_2025_06 ALTER COLUMN payload SET COMPRESSION lz4;
VACUUM FULL events_2025_06;
ALTER TABLE events ATTACH PARTITION events_2025_06
      FOR VALUES FROM ('2025-06-01') TO ('2025-07-01');

Operational note: The reattach re-validates that every row falls inside the bounds unless a matching CHECK constraint already exists on the child. Add the constraint before detaching and the attach is instant instead of a full scan.

DBA tip: VACUUM FULL needs free space equal to the new table plus its indexes while it runs. On a nearly full volume the rewrite fails partway and you are left with the original table plus a wasted attempt — check free space first.

Step 4 — Tier the rewritten partition onto cheaper storage

Compression and tiering compose: shrink the partition, then move it somewhere cheaper per byte.

CREATE TABLESPACE cold_storage LOCATION '/mnt/cold/pgdata';

-- move the rewritten partition; ACCESS EXCLUSIVE for the copy duration
ALTER TABLE events_2025_06 SET TABLESPACE cold_storage;

-- and its indexes, which do not follow automatically
ALTER INDEX events_2025_06_occurred_at_idx SET TABLESPACE cold_storage;
Compression and tiering compose multiplicatively One 391 gigabyte partition costs about 94 dollars a month on fast storage. Compressing it to 148 gigabytes costs 36. Tiering the uncompressed partition to cheap storage costs 31. Doing both costs 12 dollars a month, an 87 percent reduction, with read latency rising from sub-millisecond to a few milliseconds on the rare queries that touch it. untouched · 391 GB · fast compressed · 148 GB · fast tiered · 391 GB · cold both · 148 GB · cold $94/mo $36/mo $31/mo $12/mo — 87% saved Multiply by the number of cold partitions before deciding: 13 partitions at $82 saved each is $1,066 a month, which justifies a maintenance window. Two partitions is $164, which does not.

Operational note: Indexes have their own tablespace and do not follow the table. A tiered table with hot-storage indexes still costs the index bytes at the fast rate — and on a cold partition the indexes are often the majority of what remains after compression.

SRE tip: Set temp_tablespaces away from the cold volume. A rewrite that spills sort work onto slow storage takes far longer than it needs to.

Verification

Confirm the space actually came back and the data is intact:

SELECT pg_size_pretty(pg_total_relation_size('events_2025_06')) AS size_after,
       (SELECT count(*) FROM events_2025_06)                    AS rows_after,
       spcname                                                  AS tablespace
FROM   pg_class c
LEFT   JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE  c.relname = 'events_2025_06';
 size_after | rows_after  | tablespace
------------+-------------+--------------
 148 GB     |   418223104 | cold_storage

Row count must be identical to the pre-rewrite value. Then run a representative query against the compressed partition and compare its latency with the same query against an untouched one — the decompression cost is real and should be measured rather than assumed.

Failure mode table

Failure mode Root cause SRE mitigation
VACUUM FULL fails partway with a disk error the rewrite needs free space equal to the new table plus indexes, and the volume was too full check free space before starting; rewrite after detaching so the work can happen on a different volume
Compression ratio far below expectation the bulk of the bytes are indexes or already-dense numeric columns, not compressible text measure per component with pg_total_relation_size minus pg_indexes_size; consider dropping cold-partition indexes instead
Reattach takes hours no matching CHECK constraint existed, so PostgreSQL scanned the whole partition to validate the bounds add the constraint before detaching; the attach then validates from the catalog and returns immediately

FAQ

Does changing a column's compression method rewrite existing rows?

No. ALTER TABLE ... ALTER COLUMN ... SET COMPRESSION affects values written after the change; existing values keep whatever method compressed them. To apply it to historical data you must rewrite the partition, either with VACUUM FULL, with a table rewrite, or by copying into a fresh table and swapping it in.

Is VACUUM FULL safe on a cold partition?

On a partition that receives no writes it is safe in the sense that nothing is lost, but it takes an ACCESS EXCLUSIVE lock on that partition for the whole rewrite and needs free space equal to the table’s size. Queries touching only other partitions are unaffected; queries that span the partition being rewritten will block, so run it in a low-traffic window.

How much compression should I actually expect?

It depends entirely on the data. Repetitive JSON and text commonly reach 3:1 or better with LZ4 and 4:1 with zstd; numeric and timestamp columns barely move because they are already dense. Measure on one partition before planning a fleet-wide job — the difference between an assumed 4:1 and an actual 1.3:1 is the difference between a project worth doing and one that is not.

Back to Partition Lifecycle Retention Management