Capstone: Production PostgreSQL Cluster

This capstone integrates the entire course into one deliverable: design and operate a production-grade PostgreSQL cluster that is highly available, secure, performant, observable, and recoverable. You will bring together schema and index design, partitioning, transactions and MVCC-aware maintenance, replication and automatic failover, connection pooling, backups with point-in-time recovery, security with roles and RLS, and deployment — the full operational picture of running PostgreSQL well.

The scenario is the production database tier for a multi-tenant SaaS application: it must stay available through node failures, isolate tenants' data, sustain high read/write concurrency, recover from mistakes, and be observable enough to operate confidently. The point is integration — making replication, pooling, backups, security, and maintenance work together as one coherent, resilient system rather than as isolated features.

Analogy🏏Cricket

🏏 Think of it like cricket: Just as running a season's full statistics operation demands more than recording scores — you need organised archives by season, quick-reference leaderboards, and efficient ways to answer 'top scorers this month' without re-reading every scorecard, an analytics database demands partitioning, indexing, and precomputed summaries working together. The insight is that performance at scale is an orchestration of techniques, not one trick: the season's stats desk is fast because every part — archives, indexes, summaries — is designed to support the questions actually asked. The project's real lesson is how the parts reinforce each other, the way a stats desk's systems do: the season archives (range partitions) mean any monthly question touches one slim volume, which keeps each volume's quick-reference lists (per-partition indexes) small and sharp, which makes the evening leaderboard recompute (the materialised view refresh) fast enough to run on schedule — remove any layer and the others strain.

Learning Objectives

Integrate the whole course: schema/indexing, partitioning, HA, pooling, backups, security, and observability.
Deliver high availability with streaming replication and automatic failover that prevents split-brain.
Enforce security: least-privilege roles, multi-tenant Row-Level Security, and encrypted, verified connections.
Protect data with point-in-time recovery (base backups + WAL archiving), proven by a tested restore.
Scale connections with a pooler and route reads to replicas and writes to the primary.
Operate with observability and healthy maintenance: monitoring, autovacuum tuning, and partition lifecycle.

Technical Requirements

A primary plus at least two replicas with automatic failover (Patroni + a distributed consensus store, or a Kubernetes operator).
PgBouncer (transaction pooling) in front, with reads routed to replicas and writes to the primary.
Continuous physical backups with WAL archiving for PITR (pgBackRest/Barman/WAL-G), stored off-host.
Security: non-superuser app roles, separate DDL/DML roles, FORCE Row-Level Security for tenant isolation, and TLS (verify-full).
A well-indexed, partitioned schema for the largest tables, with autovacuum tuned per hot table.
Monitoring (a Prometheus exporter or equivalent) with alerts on replication lag, bloat, disk, and backup success.

Architecture & Design

The cluster is a primary with two streaming replicas, orchestrated for automatic failover by Patroni backed by an odd-sized etcd quorum (or an equivalent Kubernetes operator). PgBouncer fronts the cluster in transaction mode, sending writes to the current primary and reads to replicas via the routing the orchestrator maintains. Continuous WAL archiving plus periodic base backups to off-host object storage provide point-in-time recovery independent of the replicas.

Security is layered throughout: applications connect over TLS as least-privilege, non-owner roles; a separate role runs migrations; and FORCE Row-Level Security enforces tenant isolation in the database itself. The schema partitions the largest tables by time, indexes match the hot queries, autovacuum is tuned per busy table, and a metrics exporter feeds dashboards and alerts. Every module's concern has a defined place in this one design.

Analogy🏏Cricket

🏏 Think of it like cricket: Just as a stats desk keeps raw ball-by-ball logs archived by match but publishes precomputed leaderboards updated each evening, so fans read the leaderboard instantly rather than waiting for every log to be re-tallied, your design keeps raw events partitioned while serving dashboards from refreshed materialised views. The insight is that you separate the firehose of raw records from the polished summaries — ingest cheaply, summarise periodically, and let readers consult the summary, not the firehose. The separation buys each side its own optimisation: the raw log side is tuned for cheap, relentless ingest — append-only entries into the current season's volume, minimal indexes on the hot partition, no triggers recomputing aggregates per ball — while the leaderboard side is tuned purely for reading: precomputed, indexed, small. The seam between them is the refresh, with its own disciplines: run it concurrently so fans never stare at a blank board mid-update, schedule it to follow close of play rather than a blind timer, and stamp it 'accurate as of stumps' — freshness as an explicit contract, not an accident.

sql

-- Topology (conceptual): one writable primary, two read replicas, automatic failover
--
--   app --> PgBouncer(tx pooling) --> [ Patroni-managed cluster ]
--                                       primary  (writes)  <-- etcd quorum elects leader
--                                       replica1 (reads)
--                                       replica2 (reads)
--                                   --> WAL archive + base backups (off-host, PITR)
--
-- Security & isolation applied across it:
ALTER TABLE tenant_data ENABLE ROW LEVEL SECURITY;
ALTER TABLE tenant_data FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_iso ON tenant_data
    USING (tenant_id = current_setting('app.tenant')::bigint)
    WITH CHECK (tenant_id = current_setting('app.tenant')::bigint);

Phase 1 — HA Cluster, Pooling, and Routing

Build the availability foundation first: a primary and two streaming replicas with replication slots, orchestrated by Patroni and an odd-sized etcd quorum (or a Kubernetes operator) so a primary failure triggers automatic, split-brain-safe promotion. Put PgBouncer in front in transaction mode, routing writes to the leader and reads to replicas.

Verify the core HA loop by drilling failover: kill the primary, confirm a replica is promoted within your RTO, and confirm clients — via the pooler and the orchestrator's leader routing — follow to the new primary automatically. This phase delivers a cluster that survives node loss and absorbs connection load before any data or security work is layered on.

Analogy🏏Cricket

🏏 Think of it like cricket: Just as a tournament first secures its grounds, reserve venues, and succession-of-officials protocol before worrying about ticketing or broadcast, you stand up HA, pooling, and routing before data and security concerns. The insight is that availability is the load-bearing foundation — there is no point refining the schema or security if the cluster cannot survive a node failure or handle the connection load, so the resilient base comes first. The dependency logic runs deeper than priority: later work physically builds on the base, so reversing the order forces rework — data loaded before the cluster is replicated must be re-seeded onto the eventual topology; applications wired to a single node's address must be re-pointed once routing exists; even security hardening assumes the final shape, since the replication channel and the DCS only get locked down if they exist to be locked. Standing the foundation up first also means the hardest guarantees are tested while the system is simple: a failover drill on an empty cluster isolates the mechanics — lease, promotion, rerouting — with nothing else to confound the result, exactly as a venue certifies its floodlights and evacuation routes before any spectators arrive.

sql

# Patroni cluster (3 nodes) + etcd quorum; PgBouncer routes by role.
# Drill the failover loop and confirm clients follow:
patronictl -c /etc/patroni.yml list                 # confirm 1 leader, 2 replicas
docker stop pg-primary       # or: patronictl switchover (planned)
patronictl -c /etc/patroni.yml list                 # a replica is now Leader
# PgBouncer 'app_rw' points at the leader, 'app_ro' at replicas:
#   [databases]
#   app_rw = host=patroni-leader  port=5432 dbname=app
#   app_ro = host=patroni-replicas port=5432 dbname=app  pool_mode=transaction

Phase 2 — Schema, Security, and Performance

On the HA base, build the multi-tenant schema: partition the largest tables by time, design indexes (composite, partial, covering) to match the application's hot queries validated with EXPLAIN ANALYZE, and tune autovacuum per busy table so MVCC bloat is controlled. This is where the indexing, partitioning, and maintenance modules become concrete in one schema.

Apply security in depth: applications connect over TLS (verify-full) as least-privilege, non-owner roles, with a separate role for migrations; revoke broad PUBLIC defaults; and enforce tenant isolation with FORCE Row-Level Security so no application bug can leak across tenants. Set the tenant per request via a session setting that the RLS policy reads.

Analogy🏏Cricket

🏏 Think of it like cricket: Just as, with the grounds secured, the organisers turn to preparing each pitch precisely, setting access rules for each area, and drilling the players for performance, you turn from HA to schema design, access control, and query tuning. The insight is that once the venue is safe and available, the work shifts to making it perform and keeping each party to its proper area — the right pitch, the right pass, the right preparation, all atop the secure foundation. The three workstreams share a discipline from the earlier lessons, now applied together: each is designed against its actual demand, not in the abstract — the pitch is prepared for the teams actually playing (schema and indexes derived from the real query patterns, verified with EXPLAIN against production-scale data), the access rules assume passes will be stolen (roles scoped to least privilege, RLS for tenant isolation, so a leaked application credential has a bounded blast radius), and the drilling is measured, not felt (pg_stat_statements naming the queries that dominate load, baselines recorded before each tuning change).

sql

-- Partitioned, indexed, isolated tenant table; autovacuum tuned for churn
CREATE TABLE tenant_data (
    id bigint GENERATED ALWAYS AS IDENTITY, tenant_id bigint NOT NULL,
    created_at timestamptz NOT NULL, payload jsonb,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);
CREATE INDEX ON tenant_data (tenant_id, created_at DESC);          -- hot-query index
ALTER TABLE tenant_data SET (autovacuum_vacuum_scale_factor = 0.02); -- control bloat

-- Least privilege + isolation
CREATE ROLE app_runtime LOGIN PASSWORD '...';   -- DML only, not owner, not superuser
REVOKE ALL ON SCHEMA public FROM PUBLIC;
-- (RLS enabled + FORCED with the tenant policy from Architecture above)

Phase 3 — Backups, Observability, and Recovery Drills

Protect the data independently of the replicas: configure continuous WAL archiving and periodic base backups to off-host object storage with a tool like pgBackRest, enabling point-in-time recovery. Then prove it — perform a PITR restore to a scratch environment targeting a moment before a simulated bad change, and confirm the data comes back correctly.

Add observability: a Prometheus exporter feeding dashboards and alerts on replication lag, dead-tuple bloat, disk usage, backup success, and wraparound age, so problems are caught early. Document the runbook — failover, restore, and routine maintenance (partition lifecycle via pg_cron, autovacuum monitoring) — so the cluster is genuinely operable, not just configured.

Analogy🏏Cricket

🏏 Think of it like cricket: Just as a tournament is only truly ready once it has rehearsed its rain and emergency plans, installed its broadcast and analytics, and written the operations runbook — not merely set up the grounds, you finish by drilling recovery, wiring observability, and documenting operations. The insight is that production readiness is proven by rehearsal and visibility: an untested backup or an unmonitored cluster is as fragile as an emergency plan no one has ever practised, however good it looks on paper. The finishing layer converts a working system into an operable one, and each piece has a bar to clear: the rehearsal is timed, not just attempted — kill the primary and the stopwatch runs until the application writes again; restore the backup to a scratch instance and prove the data is genuinely there (the untested backup being the sport's most reliably fatal superstition); the observability must alert on leading indicators — replication lag climbing, connections saturating, disk filling, autovacuum falling behind — because a dashboard consulted only during outages is scenery.

sql

# Continuous backup + PITR with pgBackRest, then PROVE recovery
pgbackrest --stanza=main backup --type=full          # periodic base backup
# (WAL archived continuously via archive_command)
pgbackrest --stanza=main --type=time \
   --target='2026-06-16 14:32:00' --delta restore     # PITR to a scratch host

-- Observability: alert-worthy signals to export and watch
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag FROM pg_stat_replication;
SELECT relname, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;
SELECT last_archived_time, failed_count FROM pg_stat_archiver;   -- backups succeeding?

Evaluation Rubric

High availability (25%): automatic, split-brain-safe failover; clients follow the new primary; failover drilled successfully.
Security (20%): least-privilege non-owner roles, separate DDL/DML, FORCE RLS tenant isolation, and TLS verify-full.
Backup & recovery (20%): continuous PITR configured off-host and proven by a successful tested restore.
Performance & maintenance (15%): partitioned, well-indexed schema validated by EXPLAIN; autovacuum tuned per hot table.
Connection management (10%): pooler in transaction mode with correct read/write routing.
Observability & operability (10%): monitoring and alerts on lag, bloat, disk, and backups, plus a documented runbook.

Extension Challenges: Add synchronous replication for a zero-RPO subset of critical transactions and measure the latency cost; introduce logical replication to feed a separate reporting database without loading the primary; deploy the whole cluster via a Kubernetes operator (CloudNativePG) and compare the operational effort to the hand-built Patroni setup; and run a full game-day exercise — simultaneously simulating a node failure and a PITR restore — to validate that your runbook, monitoring, and team response actually work under pressure.

Capstone: Production PostgreSQL Cluster

Learning Objectives

Technical Requirements

Architecture & Design

Phase 1 — HA Cluster, Pooling, and Routing

Phase 2 — Schema, Security, and Performance

Phase 3 — Backups, Observability, and Recovery Drills

Evaluation Rubric

Submit your capstone project