PostgreSQL

Database / 16 Sep 2026 / 5 min read

PostgreSQL VACUUM Tail Truncation Requires an Exclusive Lock

Plain PostgreSQL VACUUM normally leaves reclaimed heap space inside the relation for later reuse. A distinct tail-truncation phase can instead shorten the relation file when a contiguous run of empty pages exists at its physical end. That phase requires an ACCESS EXCLUSIVE lock. The lock boundary makes tail truncation materially different from ordinary vacuum cleanup. Routine heap and index maintenance is designed to coexist with normal reads and writes, while shortening the physical relation requires a brief period in which concurrent table access cannot proceed.

PostgreSQL Database

Software Engineering / 16 Sep 2026 / 7 min read

PostgreSQL SKIP LOCKED Turns Row Contention Into Visible Omission

A PostgreSQL query using FOR UPDATE SKIP LOCKED can omit a row that satisfies its predicate solely because another transaction already holds a conflicting row lock. The omitted row has not stopped matching the query. It is absent from that execution because lock acquisition would wait. That behavior changes the meaning of a locking read. Ordinary selection asks which visible rows satisfy a predicate. SKIP LOCKED adds an operational condition: among qualifying rows, return only those whose requested locks can be acquired without waiting at the point PostgreSQL attempts to lock them.

Software Engineering PostgreSQL

Software Engineering / 16 Sep 2026 / 6 min read

PostgreSQL Serializable Reads Track Conflicts Without Blocking Writers

A PostgreSQL transaction at SERIALIZABLE isolation can read a set of rows while a concurrent transaction writes data relevant to that read without the reader taking a blocking row lock. PostgreSQL preserves serializable outcomes by tracking read-write dependencies and rejecting a transaction when the observed dependency structure could admit a serialization anomaly. That mechanism differs from treating every read predicate as a barrier against matching writes. The database keeps MVCC snapshot behavior, adds SIReadLock state for dependency detection, and makes transaction retry part of the isolation contract.

Software Engineering PostgreSQL

Software Engineering / 16 Sep 2026 / 7 min read

PostgreSQL Sequence Values Survive Transaction Rollback

A PostgreSQL transaction can call nextval, roll back every row change it made, and still leave the allocated sequence value consumed. The row state returns to its earlier transactional form; the sequence allocation does not. This asymmetry is intentional and places sequence generators outside the rollback semantics developers often associate with database writes. That boundary matters whenever a generated identifier is treated as more than an opaque key. A sequence provides concurrent value allocation with atomic nextval calls. It does not provide a gapless ledger, a count of committed rows, or a transactionally reversible numbering stream.

Software Engineering PostgreSQL

Software Engineering / 16 Sep 2026 / 7 min read

PostgreSQL Savepoint Rollback Releases Later Locks

A PostgreSQL transaction can remain open while a lock acquired during part of that transaction is released. If the lock was acquired after a savepoint and execution rolls back to that savepoint, PostgreSQL releases the lock immediately rather than retaining it until the outer transaction ends. That behavior creates a lock-lifetime boundary inside a transaction. The common rule that transaction locks last until commit or rollback remains useful, but savepoints add a narrower scope for locks acquired after the marked point.

Software Engineering PostgreSQL

Software Engineering / 16 Sep 2026 / 7 min read

PostgreSQL NOT VALID Constraints Separate Installation From Table Verification

PostgreSQL can add a foreign key or CHECK constraint to a populated table without proving at that moment that every existing row satisfies it. With NOT VALID, the database records the constraint, enforces it against subsequent writes, and leaves a separate verification step for historical rows. That split creates a useful migration boundary. Constraint installation changes the rules for new data immediately, while VALIDATE CONSTRAINT later establishes that pre-existing data also conforms. The two operations have different work profiles and locking behavior, so treating them as one indivisible schema change can hide an important operational distinction.

Software Engineering PostgreSQL

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

Software Engineering / 16 Sep 2026 / 5 min read

PostgreSQL Foreign Keys Turn Reference Checks Into Row Locks

A PostgreSQL insert into a child table can block a concurrent transaction that tries to delete the referenced parent row, even though the two statements modify different tables. Foreign key enforcement is not only a value lookup. The database must also prevent the referenced key from disappearing before the referencing transaction reaches its boundary. That requirement creates a concurrency relationship between child writes and parent-row changes. The relationship is narrower than a general parent-row write lock: PostgreSQL has a row-lock mode specifically compatible with updates that leave key columns intact.

Software Engineering PostgreSQL

Software Engineering / 16 Sep 2026 / 7 min read

PostgreSQL Exclusion Constraints Express Pairwise Conflicts Beyond Equality

A PostgreSQL exclusion constraint can reject two rows even when none of their stored values are equal. Its rule is pairwise: for any two candidate rows, the configured operator comparisons must not all evaluate to true. That makes the constraint suitable for invariants such as non-overlapping time intervals, where scalar uniqueness does not describe the forbidden state. The mechanism is more general than a scheduling convenience. It turns an operator-defined notion of conflict into a database constraint, with an index access method participating in conflict detection. The exact operators, their null behavior, range boundaries, and any constraint predicate determine which row pairs are legal.

Software Engineering PostgreSQL

Software Engineering / 16 Sep 2026 / 7 min read

PostgreSQL Deferrable Uniqueness Moves Conflict Detection to a Transaction Boundary

A PostgreSQL transaction can temporarily contain rows that violate a unique constraint and still remain executable. That state is possible only when the constraint is declared deferrable and its current mode is deferred. The duplicate is not accepted as valid data; enforcement has moved from the statement boundary to a later constraint-check boundary. This timing distinction changes which multi-statement transformations are representable. It also changes where an error can surface, which statements can act as conflict arbiters, and what application code can safely infer from the success of an individual write.

Software Engineering PostgreSQL

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

Software Engineering / 16 Sep 2026 / 6 min read

PostgreSQL Advisory Lock Lifetime Follows Acquisition Scope

A PostgreSQL advisory lock can survive a transaction rollback when it was acquired at session scope. The SQL transaction may have ended with no committed data changes, yet the same database session can continue holding the application-defined lock until an explicit release or session termination. That behavior places lock lifetime at an interface boundary that is easy to blur in pooled applications. Advisory locks have application-defined meaning, but PostgreSQL still gives each acquisition precise server-side scope.

Software Engineering PostgreSQL

Software Engineering / 16 Sep 2026 / 7 min read

Long PostgreSQL Snapshots Delay Dead Tuple Reclamation

A PostgreSQL transaction can remain idle while still preserving a visibility horizon that constrains cleanup elsewhere. Rows updated or deleted after that transaction acquired its snapshot may become obsolete for newer transactions, yet some older versions can remain potentially visible to the retained snapshot. VACUUM cannot reclaim a row version merely because the newest application state no longer references it. This is a direct consequence of multiversion concurrency control. Visibility and physical reclamation are separate decisions: one transaction changes which row version is current, while the database must retain versions that can still be observed by relevant snapshots.

Software Engineering PostgreSQL

Database / 15 Sep 2026 / 5 min read

PostgreSQL Visibility Map Tracks Heap Page State

PostgreSQL keeps tuple visibility metadata in heap rows, but checking every heap tuple is unnecessary when an entire page is already known to satisfy stronger conditions. A visibility map stores that page-level state in a compact relation fork. Each heap page has two corresponding bits. The all-visible bit records that every tuple on the page is visible to every current and future transaction. The all-frozen bit records that every tuple on the page is frozen. Those facts let PostgreSQL avoid work in index-only scans and vacuum operations without moving MVCC visibility data into indexes.

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

PostgreSQL Virtual Generated Columns Compute Values at Read Time

PostgreSQL 18 can keep a generated column out of the stored row entirely. A virtual generated column evaluates its expression when the column is read, while a stored generated column evaluates on write and occupies storage like an ordinary column. PostgreSQL 18 also makes the virtual form the default when neither VIRTUAL nor STORED is specified. That distinction changes where computation occurs and which expressions PostgreSQL accepts. It also affects triggers, inheritance, partitioning, and logical replication.

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 Tuple Freezing Keeps Transaction IDs Comparable

PostgreSQL transaction IDs are 32-bit values, so the normal XID space eventually wraps. MVCC visibility still has to distinguish old row versions from transactions that have not happened yet. PostgreSQL resolves that finite-number problem by freezing sufficiently old tuple versions during vacuum processing. Freezing is not primarily a space-reclamation feature. It is a correctness mechanism that lets long-lived rows remain valid as the transaction counter continues around its finite range.

PostgreSQL Database

Database / 15 Sep 2026 / 4 min read

PostgreSQL TOAST Moves Large Values Outside Heap Rows

PostgreSQL TOAST Moves Large Values Outside Heap Rows PostgreSQL heap tuples cannot span data pages. A row containing a large text, bytea, jsonb, or other variable-length value therefore cannot simply continue onto the next heap page. TOAST provides the storage mechanism that keeps such rows representable: eligible values can be compressed, moved out of line, or both. The visible SQL value does not change when this happens. The physical representation does. A heap tuple may contain a compact reference while the value’s bytes reside as chunk rows in a separate relation associated with the table.

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

PostgreSQL NOT VALID Constraints Separate Enforcement from Table Scans

Adding a constraint to a populated PostgreSQL table can combine two distinct jobs: establish enforcement for future writes and prove that every existing row already satisfies the rule. NOT VALID separates those jobs for foreign-key and check constraints. The constraint becomes active for later inserts and updates, while verification of older rows is deferred. That separation changes the locking profile of a schema migration without changing the intended final constraint. It also creates a temporary catalog state in which PostgreSQL enforces the rule but has not yet established that all stored rows satisfy it.

PostgreSQL Database

Database / 15 Sep 2026 / 4 min read

PostgreSQL Multixact Vacuum Bounds Member Age

PostgreSQL can place more than one transaction behind a tuple’s xmax. This occurs when concurrent transactions hold compatible row-level locks on the same tuple. A single transaction ID cannot represent that set, so PostgreSQL records a multixact identifier that refers to members stored outside the heap tuple. That indirection creates its own age boundary. Multixact identifiers are finite, and their member records occupy SLRU-backed storage. Old tuple metadata therefore cannot retain multixact references indefinitely. VACUUM participates in keeping both identifier age and member storage bounded.

PostgreSQL Database

Database / 15 Sep 2026 / 5 min read

PostgreSQL Join Collapse Bounds Planner Search

PostgreSQL can reorder many joins instead of treating SQL text order as a fixed execution sequence. That freedom gives the planner more candidate plans, but the search space grows rapidly as a query brings more relations into one join problem. Two planner settings, join_collapse_limit and from_collapse_limit, place boundaries on how aggressively PostgreSQL flattens query structure before it searches for a plan. These limits are not execution-time row caps. They shape planner search. Changing them can alter planning time and can also alter the set of join orders available for consideration.

PostgreSQL Database