Your database is ticking along nicely - until a simple DELETE brings it to its knees. What went wrong? While we tend to focus on optimizing SELECT and INSERT operations, we often overlook the hidden complexities of DELETE. Yet, removing unnecessary data is just as critical. Outdated or irrelevant data can bloat your database, degrade performance, and make maintenance a nightmare. Worse, retaining some types of data without valid justification might even lead to compliance issues.

At first glance, the DELETE command seems straightforward. Even the PostgreSQL documentation provides simple examples like:

DELETE FROM films WHERE kind <> 'Musical';
DELETE FROM films;

These queries might work effortlessly on your development machine, where only a few hundred records exist. But what happens when you try running a similar DELETE in production, where datasets are orders of magnitude larger?

In this article, we’ll uncover why DELETE operations demand careful consideration and explore how to handle them effectively.

What really happens when you DELETE the data?

For every row the statement matches, PostgreSQL does roughly this:

  1. Row identification: same as a SELECT, the executor finds the rows visible to the statement's snapshot.
  2. Lock and BEFORE DELETE triggers: if a BEFORE DELETE trigger exists, the row is locked first and the trigger runs against the locked version.
  3. Stamping xmax: the transaction writes its xid into the tuple's xmax. That write is the delete and the row lock at once; there's no separate lock entry (where PostgreSQL stores row locks shows the bits). The tuple stays put. TOASTed values get the same stamp on every chunk, and chunks are about 2 KB, so a 1 MB value means roughly 500 more deletes.
  4. WAL: one record per row and one per TOAST chunk, written as each row is stamped.
  5. AFTER triggers and foreign keys: ON DELETE CASCADE and the other FK actions are AFTER ROW triggers, queued with your own and fired at the end of the statement.

DELETE doesn't touch indexes at all. Every entry still points at the dead tuple after commit, until a later index scan flags it, a B-tree page about to split clears it, or VACUUM removes it. VACUUM Is a Lie shows what that leaves in a B-tree.

After commit the delete is visible to every new snapshot, but nothing is physically removed. That's the bloat: dead rows sit in the table and every index until VACUUM gets to them.

Per row, DELETE is cheaper than UPDATE. A non-HOT UPDATE writes a new tuple and an entry in every index; DELETE writes neither. HOT updates skip the index inserts when no indexed column changes and the new version fits on the page, which comes down to fillfactor and what you index. Cascades aren't DELETE-only either (ON UPDATE CASCADE exists). The DELETE's cost arrives later, as cleanup, and a big one hands VACUUM all of it in one go.

Foreign keys pointing at the table

This is a common way for a simple DELETE to take production down. Each foreign key referencing the table installs a trigger, and for every deleted parent row it queries the child table. For NO ACTION and RESTRICT:

SELECT 1 FROM ONLY order_items x WHERE order_id = $1 FOR KEY SHARE OF x;

For CASCADE it's DELETE FROM ONLY order_items WHERE order_id = $1. PostgreSQL requires an index on the parent's key, not on the referencing column. Without one on order_items.order_id, each lookup is a sequential scan of the child table.

PostgreSQL 18, 100,000 orders, 1,000,000 items, no index on order_id, deleting 1,000 orders whose items were already gone:

 Delete on orders (actual time=0.400..0.400 rows=0.00 loops=1)
   Buffers: shared hit=2016
   ->  Index Scan using orders_pkey on orders (actual time=0.009..0.096 rows=1000.00 loops=1)
         Index Cond: (id <= 1000)
         Index Searches: 1
         Buffers: shared hit=10
 Planning:
   Buffers: shared hit=18
 Planning Time: 0.095 ms
 Trigger for constraint order_items_order_id_fkey: time=30245.918 calls=1000
 Execution Time: 30246.675 ms

After CREATE INDEX ON order_items (order_id), the same statement:

 Trigger for constraint order_items_order_id_fkey: time=6.413 calls=1000
 Execution Time: 6.888 ms

The trigger's time and reads aren't in the plan tree or its Buffers line. The Trigger for constraint row is the only trace, and plain EXPLAIN doesn't print it. If you delete from a parent, index the FK columns pointing at it.

And then comes AUTOVACUUM

Autovacuum picks a table up once dead tuples pass autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples (capped by autovacuum_vacuum_max_threshold since 18). Then three passes, walked through byte by byte in VACUUM at the Page Level:

  1. Heap scan. Skips all-visible pages via the visibility map. Tuples dead to the oldest snapshot still alive are pruned; their space is freed, but the line pointers stay as LP_DEAD because indexes still point at them.
  2. Index vacuum. Reads every index end to end and removes entries for the dead TIDs, whether you deleted ten rows or ten million (14+ can skip this when very few pages are affected).
  3. Heap vacuum. LP_DEAD pointers become reusable, the free space map and visibility map get updated, and an empty tail of the table is truncated.

TOAST tables are vacuumed separately, with their own thresholds. At the end VACUUM updates relpages, reltuples and relallvisible. Column statistics are ANALYZE's job, on a separate trigger.

Autovacuum is throttled: after autovacuum_vacuum_cost_limit worth of I/O it sleeps autovacuum_vacuum_cost_delay (2 ms) and continues. It can only remove rows deleted before the oldest live snapshot, so a long transaction, a forgotten replication slot or hot_standby_feedback will leave a big DELETE's bloat in place. That's usually the answer when VACUUM "did nothing".

Before 17, dead TIDs fit into at most 1 GB of maintenance_work_mem. Fill it and VACUUM runs the whole index pass and starts over, so a huge DELETE meant several full scans of every index. 17 removed the cap.

Without autovacuum, dead tuples never go away and the table grows until the disk is full.

Further considerations

For what seems like a simple DELETE, a surprising amount of work has already taken place, but the complexity doesn’t stop there. DELETE operations can introduce additional challenges, particularly when replication, resource contention, or the size of the operation comes into play.

Replication is asynchronous by default, so the commit doesn't wait for a standby at all. With synchronous_standby_names set, the commit record waits for confirmation, and synchronous_commit decides what's confirmed: remote_write, on (flushed), or remote_apply (replayed). Only remote_apply lets a slow standby replay hold up the primary.

On a standby, query cancellations come from replaying the cleanup that follows a DELETE: page pruning, index entry removal, VACUUM truncating the table. A query that still needs the removed rows, or holds a lock the truncation wants, is cancelled after max_standby_streaming_delay. hot_standby_feedback prevents that by holding cleanup back on the primary, at the price of bloat there.

The size of the DELETE operation also plays a critical role. Small DELETEs, such as removing a single row, tend to have minimal impact. However, as the size of the operation grows, so does the volume of WAL records generated. Large DELETEs can overwhelm the system, slowing down transactions and straining the replication process. Standby servers must work harder to process the incoming WAL stream, which can bottleneck performance if their throughput is insufficient.

Resource contention adds yet another layer of complexity, particularly for large DELETEs. Generating WAL records, handling regular transactional workloads, and running background processes can collectively push the system towards I/O saturation. This creates competition for CPU and memory resources, leading to slower operations across the board.

Finally, once the data is marked for deletion, the autovacuum process must eventually step in to physically remove it. This introduces its own set of challenges, as autovacuum must deal with the same resource contention and I/O demands, compounding the overall impact of the initial DELETE operation.

Soft-deletes are not the solution

Soft-deletes might seem like an easy way to sidestep the complexities of traditional DELETE operations. Setting deleted_at can be a HOT update, but only while nothing indexes it. The usual partial index WHERE deleted_at IS NULL does, so every soft-delete becomes a non-HOT update with a new entry in every index on the table. And the data is still there.

While soft-deletes can provide a simple way to implement “undo” functionality, they raise serious questions about data consistency. For instance, do you only mark the main entity as deleted, or do you also cascade the status to all related records in referenced tables? Failing to cascade properly can leave your database in an inconsistent state, making it difficult to maintain data integrity.

Soft-deletes also require consideration in your application logic. Every query must include appropriate filters to exclude “deleted” rows, which can complicate query design and increase the risk of oversights. One missed filter could expose data that should no longer be visible, leading to potential security or business logic issues.

Finally, soft-deletes don’t solve the problem - they do merely postpone it. The data is still in your database, consuming storage and potentially contributing to performance degradation over time. Sooner or later, you’ll need to deal with the actual removal of this data, bringing you back to the same challenges that DELETEs pose in the first place.

PostgreSQL 18 added temporal keys (PRIMARY KEY and UNIQUE with WITHOUT OVERLAPS, built on the range types and exclusion constraints from Beyond Start and End) and temporal foreign keys with PERIOD. For deletes, not much yet: temporal foreign keys support only NO ACTION, and ON DELETE CASCADE or RESTRICT fails on 18 and 19beta4 with unsupported ON DELETE action for foreign key constraint using PERIOD. DELETE ... FOR PORTION OF isn't in 19beta4 either.

Batching is the answer

Giving PostgreSQL time to process and catch up with large-scale changes is critical when dealing with operations like DELETEs. The core issue here is the duration and magnitude of the transaction. The shorter the transaction and the fewer changes made, the better PostgreSQL can manage and reconcile those changes. This principle is universal across all database operations and underscores the importance of minimizing the impact of individual transactions.

While you can optimize certain aspects, like row identification (using indexes, clustering, or similar techniques), larger datasets demand a more strategic approach - batching. For example, deleting 1 million rows in a single transaction is a textbook case of what not to do. Instead, splitting the operation into smaller batches, such as deleting 10,000 rows across 100 iterations, is far more effective.

Will this method be faster than performing one massive DELETE? Likely not, especially if you include a wait time between batches to allow PostgreSQL to handle other workloads. However, the trade-off is worthwhile. By batching, you give PostgreSQL more breathing room to manage changes without overwhelming regular transactional workloads - unless, of course, you’ve scheduled dedicated maintenance time for the operation.

How to Batch DELETEs

PostgreSQL has no DELETE ... LIMIT, so the batch gets picked in a subquery (or a CTE). Instead of:

DELETE FROM films WHERE kind <> 'Musical';

the common trick deletes by physical address, ctid, repeated until DELETE 0 (\watch in psql):

DELETE FROM films
WHERE ctid IN (
    SELECT ctid FROM films
    WHERE kind <> 'Musical'
    LIMIT 250
);

ctid is the (page, line pointer) pair from Inside PostgreSQL's 8KB Page. The plan you want ends in a Tid Scan:

 Delete on films
   ->  Nested Loop
         ->  HashAggregate
               Group Key: "ANY_subquery".ctid
               ->  Subquery Scan on "ANY_subquery"
                     ->  Limit
                           ->  Seq Scan on films films_1
                                 Filter: (kind <> 'Musical'::text)
         ->  Tid Scan on films
               TID Cond: (ctid = "ANY_subquery".ctid)

Check with EXPLAIN first; a hash semi join reads the whole table every batch. Within one statement ctid is safe: if another session updates a row meanwhile, the recheck fails on the new version and the row waits for the next batch. Reading ctids in one statement and deleting in another isn't safe.

The subquery's sequential scan starts at page 0 and walks every page earlier batches emptied. On a 1-million-row table (74 MB, PostgreSQL 18), with about 810,000 rows gone, a batch of 250 read 5,457 pages. VACUUM between batches doesn't help; empty pages still get read.

That was with synchronize_seqscans = off. With the default on, tables over a quarter of shared_buffers (128 MB here) resume scans where the last one stopped, and the same batch read 6 pages. Don't rely on it: any other seq scan moves the start point, and smaller tables always start at 0. Batch by an indexed key instead:

DELETE FROM films
WHERE id IN (
    SELECT id FROM films
    WHERE kind <> 'Musical' AND id > :last_id
    ORDER BY id
    LIMIT 250
)
RETURNING id;

Feed the highest returned id back as :last_id. For retention deletes, a range on an indexed timestamp does the same.

Planning for Autovacuum

Batching alone doesn’t directly solve the issue of autovacuum catching up with the changes. You’ll need to plan for that separately. Adjusting autovacuum settings or triggering manual VACUUM and VACUUM ANALYZE runs can help manage bloat created during the DELETE process. However, disabling autovacuum is rarely advisable unless you’ve carefully planned for manual maintenance throughout the batch operations. Skipping this step risks leaving behind performance-impacting bloat that will require even more effort to address later.

Drop whole range of data with partitioning

Data that is naturally segmented (by creation time, for example) makes it an excellent candidate for removal through partitioning. Partitioning allows you to bypass DELETE operations altogether by detaching and dropping the relevant partitions (DETACH PARTITION ... CONCURRENTLY, since PostgreSQL 14, avoids an exclusive lock on the parent). This approach is far more efficient and avoids the overhead of scanning, locking, and marking rows as deleted, effectively eliminating the problem with the bloat.

While partitioning adds some complexity to schema design and query planning, it can provide significant performance benefits for DELETE-heavy workloads, especially when combined with automated partition management.

Conclusion

DELETE operations are often a source of unpleasant surprises - not just by affecting performance and creating bloat, but by striking back at the times we least expect. To handle them effectively, focus on strategies such as batching, monitoring autovacuum, or leveraging partitioning for large datasets. By considering DELETE operations during schema design, you can maintain an efficient database, reduce maintenance headaches, and ensure it continues to run smoothly as your data grows.