Database durability and operations interview questions

write() copies data into the kernel's page cache. The kernel still holds it if the process dies, but power loss or a kernel crash before fsync drops it.

A process crash does not erase the page cache. write() returning is not durability. Disk failure is a separate risk, the trap here is the cache.

With synchronous_commit on, Postgres flushes WAL up to the commit record before acknowledging. Data pages are written later by checkpoints, and crash recovery replays WAL from the last checkpoint.

Writing data pages on every commit is what WAL avoids. A standby is optional and may be async. A power loss cannot flush RAM.

COMMIT returns before the WAL flush, so a crash loses up to about three times wal_writer_delay of recent commits. Unlike fsync = off, it cannot corrupt data.

Corruption is the fsync = off risk. Replication still runs. The setting governs local WAL flush too, not only standbys.

Slow queries hold pooled connections, so other requests wait on the pool and time out. Capping statement time frees connections, and the report gets fixed or moved to its own pool or a replica.

Bigger pools let more slow queries pile onto the database and slow everything. Replicas do not stop a slow query hogging a pool. Restarts only reset the problem.

Each Postgres connection is a process with memory overhead, and throughput peaks at a modest number of active connections. A pooler multiplexes many clients over few server connections.

Thousands of active backends cause context switching and lock contention. Connecting per request adds setup latency and still spikes connections. One connection per instance serialises all requests.

CDC is asynchronous by design. Product-wise, the creator sees their item from the source of truth while search catches up.

There is no shared transaction across Postgres and Elasticsearch. A synchronous pipeline couples request latency to the search cluster. Polling adds latency and still fails when the pipeline is behind.