Impact of Inserts on Query Performance in PostgreSQL

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

Table of Contents

Test Cases

First, we enable data checksums when initializing the PostgreSQL database:

initdb --data-checksums

Note: PostgreSQL 18 changed initdb default to enable data checksums. Checksums can be disabled with the new initdb option --no-data-checksums.

Create a table, insert a certain amount of initial data, and perform a VACUUM operation on the table data:

CREATE TABLE test_table(id int PRIMARY KEY, info text);

INSERT INTO test_table
  SELECT n, md5(random()::text)
    FROM generate_series(1, 1000000) as n;

VACUUM (analyze) test_table;

Next, insert a small portion of additional data — accounting for 10% of the total number of rows in the table. Then trigger a checkpoint operation to flush all dirty pages in the buffer:

INSERT INTO test_table
  SELECT n, md5(random()::text)
    FROM generate_series(1000001, 1100000) as n;

CHECKPOINT;

Now, we query 3 records that reside on the same page and observe the WAL (Write-Ahead Log) record information generated by these 3 queries:

EXPLAIN (analyze, buffers, wal, costs false)
  SELECT ctid, info FROM test_table WHERE id = 1050001;
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Index Scan using test_table_pkey on test_table (actual time=0.063..0.065 rows=1 loops=1)
   Index Cond: (id = 1050001)
   Buffers: shared hit=4 dirtied=1
   WAL: records=1 fpi=1 bytes=8233
 Planning Time: 0.099 ms
 Execution Time: 0.090 ms
(6 rows)

EXPLAIN (analyze, buffers, wal, costs false)
  SELECT ctid, info FROM test_table WHERE id = 1050002;
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Index Scan using test_table_pkey on test_table (actual time=0.065..0.067 rows=1 loops=1)
   Index Cond: (id = 1050002)
   Buffers: shared hit=4
 Planning Time: 0.129 ms
 Execution Time: 0.108 ms
(5 rows)

CHECKPOINT;

EXPLAIN (analyze, buffers, wal, costs false)
  SELECT ctid, info FROM test_table WHERE id = 1050003;
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Index Scan using test_table_pkey on test_table (actual time=0.095..0.099 rows=1 loops=1)
   Index Cond: (id = 1050003)
   Buffers: shared hit=4 dirtied=1
   WAL: records=1 fpi=1 bytes=8233
 Planning Time: 0.164 ms
 Execution Time: 0.147 ms
(6 rows)

Above, before querying the 3rd record, we triggered a checkpoint operation to flush all dirty pages in the buffer. It can be seen that when the page is not dirty, querying new records will generate WAL logs that record full page images. At the same time, this also brings a certain degree of performance overhead to the query. The test results here are not obvious because SELECT queries do not involve flushing WAL logs during transaction commits. The results would be different if it occurred within a transaction block.

Next, let’s confirm the physical locations of the 3 records queried earlier. As shown below, these 3 records all reside on the same page with page number 8750:

SELECT ctid, info FROM test_table
  WHERE id > 1050000 AND id <= 1050003;
   ctid   |               info
----------+----------------------------------
 (8750,1) | 3b93bf1cbf252960adf883830392c32a
 (8750,2) | 1ef1dd34d614bec1ff42ed41b1b83db8
 (8750,3) | d3e2c47bb12853a9392c850bc37b307a
(3 rows)

Why was no WAL record generated when querying the 2nd record? Let’s refer to the explanation in the PostgreSQL source code:

This routine might get called many times on the same page, if we are making the first scan after commit of an xact that added/deleted many tuples. So, be as quick as we can if the buffer is already dirty.

Cleanup Cost After INSERT

After an INSERT operation in PostgreSQL is completed, there is actually a delayed secondary modification process that occurs afterward.

When PostgreSQL inserts data into a table, it creates a new tuple. The header of the tuple records the transaction ID that created the tuple, which is called the tuple’s xmin. PostgreSQL stores the current state information of each transaction in the Commit Log (CLOG). Checking the status of a large number of transactions in the CLOG consumes significant resources, so PostgreSQL caches transaction state information directly in the tuple header. For example, if it is detected that the xmin transaction has completed when executing a SELECT statement, PostgreSQL saves this information to the so-called “hint bits” of the tuple. This process dirties the table page (marking it as needing to be written to disk) and requires recording WAL logs.

In other words, the INSERT operation in PostgreSQL actually shifts and distributes part of its cost to subsequent SELECT operations.

Conclusion

The checksum feature for data pages has been supported since PostgreSQL 9.3, but it has been disabled by default until PostgreSQL 17.

The issue phenomenon described above does not exist when data checksums are disabled. Below is a record of how this issue was discovered:

A few days ago, a community member evaluated a recently open sourced storage engine, and found that its INSERT operation took twice as long as in the PostgreSQL community edition.

Next, we tested and compared the insertion performance of PostgreSQL, and during further research and analysis, we discovered this version risk introduced in PostgreSQL 18.

Currently, the Redrock Postgres product team is analyzing and formulating a solution to upgrade and improve this version to address the issue.