PostgreSQL

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 / 5 min read

PostgreSQL Hash Aggregation Spills Groups in Batches

A HashAggregate node does not require every group to remain in memory for the full query. If the hash table grows past its executor memory limit, PostgreSQL can retain active groups in memory while routing tuples for additional groups into temporary batches. Those batches are processed later, so hash aggregation can complete without allowing an unexpectedly large group set to consume unbounded memory. This behavior matters because the planner chooses an aggregation strategy from estimates, while the executor has to handle the rows that actually arrive. A cardinality estimate can be imperfect, data can change after statistics were collected, and a grouping key can produce far more distinct groups than a small sample suggests. Disk-backed hash aggregation provides an execution path for those cases.

PostgreSQL Database

Database / 15 Sep 2026 / 6 min read

PostgreSQL Full-Page Writes Raise WAL Volume After Checkpoints

A PostgreSQL page modified for the first time after a checkpoint can generate much more WAL than a later modification to the same page. With full_page_writes enabled, the first protected change records a full page image so crash recovery can reconstruct a page even if an operating-system failure interrupts a physical page write. That protection creates a recurring WAL pattern tied to checkpoint boundaries. A checkpoint resets the condition for pages, and subsequent writes gradually encounter pages that need a new full page image.

PostgreSQL Database

Database / 15 Sep 2026 / 5 min read

PostgreSQL Full Page Writes Repair Torn Pages

A PostgreSQL data page can be larger than the atomic write unit provided by storage. If the host fails while a page is being written, part of that page may reach durable storage while another part remains from an older version. Recovery cannot safely apply ordinary change records to a page whose internal structure may already be inconsistent. full_page_writes addresses that failure mode. With the setting enabled, PostgreSQL records a complete image of a page in write-ahead log (WAL) on the first modification of that page after a checkpoint. During crash recovery, that image can replace a torn on-disk page before later WAL records are replayed.

PostgreSQL Database

Database / 15 Sep 2026 / 6 min read

PostgreSQL Frozen Pages Bound Transaction ID Maintenance

A PostgreSQL heap page can reach a state where anti-wraparound vacuum no longer needs to inspect its tuple transaction IDs. The visibility map records this state with the all-frozen bit, allowing later aggressive vacuum work to skip the page until a data change invalidates that fact. This is separate from reclaiming dead tuples. A table with little update or delete activity can still require vacuum work because transaction IDs have a finite comparison range. Freezing converts sufficiently old tuple transaction metadata into a form that remains valid across transaction ID wraparound.

PostgreSQL Database

Database / 15 Sep 2026 / 6 min read

PostgreSQL Extended Statistics Model Correlated Columns

PostgreSQL normally collects statistics for each column independently. That model works well when predicates on separate columns are close to independent, but it can misestimate row counts when the values move together. A table might store country_code and currency_code, for example. If most rows with country_code = 'JP' also have currency_code = 'JPY', multiplying the two single-column selectivities treats a strong relationship as coincidence. The resulting cardinality estimate can be far below the actual row count.

PostgreSQL Database

Database / 15 Sep 2026 / 5 min read

PostgreSQL Execution-Time Partition Pruning Removes Subplans

A PostgreSQL plan can contain partition subplans that never execute. When a partition key predicate depends on a value unavailable during planning, the executor can apply partition pruning after that value becomes available and skip partitions whose bounds cannot match it. This behavior matters for prepared statements, parameterized nested-loop joins, and predicates fed by subqueries. In these cases, the set of relevant partitions can become narrower after the planner has already produced the plan.

PostgreSQL Database

Database / 15 Sep 2026 / 5 min read

PostgreSQL Deferrable Unique Constraints Postpone Conflict Checks

A uniqueness rule can be valid for a transaction even when an intermediate statement temporarily creates duplicate keys. PostgreSQL supports that distinction through deferrable unique constraints: enforcement can move from each modifying statement to a later constraint-check point. This behavior changes transaction semantics rather than removing the rule. Duplicate values may exist transiently in transaction-local work, but a deferred constraint still has to be satisfied before the transaction can commit successfully.

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