It is 3 AM at a trading platform. API pods are healthy, CPU is calm, memory is fine. Every request still times out. The database is up. It answers `SELECT 1` in milliseconds. And yet nothing works. This is the pattern that makes databases uniquely dangerous for SREs. A stateless service that is unhealthy usually looks unhealthy - crashing pods, failed probes, visible errors. A database under stress often looks perfectly fine on the surface while the actual bottleneck, a connection pool, a replication lag, a stuck vacuum, sits one layer below anything a basic health check would catch. ### Why this module is different from the rest of the roadmap Earlier modules taught you to reason about stateless compute - pods, autoscaling, circuit breakers. A database is the one component in most architectures that cannot simply be replaced with a fresh copy when it misbehaves. It holds state that took years to accumulate. This changes the entire reliability posture - you cannot always just restart it, and every fix has to consider what happens to data that is mid-flight. ### Where this connects to the rest of the SRE roadmap This builds directly on the Kubernetes reliability module - the same PersistentVolumeClaim and CSI driver concepts apply here, but now with a stateful workload that actively cares about which node and zone it lands on. It also connects to capacity planning - a database connection pool is a finite resource with its own Little's Law relationship between concurrency and latency, exactly like the web-tier examples covered there. ---
Before touching replication, failover, or backups, you need the one concept they all depend on. ### What the WAL is The **write-ahead log (WAL)** is a sequential, append-only record of every change PostgreSQL makes to its data files, written before the change is applied to the actual table on disk. Think of it like a ship's logbook - the captain writes "turning 10 degrees north" in the log before actually turning the wheel, so that if the ship loses power mid-turn, anyone reading the log later knows exactly what was supposed to happen and can replay it. ### Why it exists Without a WAL, a crash mid-write could leave a database file in a corrupted, half-written state with no way to know what was in progress. The WAL exists so PostgreSQL can always recover to a consistent state after a crash - on restart, it replays any WAL entries that were not yet confirmed as applied to the actual data files. ### When it matters to an SRE specifically Every replication mechanism, every point-in-time recovery, and every backup strategy in PostgreSQL is built entirely on top of shipping and replaying WAL segments. If you understand the WAL, replication lag and PITR stop being separate topics and become one topic viewed from two angles. ```bash ## Check the current WAL write position on the primary psql -U postgres -c "SELECT pg_current_wal_lsn();" ## Check how many WAL files are waiting to be archived ls -la /var/lib/postgresql/data/pg_wal/ | wc -l ## high count = archiving is falling behind ``` > **Note:** LSN stands for Log Sequence Number - a monotonically increasing > position marker inside the WAL stream. Comparing the primary's LSN to a > replica's LSN is literally how PostgreSQL measures replication lag. ---
This is the PostgreSQL failure mode that catches teams who have run smoothly for years and then suddenly cannot write to their database at all. ### What autovacuum is PostgreSQL uses a technique called MVCC (multi-version concurrency control) - instead of overwriting a row when it is updated, it writes a new version and marks the old one as dead. **Autovacuum** is the background process that reclaims space from these dead row versions and updates internal statistics the query planner depends on. ### Why it exists Without autovacuum, dead rows would accumulate forever, tables would bloat to many times their real data size, and the query planner would make increasingly bad decisions based on stale statistics. Autovacuum is what keeps a long-running PostgreSQL instance from slowly rotting under its own history. ### Transaction ID wraparound - the scary part PostgreSQL identifies every transaction with a 32-bit transaction ID. That number can wrap around after roughly 2 billion transactions. **Transaction ID wraparound** is the failure mode where PostgreSQL, approaching that limit, first throws increasingly urgent warnings, and if truly ignored, stops accepting new writes entirely to protect data integrity - because letting the counter wrap silently would make old transactions look like they happened in the future, corrupting visibility rules for every row in the database. > ⚠️ **Security:** Wraparound protection is a genuine last-resort safety > mechanism, not a bug. PostgreSQL refusing writes is the database protecting > your data from silent corruption - the real failure already happened earlier, > when autovacuum was disabled or starved for too long. ### How to detect it before it becomes an outage ```bash ## Check how close any database is to the wraparound danger zone psql -U postgres -c " SELECT datname, age(datfrozenxid) AS xid_age FROM pg_database ORDER BY xid_age DESC; " ``` > **Note:** `age(datfrozenxid)` returns how many transactions have occurred > since the oldest unfrozen transaction in that database. PostgreSQL's default > warning threshold is around 200 million; the hard stop is close to 2 billion. > A healthy, well-vacuumed database should sit far below the warning line. > 🔴 **Common Mistake:** Disabling autovacuum "temporarily" during a large > batch import to improve write throughput, then forgetting to re-enable it. > Weeks later, the team is debugging a mysterious "database refusing writes" > incident that traces back to that one disabled setting. ---
### What a connection pool is Every PostgreSQL connection is a full operating system process on the server side, not a lightweight thread. **PgBouncer** is a connection pooler that sits between your application and PostgreSQL, maintaining a small set of real database connections and multiplexing many client connections onto them - similar to how a restaurant with 10 tables can serve 200 customers over an evening by turning tables over, rather than needing 200 tables. ### Why it exists Without pooling, an application that scales to hundreds of pods, each opening its own handful of direct connections, can easily exceed PostgreSQL's practical connection ceiling. Each additional native connection costs real memory and CPU scheduling overhead on the database server itself, even when idle. ### The three PgBouncer pool modes | Mode | Behaviour | When to use | |:---|:---|:---| | Session | One server connection per client for the whole session | Needed for session-level features like advisory locks | | Transaction | Server connection returned to pool after each transaction | Best default for most stateless web applications | | Statement | Server connection returned after each statement | Rarely safe - breaks multi-statement transactions | > 💡 **Tip:** Transaction mode is the right default for the vast majority of > web application workloads. Only reach for session mode if you specifically > rely on session-scoped features like `LISTEN/NOTIFY` or prepared statements > tied to a session. ---
This is one of the most common production incidents involving a database that is, itself, completely healthy. ### The failure mechanism Normal state: App pods (50) --> PgBouncer pool (20 slots) --> PostgreSQL (healthy) Step 1: A slow downstream call makes each request hold its database connection open longer than usual Step 2: New requests keep arriving at the normal rate, but connections are not being returned to the pool fast enough Step 3: The pool's 20 slots fill up and stay full Step 4: New requests queue waiting for a free connection, then start timing out Step 5: PostgreSQL itself shows near-zero CPU and no errors - it has plenty of spare capacity, it just never got asked ### Why this is so hard to diagnose live Every dashboard an on-call engineer checks first - database CPU, database memory, database disk - looks completely calm, because the bottleneck is not inside PostgreSQL at all. It is in the pool sitting in front of it. Teams that do not know to check pool utilisation specifically can lose significant time investigating a database that was never actually the problem. ### The correct sizing formula A commonly cited starting formula for PostgreSQL connection pool sizing is: ```text connections = ((core_count * 2) + effective_spindle_count) ``` > **Note:** This formula, popularized by PgBouncer and PostgreSQL performance > literature, is a starting point for the *database-side* pool size, not an > exact law. `effective_spindle_count` is typically 1 for SSD-backed storage. > The right number for your workload should be confirmed by load testing, not > just calculated once and left alone. > 🔴 **Common Mistake:** Assuming a bigger connection pool is always safer. > Oversized pools can let PostgreSQL accept more concurrent work than its CPU > and I/O can actually handle well, causing contention that makes every > individual query slower - the opposite of the intended effect. ---
### What replication is PostgreSQL **streaming replication** continuously ships WAL segments from a primary to one or more replicas, which replay them to stay in sync. It is like a second scribe copying the ship's logbook in real time from another room - usually only seconds behind, but never guaranteed to be perfectly caught up. ### Why replication lag exists at all Replicas apply WAL asynchronously by default. If the replica's hardware is slower, the network between primary and replica is congested, or the replica is busy serving heavy read queries, the gap between "the primary wrote this" and "the replica has applied this" grows. **Replication lag** is exactly that gap, usually measured in bytes of WAL behind, or in seconds. ### When lag actually becomes an incident A small, stable lag of a few hundred milliseconds is often harmless for read-replica traffic. Lag becomes a real problem when an application reads from a replica immediately after writing to the primary and gets stale data - a classic case in a fintech context is a user completing a payment, then immediately checking their balance on a lagging replica and seeing the old number. ```bash ## Check replication lag directly, in seconds, from the replica's own view psql -U postgres -c " SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag; " ``` > 📌 **Remember:** Replication lag should be a first-class SLI for any > service that reads from replicas. Alert on it the same way you would alert > on request latency - a growing lag trend often reveals a struggling replica > well before anything else does. ---
It is 3 AM at a trading platform. API pods are healthy, CPU is calm, memory is fine. Every request still times out. The ...
Before touching replication, failover, or backups, you need the one concept they all depend on. What the WAL is The writ...
This is the PostgreSQL failure mode that catches teams who have run smoothly for years and then suddenly cannot write to...
What a connection pool is Every PostgreSQL connection is a full operating system process on the server side, not a light...
This is one of the most common production incidents involving a database that is, itself, completely healthy. The failur...
What replication is PostgreSQL streaming replication continuously ships WAL segments from a primary to one or more repli...
What PITR is Point-in-time recovery (PITR) restores a PostgreSQL database to any exact moment in its history by combinin...
What the CloudNativePG operator does Running PostgreSQL reliably on Kubernetes by hand - managing replication, detecting...
Redis is commonly treated as "just a cache" until the incident where it was not, and losing its data mattered a great de...
Sentinel - high availability for a single dataset Redis Sentinel monitors a primary and its replicas, and automatically ...
What eviction policies do When Redis reaches its configured maxmemory limit, an eviction policy decides what happens nex...
This section connects directly back to the Kubernetes reliability module - the same PersistentVolumeClaim mechanics appl...
Choosing database-specific SLIs The Four Golden Signals still apply, but a database needs its own concrete translation o...
Set up a PostgreSQL primary and one streaming replica in a scratch namespace, using the CloudNativePG operator. Trigger ...
Concept Key fact WAL Underlies replication, PITR, and crash recovery - one mechanism, three uses Autovacuum Must never b...
Aligns directly with DevOps, Site Reliability (SRE), and Platform Engineering job descriptions.