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 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. ...
Because pg_stat_statements aggregates every execution of one query shape, it turns a vague “the page is slow” into a ranked table. The setup is a config line and a restart; the payoff is knowing which of your query shapes actually owns the clock. ...
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. ...
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. ...
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 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 sequence answers one number at a time under a lock, so caching turns many small locked calls into occasional large jumps. That single decision — correctness through a short lock, speed through batching — explains every sequence surprise: gaps after restarts, interleaved ids under concurrency, missing numbers in audit-sensitive tables. None of it is a bug; all of it follows from treating the sequence as a fast unique-number dispenser rather than an odometer. ...