PostgreSQL

Database / 14 Sep 2026 / 6 min read

PostgreSQL HOT Updates Reduce Index Churn

A PostgreSQL UPDATE creates a new row version rather than overwriting the old tuple in place. That MVCC behavior supports concurrent readers, but a routine update can also create work in every index attached to the table. Heap-only tuples, usually called HOT updates, let PostgreSQL avoid much of that index work when the new row version meets a narrow set of conditions. HOT is not a different SQL operation. It is a storage-level optimization selected by PostgreSQL during an ordinary UPDATE.

PostgreSQL Database

Database / 14 Sep 2026 / 5 min read

PostgreSQL Extended Statistics Model Column Relations

PostgreSQL normally collects planner statistics for individual columns. That model works well when predicates can be estimated independently, but real schemas often contain related values. A country and region pair, a tenant identifier and status, or two derived date expressions can have distributions that single-column statistics cannot represent. Extended statistics add a second layer of information across multiple columns or expressions. They do not create an access path and they do not change stored table data. Their role is narrower: provide the planner with a better model for cardinality estimation when values are related.

PostgreSQL Database

Database / 14 Sep 2026 / 4 min read

PostgreSQL Expression Indexes Store Derived Keys

A PostgreSQL index key does not have to be a column copied directly from a table row. It can be the result of an expression computed from that row. The stored key then represents the transformed value, allowing a matching predicate to use ordinary indexed access instead of computing the expression across every candidate row. Case-normalized text is a compact example. An application may preserve the original spelling of an email address while searching on a normalized form:

PostgreSQL Database

Database / 14 Sep 2026 / 5 min read

PostgreSQL Exclusion Constraints Block Overlapping Ranges

A unique constraint can reject two equal scalar values, but equality is too narrow for many scheduling and allocation rules. Two reservations can have different start and end timestamps while still occupying the same interval. PostgreSQL exclusion constraints express this kind of conflict directly in the database. An exclusion constraint compares pairs of rows with declared operators. A pair is rejected when every operator comparison in the constraint is true. This turns operators such as range overlap into enforceable cross-row rules without relying on a query followed by an insert.

PostgreSQL Database

Database / 14 Sep 2026 / 4 min read

PostgreSQL Deferrable Constraints Shift Check Timing

Most PostgreSQL constraints reject invalid state as soon as the relevant statement is checked. That timing is usually desirable, but some valid multi-statement changes pass through a temporary state that violates a uniqueness, foreign-key, primary-key, or exclusion rule. A deferrable constraint changes the timing rather than the rule itself. PostgreSQL can postpone its check until transaction commit, allowing intermediate row states that would fail under immediate checking. The final transaction state must still satisfy the constraint.

PostgreSQL Database

Database / 14 Sep 2026 / 6 min read

PostgreSQL CTE Materialization Controls Planner Boundaries

A PostgreSQL common table expression can either become part of the surrounding query plan or remain a separately computed result. That distinction changes more than plan shape. It controls whether restrictions can move across the CTE boundary and whether repeated references can cause repeated computation. Since PostgreSQL 12, a non-recursive, side-effect-free CTE is eligible for folding into its parent query. PostgreSQL normally folds such a CTE when the parent references it once. Multiple references normally lead to materialization instead. MATERIALIZED and NOT MATERIALIZED make that boundary explicit when the default does not fit the query.

PostgreSQL Database

Database / 14 Sep 2026 / 5 min read

PostgreSQL BRIN Indexes Summarize Block Ranges

A PostgreSQL BRIN index does not store one index entry for every indexed row. It stores summary data for consecutive ranges of heap blocks. That distinction gives BRIN a very different cost and selectivity profile from a B-tree. The access method fits large tables where indexed values tend to follow heap location. Timestamped append-heavy data is a common shape: older values tend to occupy earlier blocks and newer values tend to occupy later blocks. A range predicate can then eliminate many block ranges using compact summary data.

PostgreSQL Database

Database / 14 Sep 2026 / 5 min read

PostgreSQL Bitmap Scans Combine Index Results

A PostgreSQL query does not need a single index that represents every useful predicate. The planner can scan separate indexes, turn their matching tuple locations into bitmaps, combine those bitmaps, and then visit the required heap pages. This is the basis of bitmap index scans and Bitmap Heap Scan plans. The mechanism sits between two familiar choices. A sequential scan reads the table broadly, while a plain index scan follows index entries to heap tuples as it encounters them. A bitmap plan first gathers locations, then performs heap access as a distinct phase.

PostgreSQL Database

Database / 14 Sep 2026 / 5 min read

PostgreSQL B-Tree Deduplication Packs Duplicate Keys

A PostgreSQL B-tree can represent several equal index keys with one physical key value followed by multiple heap tuple identifiers. This representation, called a posting-list tuple, reduces repeated key storage on leaf pages without changing the logical contents of the index. The mechanism matters most when an indexed value occurs many times. An index on a low-cardinality status column, for example, may contain thousands of entries whose key is pending. Logically those entries still identify separate table tuples. Physically, B-tree deduplication can pack groups of equal keys so the key datum is stored once for a group of TIDs.

PostgreSQL Database

Database / 02 Sep 2026 / 7 min read

PostgreSQL Partial Indexes for Focused Query Workloads

A normal PostgreSQL index contains entries for every table row that has indexable values. That is often appropriate, but some workloads repeatedly query a small, well-defined subset of a much larger table. A partial index stores entries only for rows that satisfy an index predicate. When the predicate matches a stable access pattern, the index can be smaller and cheaper to maintain than an equivalent full-table index. The trade-off is specificity: PostgreSQL can use the partial index only when it can determine at planning time that the query condition implies the index predicate.

Database PostgreSQL

Database / 02 Sep 2026 / 5 min read

Covering Indexes and Index-Only Scans for Faster Database Reads

An index normally helps a database find rows. A covering index can go further: it contains all columns needed by a query, allowing the database engine to answer some reads without fetching every matching row from the table. That can reduce random I/O for read-heavy workloads, but it also makes indexes larger and writes more expensive. Covering is a workload-specific optimization, not a reason to copy every selected column into every index.

Database PostgreSQL

Database / 01 Sep 2026 / 3 min read

Enforce Non-Overlapping Time Ranges with PostgreSQL Exclusion Constraints

Applications that schedule rooms, equipment, or maintenance windows often need a simple invariant: two active reservations for the same resource must not overlap. Checking for conflicts in application code looks easy, but concurrent transactions can both pass the check before either inserts. PostgreSQL can enforce this invariant inside the database with range types and exclusion constraints. Model the interval explicitly A half-open timestamp range includes its start and excludes its end. That lets adjacent bookings touch without overlapping.

Database PostgreSQL