PostgreSQL
Focused postgresql articles with clear context, practical examples, source links where needed, and honest limits.
15 articles in this section.
PostgreSQL covering indexes: when INCLUDE helps and when it does not
A covering index can reduce table reads for a common query, but it also increases write and storage cost. Use the query plan and workload, not the featu...
PgBouncer transaction pooling and prepared statement surprises
Transaction pooling reuses server connections across clients, so session assumptions and prepared statement behavior must match the PgBouncer version and driver settings.
PostgreSQL covering indexes with INCLUDE: avoid over-wide keys
INCLUDE columns can enable index-only scans without affecting key order, but every extra column increases index size and write cost.
JSONB GIN indexes: jsonb_ops vs jsonb_path_ops
The default GIN operator class supports more query forms while jsonb_path_ops can be smaller and faster for containment-heavy workloads.
PostgreSQL advisory locks: choose the lock key carefully
Advisory locks coordinate application-defined resources, but PostgreSQL does not know whether two integer keys represent the same business object.
SKIP LOCKED job queues in PostgreSQL: safe worker pattern
FOR UPDATE SKIP LOCKED lets workers claim different rows concurrently, but leases, retries, ordering, and poison jobs still need explicit design.
Retry PostgreSQL deadlocks without repeating side effects
A deadlock aborts one transaction by design, so retry the whole transaction only when surrounding side effects are idempotent or deferred.
statement_timeout vs lock_timeout in PostgreSQL
lock_timeout limits time waiting for a lock while statement_timeout limits the total statement duration; production APIs often need both at different values.
Use OLD and NEW in PostgreSQL RETURNING clauses
PostgreSQL 18 can return old and new values from data-changing statements, reducing extra reads when building change records.
Virtual generated columns in PostgreSQL 18: compute on read
Virtual generated columns avoid stored duplicate values but charge computation on reads and allow only suitable immutable expressions.
PostgreSQL uuidv7(): ordered UUIDs without application generators
PostgreSQL 18 can generate time-ordered UUIDv7 values, improving index locality while preserving globally unique opaque identifiers.
PostgreSQL COPY REJECT_LIMIT: safer bulk imports without hiding bad data
REJECT_LIMIT can keep a bulk load moving through a small number of malformed rows, but rejected data still needs an auditable.
PostgreSQL 18 EXPLAIN memory and disk fields: read them correctly
New EXPLAIN details make spills easier to see, but one execution is not enough to choose work_mem or rewrite a.
PostgreSQL 18 skip scans: when a multicolumn index starts helping
Skip scans let PostgreSQL use some multicolumn B-tree indexes even when the leading column is not constrained, but they do not make every index order.
PostgreSQL 18 asynchronous I/O: what backend teams should measure
Asynchronous I/O can improve scans and vacuum work, but an upgrade benchmark must separate storage latency, cache effects, and query-plan.