How GitLab would drop a 4 TB table in PostgreSQL without stalling replicas

An inverse problem to 2017

GitLab has a public history of deleting the wrong thing: in January 2017, an engineer trying to restore replication removed a data directory he thought was on the secondary, but it was on the primary. The post-mortem published afterward remains a reference on database incidents.

At PGConf.EU in Valencia, people from GitLab brought the author of the boringSQL newsletter the opposite problem: a 4 TB archive table, on a primary with ten replicas, that nobody writes to and that nobody should read anymore. They want to delete it on purpose, with the smallest possible impact. The boringSQL author tested the obvious answer, DROP TABLE, and found that it depends on three things: whether anyone still reads the table, how much of it the kernel keeps cached, and how the files are laid out on disk.

What DROP TABLE really costs

The tests ran on PostgreSQL 18.6, with data checksums on and autovacuum off, on a synthetic log table (build_logs, 28 million rows, secondary index on project_id and created_at) replicated between a laptop, a pair of Hetzner servers, and a GCE machine with local SSDs. On a laptop with 9.2 GB of data, DROP TABLE took 67 ms and moved only 81,737 bytes of WAL, almost all of it catalog rows leaving pg_class and pg_attribute, plus six Standby/LOCK records, one per object with its own storage.

The data itself never goes through WAL on the way out: the commit record carries a list of relfilenodes, and whoever replays it, a replica or the primary itself after a crash, deletes those files. The critical point is the lock: DROP TABLE requires an AccessExclusiveLock, which conflicts with a simple SELECT. In a wait queue, PostgreSQL doesn't let later requests jump ahead of someone already waiting for an exclusive lock.

In short: an old, forgotten transaction that only reads the table holds up the DROP in the queue, and the DROP holds up every application query that arrives after it. A table that "nobody uses anymore" stays unavailable for as long as the oldest transaction still open on it remains open.

Replicas pay twice

On replicas, the same AccessExclusiveLock is applied during WAL replay. If a query on the replica is reading the table when the record arrives, the startup process, responsible for applying WAL, waits, and nothing else gets replicated in the meantime. In the boringSQL author's test, the read was cancelled after 30 seconds, the default value of max_standby_streaming_delay, with the log showing the recovery conflict.

This matters even more for anyone using read load balancing like GitLab's, which drops a replica when it falls more than max_replication_lag_time (60 seconds by default) behind. A replica stuck on a lock looks exactly like a lagging replica, and if something reads the table on all ten at the same time, all of them stall on the same WAL record. With the default 30-second timeout this still stays below the 60-second limit, so the second cost is bigger: unlinking the files.

On the primary, the backend itself deletes the files, after releasing the locks, and the rest of the system keeps moving. On the replica, it's the startup process that does this work, and replay waits. On a 192 GB table on the Hetzner pair, DROP took 15.9 s on the primary, but the replica went 11 seconds without applying anything, with every concurrent change on the primary queued up behind it.

The filesystem decides unlink speed

The boringSQL author broke the cost down into three terms: per segment, per gigabyte cached, and per physical layout. With a cold cache, each 1 GB segment costs about 17 ms to delete, from 256 GB up to 1 TB, in a nearly linear fashion; each gigabyte resident in the page cache adds another 34 ms. Extrapolated to 4,000 segments, a 4 TB table can stall replay for four to six minutes, bounded by available RAM.

The comparison between filesystems is surprising:

XFS was more than a hundred times faster on both tables, because the cost of a cold delete follows the number of extents to free, and parallel writes (28 concurrent sessions, in this case) are close to the worst-case scenario for ext4's allocator. Anyone running tables at this scale on ext4 carries an infrastructure cost that XFS simply doesn't have.

When the table still has traffic

If the premise that nobody reads the table turns out to be wrong, and it often fails in mundane ways, like an append-only log still receiving inserts, the answer doesn't change families, but it changes tactics:

• TRUNCATE locks only the child table, because foreign-key triggers live on the parent table; it gives new, empty relfilenodes, with minimal WAL, and any forgotten code gets zero rows instead of an error
• Renaming the table and waiting out a release cycle before the DROP exposes any still-live reads loudly, with the data still there to restore
• SET lock_timeout = '2s' with a retry loop keeps the DROP from sitting indefinitely in the lock queue

GitLab's migration guide already recommends this loop, wrapped in with_lock_retries, and goes further: when the table has foreign keys, each one must come out in its own migration, because dropping them together also requires an AccessExclusiveLock on the referenced table.

is likely to stall concurrent traffic

GitLab, migration style guide

In the boringSQL author's tests, dropping a child table did in fact take the exclusive lock on the parent table (a projects table, in the example), because that's where the foreign-key validation triggers live. The queuing problem then applies to the hottest table in the system.

Deleting in batches costs more WAL than it looks

When rows need to come out of a table that's still in use, the known practice is to delete in batches with a COMMIT between them, via a procedure like:

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 $$;

On the laptop, deleting 25.2 million rows (90% of the table) generated 8,179 MB of WAL to remove about 7 GB of heap data: the DELETE wrote more WAL than the table itself occupied. The reason is the full page image: the first change to a page after a checkpoint writes the entire 8 KB page, and a batch that sweeps the table from start to end touches every page for the first time.

On the Hetzner pair, with 540 million rows deleted, the batch took 36 min 37 s and wrote 229 GB of WAL. The following vacuum ran for 15 minutes, wrote another 36 GB, and lagged the replica by up to 7 GB and 38 seconds, truncating only 5,670 pages, because the surviving rows are scattered across the whole file, not concentrated at the end. Extrapolated to 4 TB, the delete, the first read after it, and the vacuum add up to something close to 9 TB of WAL, replicated to all ten replicas and to the archive.

The answer that was already right: partition first

If the table is range-partitioned on the column that defines "old" (created_at, in the example), retiring a month is an ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY followed by a DROP TABLE on something nothing else references:

ALTER TABLE build_logs DETACH PARTITION build_logs_y2024m01 CONCURRENTLY;
DROP TABLE build_logs_y2024m01;

This reduces the entire problem to the few kilobytes of WAL and the unlink cost measured above, but once per partition, with no dead tuples and no vacuum to clean up afterward. Since the damage from replay scales with the size of what gets deleted, a month is always a small slice of 4 TB, which caps the impact ceiling on any replica. Partitioning doesn't solve, however, a table that receives updates on old rows or one that can't be repartitioned this week.

What remains open

Recovering the physical space after a mass delete requires rewriting the table: VACUUM FULL (exclusive lock for the entire duration, unworkable on a live table), pg_repack, or the new REPACK (CONCURRENTLY), arriving in PostgreSQL 19, still a release candidate at the time of testing. Each option rewrites the surviving rows and rebuilds indexes, adding more WAL on top of what the delete and the vacuum already generated.

For anyone running a primary with read replicas and a load balancer that decides based on replication lag, like GitLab's own, the practical point is to measure before acting: turn on log_recovery_conflict_waits, check the filesystem under the large tables, and decide, case by case, between TRUNCATE, batching with retries, or partitioning. None of these tactics is new, but the boringSQL author's numbers give them a concrete scale, and the difference between ext4 and XFS measured there is worth an infrastructure audit on its own.

Source: Planet PostgreSQL (https://postgr.es/p/9AI)