Database internals

What does a database buffer pool cache?

Recently used database pages in memory, including dirty pages not yet written back. A cache hit avoids some storage I/O but still incurs CPU, synchronization, and possibly decompression or visibility work.

Why can a committed row exist only in a dirty memory page temporarily?

With a suitable WAL durability protocol, the committed change is recoverable from durable log records even before its data page is flushed. Commit durability does not require synchronously writing every modified table page.

What ordering does write-ahead logging require?

Log records describing a page change must reach the required durable storage before that changed page is written to durable data storage. Recovery can then redo changes whose data pages were not persisted before a crash.

What should be checked when a database acknowledges commit unusually quickly?

The configured local flush and replica acknowledgment guarantees. Asynchronous durability settings may acknowledge before the promised crash-resistant storage point; low latency can reflect a weaker durability contract.

How can group commit reduce transaction overhead?

Several transactions share a log flush rather than forcing a separate storage sync for each. It improves throughput, while batching policy, load, and storage latency affect individual commit waiting time.

What does a checkpoint contribute to crash recovery?

It establishes a recovery reference and coordinates persistence of prior changes so recovery need not replay all history. It does not replace WAL or imply every concurrent transaction has completed.

Why can checkpoints cause periodic latency spikes?

Flushing dirty pages and associated log activity can compete with foreground I/O. Inspect checkpoint timing, write volume, storage latency, and smoothing settings before assuming the query plan changed.

How do redo and undo differ conceptually?

Redo reapplies logged changes needed after a crash; undo reverses or makes uncommitted changes invisible, depending on engine design. Engines implement transaction visibility and recovery differently, so do not assume one universal undo log.

Why do B-tree-family indexes work well for ranges?

Their ordered keys and balanced structure let a lookup seek to a boundary and traverse adjacent entries. Actual cost depends on selectivity, page locality, and whether table rows must also be fetched.

What happens when an index page lacks room for a new entry?

The engine may split or reorganize the page and update parent references. This adds writes and can affect fragmentation or contention; behavior depends on key distribution and the index implementation.

How does column order affect a composite index?

It determines the lexicographic ordering and useful search prefixes. Equality conditions on leading columns often enable efficient later ranges, but optimizer features vary; validate the actual access path and sort requirements.

Why can a covering index still trigger table access in PostgreSQL?

The index contains needed values but not complete tuple-visibility information. The visibility map can allow heap visits to be skipped for suitable pages; recently modified pages may still require heap checks.

When can a partial index be useful?

When a frequently queried subset, such as active jobs, is small and has a stable predicate. The planner must establish that the query implies the index predicate; a superficially similar parameterized filter may not qualify.

What must match for an expression index to support a lookup?

The query expression, operator behavior, types, and relevant collation must be compatible with the indexed expression. Use the actual plan to verify use; indexing lower(email) is not the same as indexing raw email.

Why can adding indexes slow an otherwise unchanged write workload?

Relevant inserts, deletes, and updates maintain additional structures, generating log records and I/O. Measure read benefit against write amplification, storage, vacuum/maintenance work, and cache pressure.

How does an LSM storage engine organize writes differently from a typical B-tree engine?

Writes accumulate in memory and are flushed to immutable sorted files, often protected by a log. Compaction merges files later, shifting work from immediate page updates to background reorganization.

Why can LSM compaction affect foreground latency?

It consumes CPU, I/O bandwidth, and temporary space while rewriting sorted files. Falling behind can increase read amplification or stall writes; tune against workload and available resource headroom.

What does a Bloom filter tell an LSM reader?

A negative result can exclude a file from containing the key under the filter's guarantees; a positive result may be false and still needs lookup. It does not return the value or eliminate all reads.

Why can deleting a key initially increase storage work?

A tombstone records the deletion so older values remain hidden until safe compaction or garbage collection. Reclaiming bytes must respect snapshots, replicas, and retention; deletion is not always immediate physical removal.

How does MVCC let readers and writers overlap?

Readers select visible row versions under a snapshot while writers create or modify versions according to the engine's protocol. This reduces some read/write blocking but does not eliminate locks or transaction conflicts.

What determines whether a row version is visible to a transaction?

The database's snapshot and transaction-visibility rules, including commit state and isolation level. Wall-clock timestamps alone do not generally determine visibility, and a transaction may see its own writes.

Why can an idle long-running transaction harm storage maintenance?

Its snapshot or transaction state may keep old row versions relevant and delay cleanup. Inspect long transactions, replication retention, and blockers rather than blaming vacuum solely for growing tables.

What does ordinary PostgreSQL VACUUM usually do with dead-row space?

It makes eligible space reusable within table and index storage and updates maintenance metadata. It usually does not shrink the table file to minimum size; VACUUM FULL rewrites storage and takes stronger locks.

Why is transaction-ID freezing operationally important in PostgreSQL?

It preserves correct visibility of sufficiently old tuples despite finite transaction-ID space. Monitor age and maintenance progress; disabling or blocking autovacuum can create availability risk beyond ordinary table bloat.

What can a PostgreSQL heap-only tuple update avoid?

For eligible updates with space on the same heap page and no disqualifying indexed-column changes, it can avoid creating new entries in ordinary indexes. Check actual eligibility rather than assuming every non-key update is HOT.

What does SELECT FOR UPDATE protect, and what does it not automatically protect?

It locks selected rows against conflicting operations according to engine rules. It does not necessarily lock absent rows or an arbitrary business predicate; invariants over sets may need stronger isolation or other constraints.

How does a deadlock differ from a long lock wait?

A deadlock has a cycle of transactions waiting on each other's held resources. A long wait can have no cycle and persist until the holder finishes; inspect blockers and transaction scope before changing timeouts.

How does consistent lock ordering reduce deadlocks?

Transactions acquire shared resources in the same global order, reducing cycles. It does not remove all possible database locks or external waits, so keep transactions short and handle deadlock retries.

Why should isolation-level names be checked against the actual engine?

The allowed anomalies and implementation differ across databases. For example, a level named repeatable read may provide snapshot behavior with different phantom or write-conflict rules than another engine.

What should an application expect from serializable isolation?

Committed transactions behave as if executed in some serial order under the engine's guarantee. The database may abort conflicting work, so the application needs bounded whole-transaction retries and safe external effects.

In PostgreSQL Read Committed, can two SELECT statements in one transaction see different committed data?

Yes. Each statement can use a new snapshot, so concurrent commits may become visible between statements. Use the isolation or locking pattern appropriate to the required invariant.

Why can two transactions updating different rows still violate an invariant?

They can both read a shared condition and independently make changes that are safe alone but unsafe together. Row-level write conflict detection may miss this; test the set-level invariant under concurrency.

Why is a unique constraint stronger than checking for an existing row before insert?

It enforces the declared key rule atomically against concurrent operations. An application precheck can race; still handle conflicts, null semantics, and the intended scope of uniqueness.

Why can foreign-key operations create unexpected waits?

The engine must protect referential integrity while referenced or referencing rows change. Missing supporting indexes and long transactions can increase scan and lock costs; inspect both sides of the relationship.

What inputs does a query optimizer use to choose a plan?

Query structure, indexes, statistics, estimated cardinalities, cost settings, and available execution strategies. A plan is an estimate-based choice, not a guarantee of fastest execution for every parameter value.

Why is a badly estimated join cardinality often more important than the chosen join label?

An underestimate can lead to repeated probes, undersized memory, or an unsuitable join order. Compare estimated and actual rows at each stage to locate the first major divergence.

Why can independent column statistics misestimate a combined predicate?

Columns may be correlated, such as country and postal region. The optimizer can misjudge selectivity when it multiplies independent estimates; appropriate multicolumn statistics or query/schema changes may help.

When can a nested-loop join be efficient?

When the outer input is small and each inner lookup is cheap, often indexed. It can become costly when the outer cardinality is underestimated and millions of probes replace the expected few.

What resource risk comes with a hash join?

The build-side hash table needs memory; insufficient memory can cause partitioning and disk spill. Input size, skew, concurrent operators, and per-query settings determine total resource pressure.

What does a merge join need from its inputs?

Compatible ordering on the join keys, obtained from indexes or sorting. It can process ordered streams efficiently, but sorting cost and duplicate-key multiplicity still affect total work.

What should be checked before raising memory to fix a sort spill?

Actual spill size, concurrency, number of operators, cardinality estimates, and whether an index or query change avoids the sort. A per-operation setting multiplied across workers and queries can exhaust memory.

Why can a prepared statement be fast for one parameter and slow for another?

Data skew can make different parameter values favor different plans, while cached or generic planning may reuse a compromise. Compare representative values and the engine's plan-cache behavior.

Why must EXPLAIN ANALYZE be used carefully on production?

It actually executes the statement and adds measurement overhead. Modifying statements can change data, and expensive reads can consume resources; use appropriate transactions, replicas, or controlled workloads.

Which stages can contribute to replication lag?

Log generation, transport, durable receipt, replay, and query-visible application can each lag. Distinguish the measured position and inspect bottlenecks instead of treating one lag number as a complete diagnosis.

What tradeoff does synchronous replication introduce?

Acknowledgment waits for the configured replica condition, improving specified durability guarantees at the cost of latency or availability during replica/network trouble. The exact acknowledgment level must be stated.

What is required for point-in-time recovery using physical backups and logs?

A usable base backup, an unbroken required log sequence, compatible recovery configuration, and a chosen recovery target. Test restoration; merely enabling archiving does not prove all needed segments are retained.

How do logical and physical replication differ operationally?

Physical replication follows storage-level changes and usually needs closely compatible engines. Logical replication transfers selected data changes with different schema and identity requirements; neither automatically handles every DDL or external effect.

Why can an online or concurrent index build still require planning?

It consumes I/O and CPU, may wait for transactions, and has engine-specific failure and lock behavior. Verify validity afterward and budget disk space; 'online' does not mean zero impact.

What should a database recovery exercise verify beyond startup?

Committed-data correctness, application compatibility, permissions and keys, replay target, recovery time, and data-loss bounds. A process accepting connections is not evidence that the restored business state is usable.