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 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.
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.
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.
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.
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 Scan persisted 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.Planning section was absent, as seen in the initial EXECUTE after PREPARE, which showed Buffers: shared hit related to catalog lookups.Planning Time was brief, only covering the time to retrieve the plan from cache.Disabled: true indicator or a very high cost noted in earlier PostgreSQL versions for Seq Scan, indicating that enable_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.
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.
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.
These examples clearly show that the important planner settings are those active when PostgreSQL creates a plan, not necessarily when ... (truncated)
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)
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.
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.