Getting rid of a 4 TB table in PostgreSQL

GitLab has a public story about deleting the wrong thing. In January 2017 an engineer who was trying to restore replication wiped a data directory they believed was on the secondary. It was on the primary. The postmortem they published afterwards is still one of the better things written about a database outage.

At PGConf.EU in Valencia last week a few people from GitLab came to me with the inverse problem. They have one table, 4 TB, on a primary with ten replicas, and they want it gone, on purpose, with as little impact as they can manage. It's an archival table: nothing writes to it, and nothing is supposed to read it.

DROP TABLE is the obvious answer and most of the time the right one. On eleven servers, a WAL (write-ahead log) archive and load balancer that routes reads by replica lag and the cost depends on three things. One is whether something still reads the table. The others are how much of it the kernel has cached and how the files sit on disk. So I measured it twice, on a Hetzner pair with a real streaming replica and on a GCE machine where a 1 TB table can't fit in the page cache. The other half is what happens when "nothing is supposed to read it" turns out to be only mostly true.

The test table

CREATE TABLE build_logs (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  project_id bigint NOT NULL,
  created_at timestamptz NOT NULL,
  status smallint NOT NULL,
  payload text NOT NULL
);

INSERT INTO build_logs (project_id, created_at, status, payload)
SELECT (random()*50000)::bigint,
       timestamptz '2019-01-01' + (i * interval '4 seconds'),
       (random()*5)::smallint,
       md5(i::text) || repeat('x', 150 + (i % 80))
FROM generate_series(1, 28000000) i;

CREATE INDEX build_logs_project_idx ON build_logs (project_id, created_at);

  heap   |   idx   |  total
---------+---------+---------
 7747 MB | 1442 MB | 9192 MB

Twenty-eight million rows, ids that grow with time, and the kind of secondary index every log-shaped table ends up with. I kept it in a template database and cloned a fresh copy for each test with CREATE DATABASE ... STRATEGY FILE_COPY, so every experiment started from the same bytes on the primary and the replica.

What DROP TABLE costs

DROP TABLE build_logs;

DROP TABLE
Time: 67.751 ms

The WAL position moved by 81,737 bytes, and none of it is table data. Most of it is catalog rows leaving pg_class, pg_attribute and friends, plus six Standby/LOCK records, one AccessExclusiveLock per relation with storage (the heap, its identity sequence, its TOAST table and index, and two indexes). The record that does the actual work is the commit:

rmgr: Transaction len (rec/tot): 2181/2181, tx: 762, lsn: 6/29014870,
  desc: COMMIT 2026-10-23 06:21:56.998901 UTC; rels: base/16400/16385
  base/16400/16384 base/16400/16393 base/16400/16394 base/16400/16395
  base/16400/16397; dropped stats: ...

It carries a list of relfilenodes to delete, and whoever replays it, a replica or the primary itself after a crash, deletes those files. The data never goes through WAL on its way out. (PostgreSQL stores a relation as 1 GB files called segments, and the replica unlinks all of them at once.)

Finding out whether anyone still reads it

DROP TABLE needs AccessExclusiveLock, which conflicts with everything including a plain SELECT. For a table nobody reads that costs nothing, so the real work is finding out whether somebody does. Take one session holding a read on the table inside a transaction that's busy with something else, then the drop, then three ordinary application queries:

 pid | application_name | wait_event_type | wait_event | blocked_by | query
-----+------------------+-----------------+------------+------------+--------------------------------
 204 | psql             | Timeout         | PgSleep    | {}         | BEGIN; SELECT count(*) FROM bu
 210 | dropper          | Lock            | relation   | {204}      | DROP TABLE build_logs
 216 | app_1            | Lock            | relation   | {210}      | SELECT * FROM build_logs WHERE
 222 | app_2            | Lock            | relation   | {210}      | SELECT * FROM build_logs WHERE
 228 | app_3            | Lock            | relation   | {210}      | SELECT * FROM build_logs WHERE

The app queries' locks would be compatible with the long reader's, yet blocked_by points at the DROP. It sits in the queue ahead of them, and PostgreSQL doesn't let later requests jump an exclusive waiter. So a table that "nobody really uses anymore" is unavailable for as long as the oldest transaction touching it.

The standard fix is a short lock timeout (SET lock_timeout = '2s') and a retry loop around the DROP, and GitLab already has one: their migration framework wraps DDL in with_lock_retries. Their migration style guide also warns that when the table has foreign keys to another table, the DROP TABLE "is likely to stall concurrent traffic", so each foreign key goes in its own migration first. I checked in pg_locks: dropping the child takes AccessExclusiveLock on the parent (a projects table, say), because the enforcement triggers live there. The whole queue problem now applies to your hottest table.

If I were doing this on my own system I'd also rename the table before dropping it and wait a release cycle. A rename takes the same lock but holds it for a moment, and another rename undoes it. Any code path that still reads the table fails loudly while the data is still there to put back.

TRUNCATE is the softer variant. It locks only the child, because the foreign key's triggers stay where they are, and it gives the table new empty relfilenodes (47 KB of WAL for a 956 MB table). The old files go through the same unlink path on the primary and every replica. A forgotten code path gets zero rows instead of relation does not exist, and the table can go later, when there's nothing left to unlink.

What the replicas see

A DROP TABLE reaches every replica through the WAL and costs it twice, once for the lock and once for the files.

The lock. The six Standby/LOCK records make each hot standby take the same AccessExclusiveLock during replay. If a query on the replica is reading the table when the record arrives, the startup process waits for it. That's the process that applies WAL on a standby, so while it waits nothing is replayed, for any table. I ran a long read on the replica and dropped the table on the primary:

06:25:07.602 LOG:  recovery still waiting after 1002.443 ms: recovery conflict on lock
06:25:07.602 DETAIL:  Conflicting process: 236.
06:25:07.602 CONTEXT:  WAL redo at 6/290175D0 for Standby/LOCK: xid 766 db 16401 rel 16385
06:25:36.602 ERROR:  canceling statement due to conflict with recovery
06:25:36.602 DETAIL:  User was holding a relation lock for too long.
06:25:36.607 LOG:  recovery finished waiting after 30008.064 ms: recovery conflict on lock

Thirty seconds later the reader was killed, which is the default max_standby_streaming_delay. hot_standby_feedback doesn't help, because it's about row versions and not locks. The log lines are there because log_recovery_conflict_waits = on, and I'd switch that on before any change like this one. Without it the wait is invisible.

GitLab's read load balancing makes this matter more. Per their load balancing docs, a replica is skipped once it's more than max_replication_lag_time behind (60 seconds by default). max_replication_difference (8 MB) covers a replica that hasn't replicated data recently, and reads fall back to the primary when no secondary is available. A replica stuck on a lock looks exactly like a lagging one, and if something on every replica is reading the table, all ten stall on the same WAL record at the same moment. I'd be skeptical of how far that goes in practice: with the default 30 seconds the lock wait alone stays under their 60, so the files below are the bigger risk.

The files. On the primary the backend unlinks them after it has released its locks, and xact.c says why: the deletion "may take many seconds". The standby keeps the same order (xact_redo_commit() calls StandbyReleaseLockTree() first and DropRelationFiles() after), so a reader isn't held up by the unlinking itself. What differs is who does the work. On the primary it's the committing backend and everyone else carries on. On a standby the startup process does it, and replay waits.

On the 9 GB laptop table you can't see any of this: committed and replayed about 70 ms apart. The 192 GB table on Hetzner (162 heap segments plus the indexes) took 15.9 s to drop on the primary. The commit landed 15 ms after the DROP started and the backend spent the rest unlinking files with nobody waiting on it, but the replica, polled every 10 ms, applied nothing for 11 of those seconds, and every other change on the primary in that window queued behind it. A second table of 33 GB: 4.9 s on the primary, 2.9 s of held replay.

I tried the filesystem first: ext4 and XFS on loop devices over the same Hetzner NVMe, a 33 GB table dropped from each, 3.5 s against 2.7 s. After drop_caches both fell under 0.3 s, so it looked like a page-cache cost.

At 4 TB that arithmetic gets ugly. Multiply the warm rate by 4,000 segments and you get four to six minutes of frozen replay. A cost that follows the page cache is capped by RAM, though, so the multiplication overstates it for a table nobody reads. To separate the per-segment cost, the per-cached-gigabyte cost and the layout I needed a table the kernel couldn't hold. Hence the GCE machine: 128 GB of RAM, 16 local NVMe SSDs, and tables with 7.5 kB rows (one per page, so a 1 GB segment is exactly 131,072 rows and the sizes come out round). They're unlogged, which loads at 1.7 GB/s and goes through the same unlink path. Twenty-eight sessions appended to each table at once, which is hard on an allocator, and every drop was preceded by sync and drop_caches.

Same test without loop devices: two arrays of 8 SSDs, one ext4 and one XFS, the same tables loaded into each.

XFS was over a hundred times faster at both sizes.

It isn't deferred work (used space on the XFS array fell from 201 GB to 21 GB within 0.4 s of the DROP starting). Cold unlink cost follows the number of extents to free, and parallel appenders are close to the worst case for ext4's allocator. That sits badly with Hetzner, where XFS on a loop device was only a quarter faster. I can't say whether the loop device or the machine accounts for the gap.

The single 16-SSD ext4 array gives the per-segment and per-cached-gigabyte terms. Where a cache is listed, I read the first N segments back with cat and let vmtouch report what was resident just before the DROP.

A cold drop costs about 17 ms per segment from 256 GB up to 1 TB, a straight line, and each resident GB adds about 34 ms. Both terms together land within a second of the 1 TB row.

The replica tells the same story from the other side. It's a standby container on the same machine, fed by pg_basebackup, with logged 40 GB tables because logged tables load at only 0.4 GB/s. I polled pg_last_xact_replay_timestamp() on the standby every 20 ms and took the gap between the commit time and the first poll that showed it:

Both nodes were unlinking on the same array at the same moment, and each row is a single run.

vmtouch -e asks the kernel to drop a file's cached pages ahead of time, in a process nobody waits for. On GCE a fully cached 100 GB table took 5.6 s to evict, and the DROP after it took 0.77 s. The paths come from pg_relation_filepath() and are the same on every replica. A base backup or a pg_dump between the eviction and the drop reads the files straight back in.

When the table still has traffic

Premises like "nobody reads it" tend to fail in boring ways. The one I'd expect most is an append-only log: inserts still arrive, nothing updates or deletes, and the few reads that exist look at recent rows. A DROP of that table queues behind every insert transaction in flight, the lock queue forms exactly as in the output earlier, and the same short timeout and retry loop apply. The more useful change is in what you want. You want the old rows gone and the new ones kept, and a DELETE is the wrong tool for that.

If the table is range-partitioned on the column that defines "old" (created_at here), retiring a month is ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY followed by a DROP of a table nothing references anymore. That's the 80 KB of WAL and the unlink cost from the sections above, once per partition, with no dead tuples and no vacuum to follow. The DELETEs are difficult post ended with a short paragraph recommending exactly this, and after these numbers I'd move it to the top. A monthly partition also caps the replay pause, since the pause scales with what you drop and a month is a small slice of 4 TB.

Partitioning does nothing for a table that gets updates to old rows, or one that can't be repartitioned this week.

Deleting in batches

If rows have to leave a table that stays in place, the 2024 post's advice stands: delete in batches. That post called batching slower than one big DELETE and worth it for the breathing room, and gave no figure for what the room costs in WAL. I deleted the oldest 90% (25.2 million rows) in batches of 50,000, committing after each one:

CREATE PROCEDURE purge_batched(p_upto bigint, p_batch int) LANGUAGE plpgsql AS $$
DECLARE v_lo bigint := 0;
BEGIN
  WHILE v_lo < p_upto LOOP
    DELETE FROM build_logs WHERE id > v_lo AND id <= least(v_lo + p_batch, p_upto);
    v_lo := v_lo + p_batch;
    COMMIT;
  END LOOP;
END $$;

CALL purge_batched(25200000, 50000);

On the laptop the deleted rows came to about 7 GB of heap, so the delete wrote more WAL than the data it removed. One WAL record per row is what you'd expect, since a DELETE only stamps xmax on each tuple. The volume comes from the full page images, almost exactly the number of heap pages holding the deleted rows. The first change to a page after a checkpoint logs the whole 8 KB page, and a batch delete walking the table front to back makes a first change to every page it touches.

A DELETE doesn't touch the indexes. Vacuum cleans them later along with the heap, and its full page images are cheaper because a pruned page is mostly empty space that the image skips. What vacuum can't do is give back anything but empty pages at the very end of the file. Here the surviving 10% are the newest rows and sit at the end, so there's nothing for vacuum to truncate.

On Hetzner the images outnumber the heap pages the delete touched by about 8 million. That table was loaded by 16 sessions, so ids are scattered through the file and the delete revisits pages across the 15-minute checkpoints, which the laptop's sequential load doesn't. The delete never put the replica more than 127 MB behind. The vacuum afterwards did: it ran for 15 minutes, wrote another 36 GB, and the replica trailed by up to 7 GB and 38 seconds. It truncated only 5,670 pages, because the same scattering leaves surviving rows throughout the file, and the heap stayed at 162 GB. Scaled linearly to 4 TB, the delete, the first read and the vacuum add up to roughly 9 TB of WAL. That goes to ten replicas and the archive, if the scattering and the checksums behave the same way.

Getting the space back takes a rewrite: VACUUM FULL (with an exclusive lock for the whole duration, so not on a live table), pg_repack, or REPACK (CONCURRENTLY) from PostgreSQL 19, which is at release candidate stage as I write. Each of them writes the surviving rows and rebuilds the indexes, which is more WAL on top of what the delete and the vacuum already wrote.

The first SELECT after the delete

The first count(*) after the delete wrote 7.8 GB of WAL on the laptop. On the 192 GB Hetzner table it ran for 41 minutes and wrote 151 GB, against 87 seconds for the second one, and every byte of it went to the replicas. Whoever reads the table first after a mass delete, a report or a pg_dump say, ends up doing the hinting and pruning inside their own query. Vacuum still makes its own full pass afterwards.

To look at that reader on its own I repeated the load and the batched delete on the laptop in a fresh container, ran a CHECKPOINT the way the server eventually would by itself, and let SELECT count(*) FROM build_logs go first. The delete had cleared the visibility map bit (the flag that lets an index-only scan skip a page's heap) on every page it touched, so the scan made 25.2 million heap fetches. The pg_waldump view of an earlier run of the same experiment:

Type                       N    Record size        FPI size    Combined size
XLOG/FPI_FOR_HINT     959025       46992220      7688857048       7735849268
Heap2/PRUNE_ON_ACCESS 888284       98127982               0         98127982

Each heap fetch finds a row whose deleting transaction has committed, and the reader writes that down on the page (a hint bit, plus a dead marker on the index entry) so the next one doesn't have to check. With checksums on (the PostgreSQL 18 default), even that small change means the first one after a checkpoint logs the whole 8 KB page. That's the 959,025 images: the heap pages the delete touched plus the primary key's leaf pages.

Without checksums or wal_log_hints, the hint images disappear and the pruning doesn't. I repeated the run in a container initialised with --no-data-checksums (wal_log_hints = off):

The first prune of each page after a checkpoint still logs the page, one image per heap page, but a page full of dead tuples is nearly all hole, so those images average about 137 bytes. The 7.7 GB is the hint images, and checksums are what produce them. The second count(*) took 1.5 seconds, made 2 heap fetches and wrote nothing.

And the index still holds a dead entry per row. On Hetzner an ORDER BY id LIMIT 1 walked 1.48 million index pages and took 1.7 s per call until vacuum caught up.

wal_compression = zstd shrinks these images: 7517 MB of WAL down to 1458 MB on this table, and 7496 MB down to 3825 MB when I filled it with seven copies of md5(random()::text).

Copying the keepers instead

Turn the problem around and it gets small. If 90% of the rows are going away, copy the 10% that stay into a new table and drop the old one. The drop is the cheap operation from the first half, and the copy only writes WAL for what you keep.

I ran this on a fresh clone with pgbench doing 300 transactions per second against the table, 20% inserts and 80% primary key lookups on recent rows. The script first records the high-water mark (hwm below, the highest id at that moment) and copies everything up to it:

CREATE TABLE build_logs_new (LIKE build_logs INCLUDING DEFAULTS INCLUDING CONSTRAINTS EXCLUDING IDENTITY);
ALTER TABLE build_logs_new ALTER id ADD GENERATED ALWAYS AS IDENTITY;

SELECT max(id) AS hwm, clock_timestamp() AS t0 FROM build_logs \gset

-- wait until no other transaction that started before t0 is still open
SELECT pid, state, xact_start FROM pg_stat_activity
WHERE pid <> pg_backend_pid() AND datname = current_database() AND xact_start < :'t0'
\watch 0.5 min_rows=1

INSERT INTO build_logs_new OVERRIDING SYSTEM VALUE
SELECT * FROM build_logs WHERE id > 25200000 AND id <= :hwm;

ALTER TABLE build_logs_new ADD PRIMARY KEY (id);
CREATE INDEX build_logs_new_project_idx ON build_logs_new (project_id, created_at);

INSERT 0 2801207
Time: 5227.946 ms (00:05.228)
ALTER TABLE
Time: 1365.478 ms (00:01.365)
CREATE INDEX
Time: 992.124 ms

 wal_records | wal_fpi |  wal
-------------+---------+--------
     2803416 |    8155 | 882 MB

That's about a tenth of the batch delete's WAL before vacuum is even counted, and the old table hasn't been locked yet. The wait before the copy matters for correctness: identity values are handed out in order but committed in whatever order transactions finish, so without it a row can end up in neither table. I reproduced the loss with two psql sessions; waiting out every transaction older than the moment hwm was read prevents it.

Inserts kept arriving the whole time, so the swap catches up on the rows written after :hwm, and it does that while holding the lock:

BEGIN;
SET LOCAL lock_timeout = '2s';
LOCK TABLE build_logs IN ACCESS EXCLUSIVE MODE;
INSERT INTO build_logs_new OVERRIDING SYSTEM VALUE
SELECT * FROM build_logs WHERE id > :hwm;
SELECT setval(pg_get_serial_sequence('build_logs_new', 'id'),
              (SELECT max(id) FROM build_logs_new));
ALTER TABLE build_logs RENAME TO build_logs_old;
ALTER TABLE build_logs_new RENAME TO build_logs;
COMMIT;

With per-transaction pgbench logs lined up against the swap, lock to commit took 184 ms and brought 442 late rows across. The queries queued behind the lock took up to 172 ms and all finished within a millisecond of the COMMIT, against a 2 ms average for the run. Queries blocked on the lock resolve the table name again once it's granted, so they land on the new table; a blocked SELECT and a blocked INSERT both did in a test with three waiting sessions.

DROP TABLE build_logs_old took 114 ms afterwards, and the new table is 919 MB instead of 9,192 MB. The swap writes Standby/LOCK records too, so everything in the replica section applies to it, and a long read on a replica holds replay up for the same reasons.

GitLab's own swapping tables guide has the same setval step (reset_pk_sequence!), with a warning that new rows would otherwise start at id 1. My version leaves out a good deal more than that, and a real table probably needs most of it:

• Updates and deletes to rows that were already copied: the catch-up only finds new ids. A trigger on the old table recording changed ids, applied again inside the swap, is what pg_repack has used for years. REPACK (CONCURRENTLY) in PostgreSQL 19 does it with logical decoding but has no WHERE clause.
• Foreign keys: incoming ones point at the old table's OID, not its name, and have to be recreated. ADD FOREIGN KEY takes SHARE ROW EXCLUSIVE on the referenced table even with NOT VALID; VALIDATE CONSTRAINT only needs SHARE UPDATE EXCLUSIVE on the child and ROW SHARE on the parent. Outgoing ones are the projects problem from earlier.
• What LIKE doesn't copy: grants, triggers, indexes (the primary key included), fillfactor and other reloptions, replica identity, comments, publication membership, extended statistics and the owner. The sequence and indexes keep their _new names, and the new table has no statistics until you ANALYZE it.
• Prepared transactions: they don't appear in pg_stat_activity, so the wait above passes straight over them. Check pg_prepared_xacts too, and give the wait a timeout.

The 4 TB table and its ten replicas

On their table the drop should stall replay on each replica for anywhere from well under a second to about a minute. The filesystem mostly decides where. A 4 TB table is about 4,100 segments. At the 16 to 18 ms each I saw on fragmented ext4 that's about 70 seconds. On XFS it's a fraction of a second, and Hetzner's ext4 landed at 36. Cache adds a few seconds at most. GitLab's 60-second skip threshold only matters at the slow end, and I don't know their filesystem.

If it were my table I'd work through it in this order. Drop the foreign keys one per migration, each under a lock timeout with retries. Rename the table and give it a release cycle, watching pg_stat_activity and pg_locks on every replica as well as the primary, since a reader on any one of them stalls replay on that node. Turn on log_recovery_conflict_waits, check which filesystem the replicas use, and time a drop on a clone of one replica's files if there's room for one. Then drop it in the quietest hour, with a short lock timeout and a retry loop. Evicting from the page cache beforehand won't help a cold table. If the pause turns out to matter, a single 4 TB table has no other lever. Partitioned, the same unlinks would come as pauses of a few seconds.

Three things stay unmeasured. Their filesystem may be mounted with discard, which sends a TRIM for every freed block; none of my runs used it. Their replicas may or may not hold any of the table in cache. And I don't know whether their cluster logs hints, which decides the first-reader cost if rows ever have to be deleted from a table like this one instead of dropped.