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. ...
A pool leak is an ownership failure: application work finishes, but its borrowed connection remains checked out. The database may still answer other clients normally while this application has no leases left. Treat release as part of the operation’s contract, including exceptions and early returns, rather than a cleanup courtesy after success. ...
A B-tree updates a page where the row lives, which means a random-key write pattern turns into a random-page write pattern. An LSM tree refuses that trade: it appends, and rewrites in bulk later. Everything else about the design — the memtable, the bloom filters, the compaction policy — follows from that one refusal. ...
A checkpoint records progress that lets a database recover without treating its entire log history as outstanding work. The exact operation depends on the engine. PostgreSQL synchronizes the data changes required for a checkpoint and establishes a redo starting point. SQLite in WAL mode transfers eligible committed page frames from its WAL into the main database file. Neither operation is simply another name for transaction commit. ...
Parameter binding gives a database value a designated position in a SQL statement. A name containing a quote or a semicolon remains a value instead of becoming another clause. Reusing a named prepared statement adds a separate optimization: the server can retain statement information across executions in one session. Safe binding does not require the application to create a named statement for every query. ...
A partial index stores entries only for rows satisfying its predicate. This can reduce index size when the useful subset is small, but it can also enforce a rule that applies only to that subset. For example, a seat may have many historical reservations while allowing at most one active reservation. A partial unique index expresses that rule at the database boundary. ...
A covering index contains the values needed by a particular query. PostgreSQL can sometimes return those values through an index-only scan, avoiding heap visits for qualifying rows. The word “sometimes” matters: having the columns is a structural requirement, while visibility information determines whether execution can actually skip the heap. An Index Only Scan label alone does not prove zero table access. ...
A query planner chooses an execution path using estimated work. It does not run every candidate, time them, and keep the winner. PostgreSQL compares possible scans, joins, sorts, and other operations using statistics and a cost model. The chosen plan is therefore a prediction under current assumptions. Understanding a wrong prediction requires identifying which assumption failed, rather than treating a sequential scan or a nested loop as inherently suspicious. ...