Database

Database / 19 Sep 2026 / 5 min read

Run an Embedded Turso Database in SvelteKit Without Turso Cloud

A SvelteKit application does not need Turso Cloud to use Turso. The @tursodatabase/database package can open a database file directly inside the Node.js process: import { connect } from '@tursodatabase/database'; const db = await connect('local.db'); There is no database URL, authentication token, or network round trip in this configuration. The application reads and writes a local database file.

Database Turso

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

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

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