Indexes

Database / 16 Sep 2026 / 5 min read

PostgreSQL HOT Updates Avoid New Index Entries

A PostgreSQL UPDATE can create a new physical row version without creating corresponding new entries in ordinary tuple-addressing indexes. This heap-only tuple optimization, commonly called HOT, applies when the replacement tuple remains on the same heap page and the update does not change values that disqualify HOT for the table’s indexes. That distinction matters because PostgreSQL implements MVCC updates by retaining row versions rather than overwriting a tuple in place. Without HOT, an update can add work to both the heap and every index even when the indexed key values remain stable.

PostgreSQL Database

Database / 16 Sep 2026 / 5 min read

PostgreSQL B-Tree Deduplication Compresses Duplicate Keys

A PostgreSQL B-tree leaf page can hold many index tuples with identical key values. When deduplication is applicable, PostgreSQL can represent a group of those tuples as one posting-list tuple: the indexed key appears once, followed by a sorted array of heap tuple identifiers. This representation changes physical index density without changing the logical set of index entries. Each heap tuple remains individually addressable through its TID, while repeated key material occupies less leaf-page space.

PostgreSQL Database

Database / 15 Sep 2026 / 5 min read

PostgreSQL Visibility Map Controls Index-Only Heap Fetches

An Index Only Scan can still report heap fetches. The index may contain every value required by the query, yet PostgreSQL must also establish that each matching tuple is visible to the current MVCC snapshot. Index entries do not carry enough tuple-visibility state to make that decision independently. The visibility map supplies a page-level shortcut. When the heap page referenced by an index tuple is marked all-visible, the executor can accept the tuple’s visibility without reading that heap page. When the bit is clear, the executor visits the heap tuple and performs the normal visibility check.

PostgreSQL Indexes

Database / 15 Sep 2026 / 5 min read

PostgreSQL Visibility Map Controls Index-Only Heap Access

An index can contain every value a PostgreSQL query needs and still require heap access. The missing piece is MVCC visibility: index entries do not carry enough information to prove that a tuple is visible to the current snapshot. PostgreSQL resolves that gap with the visibility map. An index-only scan checks this compact structure before deciding whether a matching index tuple can be returned without visiting the heap. The result depends on page state, not merely on index coverage.

PostgreSQL Database

Database / 15 Sep 2026 / 3 min read

PostgreSQL Unique Constraints Can Treat NULL Values as Equal

A nullable column with a conventional PostgreSQL unique constraint can contain more than one NULL. The index still rejects repeated non-null values, but null entries are distinct for uniqueness checks by default. NULLS NOT DISTINCT changes that rule and makes null entries collide with each other. This option is useful when NULL represents a single missing or unassigned state that must occur at most once within the constrained key. It changes uniqueness semantics without changing the column into NOT NULL.

PostgreSQL Database

Database / 15 Sep 2026 / 6 min read

PostgreSQL ON CONFLICT Arbitrates Concurrent Inserts

Two concurrent PostgreSQL transactions can attempt to insert the same unique key before either transaction has committed. INSERT ... ON CONFLICT resolves this race through the unique index chosen as the conflict arbiter, rather than by running a separate existence test before the insert. That distinction matters because a prior SELECT cannot reserve the absence of a row under ordinary Read Committed execution. Another transaction can insert the same key after the check. Conflict arbitration places the decision inside the write operation, where PostgreSQL can coordinate with concurrent index activity.

PostgreSQL Database

Database / 15 Sep 2026 / 5 min read

PostgreSQL HOT Updates Reuse Existing Index Entries

A PostgreSQL UPDATE creates a new tuple version rather than overwriting the old tuple in place. That MVCC behavior normally has a second cost: indexes need entries that lead scans to the new version. Heap-only tuple updates, usually called HOT updates, avoid that index work under a specific set of conditions. HOT is not a separate update command or an optimizer choice exposed in SQL. It is a storage optimization selected while PostgreSQL updates a heap tuple. Its effect is concentrated in the relationship between heap pages and indexes: an existing index entry can remain useful across multiple row versions.

PostgreSQL Database

Database / 15 Sep 2026 / 5 min read

PostgreSQL HOT Updates Keep Index Entries Stable

A PostgreSQL UPDATE normally creates a new physical row version. That MVCC behavior can also require fresh index entries, even when an application changes only a small non-key field. Heap-only tuple updates, commonly called HOT updates, avoid that index work under specific conditions by keeping successive row versions on one heap page and retaining the existing index reference. HOT is therefore a property of a particular update, not a permanent table mode. Whether an update qualifies depends on the columns it changes and the free space available on the heap page that contains the current row version.

PostgreSQL Database

Database / 15 Sep 2026 / 6 min read

PostgreSQL BRIN Unsummarized Ranges Expand Heap Rechecks

A PostgreSQL BRIN index can contain block ranges with no summary tuple. Newly completed ranges do not receive an initial summary merely because inserts crossed the range boundary. Until maintenance creates that summary, a BRIN scan cannot use range metadata to exclude the affected heap pages. This state is normal index maintenance behavior rather than index corruption. It follows from BRIN’s compact design: the index stores summaries for groups of adjacent heap pages instead of one entry per indexed row.

PostgreSQL Database

Database / 15 Sep 2026 / 3 min read

PostgreSQL BRIN Unsummarized Ranges Delay Page Skipping

A BRIN index does not maintain one index tuple for every heap row. It stores summary data for groups of adjacent heap pages. That compact structure also creates a maintenance boundary: a newly allocated block range can exist without a summary tuple, leaving the index without summary data for that range until a summarization event occurs. This state matters most on append-heavy tables. Existing summarized ranges continue to track inserted values, but a new range is not automatically given its initial summary under the default settings.

PostgreSQL Database

Database / 15 Sep 2026 / 5 min read

PostgreSQL B-Tree Skip Scan Repositions Index Searches

A multicolumn B-tree does not always require an equality condition on its first column to avoid reading the entire index. PostgreSQL can use skip scan to perform repeated targeted searches when a predicate constrains a later column and the planner estimates that repositioning will bypass enough index entries. Consider an index whose key order is (region, created_at): CREATE INDEX orders_region_created_at_idx ON orders (region, created_at); A query that filters only created_at has no explicit condition on region:

PostgreSQL Database

Database / 15 Sep 2026 / 6 min read

PostgreSQL B-Tree Fillfactor Reserves Space Before Page Splits

A PostgreSQL B-tree leaf page has finite space for index tuples. When an incoming tuple belongs on a page that no longer has room, the access method must make space, and a page split can add another leaf page plus a new parent downlink. The fillfactor storage parameter controls how tightly leaf pages are packed at selected points in the index lifecycle, leaving capacity that later writes can consume. For B-tree indexes, PostgreSQL uses a default fillfactor of 90. A value below 100 deliberately exchanges denser initial storage for free space on leaf pages. That space is not a permanent reservation for a particular row or key. It is simply unused page capacity available to later index activity.

PostgreSQL Database

Database / 14 Sep 2026 / 5 min read

PostgreSQL Visibility Maps Gate Heap-Free Index Scans

An index can contain every value a query needs and still require heap access. PostgreSQL must also establish that each candidate tuple is visible to the current MVCC snapshot. Index entries do not carry enough transaction visibility state to answer that check on their own. The visibility map provides a page-level shortcut. When a heap page is marked all-visible, an index-only scan can trust that every tuple on that page is visible and return indexed values without visiting the heap tuple.

Database PostgreSQL

Database / 14 Sep 2026 / 5 min read

PostgreSQL Skip Scan Reuses Multicolumn B-Tree Prefixes

A multicolumn B-tree is ordered first by its leading key, then by later keys inside each leading-key group. That ordering normally favors predicates that constrain the left side of the index. PostgreSQL 18 can also use skip scan in selected cases where a query constrains a later key and leaves an earlier key without an equality condition. Skip scan does not turn column order into an irrelevant detail. It changes the cost of some searches by allowing the executor to perform repeated targeted probes instead of reading a large continuous span of the index.

Database PostgreSQL

Database / 14 Sep 2026 / 5 min read

PostgreSQL Partial Indexes Store Selected Rows

A PostgreSQL index does not have to represent every row in its table. A partial index adds a predicate to the index definition, and only rows satisfying that predicate receive index entries. The result is an index whose physical contents encode a condition about the table. That narrower scope changes both storage and planning behavior. Rows outside the predicate do not occupy entries in the index, but a query can use the index only when PostgreSQL can establish that the query condition implies the index predicate.

PostgreSQL Database

Database / 14 Sep 2026 / 5 min read

PostgreSQL Partial Indexes Focus Index Entries

A PostgreSQL index does not have to represent every row in its table. A partial index adds a predicate to the index definition, so only rows satisfying that predicate receive index entries. This changes both the physical scope of the index and the set of queries for which the planner can use it. The mechanism fits workloads where a stable subset of rows receives disproportionate query attention. An application might repeatedly inspect pending jobs while completed jobs remain mostly historical, or query active accounts while disabled accounts stay in the same table.

PostgreSQL Database

Database / 14 Sep 2026 / 5 min read

PostgreSQL Null-Aware Unique Constraints

A PostgreSQL unique constraint normally permits more than one null value. That behavior follows from the default treatment of nulls as distinct for uniqueness checks, and it can leave a gap when a nullable column still represents a business key. NULLS NOT DISTINCT changes that specific part of uniqueness semantics. Null values compare as equivalent for the constraint, so a second row with the same null-bearing key is rejected. Default uniqueness permits repeated nulls Consider a table that stores one external identifier per account, with the identifier optional during an initial state:

PostgreSQL Database

Database / 14 Sep 2026 / 6 min read

PostgreSQL HOT Updates Reuse Index Entries

An UPDATE in PostgreSQL creates a new row version. That MVCC behavior can imply fresh index entries even when an application changes only a non-indexed attribute. Heap-only tuple updates, usually called HOT updates, provide a narrower path: under specific conditions, PostgreSQL can link the new row version on the same heap page and keep existing index entries in place. The optimization reduces index maintenance for eligible updates. Its boundary is physical as well as logical. Unchanged indexed values are not enough; the heap page must also have room for the new tuple version.

PostgreSQL Database

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 / 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 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