How do I reduce the size of a PostgreSQL table?
Updated · DBShifts
Short answer
First find out whether the space is bloat or live data. Bloat — space left by deleted and updated rows — comes back with VACUUM FULL or pg_repack. Live old rows only get smaller by deleting, archiving, dropping old partitions, or storing them compressed, because PostgreSQL's TOAST compression never touches narrow rows.
Key facts
- pg_total_relation_size counts a table, its TOAST table and its indexes; pg_relation_size counts only the table's main data.
- Plain VACUUM makes the space of dead rows reusable but rarely returns it to the operating system.
- VACUUM FULL rewrites the table into a new file and returns the space, holding an ACCESS EXCLUSIVE lock for the whole rewrite.
- pg_repack rewrites a table with only brief locks, but needs free disk for a full copy of the table and its indexes.
- TOAST compresses values only when a row grows past about 2 KB, so rows of integers, timestamps and short text are stored uncompressed.
- Since PostgreSQL 14, ALTER TABLE ... ALTER COLUMN ... SET COMPRESSION lz4 switches a column's TOAST compression to lz4 for values written afterwards.
Find out where the space is
Start with three numbers per table: pg_relation_size for the rows themselves, pg_indexes_size for the indexes, and pg_total_relation_size for everything including TOAST. On a table that has been updated for years, the indexes are often a third of the total or more, and an index nobody reads is pure cost.
Then check for dead rows. pg_stat_user_tables.n_dead_tup shows how many row versions are waiting for vacuum, and the pgstattuple extension reports exactly how much of a table's file is free space. A table that is mostly free space has a bloat problem, not a data problem, and the fixes are different.
Bloat: space you already freed
Every UPDATE in PostgreSQL writes a new row version and leaves the old one behind; DELETE leaves the row in place. Autovacuum marks that space reusable, so a table with steady churn stops growing, but the file keeps the size it reached at its peak.
To hand the space back, the table has to be rewritten. VACUUM FULL does it in one statement but blocks reads and writes until it finishes, which on a large table can be a long outage. pg_repack builds the new copy alongside the old one and swaps them with short locks, which is why it is the usual choice on a live system. Both need enough free disk for the new copy before the old one is removed.
If bloat keeps coming back, tune autovacuum for that table (a lower autovacuum_vacuum_scale_factor) rather than rewriting it every month.
Old rows: the space you still need
If the space is real data, something has to happen to it. Deleting rows you no longer need is the cheapest option, done in small batches so each transaction stays short. Archiving moves old rows to another table, database or file, which frees space but means a different place to look when someone needs them.
Partitioning by time turns cleanup into metadata: detaching or dropping a month-old partition is instant, where deleting the same rows one by one generates WAL and bloat. It has to be designed in, though; converting an existing large table to partitions means copying it.
The remaining option is to keep the rows where they are but store them compressed, so they stay readable without moving anywhere.
What compression PostgreSQL gives you
Built in, PostgreSQL compresses individual large values through TOAST: long text, JSON documents, arrays and binary data, once a row passes about 2 KB. pglz is the default and lz4 is available from version 14. A typical business table — ids, foreign keys, amounts, statuses, timestamps — never reaches that threshold, so TOAST does nothing for it.
To compress narrow rows you need something that stores many rows together. Extensions such as Citus columnar and TimescaleDB's compression for hypertables do this by changing how the table is stored; they bring their own install, their own limits on updates and indexes, and in TimescaleDB's case a hypertable to convert to.
Checklist
- 1
Measure pg_relation_size, pg_indexes_size and pg_total_relation_size for the table.
- 2
Check n_dead_tup, or pgstattuple, to tell bloat from live data.
- 3
Reclaim bloat with pg_repack on a live system, VACUUM FULL only in a maintenance window.
- 4
Look for indexes that are never scanned (pg_stat_user_indexes.idx_scan) before buying disk for them.
- 5
Decide which old rows can be deleted, which must stay readable, and which can move elsewhere.
- 6
Keep cleanup deletes small, a few thousand rows per transaction.
Common mistakes
Running VACUUM FULL on a busy table
It holds an ACCESS EXCLUSIVE lock until the rewrite ends. Every query on the table waits, including reads.
Expecting TOAST to shrink narrow rows
TOAST only compresses rows that grow past about 2 KB. Turning on lz4 changes nothing for a table of integers and timestamps.
One giant DELETE
Deleting millions of rows in one transaction holds locks, writes a burst of WAL and leaves the same amount of bloat behind for vacuum to deal with.
Forgetting the indexes
Indexes can be as large as the table. Dropping an unused one often frees more than any rewrite.
How LCS does it
- LCS keeps the table's recent rows exactly as they are and moves rows older than an age you choose into compressed blocks, in a table beside the original in the same database.
- Each block holds about 1,000 rows stored column by column, so narrow rows of ids, amounts and timestamps compress too — the case TOAST never reaches.
- Moving a block and deleting its rows from the table happen in one transaction, so a crash leaves every row in exactly one place.
- Compressed rows are read and changed from your DBShifts account, not through plain SQL. Recent rows stay ordinary rows.
- Measuring a table reads a sample and reports what LCS would use against the table's own size, without changing anything.
Questions people also ask
Does VACUUM shrink a PostgreSQL table?
Plain VACUUM makes dead space reusable inside the table and can cut empty pages off the end of the file, but it rarely returns much to the operating system. VACUUM FULL or pg_repack rewrite the table to do that.
Is lz4 better than pglz for TOAST?
lz4 usually compresses and decompresses faster with a similar ratio. It only applies to values that TOAST compresses at all, and only to values written after the change, unless the table is rewritten.
How do I see how much space indexes use?
pg_indexes_size('table') gives the total for one table, and pg_relation_size on each index name gives them one by one. pg_stat_user_indexes shows how often each is scanned.
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.