“PostgreSQL resolves uniqueness through heap tuple visibility”
I recently commented on Jonathan Lewis’s blog, Savepoint Funny, where I compared how PostgreSQL handles uniqueness differently: “PostgreSQL resolves uniqueness through heap tuple visibility". This deserves a more detailed explanation.
In Oracle, unique indexes store unique entries because the B-tree key is the index key, preventing duplicates. Non-unique indexes add the ROWID to ensure that all entries are physically unique, even when indexed column values are duplicated.
In PostgreSQL, all indexes, even unique ones, created explicitly by CREATE UNIQUE INDEX or implicitly to enforce a unique constraint, behave like non-unique indexes by appending the TID (tuple ID, similar to Oracle's ROWID) to the index key. This indicates that the index itself doesn't guarantee physical uniqueness, allowing multiple entries to have identical logical keys but point to different heap tuples. The actual uniqueness verification occurs at the heap level, not within the index entries.
Initially, this might seem unusual—a unique index that permits duplicates. However, PostgreSQL requires this because of its MVCC system. MVCC allows duplicate entries to coexist in an index, since they can represent different versions of the same logical row. Still, PostgreSQL must guarantee that no MVCC snapshot views two rows with the same index key. Oracle doesn't face this issue because its MVCC implementation also versions index blocks, allowing a single index version to maintain unique keys.
Let’s show that.
Page inspect
In PostgreSQL, the heap contains the table data, and index entries point to heap tuples. Visibility depends on the heap header, especially the transaction information. Index scans often visit the heap pages to check visibility, except for index-only scans, which use the heap's visibility maps as an optimization. B-tree indexes can store entries for multiple versions of the same logical row, including versions that are no longer visible to current snapshots. To ensure uniqueness, the B-tree must check the heap for matching keys to verify whether multiple entries point to visible heap tuples in the same MVCC snapshot.
I used pageinspect to look at heap and index pages. This is not application-level SQL. This is a physical page inspection, useful for debugging and understanding internals. I used it to see all tuple versions, including those not visible to my MVCC snapshot. In some ways, you can compare it to Oracle flashback query, which shows all versions of a row.
CREATE EXTENSION IF NOT EXISTS pageinspect;
DROP TABLE IF EXISTS demo_unique_mvcc;
CREATE TABLE demo_unique_mvcc
(
id bigint generated always as identity,
email text not null,
payload text not null,
marker int not null,
CONSTRAINT demo_unique_mvcc_email_key UNIQUE (email)
) WITH ( FILLFACTOR = 10 );
CREATE INDEX demo_unique_mvcc_marker_idx
ON demo_unique_mvcc(marker);
ALTER TABLE demo_unique_mvcc SET (autovacuum_enabled = false);
I disabled auto-vacuum to avoid garbage collection during my experimentation.
Unique index
I created a unique constraint on email:
postgres=# \d demo_unique_mvcc
Table "public.demo_unique_mvcc"
Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+------------------------------
id | bigint | | not null | generated always as identity
email | text | | not null |
payload | text | | not null |
marker | integer | | not null |
Indexes:
"demo_unique_mvcc_email_key" UNIQUE CONSTRAINT, btree (email)
"demo_unique_mvcc_marker_idx" btree (marker)
postgres=#
I also created another index on marker. This is deliberate. If I update only a non-indexed column, PostgreSQL may use HOT updates, so no new entries are needed in the indexes. I want a non-HOT update to show the general case, so I update an indexed column, marker, while keeping the same email. To prove my point, I've set FILLFACTOR to 10% so that HOT updates are possible.
I inserted one row:
postgres=# INSERT INTO demo_unique_mvcc(email, payload, marker)
VALUES ('a@example.com', 'first version', 1)
;
INSERT 0 1
postgres=# SELECT ctid, xmin, xmax, *
FROM demo_unique_mvcc
;
ctid | xmin | xmax | id | email | payload | marker
-------+------+------+----+---------------+---------------+--------
(0,1) | 697 | 0 | 1 | a@example.com | first version | 1
(1 row)
The precise xmin may vary as it's your transaction identifier. What's crucial is the ctid. In this case, the visible row version is (0,1), which is the first tuple on the first page.
Now let’s look at the heap page:
postgres=# SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask, t_infomask2
FROM heap_page_items(get_raw_page('demo_unique_mvcc', 0))
ORDER BY lp
;
lp | t_xmin | t_xmax | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+------------+-------------
1 | 697 | 0 | (0,1) | 2306 | 4
(1 row)
This tuple was inserted by committed transaction 697, has not been deleted or updated (xmax is invalid), contains at least one variable-length column, and stores 4 attributes.
I inspect the unique index. For a B-tree index, block 0 is the metapage, so the first leaf page is usually block 1 for a tiny index:
postgres=# SELECT itemoffset, ctid, htid, dead,
data, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape')
FROM bt_page_items('demo_unique_mvcc_email_key', 1)
ORDER BY itemoffset
;
itemoffset | ctid | htid | dead | data | encode
------------+-------+-------+------+-------------------------------------------------+-----------------------
1 | (0,1) | (0,1) | f | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
(1 row)
One visible row. One index entry in block 1 of the index (ctid) addressing block 1 of the heap table (htid). Nothing surprising yet.
Multiple updates
I updated the row two times, but I did not change the unique key:
postgres=# UPDATE demo_unique_mvcc
SET payload = 'second version', marker = 2
WHERE email = 'a@example.com'
;
UPDATE 1
postgres=# UPDATE demo_unique_mvcc
SET payload = 'third version', marker = 3
WHERE email = 'a@example.com'
;
UPDATE 1
The email value did not change. Logically, there is still one row with email = 'a@example.com':
postgres=# SELECT ctid, xmin, xmax, *
FROM demo_unique_mvcc
;
ctid | xmin | xmax | id | email | payload | marker
-------+------+------+----+---------------+---------------+--------
(0,3) | 711 | 0 | 1 | a@example.com | third version | 3
(1 row)
Only one row is visible to my SQL query.
But the heap page tells a different physical story:
postgres=# SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask, t_infomask2
FROM heap_page_items(get_raw_page('demo_unique_mvcc', 0))
ORDER BY lp
;
lp | t_xmin | t_xmax | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+------------+-------------
1 | 697 | 710 | (0,2) | 1282 | 4
2 | 710 | 711 | (0,3) | 9474 | 4
3 | 711 | 0 | (0,3) | 10498 | 4
(3 rows)
There are three heap tuple versions. The first is no longer current and has been superseded by transaction 710, which has set xmax. The second one is the transaction 710 change superseded by transaction 711. The third is the current version with xmax set to 0. Note that t_ctid points to the next version, so it can chain from old versions to new ones, except for the current version, which points to itself. Typically, here, an index entry pointing to (0,1) (line pointer lp=1 in block 0) can continue to (0,2) and then (0,3).
The old versions are not visible to my current query, but they are still physically present because I disabled autovacuum and have not vacuumed the table.
Now inspect the unique index:
postgres=# SELECT itemoffset, ctid, htid, dead,
data, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape')
FROM bt_page_items('demo_unique_mvcc_email_key', 1)
ORDER BY itemoffset
;
itemoffset | ctid | htid | dead | data | encode
------------+-------+-------+------+-------------------------------------------------+-----------------------
1 | (0,1) | (0,1) | t | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
2 | (0,2) | (0,2) | f | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
3 | (0,3) | (0,3) | f | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
(3 rows)
This is the important point.
The unique index can physically hold multiple entries for the same logical key (data) because these entries point to different versions of heap tuples (htid).
Duplicate key violations
I tried to insert the same email into a new row:
postgres=# INSERT INTO demo_unique_mvcc(email, payload, marker)
VALUES ('a@example.com', 'another logical row', 10)
;
ERROR: duplicate key value violates unique constraint "demo_unique_mvcc_email_key"
DETAIL: Key (email)=(a@example.com) already exists.
postgres=#
This fails. The B-tree found matching key entries in the index. Then PostgreSQL checked the heap tuple visibility of the referenced tuples. At least one conflicting tuple is visible, so this is a uniqueness violation. The index alone did not decide everything. The heap visibility check decides whether the duplicate index entry is a real conflict.
The duplicate tuple was inserted into the heap before the statement was aborted, and this is visible from the physical history:
postgres=# SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask, t_infomask2
FROM heap_page_items(get_raw_page('demo_unique_mvcc', 0))
ORDER BY lp
;
lp | t_xmin | t_xmax | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+------------+-------------
1 | 697 | 710 | (0,2) | 1282 | 4
2 | 710 | 711 | (0,3) | 9474 | 4
3 | 711 | 0 | (0,3) | 10498 | 4
4 | 712 | 0 | (0,4) | 2050 | 4
(4 rows)
PostgreSQL does not determine visibility from xmin/xmax alone. The infomask bits tell whether the transaction status is already known (committed, aborted, invalid, updated, locked, etc.). When the bits are not set, PostgreSQL consults the transaction status and may later set hint bits in the tuple header:
postgres=# SELECT txid_status('744')
;
txid_status
-------------
aborted
(1 row)
postgres=# SELECT txid_status('742')
;
txid_status
-------------
committed
(1 row)
Nothing changed in the index. It was used to find the multiple versions, but a duplicate key was detected before updating it:
postgres=# SELECT itemoffset, ctid, htid, dead,
data, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape')
FROM bt_page_items('demo_unique_mvcc_email_key', 1)
ORDER BY itemoffset
;
itemoffset | ctid | htid | dead | data | encode
------------+-------+-------+------+-------------------------------------------------+-----------------------
1 | (0,1) | (0,1) | t | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
2 | (0,2) | (0,2) | t | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
3 | (0,3) | (0,3) | f | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
(3 rows)
It is not visible here, but even if uniqueness is detected on the heap tuples, it is still part of the index access methods because concurrency control must synchronize transactions by locking the index leaf page, which is the only place where all transactions updating the same key must go, since heap tuples could be in different blocks. This explains why a unique index is required to enforce unique constraints, even though duplicate checking itself is done on the heap.
Heap-Only Tuples
I mentioned HOT updates. As I've set a low fill factor and still have lots of free space in the block (as we confirm with ctid, all updates are within the same block), an update of a single indexed column may not add a new entry:
postgres=# UPDATE demo_unique_mvcc
SET payload = 'fourth version' --, marker = 2
WHERE email = 'a@example.com'
;
UPDATE 1
postgres=# SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask, t_infomask2
FROM heap_page_items(get_raw_page('demo_unique_mvcc', 0))
ORDER BY lp
;
lp | t_xmin | t_xmax | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+------------+-------------
1 | 697 | 710 | (0,2) | 1282 | 4
2 | 710 | 711 | (0,3) | 9474 | 4
3 | 711 | 713 | (0,5) | 8450 | 16388
4 | 712 | 0 | (0,4) | 2050 | 4
5 | 713 | 0 | (0,5) | 10242 | 32772
(5 rows)
postgres=# SELECT itemoffset, ctid, htid, dead,
data, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape')
FROM bt_page_items('demo_unique_mvcc_email_key', 1)
ORDER BY itemoffset
;
itemoffset | ctid | htid | dead | data | encode
------------+-------+-------+------+-------------------------------------------------+-----------------------
1 | (0,1) | (0,1) | t | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
2 | (0,2) | (0,2) | t | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
3 | (0,3) | (0,3) | f | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
(3 rows)
The t_infomask2 added HEAP_ONLY_TUPLE (32768) to the number of attributes (4), indicating it has no index entry of its own. The entry pointing to (0,3) will follow the HOT chain to find the other version within the same page.
Deferred unique constraint
One last test. We have seen that a duplicate key insert leaves a tuple in the heap but no new entry in the index because the duplicate is detected during index update. However, if the unique constraint checking is deferred, the entry will be stored, and the detection happens at commit:
postgres=# ALTER TABLE demo_unique_mvcc
DROP CONSTRAINT demo_unique_mvcc_email_key
;
postgres=# ALTER TABLE demo_unique_mvcc
ADD CONSTRAINT demo_unique_mvcc_email_key
UNIQUE (email) DEFERRABLE;
postgres=# SELECT itemoffset, ctid, htid, dead,
data, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape')
FROM bt_page_items('demo_unique_mvcc_email_key', 1)
ORDER BY itemoffset
;
itemoffset | ctid | htid | dead | data | encode
------------+-------+-------+------+-------------------------------------------------+-----------------------
1 | (0,3) | (0,3) | f | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
(1 row)
postgres=# BEGIN;
BEGIN
postgres=# SET CONSTRAINTS demo_unique_mvcc_email_key DEFERRED
;
SET CONSTRAINTS
postgres=# INSERT INTO demo_unique_mvcc(email, payload, marker)
VALUES ('a@example.com', 'a duplicate row', 10);
;
INSERT 0 1
postgres=*# SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask, t_infomask2
FROM heap_page_items(get_raw_page('demo_unique_mvcc', 0))
ORDER BY lp
;
lp | t_xmin | t_xmax | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+------------+-------------
1 | 697 | 710 | (0,2) | 1282 | 4
2 | 710 | 711 | (0,3) | 9474 | 4
3 | 711 | 713 | (0,5) | 9474 | 16388
4 | 712 | 0 | (0,4) | 2562 | 4
5 | 713 | 0 | (0,5) | 10498 | 32772
6 | 718 | 0 | (
Searchable field-level encryption on Supabase with CipherStash
Add field-level encryption to your Supabase project with CipherStash. Searchable ciphertext, zero-knowledge key management, and no schema changes required.
July 08, 2026
Deadlocks and downtime
July 07, 2026
Logical replication improvements in Amazon RDS for PostgreSQL 18
Automate PostgreSQL audit log extraction and analysis with Amazon S3
What happens when a PostgreSQL backend crashes?
Your connection to PostgreSQL is handled by a dedicated backend process. If that process crashes, you might think only your session is affected. However, because backend processes share memory, PostgreSQL assumes the shared state may have been corrupted and immediately terminates all other connections. Recovery is then performed before new connections are accepted.
Here is a short test to demonstrate it and to see what is visible in the PostgreSQL server log and what is received in the application. I run an ephemeral Docker container, show the log file, start ten pgbench clients, and kill my own session:
docker exec -it $(
docker run -d --rm -e POSTGRES_PASSWORD=xxx postgres -c logging_collector=on
sleep 3
) psql -U postgres <<'SQL'
\! sleep 1 ; echo '\n 🐘 Start pgbench in the background...\n' ; sleep 1
\! pgbench -i -U postgres postgres
\! pgbench -c 5 -T 60 -P 1 -U postgres postgres & sleep 10
\! sleep 1 ; echo '\n 🐘 Tail the logfile...\n' ; sleep 1
select current_setting('data_directory')||'/'||pg_current_logfile() log
\gset
\setenv log :log
\! tail -f "$log" | sed -e "s/^/📜 /" &
\! sleep 1 ; echo '\n 🐘 Crash the current process...\n' ; sleep 1
select pg_backend_pid() as pid
\gset
\setenv pid :pid
\! kill -9 $pid
\! sleep 30
\q
SQL
Here is the output. I've deliberately set it to run in an ephemeral Docker container because I don't want you to do the same on an existing database.
The container started, psql connected, and pgbench started:
psql (18.4 (Debian 18.4-1.pgdg13+1))
Type "help" for help.
postgres=#
postgres=# \! sleep 1 ; echo '\n 🐘 Start pgbench in the background...\n' ; sleep 1
🐘 Start pgbench in the background...
postgres=#
postgres=# \! pgbench -i -U postgres postgres
dropping old tables...
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
vacuuming...
creating primary keys...
done in 0.21 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 0.14 s, vacuum 0.03 s, primary keys 0.03 s).
postgres=# \! pgbench -c 5 -T 60 -P 1 -U postgres postgres & sleep 10
pgbench (18.4 (Debian 18.4-1.pgdg13+1))
starting vacuum...end.
progress: 1.0 s, 504.0 tps, lat 9.719 ms stddev 7.949, 0 failed
progress: 2.0 s, 338.0 tps, lat 14.826 ms stddev 9.621, 0 failed
progress: 3.0 s, 348.0 tps, lat 14.335 ms stddev 9.640, 0 failed
progress: 4.0 s, 346.0 tps, lat 14.456 ms stddev 9.288, 0 failed
progress: 5.0 s, 357.0 tps, lat 13.976 ms stddev 9.178, 0 failed
progress: 6.0 s, 349.0 tps, lat 14.371 ms stddev 10.674, 0 failed
progress: 7.0 s, 357.0 tps, lat 14.010 ms stddev 9.035, 0 failed
progress: 8.0 s, 350.0 tps, lat 14.235 ms stddev 9.087, 0 failed
progress: 9.0 s, 341.0 tps, lat 14.788 ms stddev 9.204, 0 failed
progress: 10.0 s, 277.0 tps, lat 18.002 ms stddev 9.095, 0 failed
Displaying the PostgreSQL log file while pgbench is still running:
postgres=# \! sleep 1 ; echo '\n 🐘 Tail the logfile...\n' ; sleep 1
🐘 Tail the logfile...
progress: 11.0 s, 353.0 tps, lat 14.131 ms stddev 10.032, 0 failed
postgres=#
postgres=# select current_setting('data_directory')||'/'||pg_current_logfile() log
postgres-# \gset
postgres=# \setenv log :log
postgres=# \! tail -f "$log" | sed -e "s/^/📜 /" &
postgres=#
📜 2026-07-06 17:06:18.098 UTC [1] LOG: starting PostgreSQL 18.4 (Debian 18.4-1.pgdg13+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
📜 2026-07-06 17:06:18.099 UTC [1] LOG: listening on IPv4 address "0.0.0.0", port 5432
📜 2026-07-06 17:06:18.099 UTC [1] LOG: listening on IPv6 address "::", port 5432
📜 2026-07-06 17:06:18.101 UTC [1] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
📜 2026-07-06 17:06:18.106 UTC [75] LOG: database system was shut down at 2026-07-06 17:06:17 UTC
📜 2026-07-06 17:06:18.109 UTC [1] LOG: database system is ready to accept connections
progress: 12.0 s, 332.0 tps, lat 15.064 ms stddev 10.867, 0 failed
Crashing the current backend with kill -9 while pgbench is still running and the log file is being tailed:
postgres=# \! sleep 1 ; echo '\n 🐘 Crash the current process...\n' ; sleep 1
🐘 Crash the current process...
progress: 13.0 s, 357.0 tps, lat 13.976 ms stddev 9.172, 0 failed
postgres=#
postgres=# select pg_backend_pid() as pid
postgres-# \gset
postgres=# \setenv pid :pid
postgres=# \! kill -9 $pid
postgres=#
postgres=# \! sleep 30
📜 2026-07-06 17:06:36.241 UTC [1] LOG: client backend (PID 85) was terminated by signal 9: Killed
📜 2026-07-06 17:06:36.241 UTC [1] DETAIL: Failed process was running: select pg_backend_pid() as pid
📜 2026-07-06 17:06:36.241 UTC [1] LOG: terminating any other active server processes
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
pgbench: error: client 0 aborted in command 10 (SQL) of script 0; perhaps the backend died while processing
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
pgbench: error: client 2 aborted in command 8 (SQL) of script 0; perhaps the backend died while processing
pgbench: error: client 3 aborted in command 8 (SQL) of script 0; perhaps the backend died while processing
📜 2026-07-06 17:06:36.243 UTC [1] LOG: all server processes terminated; reinitializing
pgbench: error: client 4 aborted in command 8 (SQL) of script 0; perhaps the backend died while processing
pgbench: error: client 1 aborted in command 7 (SQL) of script 0; perhaps the backend died while processing
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 5
number of threads: 1
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 4948
number of failed transactions: 0 (0.000%)
latency average = 14.106 ms
latency stddev = 9.783 ms
initial connection time = 6.526 ms
tps = 353.948496 (without initial connection time)
pgbench: error: Run was aborted; the above results are incomplete.
📜 2026-07-06 17:06:36.250 UTC [117] LOG: database system was interrupted; last known up at 2026-07-06 17:06:18 UTC
📜 2026-07-06 17:06:36.390 UTC [117] LOG: database system was not properly shut down; automatic recovery in progress
📜 2026-07-06 17:06:36.392 UTC [117] LOG: redo starts at 0/175F960
📜 2026-07-06 17:06:36.433 UTC [117] LOG: invalid record length at 0/2687F00: expected at least 24, got 0
📜 2026-07-06 17:06:36.433 UTC [117] LOG: redo done at 0/2687ED8 system usage: CPU: user: 0.03 s, system: 0.00 s, elapsed: 0.04 s
📜 2026-07-06 17:06:36.435 UTC [118] LOG: checkpoint starting: end-of-recovery immediate wait
📜 2026-07-06 17:06:37.205 UTC [118] LOG: checkpoint complete: wrote 2054 buffers (12.5%), wrote 3 SLRU buffers; 0 WAL file(s) added, 0 removed, 1 recycled; write=0.625 s, sync=0.138 s, total=0.771 s; sync files=51, longest=0.108 s, average=0.003 s; distance=15521 kB, estimate=15521 kB; lsn=0/2687F00, redo lsn=0/2687F00
📜 2026-07-06 17:06:37.207 UTC [1] LOG: database system is ready to accept connections
postgres=#
postgres=# \q
This indicates that PostgreSQL intentionally terminated all other connections (as seen in the error messages from the five pgbench connections) when it detected a crash signal, and that it completed recovery before allowing new connections:
The logfile indicates the cause:
LOG: client backend (PID 85) was terminated by signal 9: Killed
LOG: terminating any other active server processes
The application encountered the error:
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
Unlike database engines that keep sessions separate, PostgreSQL backends share key memory areas like shared buffers, lock tables, and transaction status data. If a backend crashes unexpectedly, PostgreSQL cannot ensure these structures stay consistent. As a result, it adopts a cautious strategy: shutting down all backends and restarting from a reliable, known state.
This behavior is deliberate and is a fundamental safety feature of PostgreSQL. When a backend crashes, it is considered a possible sign of shared-memory corruption. To prevent serving inconsistent data, PostgreSQL shuts down all sessions, replays WAL during recovery, and only resumes accepting connections afterward.
Percona Operator for MySQL 1.2.0: Cross-Site Replication, Encrypted Backups, and Automatic Storage Scaling
Percona Operator for MySQL 1.2.0 is out, and it closes three gaps that platform teams hit once a MySQL deployment grows past a single cluster. Picture a fleet that has outgrown one region: you want a warm replica cluster in a second data center, backups in object storage that pass an auditor’s encryption check, … Continued
The post Percona Operator for MySQL 1.2.0: Cross-Site Replication, Encrypted Backups, and Automatic Storage Scaling appeared first on Percona.
Comparing Migration Methods from the Crunchy Data PostgreSQL Operator to the Percona Operator for PostgreSQL
Migrating a production PostgreSQL database on Kubernetes is not only about moving data from one operator to another. It is also about choosing the right trade-off between downtime, operational complexity, rollback safety, cost, and business risk. Practical migration paths from the Crunchy Data PostgreSQL Operator to the Percona Operator for PostgreSQL are described here. 1. … Continued
The post Comparing Migration Methods from the Crunchy Data PostgreSQL Operator to the Percona Operator for PostgreSQL appeared first on Percona.
How Turbopuffer's Tokenizer Led Us to a 40% Smaller Index
July 06, 2026
Dynata’s journey to lower TCO and faster modernization with AWS Database Savings Plans
July 05, 2026
PostgreSQL query planner parameters and prepared statements
PostgreSQL provides several planner configuration parameters, such as enable_seqscan and enable_indexscan, that influence how execution plans are generated. These settings affect planning, not the execution of an already-generated plan. With prepared statements, this raises an interesting question. Should planner settings be applied before PREPARE, before EXECUTE, or both?
Let's look at a simple example: a "tasks" table with a due date and a "done" status:
\c
drop table if exists tasks;
-- a table of tasks with status (done or not) and due date
create table tasks (
id bigint generated always as identity primary key,
due timestamptz,
done boolean
);
-- insert 500 tasks, with 1% not done
insert into tasks (due, done)
select
now()+interval '1 day'*n,
42 != n%100
from generate_series(1,500) n
;
-- index the todo (partial index)
create index on tasks(due,id)
where done = false;
vacuum analyze tasks;
With a partial index, I indexed only the tasks that are not yet done (done = false) because that's my most frequent query pattern:
postgres=# explain
select id, due, done from tasks
where done = false and id>0
order by due limit 1
;
QUERY PLAN
---------------------------------------------------------------------------------------
Limit (cost=0.13..3.60 rows=1 width=17)
-> Index Scan using tasks_due_id_idx1 on tasks (cost=0.13..17.47 rows=5 width=17)
Index Cond: (id > 0)
(3 rows)
With partial indexes, the condition covered by the index is not even visible in the execution plan because the index itself enforces the condition.
Prepared statement
I decided to use a prepared statement with all values as parameters. It is probably not a good idea in this case. When a parameter can have only a few different values and you expect different cardinalities for each, you should probably define one query per value, using literals. I'm doing this to illustrate what can happen, with a simple, extreme example:
postgres=# prepare c(boolean, int) as
select id, due, done from tasks
where done = $1 and id>0
order by due limit $2
;
PREPARE
postgres=# explain (analyze, settings)
execute c (false, 1)
;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.13..3.60 rows=1 width=17) (actual time=0.087..0.088 rows=1.00 loops=1)
Buffers: shared hit=4 read=1
-> Index Scan using tasks_due_id_idx on tasks (cost=0.13..17.47 rows=5 width=17) (actual time=0.087..0.087 rows=1.00 loops=1)
Index Cond: (id > 0)
Index Searches: 1
Buffers: shared hit=4 read=1
Planning:
Buffers: shared hit=3
Planning Time: 0.148 ms
Execution Time: 0.099 ms
(10 rows)
With the same prepared statement, I disabled the Index Scan before the next execution:
postgres=# set enable_indexscan to off;
SET
postgres=# explain (analyze, settings)
execute c (false, 1)
;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Limit (cost=10.28..10.28 rows=1 width=17) (actual time=0.043..0.043 rows=1.00 loops=1)
Buffers: shared hit=4
-> Sort (cost=10.28..10.29 rows=5 width=17) (actual time=0.042..0.042 rows=1.00 loops=1)
Sort Key: due
Sort Method: top-N heapsort Memory: 25kB
Buffers: shared hit=4
-> Seq Scan on tasks (cost=0.00..10.25 rows=5 width=17) (actual time=0.012..0.037 rows=5.00 loops=1)
Filter: ((NOT done) AND (id > 0))
Rows Removed by Filter: 495
Buffers: shared hit=4
Settings: enable_indexscan = 'off'
Planning:
Buffers: shared hit=12
Planning Time: 0.174 ms
Execution Time: 0.055 ms
(15 rows)
I was still using the same prepared statement, yet the execution plan had changed. PostgreSQL generated a new custom plan for this execution, so it used the planner setting active at EXECUTE time (enable_indexscan = 'off') rather than any setting active when the statement was prepared.
With plan_cache_mode set to the default auto, each execution of the prepared statement goes through the planning phase like a non-prepared statement for at least the first five executions, so the query planner parameters at EXECUTE time drive the planning. The result is a custom plan. After five executions, it may switch to a generic plan if the optimizer estimates it's worth it, comparing the generic plan's cost with the average execution cost of the previous custom plans (including planning overhead).
Because this decision depends on cost estimates, previous execution history, and the generic-versus-custom plan heuristic, plan selection may be less predictable than when plan_cache_mode is explicitly set. I recommend not relying on the auto behavior and instead deciding whether a prepared statement should be generic or custom by setting plan_cache_mode accordingly. Ideally, you should use parameters only when a generic plan is acceptable, and use different prepared statements with literals when the value matters for choosing the optimal access path.
Generic plan
You might expect a generic plan to permanently preserve the planner environment that was in effect at the time the plan was created. Here is the same example with plan_cache_mode set to force_generic_plan:
--- reset the session
postgres=# \c
You are now connected to database "postgres" as user "postgres".
postgres=# \dconfig enable*scan*
List of configuration parameters
Parameter | Value
----------------------+-------
enable_bitmapscan | on
enable_indexonlyscan | on
enable_indexscan | on
enable_seqscan | on
enable_tidscan | on
(5 rows)
-- disable auto plan cache mode and set it to generic
postgres=# set plan_cache_mode=force_generic_plan;
SET
postgres=# prepare c(boolean, int) as
select id, due, done from tasks
where done = $1 and id > 0
order by due limit $2
;
PREPARE
postgres=# explain (analyze, settings)
execute c (false, 1)
;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Limit (cost=21.46..21.52 rows=25 width=17) (actual time=0.057..0.057 rows=1.00 loops=1)
Buffers: shared hit=7
-> Sort (cost=21.46..22.08 rows=250 width=17) (actual time=0.055..0.055 rows=1.00 loops=1)
Sort Key: due
Sort Method: top-N heapsort Memory: 25kB
Buffers: shared hit=7
-> Seq Scan on tasks (cost=0.00..11.50 rows=250 width=17) (actual time=0.010..0.040 rows=5.00 loops=1)
Filter: ((id > 0) AND (done = $1))
Rows Removed by Filter: 495
Buffers: shared hit=4
Settings: plan_cache_mode = 'force_generic_plan'
Planning:
Buffers: shared hit=122
Planning Time: 0.453 ms
Execution Time: 0.073 ms
(15 rows)
With the predicate on "done" that can take any value, a generic plan cannot use the partial index that contains entries only for the true value, so the query planner falls back to a Seq Scan.
I disable Seq Scan for the next execution:
postgres=# set enable_seqscan to off;
SET
postgres=# explain (analyze, settings)
execute c (false, 1)
;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Limit (cost=21.46..21.52 rows=25 width=17) (actual time=0.059..0.060 rows=1.00 loops=1)
Buffers: shared hit=4
-> Sort (cost=21.46..22.08 rows=250 width=17) (actual time=0.057..0.057 rows=1.00 loops=1)
Sort Key: due
Sort Method: top-N heapsort Memory: 25kB
Buffers: shared hit=4
-> Seq Scan on tasks (cost=0.00..11.50 rows=250 width=17) (actual time=0.018..0.051 rows=5.00 loops=1)
Filter: ((id > 0) AND (done = $1))
Rows Removed by Filter: 495
Buffers: shared hit=4
Settings: plan_cache_mode = 'force_generic_plan', enable_seqscan = 'off'
Planning Time: 0.013 ms
Execution Time: 0.080 ms
(13 rows)
The statement was not re-planned. While there's no direct proof, several clues suggest it:
-
Seq Scanpersisted even when disabled, even though an alternative access method, such as using the primary key index, is available and would respect the directive, even if not optimal. - The
Planningsection was absent, as seen in the initial EXECUTE after PREPARE, which showedBuffers: shared hitrelated to catalog lookups. - The
Planning Timewas brief, only covering the time to retrieve the plan from cache. - There was no
Disabled: trueindicator or a very high cost noted in earlier PostgreSQL versions forSeq Scan, indicating thatenable_seqscan = 'off'was ineffective in this case.
A potential source of confusion is that enable_seqscan = 'off' appears in the Settings section, even though it was not used to produce the displayed plan. The Settings section shows planner-related GUC values active during EXPLAIN execution, which may differ from those active when a cached generic plan was created.
DDL invalidation
Prepared statements continue to use cached plans when query parameters change, but generic plans might be re-created if invalidated. DDL statements, such as adding a column, invalidate cached plans regardless of whether they depended on that column:
postgres=# alter table tasks add column description text;
ALTER TABLE
postgres=# explain (analyze, settings)
execute c (false, 1)
;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=41.54..41.60 rows=25 width=17) (actual time=0.113..0.114 rows=1.00 loops=1)
Buffers: shared hit=10
-> Sort (cost=41.54..42.17 rows=250 width=17) (actual time=0.111..0.112 rows=1.00 loops=1)
Sort Key: due
Sort Method: top-N heapsort Memory: 25kB
Buffers: shared hit=10
-> Bitmap Heap Scan on tasks (cost=20.09..31.59 rows=250 width=17) (actual time=0.069..0.105 rows=5.00 loops=1)
Recheck Cond: (id > 0)
Filter: (done = $1)
Rows Removed by Filter: 495
Heap Blocks: exact=4
Buffers: shared hit=10
-> Bitmap Index Scan on tasks_pkey (cost=0.00..20.02 rows=500 width=0) (actual time=0.050..0.050 rows=500.00 loops=1)
Index Cond: (id > 0)
Index Searches: 1
Buffers: shared hit=6
Settings: plan_cache_mode = 'force_generic_plan', enable_seqscan = 'off'
Planning:
Buffers: shared hit=23
Planning Time: 0.300 ms
Execution Time: 0.130 ms
(21 rows)
This time, enable_seqscan = 'off' was used because the prepared statement was re-planned, which effectively skipped Seq Scan in favor of a Bitmap Heap Scan.
Because the plan is generic, PostgreSQL cannot assume that $1 will always meet the partial index's predicate. Therefore, the partial index cannot be used, but the primary key index "tasks_pkey" contains entries for all rows and can be used when sequential scan is disabled.
Disabled: true (PostgreSQL 18)
I've run another DDL to remove the primary key and, consequently, the index, and the prepared statement is re-planned:
postgres=# alter table tasks drop constraint tasks_pkey;
ALTER TABLE
postgres=# explain (analyze, settings)
execute c (false, 1)
;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Limit (cost=21.46..21.52 rows=25 width=17) (actual time=0.054..0.055 rows=1.00 loops=1)
Buffers: shared hit=4
-> Sort (cost=21.46..22.08 rows=250 width=17) (actual time=0.052..0.052 rows=1.00 loops=1)
Sort Key: due
Sort Method: top-N heapsort Memory: 25kB
Buffers: shared hit=4
-> Seq Scan on tasks (cost=0.00..11.50 rows=250 width=17) (actual time=0.014..0.046 rows=5.00 loops=1)
Disabled: true
Filter: ((id > 0) AND (done = $1))
Rows Removed by Filter: 495
Buffers: shared hit=4
Settings: plan_cache_mode = 'force_generic_plan', enable_seqscan = 'off'
Planning:
Buffers: shared hit=16 dirtied=2
Planning Time: 0.251 ms
Execution Time: 0.069 ms
(16 rows)
The mention of Disabled: true indicates that the disabled node was still in use due to the absence of an alternative. The only index on this table is a partial index, which can be used only with a custom plan when parameter $1 is false, not with a standard plan.
In PostgreSQL 17, instead of Disabled: true, you would see an extremely high cost, indicating that disabled scans are deprioritized:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Limit (cost=10000000021.46..10000000021.52 rows=25 width=17) (actual time=0.144..0.145 rows=1 loops=1)
-> Sort (cost=10000000021.46..10000000022.08 rows=250 width=17) (actual time=0.143..0.143 rows=1 loops=1)
Sort Key: due
Sort Method: top-N heapsort Memory: 25kB
-> Seq Scan on tasks (cost=10000000000.00..10000000011.50 rows=250 width=17) (actual time=0.098..0.136 rows=5 loops=1)
Filter: ((id > 0) AND (done = $1))
Rows Removed by Filter: 495
Settings: plan_cache_mode = 'force_generic_plan', enable_seqscan = 'off', jit = 'off'
Planning Time: 0.014 ms
Execution Time: 0.167 ms
(10 rows)
It's important to determine if the EXECUTE-time parameters were applied to the plan, as the Settings section can be misleading. It displays the parameters set during the execution explanation, but they only affected the plan if a re-planning occurred. EXPLAIN does not reveal the parameters that established the cached plan.
In an upcoming blog post about pg_plan_advice, I'll share a different approach to guide PostgreSQL 19's query planner. We will see that changing pg_plan_advice.advice keeps behavior consistent because it doesn't invalidate cached plans, as we've observed with enable_seqscan. However, using EXPLAIN (plan_advice) shows the hints used during planning, which differ slightly from the settings output.
EXECUTE is doing the planning
These examples clearly show that the important planner settings are those active when PostgreSQL creates a plan, not necessarily when ... (truncated)
ClickHouse real time analytics
ClickHouse real time monitoring systems
Postgres vs MariaDB
Postgres vs Oracle
July 03, 2026
Extended RUM in DocumentDB: B-tree-like ordered scans for flexible BSON in PostgreSQL
The main challenge in document databases is the flexible nature of fields: the same path can be a scalar, an array, nested, or missing. Despite this, an index must specify what it covers and the order in which rows can be produced. B-tree indexes work well for fixed-scalar columns, enabling prefix filtering and returning sorted rows. GIN and RUM inverted indexes support flexible, repeated values, but traditional RUM ordering relies on distance operators on attached values rather than standard document-style ORDER BY field LIMIT n.
DocumentDB's Extended RUM closes that gap. It extends the RUM access method for compound document indexes by generating composite index terms from the indexed paths and applying an ordering transform during the scan. The result is an inverted, multikey-style index that can filter, sort, and stop at LIMIT in a single Index Scan, while preserving document semantics for arrays and missing fields.
Here is the table I created for my previous blog post, RUM—Storing More in the Index:
postgres=# \d articles
Table "public.articles"
Column | Type | Collation | Nullable | Default
-----------+-----------------------------+-----------+----------+---------------------------------------------------------------------------------------------
id | integer | | not null | nextval('articles_id_seq'::regclass)
title | text | | not null |
body | text | | not null |
category | text | | not null |
published | timestamp without time zone | | not null |
score | integer | | not null |
tsv | tsvector | | | generated always as (to_tsvector('simple'::regconfig, (title || ' '::text) || body)) stored
Indexes:
"articles_pkey" PRIMARY KEY, btree (id)
"idx_gin_tsv" gin (tsv)
"idx_rum_multi" rum (tsv rum_tsvector_addon_ops, category, published) WITH (attach=published, "to"=tsv)
The RUM index supports filtering and ordering by distance:
--EXPLAIN (COSTS OFF, ANALYZE ON, BUFFERS ON, VERBOSE ON)
SELECT id, published
, published <=> '1970-01-01'::timestamp as " <=> 1970"
, extract ( epoch from published ) as " epoch "
FROM articles
WHERE tsv @@ to_tsquery('english', 'postgresql')
AND category = 'tech'
ORDER BY published <=> '1970-06-01'::timestamp
LIMIT 5
;
id | published | <=> 1970 | epoch
-----+---------------------+------------+-------------------
20 | 2020-01-01 20:00:00 | 1577908800 | 1577908800.000000
40 | 2020-01-02 16:00:00 | 1577980800 | 1577980800.000000
60 | 2020-01-03 12:00:00 | 1578052800 | 1578052800.000000
80 | 2020-01-04 08:00:00 | 1578124800 | 1578124800.000000
100 | 2020-01-05 04:00:00 | 1578196800 | 1578196800.000000
(5 rows)
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (actual time=70.167..70.177 rows=5 loops=1)
Output: id, published, ((published <=> '1970-01-01 00:00:00'::timestamp without time zone)), (EXTRACT(epoch FROM published)), ((published <=> '1970-06-01 00:00:00'::timestamp without time zone))
Buffers: shared hit=223, temp read=550 written=550
-> Index Scan using idx_rum_multi on public.articles (actual time=70.165..70.175 rows=5 loops=1)
Output: id, published, (published <=> '1970-01-01 00:00:00'::timestamp without time zone), EXTRACT(epoch FROM published), (published <=> '1970-06-01 00:00:00'::timestamp without time zone)
Index Cond: ((articles.tsv @@ '''postgresql'''::tsquery) AND (articles.category = 'tech'::text))
Order By: (articles.published <=> '1970-06-01 00:00:00'::timestamp without time zone)
Buffers: shared hit=223, temp read=550 written=550
Planning:
Buffers: shared hit=2
Planning Time: 0.124 ms
Execution Time: 70.698 ms
(12 rows)
Although Order By is integrated into the Index Scan, temp read reveals that it isn't a straightforward ordered index traversal, unlike a B-tree. Internal RUM scan processes spilled over to temporary storage. The key point is that there's no PostgreSQL Sort node involved. However, this is still distance-based ordering rather than simple key ordering.
I used the distance operator <=> with a date earlier than any date in this table, so the query effectively retrieves the first five articles sorted by published date. RUM allows ordering by its distance operators on attached values, such as published <=> constant. This can resemble chronological ordering when the constant is outside the data range, but it isn't the same as a simple ORDER BY published.
If I use a basic ORDER BY in my query without applying the distance operator, I obtain the same result, but it takes longer to execute:
--EXPLAIN (COSTS OFF, ANALYZE ON, BUFFERS ON, VERBOSE ON)
SELECT id, published
, published <=> '1970-01-01'::timestamp as " <=> 1970"
, extract ( epoch from published ) as " epoch "
FROM articles
WHERE tsv @@ to_tsquery('english', 'postgresql')
AND category = 'tech'
ORDER BY published
LIMIT 5
;
id | published | <=> 1970 | epoch
-----+---------------------+------------+-------------------
20 | 2020-01-01 20:00:00 | 1577908800 | 1577908800.000000
40 | 2020-01-02 16:00:00 | 1577980800 | 1577980800.000000
60 | 2020-01-03 12:00:00 | 1578052800 | 1578052800.000000
80 | 2020-01-04 08:00:00 | 1578124800 | 1578124800.000000
100 | 2020-01-05 04:00:00 | 1578196800 | 1578196800.000000
(5 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Limit (actual time=21870.366..21870.368 rows=5 loops=1)
Output: id, published, ((published <=> '1970-01-01 00:00:00'::timestamp without time zone)), (EXTRACT(epoch FROM published))
Buffers: shared hit=3 read=50215 written=1
-> Sort (actual time=21870.364..21870.366 rows=5 loops=1)
Output: id, published, ((published <=> '1970-01-01 00:00:00'::timestamp without time zone)), (EXTRACT(epoch FROM published))
Sort Key: articles.published
Sort Method: top-N heapsort Memory: 25kB
Buffers: shared hit=3 read=50215 written=1
-> Bitmap Heap Scan on public.articles (actual time=85.266..21852.624 rows=50000 loops=1)
Output: id, published, (published <=> '1970-01-01 00:00:00'::timestamp without time zone), EXTRACT(epoch FROM published)
Recheck Cond: ((articles.tsv @@ '''postgresql'''::tsquery) AND (articles.category = 'tech'::text))
Heap Blocks: exact=50000
Buffers: shared hit=3 read=50215 written=1
-> Bitmap Index Scan on idx_rum_multi (actual time=75.951..75.951 rows=50000 loops=1)
Index Cond: ((articles.tsv @@ '''postgresql'''::tsquery) AND (articles.category = 'tech'::text))
Buffers: shared hit=3 read=215
Planning:
Buffers: shared read=2
Planning Time: 2.276 ms
Execution Time: 21870.406 ms
(20 rows)
RUM's ordering mechanism uses the <=> distance operator, which measures distance from a reference point. This differs from simply using ORDER BY published. When you directly apply ORDER BY published, RUM defaults to a bitmap scan combined with sorting.
For a query involving 1 million articles filtered by words = 'postgresql' and category = 'tech' (which yields 50K matches here) and sorted by published, it reads all matching rows and sorts them, taking 21 seconds because it reads 50K heap blocks instead of stopping after finding just 5 rows like expected with ORDER BY ... LIMIT.
In tables with a strict schema and no arrays, the solution is straightforward. B-tree indexes store entries in sorted order, enabling efficient filtering and retrieval. A composite B-tree index on (category, published) can filter for category='tech' and return results already ordered by published, eliminating the need for a separate sort step:
postgres=# CREATE INDEX idx_articles_category_published
ON articles (category, published)
;
postgres=# EXPLAIN (COSTS OFF, ANALYZE ON, BUFFERS ON, VERBOSE ON)
SELECT id, published
, published <=> '1970-01-01'::timestamp as " <=> 1970"
, extract ( epoch from published ) as " epoch "
FROM articles
WHERE tsv @@ to_tsquery('english', 'postgresql')
AND category = 'tech'
ORDER BY published
LIMIT 5
;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Limit (actual time=0.036..0.050 rows=5 loops=1)
Output: id, published, ((published <=> '1970-01-01 00:00:00'::timestamp without time zone)), (EXTRACT(epoch FROM published))
Buffers: shared hit=9
-> Index Scan using idx_articles_category_published on public.articles (actual time=0.035..0.048 rows=5 loops=1)
Output: id, published, (published <=> '1970-01-01 00:00:00'::timestamp without time zone), EXTRACT(epoch FROM published)
Index Cond: (articles.category = 'tech'::text)
Filter: (articles.tsv @@ '''postgresql'''::tsquery)
Rows Removed by Filter: 20
Buffers: shared hit=9
Planning:
Buffers: shared hit=2
Planning Time: 0.129 ms
Execution Time: 0.065 ms
(13 rows)
This is fast because the (category, published) B-tree walks category='tech' entries in timestamp order, and the filter predicate happens to reject only 20 rows before finding 5 matches. If the text predicate were much more selective or poorly correlated with published, this plan could also scan many rows.
B-trees index only columns with well-typed scalar values. A B-tree can index extracted scalar expressions from JSONB, but it does not naturally support document-database multikey semantics, where the same path may be scalar, an array, nested, or absent. For that, you need an inverted/multikey-style index. Here, I skipped indexing "words" because I know it can contain an array, and it relies on the fact that "category" can contain only one value. This is true in SQL, where the schema is declared for the table, but not for a polymorphic document collection.
To show the same access pattern with flexible documents, I use the DocumentDB extension for PostgreSQL. I create a collection to store the same data in a flexible schema:
SELECT documentdb_api.create_collection('db', 'articles');
SELECT documentdb_api.insert_one(
'db',
'articles',
FORMAT(
'{"_id": %s, "title": %s, "body": %s, "category": %s, "published": {"$date": {"$numberLong": "%s"}}, "score": %s, "words": %s}',
to_json(id),
to_json(title),
to_json(body),
to_json(category),
(EXTRACT(EPOCH FROM published) * 1000)::bigint,
to_json(score),
to_json(tsvector_to_array(tsv))
)::documentdb_core.bson
)
FROM articles;
SELECT documentdb_api_internal.create_indexes_non_concurrently('db',
'{ "createIndexes": "articles", "indexes": [ {
"key": { "words":1, "category": 1, "published": -1 },
"name": "idx_wrd_cat_pub"
} ] }',
true)
;
This is similar to the "articles" table and index, but in a collection where the data type and cardinality don't have to be declared in advance. The index definition doesn't need to know that "words" contains an array and that there's only one "category" per article.
Here is a similar query and its execution plan:
postgres=# SET documentdb_core.bsonUseEJson to true;
SET
postgres=# SELECT documentdb_api_catalog.bson_dollar_unwind(
cursorpage, '$cursor.firstBatch'
) FROM documentdb_api.find_cursor_first_page(
'db', '{
"find": "articles",
"filter": { "words": "postgresql", "category": "tech" },
"sort": { "published": 1 },
"limit": 5,
"projection": { "_id": 1, "title": 1, "published": 1 }
}'::documentdb_core.bson
)
;
bson_dollar_unwind
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------
{ "cursor" : { "id" : { "$numberLong" : "0" }, "ns" : "db.articles", "firstBatch" : { "_id" : { "$numberInt" : "20" }, "title" : "Article about postgresql and postgresql", "published" : { "$date" : { "$numberLong" : "1577908800000" } } } }, "ok" : { "$numberDouble" : "
1.0" } }
{ "cursor" : { "id" : { "$numberLong" : "0" }, "ns" : "db.articles", "firstBatch" : { "_id" : { "$numberInt" : "40" }, "title" : "Article about postgresql and postgresql", "published" : { "$date" : { "$numberLong" : "1577980800000" } } } }, "ok" : { "$numberDouble" : "
1.0" } }
{ "cursor" : { "id" : { "$numberLong" : "0" }, "ns" : "db.articles", "firstBatch" : { "_id" : { "$numberInt" : "60" }, "title" : "Article about postgresql and postgresql", "published" : { "$date" : { "$numberLong" : "1578052800000" } } } }, "ok" : { "$numberDouble" : "
1.0" } }
{ "cursor" : { "id" : { "$numberLong" : "0" }, "ns" : "db.articles", "firstBatch" : { "_id" : { "$numberInt" : "80" }, "title" : "Article about postgresql and postgresql", "published" : { "$date" : { "$numberLong" : "1578124800000" } } } }, "ok" : { "$numberDouble" : "
1.0" } }
{ "cursor" : { "id" : { "$numberLong" : "0" }, "ns" : "db.articles", "firstBatch" : { "_id" : { "$numberInt" : "100" }, "title" : "Article about postgresql and postgresql", "published" : { "$date" : { "$numberLong" : "1578196800000" } } } }, "ok" : { "$numberDouble" :
"1.0" } }
(5 rows)
postgres=# EXPLAIN (COSTS OFF, ANALYZE ON, BUFFERS ON, VERBOSE ON)
SELECT documentdb_api_catalog.bson_dollar_unwind(
cursorpage, '$cursor.firstBatch'
) FROM documentdb_api.find_cursor_first_page(
'db', '{
"find": "articles",
"filter": { "words": "postgresql", "category": "tech" },
"sort": { "published": 1 },
"limit": 5,
"projection": { "_id": 1, "title": 1, "published": 1 }
}'::documentdb_core.bson
)
;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
ProjectSet (actual time=0.294..0.295 rows=5 loops=1)
Output: bson_dollar_unwind(cursorpage, '$cursor.firstBatch'::text)
Buffers: shared hit=14
-> Function Scan on documentdb_api.find_cursor_first_page (actual time=0.284..0.284 rows=1 loops=1)
Output: cursorpage, continuation, persistconnection, cursorid
Function Call: documentdb_api.find_cursor_first_page('db'::text, '{ "find" : "articles", "filter" : { "words" : "postgresql", "category" : "tech" }, "sort" : { "published" : { "$numberInt" : "1" } }, "limit" : { "$numberInt" : "5" }, "projection" : { "_id" : {
"$numberInt" : "1" }, "title" : { "$numberInt" : "1" }, "published" : { "$numberInt" : "1" } } }'::bson, '0'::bigint)
Buffers: shared hit=14
Planning Time: 0.046 ms
Execution Time: 0.308 ms
(9 rows)
This is extremely fast, reading only 14 buffers, including additional lookups inside find_cursor_first_page, and is as efficient as the B-tree index on the SQL table, but on a flexible document collection.
Extended RUM is an extension of the RUM access method that shares the same on-disk page layout but overrides the scan, ordering, and cost-estimation entry points. The key addition is the ordered composite index, which matches the features of a multi-key index in MongoDB.
The find_cursor_first_page function executes everything inside a C function, so PostgreSQL's EXPLAIN only sees it as a black-box Function Scan node. To see the internal plan, I use bson_aggregation_pipeline, which generates inline SQL that the planner can optimize and expose:
postgres=# EXPLAIN (COSTS OFF, ANALYZE ON, BUFFERS ON, VERBOSE ON)
SELECT document FROM documentdb_api_catalog.bson_aggregation_pipeline(
'db',
'{"aggregate": "articles", "pipeline": [
{"$match": { "words": "postgresql", "category": "tech" }},
{"$sort": { "published": 1 }},
{"$limit": 5},
{"$project": { "_id": 1, "title": 1, "published": 1 }}
], "cursor": {}}'::documentdb_core.bson
);
... (truncated)
July 02, 2026
How CRED uses Amazon RDS Blue/Green Deployments at scale
Why Percona Backup for MongoDB Is the Right Choice for Production Backups
When you’re running MongoDB in production, backups are non-negotiable. But not all backup strategies are equal. The gap between a good backup strategy and a bad one only becomes visible at the worst possible moment: when you actually need to restore. Many teams reach for the most familiar tools first: Volume snapshots or mongodump/mongorestore + … Continued
The post Why Percona Backup for MongoDB Is the Right Choice for Production Backups appeared first on Percona.
Still on MySQL 5.7 or 8.0? Those high-severity CVE fixes are covered
Upstream MySQL published an out-of-schedule release this week with two high-severity CVE fixes. If you’re running Percona Server for MySQL 5.7 or 8.0 under Extended Lifecycle Support (ELS), the program we previously called Post EOL Support, you don’t have to do anything to qualify for them. We’ve already applied the fixes and re-released the affected … Continued
The post Still on MySQL 5.7 or 8.0? Those high-severity CVE fixes are covered appeared first on Percona.