Large tables

Most tables never need any of this. A table that is heading for hundreds of millions of rows does, and the problems arrive in a predictable order.

Vacuum stops keeping up first

Autovacuum triggers on a fraction of the table: roughly 20% dead rows by default. On a 500 million row table that is 100 million dead rows before it even starts, and then it has to scan a relation larger than memory while competing with your queries for the same disks.

Postgres 18 added an absolute ceiling on top of the fraction, which helps, but the underlying shape does not change: one big table means one big vacuum, single-threaded, holding nothing back for you.

ANALYZE has a related problem. It samples a fixed number of rows regardless of table size, so the planner's statistics get proportionally worse as the table grows. You can raise the sample for a column that matters:

ALTER TABLE events ALTER COLUMN tenant_id SET STATISTICS 1000;
ANALYZE events;

Deleting rows is the expensive way to remove data

DELETE is not the cheap operation it appears to be. It does not remove rows, it marks them; the space is reclaimed later by vacuum, and the files do not shrink. So a large DELETE:

• Writes as much WAL as the rows it "removed", which replicates and archives like any other write.
• Leaves that many dead rows for vacuum to work through afterwards.
• Leaves index entries pointing at them until the same vacuum gets there.

A cascading foreign key makes this considerably worse than it looks: deleting one row can delete gigabytes of dependents, generating a WAL burst that has nothing to do with the size of the statement you typed.

If you must delete a lot of rows, do it in bounded batches so each transaction is short and vacuum can keep pace:

DELETE FROM events
WHERE id IN (
  SELECT id FROM events WHERE created_at < now() - interval '90 days' LIMIT 10000
);
-- repeat until zero rows affected

If you are keeping less than you are removing, rewriting is cheaper than deleting:

BEGIN;
LOCK TABLE events IN ACCESS EXCLUSIVE MODE;
CREATE TEMP TABLE events_keep AS SELECT * FROM events WHERE created_at >= now() - interval '90 days';
TRUNCATE events;
INSERT INTO events SELECT * FROM events_keep;
COMMIT;

That holds an exclusive lock for the duration and only writes WAL for the rows you kept. Rehearse it against a preview database first - it is not a statement to try for the first time on production.

Partitioning is how you stop deleting

The real fix for "we remove old data on a schedule" is to make removal a DROP, not a DELETE:

CREATE TABLE events (
  id         BIGINT      GENERATED ALWAYS AS IDENTITY,
  tenant_id  BIGINT      NOT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  payload    JSONB       NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_08 PARTITION OF events
  FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

Dropping a partition unlinks a file. It costs the same whether the partition holds one row or a hundred million, it writes almost no WAL, and it leaves no cleanup behind. Compare that against deleting a month of rows and the difference is not a percentage.

Partitioning also splits everything else that was one big job into many small ones:

The partition key must be part of the primary key, and the note about the primary key above is the usual reason a first attempt at partitioning fails.

pg_partman automates creating and dropping partitions on a schedule; enable it from the extensions list.

Wide rows and TOAST

Values over roughly 2 KB are moved out of the row into a side table (TOAST) and compressed. This is usually invisible and usually good, with two things worth knowing:

• A row must fit in an 8 KB page. Many wide columns plus a low fillfactor on a huge table means a lot of sparsely used pages, and pages are what get read.
• Each toasted value takes an identifier from a per-table space of about four billion. An UPDATE of a toasted value takes a new one. A very high-churn table of large values can, eventually, exhaust that. It is rare, and when it happens the answer is partitioning - each partition gets its own space.

If a table is read-heavy, pack pages tightly (fillfactor = 100, the default). If it is update-heavy, leave room so updates can stay on the page (fillfactor = 80 or lower). One table cannot do both; partitions can.

Reclaiming space

Vacuum makes space reusable by the table. It does not give it back to the filesystem. To actually shrink a bloated table you have to rewrite it:

• VACUUM FULL table - simple, effective, takes an ACCESS EXCLUSIVE lock for the whole rewrite. Fine at 3am on a small table, not fine on a large one during business hours.
• pg_repack / pg_squeeze - rewrite online, with only a brief lock at the swap. Both need free disk space roughly equal to the table.

Either way, do it per-partition if the table is partitioned. Rewriting one 40 GB partition needs 40 GB of headroom; rewriting a 480 GB table needs 480 GB.

What to watch

Cache hit ratio is the leading indicator that a table has outgrown memory - the Low cache hit ratio advisory in your dashboard tracks it. For per-table detail:

SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total,
       n_live_tup,
       n_dead_tup,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

pg_total_relation_size includes indexes and TOAST, which is usually the number you actually care about; pg_relation_size is the heap alone.