By John Doe May 18, 2026
Summary: In this article, we will explore the performance characteristics of PostgreSQL delete operations and the underlying principles behind it.
Table of Contents
Table Data Deletion Scenario: Use Case
To clearly observe the WAL log overhead of DELETE operations, first set the parameter full_page_writes to off.
SHOW server_version;
server_version
----------------
18.4
(1 row)
SHOW full_page_writes;
full_page_writes
------------------
off
(1 row)
Next, we will create a table in the PostgreSQL database, insert some data, perform cleanup operations on the table data, and check the space occupied by the table.
CREATE TABLE t_large (id integer, data text);
INSERT INTO t_large (id, data)
SELECT i, repeat('Pg', 512)
FROM generate_series(1, 1000000) AS s(i);
VACUUM ANALYZE t_large;
CHECKPOINT;
SELECT pg_size_pretty(pg_table_size('t_large'));
pg_size_pretty
----------------
1116 MB
(1 row)
A checkpoint operation is executed to flush all dirty pages in the buffer. After that, EXPLAIN ANALYZE can be used to check detailed information of modified pages.
EXPLAIN (analyze, buffers, wal, costs false)
DELETE FROM t_large;
QUERY PLAN
--------------------------------------------------------------------------------
Delete on t_large (actual time=1157.257..1157.258 rows=0.00 loops=1)
Buffers: shared hit=1192561 read=93155 dirtied=142863 written=11970
I/O Timings: shared read=9.894 write=24.343
WAL: records=1000000 bytes=54000000 buffers full=26
-> Seq Scan on t_large (actual time=0.210..674.204 rows=1000000.00 loops=1)
Buffers: shared hit=49703 read=93155 written=11970
I/O Timings: shared read=9.894 write=24.343
Planning:
Buffers: shared hit=5
Planning Time: 0.073 ms
Execution Time: 1157.416 ms
(11 rows)
The above DELETE operation removed 1,000,000 records, modified 142,863 pages, generated 1,000,000 WAL log entries with a total WAL log size of 51 MB.
Wait for approximately 1 minute (the default value of autovacuum_naptime is 1 minute), then check the storage space occupied by the table.
SELECT pg_size_pretty(pg_table_size('t_large'));
pg_size_pretty
----------------
827 MB
(1 row)
After full-table deletion, standard PostgreSQL only reclaims partial storage space of deleted tuples.
Performance of Redrock Postgres
Let’s run the same test cases on Redrock Postgres as above. Check the current undo segment usage in the database before executing deletion operations.
SELECT oid, relname, pg_size_pretty(pg_relation_size(oid)) AS undsize
FROM pg_class WHERE relkind = 'u'
ORDER BY pg_relation_size(oid) DESC LIMIT 4;
oid | relname | undsize
------+-----------+---------
8102 | pg_undo_2 | 41 MB
8103 | pg_undo_3 | 1040 kB
8104 | pg_undo_4 | 1040 kB
8105 | pg_undo_5 | 1040 kB
(4 rows)
The result shows that the preceding INSERT operations generated 41 MB of undo logs. Then perform the same deletion operation.
EXPLAIN (analyze, buffers, wal, costs false)
DELETE FROM t_large;
QUERY PLAN
--------------------------------------------------------------------------------
Delete on t_large (actual time=1368.790..1368.791 rows=0.00 loops=1)
Buffers: shared hit=2054654 read=110057 dirtied=150071 written=26645
I/O Timings: shared read=5.627 write=100.487
WAL: records=1007204 bytes=125592003 buffers full=9792
-> Seq Scan on t_large (actual time=0.251..489.609 rows=1000000.00 loops=1)
Buffers: shared hit=32931 read=109927 written=17821
I/O Timings: shared read=4.972 write=32.084
Planning:
Buffers: shared hit=2
Planning Time: 0.106 ms
Execution Time: 1369.009 ms
(11 rows)
This DELETE operation deleted 1,000,000 records, modified 150,071 pages, produced 1,007,204 WAL log entries with a total WAL log volume of 120 MB. Check the undo segment usage again.
SELECT oid, relname, pg_size_pretty(pg_relation_size(oid)) AS undsize
FROM pg_class WHERE relkind = 'u'
ORDER BY pg_relation_size(oid) DESC LIMIT 4;
oid | relname | undsize
------+------------+---------
8117 | pg_undo_17 | 56 MB
8102 | pg_undo_2 | 41 MB
8104 | pg_undo_4 | 1040 kB
8105 | pg_undo_5 | 1040 kB
(4 rows)
The output indicates that this DELETE operation generated 56 MB of undo logs.
After a 1-minute waiting period, verify the storage usage of the target table.
SELECT pg_size_pretty(pg_table_size('t_large'));
pg_size_pretty
----------------
24 kB
(1 row)
Upon full-table deletion, Redrock Postgres can fully reclaim the storage space occupied by deleted tuples.
Cleanup Costs After DELETE
In fact, after an DELETE operation in PostgreSQL, there is a subsequent delayed secondary modification process.
When PostgreSQL deletes rows in a table, it records the transaction ID of the deletion transaction in the tuple header, known as the xmax of the tuple. PostgreSQL stores transaction state information in the Commit Log (CLOG). Checking the states of a large number of transactions in the CLOG consumes significant resources, so PostgreSQL caches transaction states directly in the tuple header. For example, if a SELECT statement confirms that the xmax transaction has been completed, PostgreSQL marks this state in the so-called “hint bits” of tuples. This process dirties table pages, which are then written to disk, and requires recording WAL logs.
Additionally, for deleted tuples, it is necessary to wait until a VACUUM operation has been performed on the entire table and indexes before the space of the deleted tuples can be fully reused.
In other words, the DELETE operation in PostgreSQL actually shifts and distributes part of its costs to subsequent SELECT and VACUUM operations.
Test Conclusions
The performance advantages of PostgreSQL DELETE are conditional, as it defers part of the overhead to later SELECT queries and VACUUM tasks. In actual production environments:
- PostgreSQL delivers better performance for scenarios dominated by insert-delete or insert-query workloads;
- For mixed business scenarios with frequent updates and deletions plus massive complex analytical queries, Redrock Postgres keeps more stable performance.