Impact of Deletes on Query Performance in PostgreSQL

Summary: In this article, we will explore the impact of PostgreSQL DELETE operations on queries and the underlying principles behind this phenomenon.

Table of Contents

Marking Transaction States of Deleted Tuples

Below, in a PostgreSQL 18.4 database, we will create a table, insert some data, and perform a cleanup operation on the table data.

-- Session 1
CREATE TABLE t_large (id integer, data text);
INSERT INTO t_large (id, data)
  SELECT i, repeat('Pg', 64)
    FROM generate_series(1, 1000000) AS s(i);

CREATE INDEX large_idx ON t_large (id);
VACUUM ANALYZE t_large;

First, we create a simple function that can iterate through one tuple in each table page based on a specified off parameter, and calculate the total data size of the visited tuples:

CREATE OR REPLACE FUNCTION calc_data_size(off int)
 RETURNS bigint
AS $$
DECLARE
  i int := off;
  siz bigint := 0;
  val text;
BEGIN
  PERFORM pg_current_xact_id();
  WHILE i < 1000000 LOOP
    SELECT data INTO val FROM t_large WHERE id = i;
    siz := siz + char_length(COALESCE(val, ''));
    i := i + 50;
  END LOOP;
  RETURN siz;
END $$ LANGUAGE plpgsql;

Note: In the function body above, we called the system function pg_current_xact_id to make the function run within a transaction, allowing us to observe the performance impact of the generated WAL log volume on the entire query.

After preparing the base table and initial data, let’s start a session, begin a transaction within that session, and keep it in an idle state:

-- Session 2
BEGIN;
SELECT pg_current_xact_id();

Let’s delete a portion of the data in the table. Then, trigger a checkpoint operation to flush all dirty pages in the buffer.

-- Session 1
DELETE FROM t_large WHERE mod(id, 50) = 1;
CHECKPOINT;

Now, let’s query the deleted rows in the table and observe the query execution.

-- Session 1
EXPLAIN (analyze, buffers, wal, costs false)
  SELECT calc_data_size(1);
                            QUERY PLAN
-------------------------------------------------------------------
 Result (actual time=323.068..323.069 rows=1.00 loops=1)
   Buffers: shared hit=77306 read=2753 dirtied=20001
   WAL: records=20001 fpi=20001 bytes=162183713 buffers full=17818
 Planning Time: 0.020 ms
 Execution Time: 323.118 ms
(5 rows)

As you can see, when the pages are clean, querying the deleted records will generate WAL logs recording the page image. 20,001 pages were modified, resulting in 20,001 WAL log records, with a total WAL log volume of 155 MB. At the same time, this also brings a certain performance penalty to the query.

Page Cleanup During Query Execution

Let’s end the idle transaction in session two. Trigger another checkpoint operation to flush all dirty pages in the buffer.

-- Session 2
COMMIT;
CHECKPOINT;

Next, let’s query the live rows in the table and observe the query execution.

-- Session 1
EXPLAIN (analyze, buffers, wal, costs false)
  SELECT calc_data_size(2);
                            QUERY PLAN
-------------------------------------------------------------------
 Result (actual time=332.218..332.219 rows=1.00 loops=1)
   Buffers: shared hit=80000 dirtied=19592
   WAL: records=19592 fpi=19592 bytes=155893544 buffers full=17046
 Planning Time: 0.020 ms
 Execution Time: 332.233 ms
(5 rows)

It can be seen that when a query accesses pages containing deleted tuples, it cleans up the accessed pages and records the corresponding WAL logs. 19,592 pages were modified, producing 19,592 WAL log records, with a total WAL log volume of 149 MB. Similarly, this also brings a certain performance penalty to the query.

Performance of Redrock Postgres

Below, we execute the same test case in a Redrock Postgres database. When querying the deleted rows in the table, the observed query execution is as follows:

EXPLAIN (analyze, buffers, wal, costs false)
  SELECT calc_data_size(1);
                      QUERY PLAN
-------------------------------------------------------
 Result (actual time=68.575..68.576 rows=1.00 loops=1)
   Buffers: shared hit=1 read=12 dirtied=3
   WAL: records=1 fpi=3 bytes=11376
 Planning Time: 0.023 ms
 Execution Time: 68.630 ms
(5 rows)

The output shows that the previous query operation modified only 3 pages on the undo segment to record the transaction state, and did not modify any data pages in the table. Next, after the idle transaction ends, we query the active records in the table:

EXPLAIN (analyze, buffers, wal, costs false)
  SELECT calc_data_size(2);
                      QUERY PLAN
-------------------------------------------------------
 Result (actual time=89.976..89.977 rows=1.00 loops=1)
   Buffers: shared hit=1 read=2 dirtied=3
   WAL: records=1 fpi=3 bytes=14496
 Planning Time: 0.023 ms
 Execution Time: 89.993 ms
(5 rows)

The output shows that the preceding query operation still modified only 3 pages on the undo segment, without modifying any data pages in the table.

Test Conclusion

The performance advantage of PostgreSQL’s DELETE comes at a cost; the subsequent marking transaction states of deleted tuples and page cleanup both require page modifications again. PostgreSQL adopts mark-for-deletion + delayed cleanup to trade for DELETE speed, but must bear the subsequent maintenance costs. Redrock Postgres, upon deletion, records rollback logs and transaction states, sacrificing DELETE speed to ensure query stability. In actual production environments:

• For scenarios with frequent DELETE operations but controllable query pressure, PostgreSQL will perform better;
• For core business scenarios where DELETEs and queries alternate frequently, such as banking transaction systems and payment platforms, Redrock Postgres will be more stable.