The only scalable delete in Postgres is DROP TABLE

Written by Tom Pang

Counterintuitively, large DELETEs add work to the database.

From experience we can plainly claim the following: the most scalable Postgres data-deletion strategies revolve around deleting entire tables.

Individual row DELETE is fine at a small scale. However, big batch DELETE operations don't immediately free up physical disk space, add write and replication overhead, and are ultimately not good for large scale row cleanup.

If your application needs to delete large amounts of data, even very rarely, we recommend moving towards schema designs that let you express that as a DROP TABLE or a TRUNCATE.

Let's study why this is by looking at how DELETE works in Postgres.

Deletes hurt

When rows mutate, Postgres can maintain multiple versions of the same row, so that different transactions can see row values as of the time they were queried. This is Postgres' implementation of "Multi-Version Concurrency Control" (MVCC) and a core principle of its design.

Postgres makes an intentional tradeoff here. It stores modified and deleted rows alongside current ones, relying on transaction IDs and visibility maps to skip over "dead tuples." Later on, a vacuum process comes along and says, "Hey, these bytes in this heap page are now free, you can overwrite them."

.

If you're running a DELETE over a large amount of data, you can imagine how it adds work to every read query and autovacuum. Be aware that using foreign keys and CASCADE for deletions can cause a single row delete to delete gigabytes of data, resulting in the same set of problems.

Drop DELETE for DROP

In contrast, DROP TABLE and TRUNCATE require a heavyweight AccessExclusiveLock on the table, but are loosely independent of data size. At the physical layer they remove files from the operating system directly, plus sweep the Postgres buffer cache to remove pages related to the table.

That sweep can be less trivial on databases with large shared buffers, but it is only a metadata sweep. Postgres keeps a small fixed-size header (a BufferDesc, padded to 64 bytes) for every 8KB buffer, and dropping a table scans those headers, not the pages themselves. At 64 bytes per 8KB page, that's 1/128th of the cache size: with 128GB of shared buffers, you are sweeping only ~1GB of memory, sequentially, which is very fast on modern hardware.

DROP TABLE and TRUNCATE scale much better than DELETE. They produce zero dead tuples, zero vacuum debt, zero work for readers. They immediately free up space for the operating system.

A performant one-off delete

One common case where folks need to delete large amounts of data is "my table is full of junk due to a bug." We encountered this recently in an internal observability tool. A bug caused the tool to write millions of rows that we wanted to delete from the database. The bad rows had an old updated_at timestamp; anything with a recent one was designed to be kept. There were only a few hundred thousand rows to keep; most of the data was junk.

For this case, especially because "lock the database for minutes" was not an issue at all, we performed some surgery, leaning on Postgres' transactional DDL:

1. BEGIN


2. Explicit LOCK TABLE ... IN ACCESS EXCLUSIVE MODE on the table in question; this prevents other transactions from reading or writing the table, so we get consistent data.


3. Create a temporary table to hold just the kept data, like so:
   

CREATE TEMP TABLE temp_keep_big_table AS

  SELECT * FROM big_table

  WHERE updated_at >= '2026-04-01';

1. TRUNCATE big_table;


2. 
   INSERT INTO big_table SELECT * FROM temp_keep_big_table;. In our example, this took a handful of minutes to process on a very small instance with hundreds of thousands of rows.

You can go further still. Partitioning in Postgres is recursive, so you could partition the top level by LIST (e.g., a "visible" rows partition), then partition the "not visible" child table by RANGE to age out old data.

Go forth and DROP

Structuring your schema and application so that large-scale DELETE becomes DROP or TRUNCATE can dramatically improve your database. It helps reduce read query latency in some cases, mitigates replication lag spikes, and overall improves database health.