Postgres

Eligible SELECT CTEs can fold into their parent or form a shared materialized result. A modifying CTE passes rows through RETURNING.

A Common Table Expression names a query you can reuse

A common table expression gives a query result a name within one SQL statement. It can make a complex report readable by naming populations and intermediate calculations before the final SELECT. That name is not a persistent table, and it does not by itself promise a cached result, a particular scan count, or a sequence of procedural steps. ...

EXPLAIN ANALYZE performs real work. After savepoint rollback, table changes are restored, but a consumed sequence value remains consumed. The article example is illustrated, not rerun.

EXPLAIN ANALYZE runs the query to show real cost

EXPLAIN describes a PostgreSQL execution plan; EXPLAIN ANALYZE also executes the statement and measures its work. The useful comparison is between estimated rows and observed rows at the same node. Neither number is a verdict by itself: an estimate is a model, while a measurement describes one execution under particular data, cache, concurrency, and instrumentation conditions. ...

Two literal predicates converge into one parameterized shape, which feeds separate total-time and mean-time summaries within the same identity and collection window.

pg_stat_statements ranks query work within a collection window

pg_stat_statements groups statement executions and accumulates their costs. It can identify a frequently executed short query whose total time is substantial, as well as an individually slow query. These are different questions. Neither ranking alone proves which change will improve application latency, and execution time is not the same as CPU consumption, storage latency, or end-to-end request duration. ...

One JSON input forks into json text retaining duplicate a keys and a jsonb object with only a=3; both can extract fields.

Postgres jsonb stores JSON for querying

PostgreSQL offers two JSON storage types with different representation costs. json validates and retains the input text, while jsonb converts it into a decomposed binary value. Both support extracting fields and paths. Choosing between them is primarily a decision about preserving representation, processing documents repeatedly, and supporting the operators your queries need. ...

Session ownership steps from two acquisitions to one and then zero through two unlocks; rollback leaves both acquisitions held.

Advisory lock lifetime belongs to its connection or transaction

An advisory lock lets an application coordinate work using an identifier that need not correspond to a table row. For example, workers can agree that only one connection may refresh a particular report at a time. PostgreSQL manages ownership and contention, while the application defines what the identifier means and which work requires it. ...

The received-and-durable WAL track passes a target while the replay track stops behind it at a pause marker.

Postgres streaming replication keeps a warm standby

Physical streaming replication sends PostgreSQL WAL to a standby, which replays the records to maintain another copy of the cluster. It can send records without waiting for a whole segment to finish. This is distinct from logical replication of selected table changes. A running receiver and a reachable standby are useful health signals, but neither establishes that a particular committed row is already visible there. ...

A broken connection takes a replacement path, while a live connection with a canceled statement takes a rollback-and-reuse loop.

Postgres connections are expensive, so pool and reuse

A warm pool reduces repeated PostgreSQL connection setup, but an idle connection can disappear while it sits in that pool. Server termination, network failure, or an intermediary’s idle policy can leave a client holding a socket that is no longer usable. Reuse must include failure detection and replacement; keeping an object in a pool is not evidence that its next query will succeed. ...

A five-value reservation marks returned one and three, rolled-back two, and never-returned four and five, with a clean-exit abandonment bracket and next backend starting at six.

Postgres sequences hand out numbers with a cache

A PostgreSQL sequence cache reserves values for a database backend, not for an application object or one transaction. The distinction explains why the sequence’s stored state can run ahead of values actually returned, why reconnecting can create holes, and why making CACHE smaller cannot make identifiers gapless. Start by separating allocation state from the rows the application successfully committed. ...