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 Tuple Freezing Bounds XID Age

PostgreSQL transaction IDs are finite. A normal transaction ID occupies 32 bits, so the numeric counter eventually wraps and reuses values. MVCC visibility cannot treat those values as an ever-growing integer sequence. PostgreSQL instead compares normal transaction IDs in a circular space, where an ID can only remain safely classifiable as old for a bounded span. Tuple freezing removes that age dependency for row versions whose creating transactions are far enough in the past. VACUUM records the tuple as frozen, allowing PostgreSQL to treat its insertion as visible to every normal transaction without relying on the original transaction ID’s position in the circular XID space.

PostgreSQL Database

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 Serializable Tracks Read-Write Conflicts

PostgreSQL Serializable isolation does not turn every read into a blocking lock. Transactions still execute against MVCC snapshots, while the database tracks read-write dependencies that can make concurrent execution inconsistent with every possible serial order. That distinction matters when an invariant spans multiple rows. Snapshot visibility can give each transaction a stable view and still permit a pair of writes whose combined result could not arise if the transactions had run one after another. Serializable Snapshot Isolation, or SSI, adds conflict detection around that snapshot model.

PostgreSQL Database

Database / 14 Sep 2026 / 5 min read

PostgreSQL Partition Pruning Skips Unneeded Tables

A partitioned PostgreSQL table can represent many physical child tables behind one logical relation. A query against the parent does not necessarily scan every child. When a predicate conflicts with a partition’s bounds, PostgreSQL can remove that partition from the plan or execution path. This behavior is partition pruning. It depends on the partition key and partition bounds rather than an index on the key. The distinction matters because pruning decides which relations can be ignored before access methods inside the remaining relations become relevant.

PostgreSQL Database

Database / 14 Sep 2026 / 5 min read

PostgreSQL Partition Pruning Removes Unneeded Partitions

A partitioned PostgreSQL table can expose one logical relation while storing rows across many physical partitions. A query that constrains the partition key does not necessarily need to inspect each child relation. Partition pruning uses the declared partition bounds to remove partitions that cannot contain matching rows. Pruning is separate from index selection. It determines which partitions remain relevant; the planner can then choose a sequential scan, index scan, bitmap scan, or another access path inside each surviving partition.

PostgreSQL Database

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 Memoize Caches Parameterized Scan Results

A nested-loop join can execute its inner plan many times. When that inner plan is parameterized by values from the outer side, repeated outer values can trigger the same inner lookup again and again. PostgreSQL can place a Memoize node above the parameterized scan so a later lookup with the same parameter key can reuse rows already produced. Memoization does not change join semantics and it does not create a persistent cache. It is an executor-level optimization attached to a particular query plan, with entries that exist only for that execution.

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