--- title: "DBOps" slug: dbops category: cloud term_status: overlapping-synonym evidence_status: verified summary: "DBOps is an informal label for running databases with DevOps habits: versioned, reviewed schema migrations that never stall production, tested point-in-time recovery, replication and failover, and steady query and connection performance." audience: [Backend engineers, DBAs and database reliability engineers, SREs and platform engineers, Engineering leaders, Beginners] last_verified: 2026-10-18 key_takeaways: - "DBOps is not a standard discipline. The label shows up as a job title and a PowerShell module's name; the work behind it is long established as database administration, database reliability engineering and \"database DevOps\"." - "Schema changes usually hurt by waiting, not by running long: a schema-change (DDL) statement queued for an exclusive lock blocks every later query on the table. Give every migration a short lock_timeout and retry." - "Rename, retype or drop through expand and contract: several small deployments, each reversible by redeploying the application, with one irreversible contract step at the end." - "A replica copies a bad DELETE within milliseconds. Point-in-time recovery from a base backup plus archived write-ahead log is the undo button, and only a timed restore proves it works." - "Asynchronous replication loses the writes in flight at failover. Fence the old primary, decide the acceptable loss in advance, and expect failover to take seconds to minutes." - "Some failures build up over months: transaction ID (XID) wraparound, a 32-bit counter running out, stopped Sentry for most of a working day in 2015 and Mandrill for about 40 hours in 2019. Alert on XID age, WAL archive age and replication lag, not only on CPU." related: [dataops, backupops, disasterrecoveryops, storageops, releaseops, sre, cloudops, kubernetesops] --- ## What DBOps is ### DBOps in 30 seconds **DBOps** is an informal name for *database operations done the DevOps way*: schema changes kept in version control and applied by a pipeline without stalling production, backups that can be replayed to any second, replicas and failover that behave as planned, and queries and connections that stay fast as data grows. Its centre is the component delivery tooling tends to treat as an afterthought, the **stateful database**: you cannot simply redeploy it or rebuild it from source. The page is PostgreSQL-first, with MySQL contrasts and Amazon RDS as the managed example: principles carry over to other engines, engine behaviour does not. ::: note Honest term status: an overlapping synonym Searches on 2026-10-18 found no standard, professional body or certification that defines "DBOps". The label appears as a job title ("Principal DBOps Engineer")[^phenom] and as the name of a PowerShell module that deploys SQL scripts[^dbops-ps]. Vendors call the change-management half "Database DevOps"[^bytebase][^harness]. The practice is long established as database administration and, since O'Reilly's *Database Reliability Engineering* (2017), database reliability engineering[^dre-book]. One DBA's 2021 article even calls DevOps for databases "DataOps"[^rathbun], a different field in this Atlas. ::: **Start with:** beginners, the [glossary](#glossary-and-mental-model) and [Lab 1](#hands-on-labs); engineers, the [lock queue](#why-migrations-wait-the-lock-queue) and [expand and contract](#core-practices-and-playbooks-expand-and-contract); leaders, [metrics](#metrics-and-kpis) and the [judgement call](#a-judgement-call-a-maintenance-window-or-an-online-migration). **Last verified** 2026-10-18 (PostgreSQL 18.6 current, 19 at release candidate 1)[^pg-versions]. ### At a glance | Aspect | Summary | |---|---| | Goal | Boring schema changes; data recoverable to a chosen second; predictable latency and failover | | People | Backend engineers, DBAs or database reliability engineers, SREs, platform teams | | In scope | Migrations as code, online schema changes (DDL), database CI/CD, point-in-time recovery (PITR), replication, failover, query performance, vacuum, pooling, managed databases, upgrades | | Out of scope | Backup policy across systems ([BackupOps](../backupops/)); invoking DR ([DisasterRecoveryOps](../disasterrecoveryops/)); disks and IOPS ([StorageOps](../storageops/)); data pipelines ([DataOps](../dataops/)); row-level data policy ([DataSecOps](../datasecops/)); privileged access ([AccessOps](../accessops/)) | | Prerequisites | SQL, transactions, Linux basics; the delivery pipeline from [DevOps](../devops/) | | Outputs | Migrations and a guard, least-privilege roles, restore records, alerts, runbooks | | Term status | Overlapping synonym (note above) | ### The problem it solves Databases fail through waiting, copying and slow accumulation. - **A tiny migration takes the API down.** GoCardless lost about 15 seconds of API availability when a planned migration waited behind a slow query for a lock and "read/write queries issued by calls to our API piled up behind it"[^gocardless-hard]. - **Replication breaks under load.** At GitLab.com in January 2017 the secondary "started to lag behind", then failed because the write-ahead log (WAL, the engine's change journal) it needed was "already removed from the primary"; while rebuilding it by hand, an engineer wiped the primary's data directory by mistake[^gitlab2017]. - **Slow-burning limits.** PostgreSQL's transaction IDs (XIDs) are 32-bit, so vacuum must freeze old rows (mark them visible to all) at least every two billion transactions or the server stops assigning new IDs[^pg-vacuum]. Sentry lost "most of the US working day" to this in 2015[^sentry]; Mandrill about 40 hours in February 2019[^mandrill]. - **Delivery friction.** DORA calls database changes "often a major source of risk and delay when performing deployments"[^dora-db]. With DBOps, a schema change is a reviewed pull request that waits seconds at most for a lock, a bad write can be undone to the second, and slow counters alert before they become incidents. ### What DBOps is not: boundaries and naming - **Not DataOps.** [DataOps](../dataops/) runs data *pipelines*; DBOps runs the *engines* they read from. - **Not backup policy or disaster recovery.** An **editorial distinction**: engine-level PITR, replication and failover here; copies, retention and the restore-test policy in [BackupOps](../backupops/), whose monthly tier-1 restore test is, for a database, this page's timed PITR drill; invoking DR in [DisasterRecoveryOps](../disasterrecoveryops/). - **Not DBA-free.** Developers write most migrations, but someone owns engines, upgrades and recovery; managed services move that work rather than remove it ([NoOps](../noops/)). ## Where it comes from and where it sits ### Origins and evolution The label has no traceable origin; the practice has documented milestones: - **2003:** Sadalage and Fowler's *Evolutionary Database Design*: every change a migration script, DBAs working with developers, a "transition phase" in which old and new access paths both work (rewritten 2016)[^evodb]. - **2014:** Danilo Sato names *parallel change*, "also known as expand and contract"[^parallel]. - **2015–2017:** GoCardless's lock-queue write-ups (2015)[^gocardless-hard][^gocardless-help], Stripe's dual-write migrations[^stripe], GitLab's postmortem[^gitlab2017] and *Database Reliability Engineering*[^dre-book]. - **Since then:** online schema-change tools, migration linters, Kubernetes operators and managed blue/green switchovers ([tooling](#tooling-landscape)). ### Glossary and mental model **Mental model: one log, three duties.** Every committed write, good or bad, enters the write-ahead log (WAL in PostgreSQL, the binary log in MySQL). That one stream serves three jobs; confusing them causes recovery mistakes. ::: figure One write-ahead log, three duties: why a replica is not a backup ```mermaid %%{init: {"flowchart": {"wrappingWidth": 170, "nodeSpacing": 18, "rankSpacing": 30, "padding": 8}}}%% flowchart TB classDef core fill:#e8f3f2,stroke:#16756f,color:#122029 classDef risk fill:#fbeceb,stroke:#b4332b,color:#122029 classDef neutral fill:#f1f4f6,stroke:#5b7a87,color:#122029 C["A committed write, good or bad"]:::neutral --> W["Write-ahead log"]:::core W --> D1["1 Crash recovery: replay since the last checkpoint"]:::core W --> D2["2 Replication: stream to standbys within milliseconds"]:::core W --> D3["3 Archive: base backup plus every WAL segment"]:::core D2 --> R2["Fast failover and read scaling, but mistakes are copied too"]:::risk D3 --> R3["Point-in-time recovery: replay up to just before the mistake"]:::core ``` Text equivalent: Each committed write enters the write-ahead log, which serves crash recovery, replication (fast failover and read scaling, but mistakes are copied too) and archiving (a base backup plus every segment, allowing recovery to just before a mistake). ::: | Term | Meaning | Why it matters | |---|---|---| | Migration | A versioned script applied once, in order, and recorded[^flyway-versioned] | Unit of review, deployment and audit | | DDL, online DDL | Schema statements (`ALTER TABLE`, `CREATE INDEX`); "online" forms let reads and writes continue | Most DDL needs a strong lock, at least briefly[^pg-alter] | | ACCESS EXCLUSIVE / metadata lock | PostgreSQL's strongest table lock, conflicting "with locks of all modes"[^pg-locks]; MySQL's equivalent wait is on the metadata lock[^mysql-mdl] | A *waiting* request already blocks newcomers | | `lock_timeout` | Abort a statement that waits longer than this for a lock; off by default[^pg-client] | Turns an outage into a retryable failed deploy | | Expand and contract | Add the new, migrate clients, remove the old[^parallel] | Every step but the last stays reversible | | Base backup, WAL archive | A physical copy plus the continuous log since it began[^pg-pitr] | Together they allow point-in-time recovery (PITR) | | Replication lag | How far a standby trails, in time or bytes[^pg-stats] | Data lost at async failover; stale reads | | Fencing (STONITH) | Stopping a failed-over old primary from accepting writes[^pg-failover] | Prevents two primaries (split brain) | | XID age | Age in transactions of the oldest unfrozen row[^pg-vacuum] | Hard stop near two billion | ### How DBOps relates to other Ops The boundaries below are **editorial distinctions** of this Atlas, not industry standards. | Neighbour | Shared ground | Choose that page when… | |---|---|---| | [DevOps](../devops/) | Pipelines, DORA metrics | You need the delivery system; DBOps adds the stateful step | | [ReleaseOps](../releaseops/) | Sequencing schema and code | You decide release strategy; DBOps makes each schema step safe | | [DataOps](../dataops/) | Source databases | The problem is pipeline correctness or data contracts | | [BackupOps](../backupops/) | Backups, restore tests | You set policy, retention and immutability across systems | | [DisasterRecoveryOps](../disasterrecoveryops/) | Failover | You invoke DR, order recovery, define RTO and RPO | | [StorageOps](../storageops/) | Volumes, snapshots | The bottleneck is media, IOPS or snapshots | | [SRE](../sre/) | SLOs, postmortems | You design SLOs; DBOps supplies database SLIs | | [CloudOps](../cloudops/), [KubernetesOps](../kubernetesops/) | Managed services, operators | The question is the provider or the cluster | | [AccessOps](../accessops/), [DataSecOps](../datasecops/) | Credentials | Privileged access and data-level policy | ## Principles and operating model ### Charter and principles ::: decision Proposed charter (Atlas proposal, not an industry standard) **Mission.** Schema changes as routine as code changes; data recoverable to a chosen second; latency and failover inside agreed limits. **Goals to calibrate:** 100% of schema changes through the pipeline (DORA's goal)[^dora-db]; no migration blocks traffic longer than its `lock_timeout`; a timed PITR drill each month per tier-1 database; WAL archive age under five minutes; XID age alert at 50% of the hard limit. **Non-goals:** pipeline data quality, cross-system backup policy, DR invocation. ::: 1. **Changes are code:** reviewed, versioned migrations applied by a pipeline and recorded in the database[^evodb][^flyway-versioned]. 2. **Waiting is the danger:** every DDL statement has a short `lock_timeout` and a retry plan[^gocardless-help]. 3. **Compatible across one version:** the schema serves the running and the next application version, so the application can roll back "without requiring a database rollback"[^dora-db]; the schema only rolls forward[^flyway-undo]. 4. **Copies are not recovery:** replicas give availability; base backups plus archived WAL, restored on a schedule, give recoverability[^pg-pitr]. 5. **Decide the loss before the failure:** asynchronous replication loses what was in flight[^pg-standby]; fence before promoting[^pg-failover]. 6. **Watch the slow counters:** XID age, archive age, slots, bloat and connections, not only CPU. 7. **Least privilege by role:** the service cannot change its schema; only the migration job holds that right[^pg-priv]. Trade-offs: safety versus speed, durability versus write latency, portability versus convenience. ### Fundamentals and building blocks - **Engine:** server, buffer cache, data files, WAL (media and volumes are [StorageOps](../storageops/)). - **Change:** a migrations directory, a history table (`flyway_schema_history`, `DATABASECHANGELOG`)[^flyway-versioned][^lb-tables], a guard or linter, a runner. - **Availability:** standbys, a failover manager (Patroni, an operator such as CloudNativePG, or the cloud service)[^patroni][^cnpg], and one endpoint clients follow. - **Recoverability:** base backups and a WAL archive in a separate failure domain (pgBackRest[^pgbackrest] or managed backups[^rds-pitr]). - **Access path:** roles, a connection pooler, client timeouts. - **Evidence:** `pg_stat_statements`[^pg-pss], lock-wait and slow-query logs[^pg-logging], replication and archiver views[^pg-stats], restore records. ### Roles, ownership and RACI | Activity | Service team | DBA / DB reliability eng. | Platform / SRE | Security | |---|---|---|---|---| | Write and test a migration | R/A | C (risky classes) | I | — | | Approve a risky or contract migration | R | A | I | — | | Migration guard, runner, CI templates | C | R | A | C | | Roles and credentials for a service | C | R | C | A | | Replication, failover, engine upgrades | I | R/A | C | I | | PITR configuration and monthly PITR drill | I | R/A | C | I (evidence) | | Query performance of a service | R/A | C | I | — | | Database incident (lock storm, lag, wraparound) | R (own queries) | R/A | R (incident command) | — | R = responsible, A = accountable, C = consulted, I = informed. In small companies one platform engineer holds the DBA column. A DBA team that writes every migration becomes a queue; one that publishes guardrails and reviews only risky classes scales. ### Lifecycle: Day 0, Day 1, Day 2 | Phase | Work | Exit criteria | |---|---|---| | Day 0: decide | Engine and version (support end date[^pg-versions]); hosting ([decision framework](#decision-framework)); RPO and RTO from DR; roles; runner | A decision record per choice | | Day 1: build | Primary, standby, WAL archiving, pooler, roles, guard in CI, dashboards | First timed restore passes; first migration ships by pipeline | | Day 2: run | Regular migrations, quarterly minors, majors inside support, restore tests, capacity reviews | Inside SLOs; no manual DDL | ### Operating model and governance Classify every migration; the class sets review and timing (an **Atlas proposal** echoing the danger lists in strong_migrations and Squawk[^strong][^squawk]): | Class | Examples | Review | When | |---|---|---|---| | Expand (safe) | New table, nullable column, `CREATE INDEX CONCURRENTLY`, `NOT VALID` constraint | Peer review + guard | Any deploy | | Risky | `VALIDATE`, backfill, `SET NOT NULL`, anything that scans or rewrites | Plus DBA review, size estimate | Business hours, not before weekends | | Contract (irreversible) | `DROP COLUMN`, `DROP TABLE`, removing a sync trigger | Plus named approver, recent restore point | After a soak, scheduled | | Cadence | Activity | |---|---| | Weekly, 30 min | Health review: top statements, lock waits, lag, archive age, connections | | Monthly | Timed PITR drill per tier-1 database; guard exceptions, unused indexes, slowest migrations | | Quarterly | Minor updates[^pg-versions] | | Yearly | Major-version plan inside support dates[^pg-versions] | ## Architecture and workflow ### Reference architecture A logical reference for one PostgreSQL service, whether managed, run by a Kubernetes operator or on VMs with Patroni. ::: figure Reference architecture: the change path and the run-time path meet at the primary ```mermaid %%{init: {"flowchart": {"wrappingWidth": 150, "nodeSpacing": 16, "rankSpacing": 30, "padding": 8}}}%% flowchart TB classDef core fill:#e8f3f2,stroke:#16756f,color:#122029 classDef risk fill:#fbeceb,stroke:#b4332b,color:#122029 classDef decision fill:#fdf3e3,stroke:#c9800f,color:#122029 classDef neutral fill:#f1f4f6,stroke:#5b7a87,color:#122029 PR["Pull request: migration plus app change"]:::neutral --> CI["CI, without production secrets: guard, throwaway DB, tests"]:::core CI --> DJ["Deploy job in a protected environment: sole holder of the migrator credential"]:::decision APP["Service instances: app role, timeouts"]:::neutral --> POOL["Connection pooler"]:::core DJ --> PRI["Primary"]:::core POOL --> PRI PRI --> SB["Standby: failover target"]:::core PRI --> ARC["Separate account: base backups and WAL archive, which production cannot delete"]:::core PRI --> MON["Metrics: locks, lag, archive age, XID age, top queries"]:::neutral MON --> ON["On-call and runbooks"]:::risk ``` Text equivalent: The change path (a pull request, then CI without production secrets, then a deploy job in a protected environment, the only holder of the migrator credential) and the run-time path (service instances with the application role, then a pooler) meet at the primary. The primary streams to a standby, ships base backups and WAL to a separate account that production cannot delete from, and feeds metrics to on-call engineers. ::: | Component | Responsibility | Trust boundary | |---|---|---| | CI with a throwaway database | Guard, roles, all migrations from empty, timing on a production-sized copy, tests | No production credentials | | Deploy job | Apply pending migrations one at a time, before or after the application as the step requires | The only holder of the migrator credential ([pipeline](#reference-implementation)) | | Pooler | Cap server connections[^pgbouncer] | Holds application credentials | | Standby and failover manager | Promote on failure; fence the old primary[^pg-failover][^patroni] | Only one manager may promote | | Archive | Base backups and WAL with deletion protection ([BackupOps](../backupops/)) | Production credentials cannot delete it | ### End-to-end workflow: from pull request to contracted schema Happy path for a column rename, the hardest everyday change ([Lab 2](#hands-on-labs) runs step 3): 1. **Plan.** Classify each step (expand, risky, contract); write migrations V2 to V7 and the application changes. 2. **CI.** Guard; all migrations applied to an empty database as the migrator role and timed on a production-sized copy; tests. 3. **Expand, backfill, constrain.** V2 adds the column and sync trigger; a throttled job backfills; V3 to V6 add the index and `NOT NULL`. Rollback at each point: redeploy the old application, or drop what was added. 4. **Switch.** The application reads and writes the new column; the previous version still works because the trigger keeps the old column complete. 5. **Contract.** After a soak period and with a fresh restore point, V7 drops the trigger and the old column. Rollback: restore only. **Exception path.** A migration hits `lock_timeout`: the file rolls back, on-call finds the blocker ([triage queries](#reference-implementation)), the job retries. A concurrent index build fails: drop the `INVALID` index first ([failures](#common-failures-and-troubleshooting)). Replica lag rises: the backfill pauses. ## Changing a live schema ### Why migrations wait: the lock queue Most `ALTER TABLE` forms take PostgreSQL's ACCESS EXCLUSIVE lock, which conflicts even with a plain `SELECT`; locks are "normally held until the end of the transaction"[^pg-locks][^pg-alter]. The statement may need milliseconds; the danger is the wait. While the `ALTER` queues behind one long transaction, "all queries which arrive after it will queue up behind it"[^gocardless-help]. MySQL documents the same for metadata locks: a pending one "blocks subsequent transactions on the table"[^mysql-mdl]. ::: figure The lock queue: a millisecond ALTER that stalls a table ```mermaid %%{init: {"flowchart": {"wrappingWidth": 165, "nodeSpacing": 16, "rankSpacing": 28, "padding": 8}}}%% flowchart TB classDef core fill:#e8f3f2,stroke:#16756f,color:#122029 classDef risk fill:#fbeceb,stroke:#b4332b,color:#122029 classDef decision fill:#fdf3e3,stroke:#c9800f,color:#122029 classDef neutral fill:#f1f4f6,stroke:#5b7a87,color:#122029 T0["t = 0 s: a report opens a transaction and reads orders (ACCESS SHARE, held to commit)"]:::neutral T1["t = 1 s: ALTER TABLE orders requests ACCESS EXCLUSIVE, conflicts and waits"]:::decision T2["t = 2 s: API reads request ACCESS SHARE, conflict with the queued request and wait"]:::risk Q{"Migration has a lock_timeout?"}:::decision A["No: every request waits until the report commits; clients time out"]:::risk B["Yes, 2 s: the ALTER aborts at t = 3 s, the queue drains, the deploy retries later"]:::core T0 --> T1 --> T2 --> Q Q -->|"no"| A Q -->|"yes"| B ``` Text equivalent: A report holds a shared lock; an ALTER TABLE queues for an exclusive lock; API reads then queue behind the ALTER. Without a lock_timeout everything waits for the report; with a 2-second timeout the ALTER aborts, the queue drains and the deploy retries later. ::: [Lab 1](#hands-on-labs) reproduces both outcomes. How short a timeout? GoCardless's library defaults to 750 ms, since you "typically don't want to block the queries from your app for more than a few hundred milliseconds"[^gocardless-help]; strong_migrations' example uses 10 seconds[^strong]. Pick the longest pause your clients tolerate and set it in the migration's session, not in `postgresql.conf`, where it "would affect all sessions"[^pg-client]. ### Lock cost of common changes From the PostgreSQL 18 and MySQL 8.4 manuals (October 2026); check your version. | Change | PostgreSQL | MySQL 8.4 (InnoDB) | |---|---|---| | Add nullable column, or one with a non-volatile default (a constant) | ACCESS EXCLUSIVE, briefly; no rewrite[^pg-alter] | `INSTANT` is the default algorithm[^mysql-ddl] | | Add column with a volatile default (`clock_timestamp()`), identity or stored generated column | Rewrites the table and indexes under ACCESS EXCLUSIVE[^pg-alter] | Engine-specific; check the online DDL table | | Create an index | Plain: blocks writes. `CONCURRENTLY`: two scans, writes continue, no transaction block[^pg-cic] | In place; concurrent DML permitted[^mysql-ddl] | | Add a CHECK or foreign key | Use `NOT VALID`, then `VALIDATE` under SHARE UPDATE EXCLUSIVE; a foreign key also takes SHARE ROW EXCLUSIVE on the referenced table, blocking its writes[^pg-alter] | Foreign key: in place only with `foreign_key_checks` disabled, otherwise a table copy[^mysql-ddl]; CHECK: not listed, test it | | `SET NOT NULL` | Full scan, unless a valid CHECK proves no NULLs; on 18+, add `NOT NULL … NOT VALID`, then `VALIDATE`[^pg-alter][^pg18-notes] | In place, but rebuilds the table; DML continues[^mysql-ddl] | | Change a column's type | "normally" rewrites table and indexes[^pg-alter] | Table copy; concurrent DML not permitted[^mysql-ddl] | | Drop or rename a column | Fast (a drop reclaims space later), but breaks every running client[^pg-alter] | `INSTANT` possible, drops within row-version limits[^mysql-ddl] | One engine difference shapes migration files. PostgreSQL can run most DDL in a transaction block (`CREATE INDEX CONCURRENTLY` cannot)[^pg-cic], so a failed file rolls back; MySQL ends any open transaction before DDL "as if you had done a COMMIT"[^mysql-commit], so keep one DDL statement per MySQL migration. ### Core practices and playbooks: expand and contract DORA advises decoupling database and application changes[^dora-db]; the technique is parallel change[^parallel], Sadalage and Fowler's transition phase[^evodb]. ::: figure Expand and contract for a column rename, with the rollback position after each step ```mermaid %%{init: {"flowchart": {"wrappingWidth": 300, "nodeSpacing": 14, "rankSpacing": 24, "padding": 8}}}%% flowchart TB classDef core fill:#e8f3f2,stroke:#16756f,color:#122029 classDef risk fill:#fbeceb,stroke:#b4332b,color:#122029 classDef decision fill:#fdf3e3,stroke:#c9800f,color:#122029 S1["1 Expand: add nullable customer_name and a sync trigger. Undo: redeploy the old app"]:::core S2["2 Backfill in batches, throttled on replica lag. Undo: stop, rerun later"]:::core S3["3 Index concurrently; CHECK NOT VALID, VALIDATE, SET NOT NULL. Undo: drop them"]:::core S4["4 Switch the app to read and write customer_name. Undo: previous app still works"]:::core S5{{"Gate: has the soak period passed, and is there a fresh restore point?"}}:::decision S6["5 Contract: drop trigger and cust_name. Irreversible: restore is the only undo"]:::risk S1 --> S2 --> S3 --> S4 --> S5 S5 -->|"yes"| S6 S5 -->|"no"| S4 ``` Text equivalent: expand (new column and sync trigger), backfill, index and NOT NULL, then switch the application, each undone by redeploying the old application or dropping what was added; only after a soak and a fresh restore point does the irreversible contract step drop the trigger and old column. ::: #### Playbook 1: a guarded DDL statement 1. `SET lock_timeout` first, plus `statement_timeout` for steps that should not scan. 2. One blocking statement per transaction. 3. On a timeout the deploy fails and retries with backoff; three failures mean investigate. Acceptance: no lock wait in the log longer than the timeout[^pg-logging]. #### Playbook 2: a backfill that cannot hurt production 1. Walk the primary key (keyset: `id > last`) in batches of a few thousand rows, each a short transaction under `lock_timeout`. Never select `WHERE customer_name IS NULL … LIMIT`: each batch rescans the rows already filled, so total work grows quadratically. 2. Rely on the sync trigger for rows written meanwhile; stay idempotent, so a restart is safe. 3. Pause while `replay_lag` exceeds a limit[^pg-stats]; retry a batch that times out. Acceptance: zero rows out of step. #### Playbook 3: indexes and constraints without long locks 1. Build indexes with `CREATE INDEX CONCURRENTLY`, outside a transaction. Flyway needs `executeInTransaction=false` in a `.conf` file beside the script and `postgresql.transactional.lock = false`[^flyway-tx][^flyway-lock]; Liquibase, `runInTransaction` set to false[^lb-changeset]. 2. Check `indisvalid`: a failed build leaves an invalid index that still costs writes[^pg-cic]. 3. Add constraints `NOT VALID` (no scan), then `VALIDATE` them in a later migration under SHARE UPDATE EXCLUSIVE[^pg-alter]. 4. For `NOT NULL` up to version 17, a validated CHECK lets `SET NOT NULL` skip its scan (Lab 2's V4–V6). On 18+, add a named `NOT NULL customer_name NOT VALID` constraint and validate it instead[^pg-alter][^pg18-notes]. #### Playbook 4: rewrites that cannot be avoided Build a new structure beside the old and switch. For MySQL, gh-ost copies into a "ghost" table fed from the binary log and can throttle, pause and postpone cut-over[^gh-ost]; pt-online-schema-change uses triggers and an atomic `RENAME TABLE`, needs care with foreign keys and pauses beyond `--max-lag`[^pt-osc]. For PostgreSQL, pgroll serves old and new schema versions through views[^pgroll]; otherwise apply the column pattern above. ## Recovery, replication and performance ### Point-in-time recovery PITR combines a physical **base backup** with the **continuous WAL archive** since it began; replay can stop at any point[^pg-pitr]. The target is a time, a named restore point or a transaction, and `recovery_target_action` pauses (the default), promotes or shuts down on arrival[^pg-recovery]. Four rules decide whether it works on the day[^pg-pitr]: 1. The archive command returns zero "if and only if it succeeds" and should refuse to overwrite an existing file. 2. Recovery needs an unbroken WAL sequence back to the start of the base backup. 3. If archiving keeps failing, `pg_wal/` fills and the server shuts down with a PANIC when the disk is full: alert on `pg_stat_archiver.failed_count` and `last_archived_time`[^pg-stats]. 4. Archive lag is exposure: it adds to the data lost in a disaster. Restore into a **new** data directory or instance, never over the damaged one, with archiving disabled on the copy ([Lab 3](#hands-on-labs)). Amazon RDS also restores to a new instance[^rds-pitr]: - it uploads transaction logs to S3 every five minutes; - restored volumes load blocks from S3 in the background, so performance "might not be at its fullest" at first; - retention is 0 to 35 days, and 0 disables automated backups. After an RDS blue/green switchover, PITR history "doesn't carry over" to the new instance[^rds-bg]. ### Replication, lag and failover "PostgreSQL streaming replication is asynchronous by default", and "The amount of data loss is proportional to the replication delay at the time of failover"[^pg-standby]. Synchronous commit closes the gap at a latency cost: `remote_write` survives a standby crash but not its operating-system crash, `on` waits for the standby's flush, `remote_apply` also for replay[^pg-recovery]. Watch: - **Lag** in time (`write_lag`, `flush_lag`, `replay_lag`) and bytes[^pg-stats][^pg-standby]: it sets failover loss and read staleness. - **Replication slots**, which keep WAL for absent standbys and "can cause the server to retain so many WAL segments that they fill up the space allocated for pg_wal"; cap them with `max_slot_wal_keep_size`[^pg-standby]. GitLab's 2017 secondary broke the other way: no WAL archive, and nothing retained the WAL it needed[^gitlab2017]. - **Standby conflicts:** DDL locks and vacuum cleanup cancel standby queries after `max_standby_streaming_delay`; `hot_standby_feedback` avoids that but "may result in undesirable table bloat" on the primary[^pg-hot]. **Failover is an external decision.** "PostgreSQL does not provide the system software required to identify a failure on the primary", and a returning old primary must be stopped from acting as one (STONITH)[^pg-failover]. Patroni stops PostgreSQL when it cannot renew its leader lock and can arm a watchdog that resets the machine if Patroni itself hangs[^patroni]. RDS Multi-AZ DB instance failover is "typically 60–120 seconds" and changes the DNS record; AWS recommends a JVM DNS cache TTL of no more than 60 seconds[^rds-maz]. Afterwards `pg_rewind` makes the old primary a standby of the new one, if it has data checksums (an initdb default only from 18) or `wal_log_hints`; its unreplicated writes do not survive, so copy its data directory first[^pg-rewind] ([reconciling lost writes](../disasterrecoveryops/#reconciling-lost-writes)). ### Query performance and indexing Measure, explain, change one thing, re-measure. `pg_stat_statements` ranks normalised statements (it needs `shared_preload_libraries`, a restart and `CREATE EXTENSION`)[^pg-pss]. Rank by **total** time, not the slowest single query; read the plan with `EXPLAIN (ANALYZE, BUFFERS)` on a copy; apply the cheapest fix. Indexes slow every write and can prevent heap-only tuple updates, so "indexes that are seldom or never used in queries should be removed"[^pg-indexes]. [Triage query 7](#reference-implementation) lists candidates. Each server keeps its own counters[^pg-hot], which reset when it starts from a crash, a base backup or PITR[^pg-stats]: check every replica that serves reads, and how long the counters have run, before dropping an index. Turn on `log_min_duration_statement` and `log_lock_waits`, both off by default[^pg-logging]. ### Vacuum, bloat and statistics Updates and deletes leave dead row versions. `VACUUM` makes their space reusable, refreshes planner statistics and freezes old rows; `VACUUM FULL` takes ACCESS EXCLUSIVE, so avoid it[^pg-vacuum][^pg-locks]. Autovacuum waits for dead rows above 50 plus 20% of the table (18 adds a 100-million cap)[^pg-vacuum-cfg]: a 100-million-row table carries about 20 million before it is cleaned. Watch `n_dead_tup` and `last_autovacuum` (triage query 6)[^pg-stats]; lower the scale factor per table for large, busy tables only[^pg-vacuum]; run `ANALYZE` after a backfill or bulk load[^pg-vacuum]. **Bloat that is already there:** `REINDEX INDEX CONCURRENTLY` rebuilds an index without blocking writes (outside a transaction block)[^pg-reindex]. The pg_repack extension rebuilds a table online, taking ACCESS EXCLUSIVE only briefly at start and swap; it needs a primary key and free disk of about twice the table plus indexes[^pg-repack]. For time-based retention, partition: dropping a partition "is far faster than a bulk operation" and skips a bulk `DELETE`'s vacuum work, though it pays off "only when a table would otherwise be very large"[^pg-partition]. ### Connection pooling Each connection costs memory, and past saturation more connections add contention, not throughput. An old PostgreSQL wiki heuristic starts from `(core_count × 2) + effective_spindle_count` active connections; it is untested on SSDs, so treat it as a first guess to measure[^pg-conn-wiki]. Poolers cap server connections but restrict session state. RDS Proxy calls this **pinning**: for PostgreSQL "setting a variable leads to session pinning" (watch `DatabaseConnectionsCurrentlySessionPinned`)[^rds-proxy]. Three rules: 1. **Know what transaction pooling breaks.** PgBouncer's transaction mode lends a server connection per transaction, so `SET`/`RESET`, `LISTEN`, `WITH HOLD` cursors, SQL `PREPARE` and session advisory locks break; protocol-level prepared statements need `max_prepared_statements` above zero[^pgbouncer]. 2. **Set session defaults per role** or in the proxy's initialisation query, not with per-session `SET`. 3. **Run migrations over a direct or session-mode connection.** Behind a transaction pooler, `SET lock_timeout` and the next statement can reach different server connections, so the timeout silently lapses and Flyway's session advisory lock breaks[^pgbouncer][^flyway-lock]. ### Engine upgrades PostgreSQL supports each major version "for 5 years after its initial release", ships minor releases at least quarterly and recommends always running the current minor[^pg-versions]. As of October 2026, 13 is unsupported and 14's support ends with its final release on 2026-11-12[^pg-versions]. A minor update needs new binaries and a restart. A major upgrade takes one of three routes: 1. **`pg_upgrade`.** Run `--check` first. With `--link` you "will not be able to access your old cluster once you start the new cluster"; `--clone` avoids that. In 18 most planner statistics carry over, and `vacuumdb --all --analyze-in-stages --missing-stats-only` fills the rest[^pg-upgrade]. 2. **Logical replication** to a new cluster, then a cut-over. 3. **A managed blue/green switchover,** typically under a minute, but it cannot replicate DDL when PostgreSQL uses logical replication[^rds-bg]. Postponing costs money. At the end of standard support, RDS enrols an instance in paid Extended Support (up to three years) if its setting allows; that is the API and CLI default, not the console's. Otherwise, or when Extended Support ends, RDS upgrades the major version automatically[^rds-es]. ## Frameworks, tools and automation ### Methods, standards and frameworks No standard or certification defines DBOps. In descending order of authority: - **Engine manuals** are normative for behaviour: lock levels, recovery settings and limits for *your* version. - **Research-backed recommendations:** DORA's database change management capability[^dora-db]. - **Practice patterns:** evolutionary database design[^evodb], parallel change[^parallel], Amazon's rollback-safe "prepare" then "activate" deployments (its example is a data format, not a schema)[^rollback-safety], dual writes at scale[^stripe]. - **Reliability engineering:** [SRE](../sre/) applied to databases in *Database Reliability Engineering*[^dre-book]. ### Tooling landscape Capabilities as documented in October 2026; editions change, so re-check. Combine one tool where safety lives in review (a linter), one in the runner, and, for big rewrites, one in execution. | Category | Job | Verified examples | Watch-outs | |---|---|---|---| | Migration runner | Apply versioned changes once, in order, with checksums | Flyway (undo scripts need the Teams edition)[^flyway-versioned][^flyway-undo]; Liquibase[^lb-changeset][^lb-tables]; Sqitch (plan with dependencies)[^sqitch]; the `dbops` PowerShell module (DbUp)[^dbops-ps] | Per-file transactions; stale run locks[^lb-tables] | | Declarative schema as code | Compute the migration from a desired state | Atlas[^atlas] | A diff cannot tell a rename from drop-and-add | | Migration linter | Block dangerous DDL before merge | Squawk[^squawk], strong_migrations (Rails)[^strong], Atlas analyzers[^atlas] | Rules are engine- and version-specific | | Online schema change | Rewrite big tables without long locks | gh-ost, pt-online-schema-change (MySQL)[^gh-ost][^pt-osc]; pgroll (PostgreSQL 14+)[^pgroll] | Copy load, lag, triggers, foreign keys | | HA and failover | Detect, promote, fence | Patroni[^patroni]; CloudNativePG (CNCF Sandbox since January 2025)[^cnpg]; RDS Multi-AZ[^rds-maz] | Fencing, DNS caching | | Backup and PITR | Base backups, WAL archive, restore | pgBackRest (2.59.3, October 2026)[^pgbackrest]; managed backups[^rds-pitr] | Measure restore time, not backup success | | Pooling | Share server connections | PgBouncer[^pgbouncer], RDS Proxy[^rds-proxy] | Session features; pinning | | Change platform | Review, approval, audit in one product | Bytebase, Harness Database DevOps[^bytebase][^harness] | Lock-in; still needs engine knowledge | **Selection criteria:** engine support; handling of transactional and non-transactional statements; behaviour after a partial failure; drift detection; the edition you need; plain SQL or XML/YAML changelogs, whichever the team reads better. ### Automation and AI Automate what is cheap to get right every time: the guard, the runner, throttled backfills, scheduled restore tests, alerts. Keep a human on decisions that trade data for availability: GitHub's automated cross-region promotion in 2018 is the cautionary case ([use cases](#use-cases-and-scenarios))[^github2018]. Language models can draft migrations and explain plans; their SQL passes the same guard, tests and review. Never give an assistant or agent the migrator role. ## DBOps in practice ### Reference implementation One service on a supported PostgreSQL (CI uses 18; the SQL was executed on 13.13), Flyway as runner, GitHub Actions as pipeline. **Assumptions:** one database per service, owned by it; production credentials only in a protected deployment environment. Layout: ```text orders-service/ db/migrations/V1__create_orders.sql ... V6__customer_name_set_not_null.sql db/migrations/V3__index_customer_name.sql.conf # executeInTransaction=false db/held-back/V7__contract_drop_cust_name.sql # moved in after the soak period db/roles.sql db/triage.sql flyway.toml migration_guard.py scripts/test-db.sh .github/workflows/db.yml .github/CODEOWNERS # DBA review of .github/ and every path db.yml watches ``` **Runner configuration.** Credentials come from environment variables (`${env.…}`) and `defaultSchema` puts the history table and unqualified names in `orders`[^flyway-config]; session-level advisory locks let concurrent index builds run[^flyway-lock]. _Example status: parsed as TOML (smol-toml 1.3.1); not executed (Flyway is not installed on the test host); keys from the cited Flyway pages._ ```toml [flyway] locations = ["filesystem:db/migrations"] defaultSchema = "orders" # created by roles.sql; holds flyway_schema_history [flyway.postgresql] transactional.lock = false [environments.ci] # the throwaway service container, roles.sql applied url = "jdbc:postgresql://localhost:5432/postgres" user = "orders_migrator" password = "${env.PGPASSWORD}" [environments.production] # values come from the protected environment url = "${env.DB_URL}" user = "${env.DB_USER}" password = "${env.DB_PASSWORD}" ``` **Roles.** The service reads and writes but cannot alter or drop tables: that right belongs to the owner and "cannot be granted or revoked"[^pg-priv]. The `REVOKE` on `public` matters only on PostgreSQL 14 or older and upgraded databases[^pg-schemas]. Each role's `search_path` is the service schema, so migrations and queries need no prefix. _Example status: executed on PostgreSQL 13.13 with V1–V6 applied as `orders_migrator` by the lab runner (standing in for Flyway): `orders_app` read and wrote the tables unqualified; its `DROP TABLE` failed ("must be owner of table orders")._ ```sql -- Least-privilege roles for one service's schema (PostgreSQL 13+). Run once -- per database by an administrator; CI runs it on every throwaway database. -- Passwords or IAM/OIDC authentication are configured outside this file. CREATE ROLE orders_owner NOLOGIN; -- owns every object CREATE ROLE orders_migrator LOGIN IN ROLE orders_owner; -- CI migration job only CREATE ROLE orders_app LOGIN; -- the running service CREATE ROLE orders_ro LOGIN; -- reports, replicas CREATE SCHEMA orders AUTHORIZATION orders_owner; REVOKE CREATE ON SCHEMA public FROM PUBLIC; -- already the default from 15 GRANT USAGE ON SCHEMA orders TO orders_app, orders_ro; ALTER DEFAULT PRIVILEGES FOR ROLE orders_owner IN SCHEMA orders GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO orders_app; ALTER DEFAULT PRIVILEGES FOR ROLE orders_owner IN SCHEMA orders GRANT USAGE, SELECT ON SEQUENCES TO orders_app; ALTER DEFAULT PRIVILEGES FOR ROLE orders_owner IN SCHEMA orders GRANT SELECT ON TABLES TO orders_ro; -- Session defaults: unqualified names resolve to the service's schema, objects -- created by the migrator belong to orders_owner, DDL gives up quickly, and -- the service cannot hold locks for long. ALTER ROLE orders_migrator SET search_path = orders; ALTER ROLE orders_app SET search_path = orders; ALTER ROLE orders_ro SET search_path = orders; ALTER ROLE orders_migrator SET role = 'orders_owner'; ALTER ROLE orders_migrator SET lock_timeout = '2s'; ALTER ROLE orders_app SET statement_timeout = '15s'; ALTER ROLE orders_app SET idle_in_transaction_session_timeout = '60s'; -- The default privileges also reach the runner's history table: revoke DML on -- it from orders_app after the first run if the audit trail must be tamper-proof. ``` **Pipeline.** `check` holds no production credentials. On a throwaway service container[^gh-services] it runs the guard, `roles.sql`, the migrations as `orders_migrator` and the tests as `orders_app`, so a missing grant fails in CI. The tests (`test-db.sh`, below) are invariant queries that must return zero, like Lab 2's `out_of_step`; pgTAP can add unit tests that exercise "views, procedures, functions, rules, or triggers"[^pgtap]. Only `migrate` references the `production` environment, so only it can read that environment's secrets; restrict the environment to `main`[^gh-env]. **The review gate (plan limits as of October 2026).** On GitHub Free, Pro and Team, an environment's required reviewers and its administrator-bypass setting exist only in public repositories. Free also limits environment secrets, deployment-branch rules, protected branches, rulesets and code owners to public repositories[^gh-env][^gh-branch]. Without required reviewers, every merge to `main` migrates production, so the merge review is the gate: 1. Protect `main` with a branch-protection rule or a ruleset that requires code-owner review of `.github/` and every path the workflow watches, and dismisses stale approvals. 2. Allow no bypass: branch-protection rules exempt administrators and bypass roles until "Do not allow bypassing the above settings" is on, and a ruleset's bypass list must stay empty. 3. Keep few admins and holders of "edit repository rules": they can still change the rule[^gh-branch]. Only then does review also stop a pull request from weakening the guard or the tests that judge it. _Example status: YAML syntax-checked; not executed. Flyway 13.10.3 with Redgate's published SHA-1[^flyway-config] and `actions/checkout` v7.0.1 pinned by commit[^gh-checkout] (October 2026); pin the image by digest._ ```yaml name: database on: pull_request: paths: &watched # the migrations and everything that checks or applies them - "db/**" - "flyway.toml" - "migration_guard.py" - "scripts/**" - ".github/workflows/db.yml" push: branches: [main] paths: *watched permissions: contents: read env: FLYWAY_VERSION: "13.10.3" # the version you tested FLYWAY_SHA1: "6bd32305c1d4489a864590f6952124ec6cb4a866" # Redgate's .sha1 file jobs: check: runs-on: ubuntu-latest services: postgres: image: postgres:18 env: POSTGRES_PASSWORD: ci-only-password ports: ["5432:5432"] options: >- --health-cmd pg_isready --health-interval 10s --health-timeout 5s --health-retries 5 env: PGHOST: localhost PGUSER: postgres PGPASSWORD: ci-only-password PGDATABASE: postgres steps: - uses: actions/checkout@3d3c42e5aac5ba805825da76410c181273ba90b1 # v7.0.1 - name: Guard new migrations run: python3 migration_guard.py db/migrations/*.sql db/held-back/*.sql - name: Production roles, plus CI-only passwords run: | psql -v ON_ERROR_STOP=1 -f db/roles.sql psql -c "ALTER ROLE orders_migrator PASSWORD '$PGPASSWORD'" \ -c "ALTER ROLE orders_app PASSWORD '$PGPASSWORD'" - name: Install Flyway (pinned, checksum-verified) # YAML anchor, reused below run: &install-flyway | f="flyway-commandline-$FLYWAY_VERSION-linux-x64.tar.gz" base=https://download.red-gate.com/maven/release/com/redgate/flyway curl -fsSLO "$base/flyway-commandline/$FLYWAY_VERSION/$f" echo "$FLYWAY_SHA1 $f" | sha1sum -c - tar -xzf "$f" && echo "$PWD/flyway-$FLYWAY_VERSION" >> "$GITHUB_PATH" - name: Apply all migrations as the migrator role run: flyway -environment=ci migrate - name: Tests as the application role run: PGUSER=orders_app ./scripts/test-db.sh migrate: if: github.ref == 'refs/heads/main' needs: check runs-on: ubuntu-latest environment: production concurrency: production-db steps: - uses: actions/checkout@3d3c42e5aac5ba805825da76410c181273ba90b1 # v7.0.1 - name: Install Flyway (pinned, checksum-verified) run: *install-flyway - name: Apply pending migrations env: DB_URL: ${{ secrets.DB_URL }} # the primary itself, never a pooler DB_USER: ${{ secrets.DB_MIGRATOR_USER }} DB_PASSWORD: ${{ secrets.DB_MIGRATOR_PASSWORD }} run: flyway -environment=production migrate ``` **Database tests.** _Example status: executed on PostgreSQL 13.13 as `orders_app` after V1–V6: three `ok` lines, no rows left behind; it failed, as intended, with the sync trigger disabled, with an out-of-step row and without the INSERT grant._ ```bash #!/usr/bin/env bash # scripts/test-db.sh: CI database tests, run as orders_app after the migrations. # Each test is one query that must print 0; anything else fails the job. # Delete the two cust_name tests in the pull request that ships V7. set -euo pipefail t() { # t : runs inside a transaction that is always rolled back got=$(psql -qAtX -v ON_ERROR_STOP=1 -c BEGIN -c "$2" -c ROLLBACK) [ "$got" = 0 ] || { echo "FAIL $1: $got" >&2; exit 1; }; echo "ok $1" } t old_app_write_synced "WITH r AS (INSERT INTO orders (id, cust_name) VALUES (-1, 'probe') RETURNING customer_name) SELECT count(*) FROM r WHERE customer_name IS DISTINCT FROM 'probe'" t out_of_step "SELECT count(*) FROM orders WHERE customer_name IS DISTINCT FROM cust_name" t invalid_indexes "SELECT count(*) FROM pg_index WHERE NOT indisvalid" ``` **Triage queries** for on-call. _Example status: executed on PostgreSQL 13.13 during a staged blocking chain (report → migration → api-read); query 6 after Lab 3; query 7 on a primary and a fresh standby, each of which counted only its own scans._ ```sql -- 1. Who blocks whom: sessions that wait, and the sessions they wait for. SELECT pid, application_name AS app, state, wait_event_type AS waits_on, pg_blocking_pids(pid) AS blocked_by, date_trunc('second', now() - xact_start) AS xact_age, left(query, 40) AS query FROM pg_stat_activity WHERE backend_type = 'client backend' AND pid <> pg_backend_pid() AND (cardinality(pg_blocking_pids(pid)) > 0 OR pid IN (SELECT unnest(pg_blocking_pids(pid)) FROM pg_stat_activity)) ORDER BY xact_start; -- 2. Connection use against the limit. SELECT count(*) AS connections, count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_tx, current_setting('max_connections')::int AS max_connections FROM pg_stat_activity WHERE backend_type = 'client backend'; -- 3. Transaction ID age: alert long before 2 billion. SELECT datname, age(datfrozenxid) AS xid_age, round(100.0 * age(datfrozenxid) / 2000000000, 1) AS pct_of_2b FROM pg_database ORDER BY xid_age DESC LIMIT 3; -- 4. Indexes left INVALID by a failed CREATE INDEX CONCURRENTLY. SELECT indexrelid::regclass AS invalid_index FROM pg_index WHERE NOT indisvalid; -- 5. Top statements by total time (needs pg_stat_statements). SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time::numeric, 2) AS mean_ms, left(query, 40) AS query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 3; -- 6. Dead rows and the last autovacuum and autoanalyze, per table. SELECT relname, n_live_tup, n_dead_tup, round(100.0 * n_dead_tup / greatest(n_live_tup + n_dead_tup, 1)) AS dead_pct, date_trunc('second', last_autovacuum) AS last_autovacuum, date_trunc('second', last_autoanalyze) AS last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5; -- 7. Indexes not scanned since the counters were last reset: candidates to drop. -- Counters are per server and reset by crash recovery: check each replica too. SELECT s.relname AS table_name, s.indexrelname AS index_name, s.idx_scan, pg_size_pretty(pg_relation_size(s.indexrelid)) AS size FROM pg_stat_user_indexes s JOIN pg_index i USING (indexrelid) WHERE s.idx_scan = 0 AND NOT i.indisunique -- unique indexes enforce constraints ORDER BY pg_relation_size(s.indexrelid) DESC LIMIT 5; ``` ### Hands-on labs Three labs, **executed on 2026-10-18** (Ubuntu 20.04, PostgreSQL 13.13, bash 5.0, Python 3.8). PostgreSQL 13 is unsupported[^pg-versions]: use a supported version; the behaviour shown is documented in the current manual[^pg-locks][^pg-alter][^pg-pitr]. **Prerequisites:** Linux, PostgreSQL server binaries (13+), `bash`, `python3`, 1 GB of disk, no root. **Cost and risk:** local CPU only; the cluster listens only on a Unix socket in a private directory with trust authentication: never point the scripts at a real data directory. Run them from their directory. **Setup.** `export LAB=$(mktemp -d)`; set `PGBIN` to your server's `bin` directory and put it on `PATH`: ```bash #!/usr/bin/env bash # lab-up.sh: create a throwaway, socket-only PostgreSQL cluster for the labs. set -euo pipefail PGBIN="${PGBIN:-/usr/lib/postgresql/13/bin}" # the major version you have LAB="${LAB:?set LAB to a new, empty directory you own (mode 0700)}" mkdir -p "$LAB/sock" "$LAB/wal-archive" "$PGBIN/initdb" -D "$LAB/primary" -U labadmin --auth=trust --data-checksums \ > /dev/null cat >> "$LAB/primary/postgresql.conf" < /dev/null PGHOST="$LAB/sock" PGPORT=55432 "$PGBIN/psql" -U labadmin -d postgres -Atc \ "SELECT 'up: ' || current_setting('server_version')" ``` **Expected output:** `up: 13.13 …` (your version). **If it fails** with "could not start server", read `$LAB/primary.log`: a socket path "too long (maximum 107 bytes)" followed by "could not create any Unix-domain sockets" means `$LAB` is too long; keep it to 87 characters or fewer (a default `mktemp -d` path has 19). **Teardown for all labs:** stop every cluster, then delete only `$LAB`: ```bash #!/usr/bin/env bash # lab-down.sh: stop every lab cluster, then delete the lab directory only. set -uo pipefail PGBIN="${PGBIN:-/usr/lib/postgresql/13/bin}"; LAB="${LAB:?}" for d in pitr standby primary; do [ -f "$LAB/$d/postmaster.pid" ] && "$PGBIN/pg_ctl" -D "$LAB/$d" -m fast -w stop done rm -rf -- "$LAB" ``` #### Lab 1 (beginner): watch the lock queue, then bound it **Objective:** see a millisecond `ALTER TABLE` block reads, and `lock_timeout` turn that into a failed, retryable deploy. **Prerequisites:** the Setup cluster. ```bash #!/usr/bin/env bash # lab1.sh: a waiting ALTER TABLE blocks readers; lock_timeout bounds the damage. set -euo pipefail : "${LAB:?}" export PGHOST="$LAB/sock" PGPORT=55432 PGUSER=labadmin PGDATABASE=postgres psql -q -v ON_ERROR_STOP=1 <<'SQL' SET client_min_messages = warning; DROP TABLE IF EXISTS orders; CREATE TABLE orders (id bigint PRIMARY KEY, cust_name text NOT NULL); INSERT INTO orders SELECT g, 'customer ' || g FROM generate_series(1, 100000) g; SQL read_once() { # an "API read" that reports how long it waited local t0; t0=$(date +%s%N) PGAPPNAME=api-read psql -qAtc "SELECT count(*) FROM orders" > /dev/null echo "api-read finished after $(( ($(date +%s%N) - t0) / 1000000 )) ms" } run() { # $1 = lock_timeout for the migration ('0' means none) echo "== migration with lock_timeout = $1" PGAPPNAME=report psql -qc \ "BEGIN; SELECT count(*) FROM orders; SELECT pg_sleep(8); COMMIT;" > /dev/null & sleep 1 # the report now holds ACCESS SHARE PGAPPNAME=migration psql -q -c "SET lock_timeout = '$1'" \ -c "ALTER TABLE orders ADD COLUMN IF NOT EXISTS customer_name text" \ 2>&1 | sed 's/^/migration: /' & sleep 1 # the migration now waits in the queue read_once & sleep 0.5 psql -Atc "SELECT application_name || ' (pid ' || pid || ') waits on ' || wait_event_type || ', blocked by pid ' || array_to_string(pg_blocking_pids(pid), ',') FROM pg_stat_activity WHERE wait_event_type = 'Lock' ORDER BY application_name" wait } run 0 psql -qc "ALTER TABLE orders DROP COLUMN customer_name" # reset run 2s ``` **Expected output** (process ids differ): ```text == migration with lock_timeout = 0 api-read (pid 3353417) waits on Lock, blocked by pid 3353412 migration (pid 3353412) waits on Lock, blocked by pid 3353402 api-read finished after 6025 ms == migration with lock_timeout = 2s api-read (pid 3353560) waits on Lock, blocked by pid 3353547 migration (pid 3353547) waits on Lock, blocked by pid 3353528 migration: ERROR: canceling statement due to lock timeout api-read finished after 1014 ms ``` **Validation:** the read is blocked by the *migration*, not by the report, and waits about six seconds, then about one. **Troubleshooting:** if nothing waits, the report ended too early: raise `pg_sleep`. **Teardown:** keep the cluster for Lab 2. #### Lab 2 (intermediate): rename a column under live writes **Objective:** run the expand half (V1 to V6) while an "old application" writes only the old column, and prove nothing drifts. **Prerequisites:** the Setup cluster, `pgbench`, and each file below saved under the name in its first line. One file per migration; V3 has a `.conf` file containing `executeInTransaction=false`, as Flyway expects[^flyway-tx]: ```sql -- ===== db/migrations/V1__create_orders.sql CREATE TABLE orders ( id bigint PRIMARY KEY, cust_name text NOT NULL ); -- ===== db/migrations/V2__expand_customer_name.sql -- Expand: new nullable column (metadata-only) plus a trigger that keeps both -- columns in step while old and new application versions run side by side. SET lock_timeout = '2s'; ALTER TABLE orders ADD COLUMN customer_name text; CREATE FUNCTION orders_sync_customer_name() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF TG_OP = 'INSERT' THEN NEW.customer_name := coalesce(NEW.customer_name, NEW.cust_name); NEW.cust_name := coalesce(NEW.cust_name, NEW.customer_name); ELSIF NEW.cust_name IS DISTINCT FROM OLD.cust_name THEN NEW.customer_name := NEW.cust_name; -- old version wrote ELSIF NEW.customer_name IS DISTINCT FROM OLD.customer_name THEN NEW.cust_name := NEW.customer_name; -- new version wrote END IF; RETURN NEW; END $$; CREATE TRIGGER orders_sync_customer_name BEFORE INSERT OR UPDATE ON orders FOR EACH ROW EXECUTE FUNCTION orders_sync_customer_name(); -- ===== db/migrations/V3__index_customer_name.sql -- Runs outside a transaction (see the .conf file): CONCURRENTLY requires it. SET lock_timeout = '2s'; CREATE INDEX CONCURRENTLY orders_customer_name_idx ON orders (customer_name); -- ===== db/migrations/V4__customer_name_check_not_valid.sql -- Brief lock, no table scan: only new and updated rows are checked from now on. -- PostgreSQL 18+: ADD CONSTRAINT orders_customer_name_nn NOT NULL customer_name -- NOT VALID here, VALIDATE it in V5, and no V6 is needed. SET lock_timeout = '2s'; ALTER TABLE orders ADD CONSTRAINT orders_customer_name_nn CHECK (customer_name IS NOT NULL) NOT VALID; -- ===== db/migrations/V5__validate_customer_name_check.sql -- Full scan, but under SHARE UPDATE EXCLUSIVE: reads and writes continue. SET lock_timeout = '2s'; ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_name_nn; -- ===== db/migrations/V6__customer_name_set_not_null.sql -- The validated CHECK proves there are no NULLs, so no second scan is needed. SET lock_timeout = '2s'; SET client_min_messages = debug1; -- lab only: shows that the scan is skipped ALTER TABLE orders ALTER COLUMN customer_name SET NOT NULL; RESET client_min_messages; ALTER TABLE orders DROP CONSTRAINT orders_customer_name_nn; -- ===== db/held-back/V7__contract_drop_cust_name.sql (not run in the lab) -- contract-approved: CHG-0000 (placeholder ticket), after a 14-day soak -- Irreversible. Ship only when no running version reads or writes cust_name. SET lock_timeout = '2s'; DROP TRIGGER orders_sync_customer_name ON orders; DROP FUNCTION orders_sync_customer_name(); ALTER TABLE orders DROP COLUMN cust_name; ``` The runner, the backfill and the "old application" for `pgbench`: ```bash #!/usr/bin/env bash # migrate.sh: minimal versioned-migration runner, a teaching aid that mimics # Flyway or Liquibase. No run lock, and history is written after each file commits: # never use it in production. Usage: migrate.sh [target-version] set -euo pipefail DIR="${1:?migrations directory}"; TARGET="${2:-999999}" psql -qX -v ON_ERROR_STOP=1 -c "SET client_min_messages = warning" \ -c "CREATE TABLE IF NOT EXISTS schema_history ( version int PRIMARY KEY, script text NOT NULL, checksum text NOT NULL, installed_at timestamptz NOT NULL DEFAULT now())" for name in $(ls "$DIR" | grep -E '^V[0-9]+__.+\.sql$' | sort -V); do f="$DIR/$name"; v="${name#V}"; v="${v%%__*}" [ "$v" -le "$TARGET" ] || break sum=$(sha256sum "$f" | cut -c1-16) done_sum=$(psql -qAtX -c "SELECT checksum FROM schema_history WHERE version = $v") if [ -n "$done_sum" ]; then # applied before: it must be unchanged [ "$done_sum" = "$sum" ] || { echo "ERROR: $name changed after it ran" >&2; exit 1; } continue fi tx=(--single-transaction) # one file = one transaction by default grep -qx 'executeInTransaction=false' "$f.conf" 2>/dev/null && tx=() psql -qX -v ON_ERROR_STOP=1 "${tx[@]}" -f "$f" psql -qX -c "INSERT INTO schema_history (version, script, checksum) VALUES ($v, '$name', '$sum')" echo "applied $name" done ``` ```bash #!/usr/bin/env bash # backfill.sh: idempotent, throttled backfill that walks the primary key # (keyset), so each batch reads only its own id range and costs the same at # the end of the table as at the start. Rows written meanwhile are kept in # step by the sync trigger. Pauses while any replica's replay lag > MAX_LAG_S. set -euo pipefail BATCH="${BATCH:-5000}"; MAX_LAG_S="${MAX_LAG_S:-5}"; last=0; total=0; fails=0 while :; do lag=$(psql -qAtX -c "SELECT coalesce(max(extract(epoch FROM replay_lag)), 0) FROM pg_stat_replication") if awk -v l="$lag" -v m="$MAX_LAG_S" 'BEGIN { exit !(l > m) }'; then echo "replica lag ${lag}s > ${MAX_LAG_S}s: pausing"; sleep 5; continue fi hi=$(psql -qAtX -c "SELECT max(id) FROM (SELECT id FROM orders WHERE id > $last ORDER BY id LIMIT $BATCH) b") [ -n "$hi" ] || break # no rows after $last: done if ! n=$(psql -qAtX -v ON_ERROR_STOP=1 -c "SET lock_timeout = '2s'" -c " WITH done AS ( UPDATE orders SET customer_name = cust_name WHERE id > $last AND id <= $hi AND customer_name IS NULL RETURNING 1) SELECT count(*) FROM done"); then fails=$((fails + 1)) # e.g. a lock timeout: retry the batch [ "$fails" -lt 3 ] || { echo "three failures: investigate" >&2; exit 1; } sleep 5; continue fi last=$hi; total=$((total + n)); fails=0 sleep 0.2 # leave room for production traffic done echo "backfill complete: $total rows updated" ``` ```text -- old_app.sql: the "old application" for pgbench; it knows only cust_name. \set id random(1, 200000) UPDATE orders SET cust_name = 'renamed ' || :id WHERE id = :id; \set nid random(200001, 9000000) INSERT INTO orders (id, cust_name) VALUES (:nid, 'new ' || :nid) ON CONFLICT (id) DO NOTHING; ``` The lab driver: ```bash #!/usr/bin/env bash # lab2.sh: rename orders.cust_name to customer_name under live writes (expand). set -euo pipefail : "${LAB:?}"; PGBIN="${PGBIN:-/usr/lib/postgresql/13/bin}" export PGHOST="$LAB/sock" PGPORT=55432 PGUSER=labadmin PGDATABASE=postgres psql -qX -c "SET client_min_messages = warning" \ -c "DROP TABLE IF EXISTS orders, schema_history CASCADE" \ -c "DROP FUNCTION IF EXISTS orders_sync_customer_name()" bash migrate.sh db/migrations 1 psql -qX -c "INSERT INTO orders SELECT g, 'customer ' || g FROM generate_series(1, 200000) g" # The "old application": updates and inserts that know only cust_name. "$PGBIN/pgbench" -n -c 2 -T 25 -f old_app.sql > "$LAB/old_app.log" 2>&1 & sleep 1 bash migrate.sh db/migrations 2 bash backfill.sh bash migrate.sh db/migrations 6 wait grep -E 'number of transactions actually processed|failed' "$LAB/old_app.log" || true psql -X -c "SELECT count(*) AS rows, count(*) FILTER (WHERE customer_name IS DISTINCT FROM cust_name) AS out_of_step FROM orders" psql -qAtX -c "SELECT version || ' ' || script FROM schema_history ORDER BY version" psql -qAtX -c "SELECT 'index valid: ' || indisvalid FROM pg_index WHERE indexrelid = 'orders_customer_name_idx'::regclass" ``` **Expected output** (excerpt; the long DEBUG line is wrapped; counts vary with timing): ```text applied V1__create_orders.sql applied V2__expand_customer_name.sql backfill complete: 198049 rows updated applied V3__index_customer_name.sql applied V4__customer_name_check_not_valid.sql applied V5__validate_customer_name_check.sql psql:db/migrations/V6__customer_name_set_not_null.sql:4: DEBUG: existing constraints on column "orders.customer_name" are sufficient to prove that it does not contain nulls applied V6__customer_name_set_not_null.sql number of transactions actually processed: 12647 rows | out_of_step --------+------------- 212641 | 0 index valid: true ``` **Validation:** `out_of_step` is 0 after about 12,600 old-application transactions; the backfill updated fewer than 200,000 rows because the trigger had filled rows the old application touched; the DEBUG line shows `SET NOT NULL` skipped its scan. **Troubleshooting:** a lock timeout on V2 to V6 means a long transaction is open ([triage queries](#reference-implementation)); rerun safely. `… changed after it ran` means an applied migration was edited: add a new one instead[^flyway-versioned]. **Teardown:** keep the cluster for Lab 3. **The pre-merge guard** (pattern matching; real linters parse SQL[^squawk][^atlas]). _Example status: executed; V1 to V7 pass (exit 0); a file with a plain `CREATE INDEX`, a foreign key without `NOT VALID`, no `lock_timeout` and an unapproved rename gets four findings (exit 1)._ ```python """Pre-merge guard for PostgreSQL migration files (a teaching aid: real linters such as Squawk, Atlas or strong_migrations parse SQL; this only pattern-matches). Usage: python3 migration_guard.py FILE.sql [...]; exit code 1 on any finding.""" import pathlib import re import sys DDL = re.compile(r"^\s*(ALTER|DROP|CREATE\s+(UNIQUE\s+)?INDEX|CREATE\s+TRIGGER)\b", re.I | re.M) RULES = [ (re.compile(r"CREATE\s+(UNIQUE\s+)?INDEX\s+(?!CONCURRENTLY)", re.I), "build indexes with CREATE INDEX CONCURRENTLY"), (re.compile(r"ADD\s+CONSTRAINT\s+\w+\s+(CHECK|FOREIGN\s+KEY)(?![^;]*NOT\s+VALID)", re.I | re.S), "add CHECK/FOREIGN KEY as NOT VALID, then VALIDATE in a later migration"), (re.compile(r"ALTER\s+COLUMN\s+\w+\s+(SET\s+DATA\s+)?TYPE", re.I), "type changes usually rewrite the table: expand/contract instead"), ] DESTRUCTIVE = re.compile(r"DROP\s+(COLUMN|TABLE)|RENAME\s+(COLUMN|TO)", re.I) def check(path: pathlib.Path) -> list: sql = path.read_text() found = [msg for rx, msg in RULES if rx.search(sql)] first_ddl = DDL.search(sql) timeout = re.search(r"SET\s+lock_timeout", sql, re.I) if first_ddl and (not timeout or timeout.start() > first_ddl.start()): found.append("SET lock_timeout before the first DDL statement") if "CONCURRENTLY" in sql.upper(): conf = path.with_name(path.name + ".conf") if not conf.exists() or "executeInTransaction=false" not in conf.read_text(): found.append("CONCURRENTLY needs executeInTransaction=false (.conf)") if DESTRUCTIVE.search(sql) and "contract-approved:" not in sql: found.append("destructive change without a 'contract-approved:' line") return found failed = False for name in sys.argv[1:]: for finding in check(pathlib.Path(name)): failed = True print(f"{name}: {finding}") print("guard: FAIL" if failed else "guard: OK") sys.exit(1 if failed else 0) ``` #### Lab 3 (advanced): a replica copies the mistake, PITR undoes it **Objective:** measure replication lag under load, then recover from a destructive `DELETE` that the standby has already applied. **Prerequisites:** Lab 2 completed (this lab deletes from its `orders` table). ```bash #!/usr/bin/env bash # lab3.sh: a replica copies a bad DELETE at once; point-in-time recovery from the # base backup plus archived WAL brings the rows back. set -euo pipefail : "${LAB:?}"; PGBIN="${PGBIN:-/usr/lib/postgresql/13/bin}" export PGHOST="$LAB/sock" PGPORT=55432 PGUSER=labadmin PGDATABASE=postgres count() { psql -qAtX -p "$1" -c "SELECT count(*) FROM orders"; } # 1. A base backup (the PITR starting point) and a streaming standby. "$PGBIN/pg_basebackup" -D "$LAB/base" -X stream -c fast "$PGBIN/pg_basebackup" -D "$LAB/standby" -X stream -c fast -R # -R: standby printf "port = 55433\narchive_mode = off\n" >> "$LAB/standby/postgresql.conf" "$PGBIN/pg_ctl" -D "$LAB/standby" -l "$LAB/standby.log" -w start > /dev/null # 2. Write load on the primary; sample replication lag three times. "$PGBIN/pgbench" -i -s 5 > /dev/null 2>&1 "$PGBIN/pgbench" -n -c 4 -T 12 > "$LAB/load.log" 2>&1 & for i in 1 2 3; do sleep 3 psql -X -c "SELECT state, write_lag, flush_lag, replay_lag, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_behind FROM pg_stat_replication" done wait # 3. The accident: note the time, then delete half the orders. T=$(psql -qAtX -c "SELECT now()"); sleep 1 psql -qX -c "DELETE FROM orders WHERE id <= 100000" psql -qAtX -c "SELECT pg_switch_wal()" > /dev/null # close and archive the segment sleep 2 echo "target time: $T" echo "primary rows: $(count 55432) standby rows: $(count 55433)" # 4. Point-in-time recovery into a NEW data directory (never over the original). cp -a "$LAB/base" "$LAB/pitr" cat >> "$LAB/pitr/postgresql.conf" < /dev/null until [ "$(psql -qAtX -p 55434 -c 'SELECT pg_is_in_recovery()')" = f ]; do sleep 1 # wait for replay and promotion done echo "recovered rows: $(count 55434)" grep -E 'starting point-in-time recovery|recovery stopping' "$LAB/pitr.log" \ | sed 's/^.*LOG: *//' psql -X -c "SELECT archived_count, failed_count, last_archived_wal, now() - last_archived_time AS archive_age FROM pg_stat_archiver" ``` **Expected output** (excerpt; lag values vary): ```text state | write_lag | flush_lag | replay_lag | bytes_behind -----------+-----------------+-----------------+----------------+-------------- streaming | 00:00:00.000026 | 00:00:00.000986 | 00:00:00.00203 | 1408 target time: 2026-10-18 02:12:12.806078+00 primary rows: 112641 standby rows: 112641 recovered rows: 212641 starting point-in-time recovery to 2026-10-18 02:12:12.806078+00 recovery stopping before commit of transaction 43893, time 2026-10-18 02:12:13.867224+00 archived_count | failed_count | last_archived_wal | archive_age ----------------+--------------+--------------------------+----------------- 20 | 0 | 000000010000000000000012 | 00:00:04.783863 ``` **Validation:** the standby lost the rows as fast as the primary; the restored copy has them all, and its log names the transaction it stopped before. **Troubleshooting:** `cp: cannot stat …/00000002.history` lines are normal timeline probes; `recovery ended before configured recovery target was reached` (the server stops) means no archived WAL reaches past the target: check `pg_stat_archiver`. **Teardown:** run `lab-down.sh`. ### Use cases and scenarios Three documented cases. | Case | Context and problem | Method | Trade-offs and pitfalls | Success measure | |---|---|---|---|---| | **Stripe, 2017: changing a data model in place**[^stripe] | Subscriptions moved from the customer record to their own table; "hundreds of millions" of objects; no downtime | Dual write; move reads; move writes; remove old data. Offline backfill on snapshots in Hadoop; never more than "a few hundred lines of code at one time" | Months of dual-write complexity; every read path must be found | Old and new data agree; old structure removed | | **Figma, 2020–2023: one database at its limit**[^figma] | One large RDS PostgreSQL above 65% CPU at peak, traffic growing about 3x a year | Largest instance, then PgBouncer, then vertical partitioning by logical replication; cut-over by pausing traffic in PgBouncer and comparing LSNs; reverse replication for rollback | "a ~30 second period of partial availability impact" per move (about 2% of requests); routing grows complex | Each move inside the one-minute goal; repeatable, reversible | | **GitHub, 2018: failover across a continent**[^github2018] | 43 seconds of lost connectivity; the failover tool promoted primaries on the West Coast | Chose to "prioritize data integrity over site usability and time to recovery": restore terabytes from backups (hours), resynchronise, keep binary logs of never-replicated writes (954 on one busy cluster) to reconcile | 24 hours 11 minutes degraded; East Coast apps could not cope with cross-country write latency | Follow-up: failovers kept inside a region | ## Measure, secure and pay ### Metrics and KPIs | KPI | Definition and formula | Source of data | Pitfall | Review | |---|---|---|---|---| | DB-related change failure share | Failed deployments with a database cause ÷ all failed deployments[^dora-db] | Incident records | Needs honest postmortem tags | Monthly | | Pipeline-applied DB changes | Pipeline-applied schema changes ÷ all schema changes; goal 100%[^dora-db] | History table vs DDL log | Manual hotfixes hide unless DDL is logged | Monthly | | Lock wait per migration | Longest lock wait; target below the timeout | Lock-wait log[^pg-logging] | Logging off by default | Per deploy | | Replication lag | p99 replay lag (s) and bytes behind[^pg-stats] | `pg_stat_replication` | NULL when idle | Alert | | WAL archive age | Now minus last successful archive; failure count growth[^pg-stats] | `pg_stat_archiver` | Quiet primaries need `archive_timeout` (forced segments are full size)[^pg-recovery] | Alert | | Restore proof | Tier-1 databases with a passing timed PITR drill in the last month ÷ tier-1 databases; restore duration | Restore records | Backup success is not a restore | Monthly | | XID headroom | `age(datfrozenxid)` ÷ 2,000,000,000[^pg-vacuum] | `pg_database` | Freeze vacuum starts at 200 million by default[^pg-vacuum-cfg]; watch the trend | Daily | | Top-statement share | Top 10 statements' total time ÷ all statements' total time[^pg-pss] | `pg_stat_statements` | Resets skew it | Weekly | **Worked calculation (illustrative numbers).** Of 4 failed deployments in a month, 3 involved a schema change: 3 ÷ 4 = **75%** DB-related, so invest there. 22 of 25 schema changes went through the pipeline: **88%**. Archive age is 7 minutes against a 5-minute target on a busy primary: a breach, because that WAL exists only on the primary. XID age is 1.1 billion: 1.1 ÷ 2.0 = **55%** of the limit. The 50% alert fires; freezing should start near 200 million, so something blocks vacuum (mastery question 6). ### Quality, security, privacy and compliance - **Separate roles** for owner, migrator, application and read-only use; the service cannot alter or drop its tables[^pg-priv] ([reference implementation](#reference-implementation)). - **One job holds the migrator credential** (environment secrets; required reviewers where the plan offers them, otherwise code-owner review that nobody can bypass)[^gh-env][^gh-branch]. - **Audit trail:** history table, pull request and pipeline log; manual DDL breaks it ([ComplianceOps](../complianceops/)). - **Production copies are personal data:** anonymise copies used for timing, or keep them inside production controls ([PrivacyOps](../privacyops/)). - **Backups and the WAL archive contain everything,** deleted rows included: protect them ([BackupOps](../backupops/)). ### Cost, capacity and sustainability **Connections, by Little's law** (L = λW)[^little]. Illustrative: 2,000 queries a second at 4 ms each keep 2,000 × 0.004 = **8** connections busy on average. Twelve pods with client pools of 10 open 120; a transaction-mode pooler can serve the load with about 16–20 server connections, near the wiki's `2 × 8 cores` starting point for cached data[^pg-conn-wiki]; then tune by measuring latency. **Cost drivers:** instance size[^figma]; standbys (a Multi-AZ DB instance standby cannot serve reads[^rds-maz]); retained WAL and backups; bloat[^pg-hot]; unused indexes that slow every write[^pg-indexes]; Extended Support[^rds-es]. Removing unused indexes and replicas saves money and energy, and, for indexes and synchronous standbys, write latency ([FinOps](../finops/), [GreenOps](../greenops/)). ### Assurance of the operating model Prove the system: a monthly **timed PITR drill** per tier-1 database; a half-yearly **failover game day** (reconnection, fencing); a monthly **drift check** of live schema against the history table; **test alerts**. Risk signals: manual DDL, a tier-1 drill older than a month, an alert never fired in a test. ## When things go wrong ### Common failures and troubleshooting | Symptom | Likely cause | Diagnosis | Fix, then prevention | |---|---|---|---| | API timeouts during a deploy | DDL queued behind a long transaction | Triage query 1: readers wait on the migration[^pg-locks] | Cancel it (`pg_cancel_backend`); `lock_timeout` everywhere[^gocardless-hard] | | Migration keeps hitting lock timeout | Long or idle-in-transaction sessions | `xact_age`, `idle_in_tx` | End the culprit; `idle_in_transaction_session_timeout` on the app role[^pg-client] | | Index exists but is never used | A failed concurrent build left it `INVALID`[^pg-cic] | Triage query 4 | Drop concurrently, rebuild; `IF NOT EXISTS` would skip it | | Disk fills with WAL | Failing archive command or inactive slot | `pg_stat_archiver`, `pg_replication_slots`[^pg-stats][^pg-standby] | Fix the archive or drop the slot; `max_slot_wal_keep_size` | | Replica lag climbing | Backfill, small standby, replay waiting on queries | Lag columns; conflict settings[^pg-hot] | Throttle; resize; tune `max_standby_streaming_delay` | | "too many connections" | No pooler, leaks, retry storms | Triage query 2 | Pooler[^pgbouncer]; fix leaks; cap client pools | | `WARNING: database … must be vacuumed within …` | Freezing blocked: long transactions, dead slots, slowed autovacuum | Triage query 3, per-table ages[^pg-vacuum] | Remove the blocker; `VACUUM`, not `VACUUM FULL`[^pg-vacuum] | | Liquibase waits for a lock forever | A crashed run left the lock row set | `LOCKEDBY` in the lock table | `liquibase release-locks` once nothing runs[^lb-tables] | ### Runbook: the primary is gone **Trigger:** the primary fails health checks and the failover manager has not acted, or has and must be verified. **Owner:** the database on-call engineer; the incident commander decides on accepting data loss. ::: figure Runbook: losing the primary, from detection to restored redundancy ```mermaid %%{init: {"flowchart": {"wrappingWidth": 200, "nodeSpacing": 14, "rankSpacing": 24, "padding": 8}}}%% flowchart TB classDef core fill:#e8f3f2,stroke:#16756f,color:#122029 classDef risk fill:#fbeceb,stroke:#b4332b,color:#122029 classDef decision fill:#fdf3e3,stroke:#c9800f,color:#122029 A["1 Confirm from two vantage points that the primary is down, not just unreachable from one place"]:::core B["2 Fence the old primary: stop it, cut its network or revoke its endpoint"]:::risk C{"3 Lag of the best standby at failure within the agreed loss?"}:::decision D["4a Promote that standby"]:::core E["4b Incident commander chooses: wait for the primary, or promote and accept the loss"]:::risk F["5 Repoint clients (endpoint, DNS, pooler) and verify a test write"]:::core G["6 Preserve the old primary's data directory, then rebuild it as a standby with pg_rewind"]:::core H["7 Restore redundancy and archiving; reconcile lost writes; postmortem"]:::core A --> B --> C C -->|"yes"| D C -->|"no or unknown"| E D --> F E --> F F --> G --> H ``` Text equivalent: Confirm the failure from two vantage points; fence the old primary; if the best standby's lag is within the agreed loss, promote it, otherwise the incident commander decides; repoint clients and test a write; preserve the old primary's data before rewinding it into a standby; restore redundancy, reconcile lost writes and hold a postmortem. ::: Worked through on PostgreSQL with streaming replication: 1. **Confirm.** `pg_isready` from two networks; a partition looks like a failure from one side ([GitHub 2018](#use-cases-and-scenarios)). 2. **Fence** before promoting: PostgreSQL leaves this to you[^pg-failover]; Patroni does it on losing the leader lock[^patroni]. 3. **Measure the loss:** `SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()` on each standby; the most advanced loses least[^pg-standby]. 4. **Promote:** `pg_ctl promote` or `SELECT pg_promote()`[^pg-failover]. 5. **Repoint and test a write;** cached DNS can keep clients on the old address[^rds-maz]. 6. **Rebuild:** copy the old data directory aside, then `pg_rewind` (needs checksums or `wal_log_hints`); unreplicated writes are lost to the rewind[^pg-rewind] ([DR reconciliation](../disasterrecoveryops/#reconciling-lost-writes)). 7. **Restore redundancy:** new standby, archiving checked in `pg_stat_archiver`, fresh base backup, postmortem. ### Anti-patterns and myths - **"The ORM generates migrations, so they are safe."** Generators write correct DDL, not safe DDL: a rename is one statement and an outage for every running client. - **Down-migrations as the rollback plan.** Undo scripts assume the forward step fully succeeded, and the older schema may not accommodate data written since then[^flyway-undo][^phil-factor]. - **A global `statement_timeout` in `postgresql.conf`:** it also kills maintenance; set timeouts per role or session[^pg-client]. - **One giant backfill `UPDATE`:** long row locks, replica lag, blocked vacuum. Batch it. - **Timid autovacuum:** before its 2015 outage Sentry ran "too high a value for autovacuum_freeze_max_age and the default of 3 autovacuum workers"[^sentry]. - **Alerts nobody owns:** Mandrill's XID monitoring ticket sat in a backlog for about three months[^mandrill]. ## Deciding and adopting ### When to use and when not to use Use the full practice when a database serves frequently deployed services, holds data you cannot recreate, or must stay writable during changes. Scale it down when a maintenance window is acceptable, the data is a rebuildable cache, or one person owns app and database (keep migrations as code, PITR and lock timeouts anyway). Do not buy a "Database DevOps" platform before the basics work. Hidden costs: owners for the guard's rules, the restore drill and the upgrade calendar. ### Decision framework Walk this tree before writing a migration; it encodes the [lock table](#lock-cost-of-common-changes). ::: figure Choosing a technique for a schema change ```mermaid %%{init: {"flowchart": {"wrappingWidth": 170, "nodeSpacing": 14, "rankSpacing": 26, "padding": 8}}}%% flowchart TB classDef core fill:#e8f3f2,stroke:#16756f,color:#122029 classDef risk fill:#fbeceb,stroke:#b4332b,color:#122029 classDef decision fill:#fdf3e3,stroke:#c9800f,color:#122029 Q1{"Breaks the running app? (rename, drop, type, NOT NULL)"}:::decision EC["Expand and contract over several deploys"]:::core Q2{"Scans or rewrites under a strong lock?"}:::decision D1["Run it with lock_timeout and retries"]:::core Q3{"Non-blocking form? (CONCURRENTLY, NOT VALID, INSTANT)"}:::decision D2["Use the non-blocking form"]:::core Q4{"Small table or agreed window?"}:::decision D3["Short window, announced, lock_timeout still set"]:::risk D4["Copy-and-swap tool or new table (gh-ost, pt-osc, pgroll)"]:::core Q1 -->|"yes"| EC Q1 -->|"no"| Q2 Q2 -->|"no"| D1 Q2 -->|"yes"| Q3 Q3 -->|"yes"| D2 Q3 -->|"no"| Q4 Q4 -->|"yes"| D3 Q4 -->|"no"| D4 ``` Text equivalent: If the running application would break, use expand and contract. Otherwise, if the statement neither scans nor rewrites under a strong lock, run it with a lock timeout. If it does, use a non-blocking form when one exists; failing that, a small table or agreed window allows a short announced window, and anything else needs a copy-and-swap tool or a new table. ::: **Where to run the database** (weights are this Atlas's assumptions for a team of 5–30 engineers without dedicated DBAs; score 1–5): | Criterion (weight) | Managed service (RDS, Cloud SQL) | Kubernetes operator (CloudNativePG) | Self-managed VMs (Patroni) | |---|---|---|---| | Operational load (0.35) | 5 | 3 | 2 | | Control over versions, extensions, settings (0.20) | 2 | 4 | 5 | | Failover and PITR out of the box (0.25) | 5 | 4 | 3 | | Cost at small scale (0.20) | 3 | 3 | 3 | | **Weighted score** | **4.00** | **3.45** | **3.05** | With experienced DBAs and strict extension needs, self-managed can win; with Kubernetes skills but no DBA, the operator often does. ### Maturity model This is **this Atlas's model**, not an industry standard. | Level | Observable behaviour | Exit to the next level | |---|---|---| | 1 Manual | DDL by hand; backups unverified; no lag or XID alerts | All schema changes of the last month are in the history table | | 2 Versioned | Migrations in Git, applied by a runner; PITR configured | No migration without `lock_timeout`; a passing timed restore per tier-1 database | | 3 Guarded | Guard in CI; risky classes reviewed; monthly timed restores | The last three breaking changes shipped through expand and contract without downtime | | 4 Online | Routine expand and contract; throttled backfills; failover game days | A year of KPIs; no major version past its support date | | 5 Measured | Failure share and lock waits trended; drift detected; upgrades on a calendar | Sustain | ### Adoption roadmap: 30/60/90 days | Window | Actions | Milestone | |---|---|---| | Days 1–30 | Inventory databases, versions and support dates[^pg-versions]; enable `pg_stat_statements`, `log_lock_waits`; alerts on lag, archive age, XID age; adopt a runner and baseline the current schema | First timed PITR restore of the most critical database | | Days 31–60 | Guard in CI; `lock_timeout` in every migration; roles split; triage queries in the runbook; migration classes agreed | First breaking change through expand and contract | | Days 61–90 | Failover game day; pooler where connections run high; upgrade plan for anything near end of life; first monthly KPI review | Level 3 exit criteria met | ## Toolkit ### Checklists **Migration pull request** - [ ] Class stated; breaking changes split across deployments - [ ] `SET lock_timeout` first; one blocking statement per transaction - [ ] Indexes `CONCURRENTLY` (non-transactional file); constraints `NOT VALID`, then `VALIDATE` - [ ] Timed on a production-sized copy; backfill batched and throttled - [ ] Compatible with the running application; rollback position written down **Before a risky or contract deploy** - [ ] No long transactions (triage query 1); replica lag normal - [ ] Restore point verified: archive age small, last drill passed - [ ] On-call aware; not before a weekend or peak **Monthly PITR drill (tier 1)** - [ ] Restore to a new instance at a chosen time; record duration and data checks - [ ] Smoke-test the application against the copy, then destroy the copy **Upgrade readiness** - [ ] Support end date known[^pg-versions]; `pg_upgrade --check` or a blue/green rehearsal[^pg-upgrade][^rds-bg] - [ ] Extensions and drivers tested; statistics step planned; rollback route chosen - [ ] Checksum settings match (18's initdb enables them; pg_upgrade needs a match)[^pg18-notes]; `pg_rewind` ready[^pg-rewind] ### Templates ```text MIGRATION PLAN — — Change: rename orders.cust_name -> customer_name Class per step: V2 expand | backfill risky | V3-V6 risky | V7 contract Size: rows, table size, measured duration on the copy Locks: strongest lock per statement; lock_timeout = 2s; retries = 3 Compatibility: app versions supported at each step Rollback: position after each step; V7 restore-only Throttle: batch size, pause when replay_lag > 5 s Owner / reviewer / window: ``` ```text RESTORE TEST RECORD — — Target time: (UTC) Base backup used: Started / consistent / promoted: (UTC, UTC, UTC) Duration vs target: Data checks (counts, sums): Problems found: Follow-up tickets: Copy destroyed at: Signed off by: ``` ### Deep comparisons | Choice | Option A | Option B | Choose A when… | |---|---|---|---| | Replication | Asynchronous: no commit delay, loss ≈ lag at failover[^pg-standby] | Synchronous: no loss of acknowledged commits; slower commits that hang if a required standby crashes[^pg-recovery][^pg-standby] | Seconds of loss are acceptable; latency matters | | Migration style | Versioned scripts (Flyway, Liquibase, Sqitch) | Declarative desired state (Atlas)[^atlas] | You need exact control of lock-sensitive statements | | Big rewrite | Copy-and-swap tool (gh-ost, pt-osc, pgroll) | Application-level dual writes (Stripe)[^stripe] | The change is within one table | ### FAQ **Is DBOps a job title to hire for?** Hire for the work; the title appears in some postings[^phenom] with no common definition. **Do managed databases make DBOps unnecessary?** No. You still own schema changes, queries, connections, retention settings and upgrade timing[^rds-pitr][^rds-es]. **Is `ADD COLUMN` safe?** A nullable column, or one with a non-volatile default, is a metadata change[^pg-alter], but it still takes ACCESS EXCLUSIVE and can queue: set `lock_timeout`. **Why not one transaction per migration?** It is the PostgreSQL default, but locks last until commit[^pg-locks], so never combine a brief-lock statement with a long scan; `CREATE INDEX CONCURRENTLY` cannot run in a transaction at all[^pg-cic]. **Does this page apply to MySQL?** The principles do; the mechanics differ ([lock table](#lock-cost-of-common-changes)). MySQL replication is also "asynchronous by default"; semisynchronous mode waits until at least one replica has logged each commit but falls back to asynchronous after a timeout, and Group Replication (single-primary mode) elects a new primary automatically[^mysql-repl]. ## Going further and wrap-up ### Advanced topics and emerging direction - **Established:** multi-version schemas through views (pgroll)[^pgroll]; logical replication for partitioning and cross-version moves, as Figma did[^figma]; Kubernetes operators with continuous backup to object storage[^cnpg]; sharding layers such as Vitess[^vitess]. - **Maturing:** declarative schema management with automated lint analysis in CI[^atlas]; managed blue/green switchovers, still with real limits for PostgreSQL logical replication[^rds-bg]. - **Speculative:** AI agents that apply migrations; the same guard, review and least privilege apply. ### Learning path | Stage | What to do | |---|---| | Prerequisites | SQL, transactions and isolation, Linux; the pipeline model in [DevOps](../devops/) | | 1 day | This page's opening parts; [Lab 1](#hands-on-labs); read the PostgreSQL "Explicit Locking" chapter[^pg-locks] | | 1 week | Labs 2 and 3; the migration plan template for a real change; `pg_stat_statements` on staging | | 30 days | Guard in CI, a timed restore, a failover game day; read *Evolutionary Database Design*[^evodb] and DORA's page[^dora-db] | | Specialise | HA (Patroni or an operator), query tuning, online schema change; then [SRE](../sre/) and [DisasterRecoveryOps](../disasterrecoveryops/) | | Portfolio | A repository with migrations, a guard, CI on a service container and a restore drill record | ### Executive recap and cheat sheet | Topic | Remember | |---|---| | Term | Informal label; the work is database reliability and change management | | The rule for changes | Short `lock_timeout`, one blocking statement per transaction, expand and contract when breaking | | The rule for recovery | Base backup + WAL archive + a timed restore each month | | The rule for failover | Fence first; at async failover you lose the lag | | Five alerts | Lag, archive failures and age, XID age, connections, lock waits | | Next actions | `lock_timeout` everywhere; a restore drill; end-of-support dates | ### A judgement call: a maintenance window or an online migration? **Situation (illustrative).** A B2B invoicing service must change `invoice.amount` from `integer` cents to `numeric(12,2)` on a 400-million-row table. Option A: a two-hour Sunday maintenance window: stop the service, run `ALTER COLUMN amount TYPE numeric(12,2) USING amount / 100.0` (without `USING`, 1234 cents silently become 1234.00), start the release that reads currency units. Option B: expand and contract over four deployments in two weeks. **Assumptions:** contracts allow monthly maintenance windows; the rewrite took 70 minutes on a production-sized copy; one engineer has two weeks. **Reasoning.** (1) *Risk of A:* a type change "will normally cause the entire table and its indexes to be rewritten"[^pg-alter] under an exclusive lock; 70 measured minutes leave 50 minutes of margin, but the rewrite also writes WAL for the whole table, which standbys and the archive must absorb. (2) *Risk of B:* two weeks of trigger overhead and dual code paths; a wrong sync trigger corrupts data silently. (3) *Rollback:* A only by restore (hours); B at any step before contract. (4) *What would change the answer:* no contractual window, or a rehearsal that does not fit with margin, makes B mandatory; a team unable to verify a sync trigger makes A safer. (5) *Fit with the [decision tree](#decision-framework):* it routes breaking changes to expand and contract because a running version would break; the window stops the service, which is what makes A legitimate. **Decision: A, with guardrails:** a fresh restore point, a rehearsal that week, standby and archive capacity checked, `lock_timeout` on the `ALTER`, a go/no-go at minute 0. **Uncertainty:** WAL volume and replay speed are estimates until rehearsed; if replay cannot keep up within the window, switch to B. ### Learning objectives and mastery check You can now explain the label and its boundaries, predict which DDL blocks traffic, plan expand and contract with rollback positions, prove PITR, reason about lag, fencing and failover loss, and set up roles, a guarded pipeline and metrics. ::: answer 1. A one-millisecond `ALTER TABLE ... ADD COLUMN` caused a minute of API errors. How? It queued for ACCESS EXCLUSIVE behind a long transaction, and every later query queued behind it[^pg-locks][^gocardless-help]. Retry with `lock_timeout`; prevent long transactions with `idle_in_transaction_session_timeout` on the application role. ::: ::: answer 2. Why is "add the new column and drop the old one in the same release" wrong? The running version still uses the old column, and the drop is irreversible. Split into expand, migrate, contract[^parallel]. ::: ::: answer 3. A developer deleted the wrong rows at 10:02:15. The standby is healthy. What do you do? The standby already applied it. Restore the base backup and archived WAL into a new instance with `recovery_target_time` just before 10:02:15[^pg-pitr][^pg-recovery], then copy the rows back; never restore over production. ::: ::: answer 4. Your async standby showed 3 s `replay_lag` when the primary died. What did you lose, and how could you have lost nothing? At most about 3 seconds of commits, probably less: replay lag includes WAL already received, which the standby replays before promotion; what is lost is WAL never received (compare receive LSNs)[^pg-standby][^pg-stats]. Synchronous commit avoids that at a latency cost, and commits hang if the required standby crashes[^pg-recovery][^pg-standby]. ::: ::: answer 5. `CREATE INDEX CONCURRENTLY` failed in a deploy and the retry with `IF NOT EXISTS` "succeeded". Is the index usable? Probably not: the failed build left an INVALID index[^pg-cic], which `IF NOT EXISTS` skips. Check `indisvalid`, drop it concurrently, rebuild. ::: ::: answer 6. XID age is 900 million and climbing although autovacuum is on. What do you look for? A blocker of freezing: a long or idle-in-transaction session, an abandoned slot, or vacuum unable to finish one huge table. Wraparound vacuum should start near 200 million[^pg-vacuum-cfg]; the server warns at 40 million remaining and refuses new IDs at 3 million[^pg-vacuum]. ::: ### Conclusion "DBOps" is a thin label over a thick craft. The craft has four habits: changes as reviewed code that **cannot wait long for a lock**; compatibility across one application version, so the database never needs to roll back; recovery proven by **timed restores**, because replicas copy mistakes; and **slow counters** (lag, archive age, XID age) watched as closely as CPU. None of it needs a platform purchase; all of it fails quietly until needed. Facts are dated October 2026; re-check versions and limits before relying on them. ## Sources and further reading All sources opened on 2026-10-18; PostgreSQL pages are the version 18 manual. Ledger: `research/dbops-ledger.md`. [^phenom]: [Principal DBOps Engineer (Phenom)](https://careers.sierraventures.com/companies/phenom-2/jobs/61404118-principal-dbops-engineer) — job posting, Sierra Ventures board (closed). Checked 2026-10-18. [^dbops-ps]: [dbops](https://github.com/dataplat/dbops) — PowerShell module, GitHub README. Checked 2026-10-18. [^bytebase]: [What is Database DevOps](https://bytebase.com/blog/what-is-database-devops) — Tianzhou, Bytebase, 2026-07-12. Checked 2026-10-18. [^harness]: [Database DevOps](https://developer.harness.io/docs/database-devops/) — Harness Developer Hub. Checked 2026-10-18. [^dre-book]: [Database Reliability Engineering (book listing)](https://www.i-programmer.info/book-watch-archive/11372-database-reliability-engineering-oreilly.html) — Laine Campbell and Charity Majors, O'Reilly, 2017; listing on I Programmer. Checked 2026-10-18. [^rathbun]: [A DBA's Perspective: What Is DevOps?](https://www.solarwinds.com/blog/a-dbas-perspective-what-is-devops) — Monica Rathbun, SolarWinds, 2021-08-04. Checked 2026-10-18. [^pg-versions]: [Versioning Policy](https://www.postgresql.org/support/versioning/) — PostgreSQL Global Development Group. Checked 2026-10-18. [^gocardless-hard]: [Zero-downtime Postgres migrations: the hard parts](https://gocardless.com/blog/zero-downtime-postgres-migrations-the-hard-parts/) — Chris Sinjakli, GoCardless, 2015-05-27 (per a 2015 Internet Archive copy; edited June 2024). Checked 2026-10-18. [^gitlab2017]: [Postmortem of database outage of January 31](https://about.gitlab.com/blog/postmortem-of-database-outage-of-january-31/) — GitLab, 2017-02-10. Checked 2026-10-18. [^pg-vacuum]: [Routine Vacuuming](https://www.postgresql.org/docs/current/routine-vacuuming.html) and [Populating a Database](https://www.postgresql.org/docs/current/populate.html) — PostgreSQL 18 docs. Checked 2026-10-18. [^sentry]: [Transaction ID Wraparound in Postgres](https://blog.sentry.io/transaction-id-wraparound-in-postgres) — David Cramer, Sentry, 2015-07-23. Checked 2026-10-18. [^mandrill]: [What We Learned from the Recent Mandrill Outage](https://mailchimp.com/what-we-learned-from-the-recent-mandrill-outage/) — Mailchimp, 2019-03-26. Checked 2026-10-18. [^dora-db]: [Capabilities: Database change management](https://dora.dev/capabilities/database-change-management/) — DORA (undated). Checked 2026-10-18. [^evodb]: [Evolutionary Database Design](https://martinfowler.com/articles/evodb.html) — Pramod Sadalage and Martin Fowler, May 2016 (first 2003). Checked 2026-10-18. [^parallel]: [ParallelChange](https://martinfowler.com/bliki/ParallelChange.html) — Danilo Sato, 2014-05-13. Checked 2026-10-18. [^stripe]: [Online migrations at scale](https://stripe.com/blog/online-migrations) — Jacqueline Xu, Stripe, 2017-02-02. Checked 2026-10-18. [^flyway-versioned]: [Versioned migrations](https://documentation.red-gate.com/fd/versioned-migrations-273973333.html) — Redgate Flyway docs. Checked 2026-10-18. [^pg-alter]: [ALTER TABLE](https://www.postgresql.org/docs/current/sql-altertable.html) — PostgreSQL 18 docs. Checked 2026-10-18. [^pg-locks]: [Explicit Locking](https://www.postgresql.org/docs/current/explicit-locking.html) — PostgreSQL 18 docs. Checked 2026-10-18. [^mysql-mdl]: [Online DDL Performance and Concurrency](https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-performance.html) — MySQL 8.4 manual. Checked 2026-10-18. [^pg-client]: [Client Connection Defaults](https://www.postgresql.org/docs/current/runtime-config-client.html) — PostgreSQL 18 docs. Checked 2026-10-18. [^pg-pitr]: [Continuous Archiving and Point-in-Time Recovery](https://www.postgresql.org/docs/current/continuous-archiving.html) — PostgreSQL 18 docs. Checked 2026-10-18. [^pg-stats]: [The Cumulative Statistics System](https://www.postgresql.org/docs/current/monitoring-stats.html) — PostgreSQL 18 docs. Checked 2026-10-18. [^pg-recovery]: [Write Ahead Log settings](https://www.postgresql.org/docs/current/runtime-config-wal.html) — PostgreSQL 18 docs. Checked 2026-10-18. [^pg-failover]: [Failover](https://www.postgresql.org/docs/current/warm-standby-failover.html) — PostgreSQL 18 docs. Checked 2026-10-18. [^gocardless-help]: [Zero-downtime Postgres migrations: a little help](https://gocardless.com/blog/zero-downtime-postgres-migrations-a-little-help/) — Chris Sinjakli, GoCardless, 2015-12-15 (per a 2016 Internet Archive copy; edited June 2024). Checked 2026-10-18. [^flyway-undo]: [Undo migrations](https://documentation.red-gate.com/fd/undo-migrations-273973334.html) — Redgate Flyway docs (Teams edition). Checked 2026-10-18. [^phil-factor]: [Rollbacks, Undos and Undonts](https://www.red-gate.com/hub/product-learning/flyway/rollbacks-undos-and-undonts) — Phil Factor, Redgate, 2022-08-22. Checked 2026-10-18. [^pg-standby]: [Log-Shipping Standby Servers](https://www.postgresql.org/docs/current/warm-standby.html) — PostgreSQL 18 docs. Checked 2026-10-18. [^pg-priv]: [Privileges](https://www.postgresql.org/docs/current/ddl-priv.html) — PostgreSQL 18 docs. Checked 2026-10-18. [^lb-tables]: [DATABASECHANGELOG](https://docs.liquibase.com/concepts/tracking-tables/databasechangelog-table.html) and [DATABASECHANGELOGLOCK](https://docs.liquibase.com/concepts/tracking-tables/databasechangeloglock-table.html) — Liquibase docs. Checked 2026-10-18. [^patroni]: [Patroni documentation](https://patroni.readthedocs.io/en/latest/) and [Watchdog support](https://patroni.readthedocs.io/en/latest/watchdog.html) — Patroni 4.1.5. Checked 2026-10-18. [^cnpg]: [CloudNativePG](https://cloudnative-pg.io/) and [CNCF project page](https://www.cncf.io/projects/cloudnativepg/) — accepted 2025-01-21, Sandbox. Checked 2026-10-18. [^pgbackrest]: [pgBackRest](https://pgbackrest.org/) — v2.59.3, 2026-10-04. Checked 2026-10-18. [^rds-pitr]: [Restoring a DB instance to a specified time](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PIT.html) and [Backup retention period](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithAutomatedBackups.BackupRetention.html) — Amazon RDS User Guide. Checked 2026-10-18. [^pg-pss]: [pg_stat_statements](https://www.postgresql.org/docs/current/pgstatstatements.html) — PostgreSQL 18 docs. Checked 2026-10-18. [^pg-logging]: [Error Reporting and Logging](https://www.postgresql.org/docs/current/runtime-config-logging.html) — PostgreSQL 18 docs. Checked 2026-10-18. [^strong]: [strong_migrations](https://github.com/ankane/strong_migrations) — Andrew Kane, GitHub README. Checked 2026-10-18. [^squawk]: [Squawk docs](https://squawkhq.com/docs/) and [require-lock-timeout](https://squawkhq.com/docs/require-lock-timeout) (replaces require-timeout-settings) — Squawk, October 2026. Checked 2026-10-18. [^pgbouncer]: [PgBouncer features](https://www.pgbouncer.org/features.html) — PgBouncer project. Checked 2026-10-18. [^mysql-ddl]: [Online DDL Operations](https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-operations.html) — MySQL 8.4 manual. Checked 2026-10-18. [^pg-cic]: [CREATE INDEX](https://www.postgresql.org/docs/current/sql-createindex.html) — PostgreSQL 18 docs. Checked 2026-10-18. [^mysql-commit]: [Statements That Cause an Implicit Commit](https://dev.mysql.com/doc/refman/8.4/en/implicit-commit.html) — MySQL 8.4 manual. Checked 2026-10-18. [^flyway-tx]: [Execute in transaction](https://documentation.red-gate.com/fd/flyway-execute-in-transaction-setting-277578997.html) and [Script configuration](https://documentation.red-gate.com/flyway/reference/script-configuration) — Redgate Flyway docs. Checked 2026-10-18. [^flyway-lock]: [PostgreSQL transactional lock setting](https://documentation.red-gate.com/fd/flyway-postgresql-transactional-lock-setting-277579114.html) — Redgate Flyway docs. Checked 2026-10-18. [^lb-changeset]: [Changeset](https://docs.liquibase.com/concepts/changelogs/changeset.html) — Liquibase docs. Checked 2026-10-18. [^gh-ost]: [gh-ost](https://github.com/github/gh-ost) — GitHub, README. Checked 2026-10-18. [^pt-osc]: [pt-online-schema-change](https://docs.percona.com/percona-toolkit/pt-online-schema-change.html) — Percona Toolkit docs. Checked 2026-10-18. [^pgroll]: [pgroll](https://github.com/xataio/pgroll) — Xata, GitHub README. Checked 2026-10-18. [^rds-bg]: [Overview of RDS Blue/Green Deployments](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/blue-green-deployments-overview.html) and [limitations](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/blue-green-deployments-considerations.html) — Amazon RDS User Guide. Checked 2026-10-18. [^pg-hot]: [Hot Standby](https://www.postgresql.org/docs/current/hot-standby.html) — PostgreSQL 18 docs. Checked 2026-10-18. [^rds-maz]: [Multi-AZ DB instance deployments](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.MultiAZSingleStandby.html) and [failover](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.MultiAZ.Failover.html) — Amazon RDS User Guide. Checked 2026-10-18. [^pg-rewind]: [pg_rewind](https://www.postgresql.org/docs/current/app-pgrewind.html) — PostgreSQL 18 docs. Checked 2026-10-18. [^pg-indexes]: [Indexes: introduction](https://www.postgresql.org/docs/current/indexes-intro.html) — PostgreSQL 18 docs. Checked 2026-10-18. [^pg-conn-wiki]: [Number Of Database Connections](https://wiki.postgresql.org/wiki/Number_Of_Database_Connections) — PostgreSQL wiki, last edited 2014-03-14. Checked 2026-10-18. [^rds-proxy]: [Avoiding pinning an RDS Proxy](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy-pinning.html) — Amazon RDS User Guide. Checked 2026-10-18. [^pg-upgrade]: [pg_upgrade](https://www.postgresql.org/docs/current/pgupgrade.html) — PostgreSQL 18 docs. Checked 2026-10-18. [^rds-es]: [Amazon RDS Extended Support](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/extended-support.html) and [creating an instance with it](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/extended-support-creating-db-instance.html) — Amazon RDS User Guide. Checked 2026-10-18. [^rollback-safety]: [Ensuring rollback safety during deployments](https://builder.aws.com/content/3F04j2yRAAMBuPSPs50xwXZqg01/ensuring-rollback-safety-during-deployments) — Sandeep Pokkunuri, Amazon Builders' Library, 2019-12-05. Checked 2026-10-18. [^sqitch]: [About Sqitch](https://sqitch.org/about/) — Sqitch project. Checked 2026-10-18. [^atlas]: [Atlas](https://atlasgo.io/) — Ariga, product homepage. Checked 2026-10-18. [^vitess]: [Vitess](https://vitess.io/) — Vitess project (CNCF graduated). Checked 2026-10-18. [^github2018]: [October 21 post-incident analysis](https://github.blog/news-insights/company-news/oct21-post-incident-analysis/) — Jason Warner, GitHub, 2018-10-30. Checked 2026-10-18. [^flyway-config]: [Environment variable resolver](https://documentation.red-gate.com/fd/environment-variable-resolver-277578943.html), [locations setting](https://documentation.red-gate.com/flyway/reference/configuration/flyway-namespace/flyway-locations-setting), [default schema](https://documentation.red-gate.com/fd/flyway-default-schema-setting-277578987.html) and [command-line download](https://documentation.red-gate.com/fd/command-line-277579359.html) (13.10.3) — Redgate Flyway docs. Checked 2026-10-18. [^pg-schemas]: [Schemas](https://www.postgresql.org/docs/current/ddl-schemas.html) — PostgreSQL 18 docs. Checked 2026-10-18. [^gh-services]: [Creating PostgreSQL service containers](https://docs.github.com/en/actions/tutorials/use-containerized-services/create-postgresql-service-containers) — GitHub Docs. Checked 2026-10-18. [^gh-env]: [Deployments and environments](https://docs.github.com/en/actions/reference/workflows-and-actions/deployments-and-environments) and [About code owners](https://docs.github.com/en/repositories/managing-your-repositorys-settings-and-features/customizing-your-repository/about-code-owners) — GitHub Docs. Checked 2026-10-18. [^figma]: [The growing pains of database architecture](https://www.figma.com/blog/how-figma-scaled-to-multiple-databases/) — Tim Liang, Figma, 2023-04-04. Checked 2026-10-18. [^pg-vacuum-cfg]: [Vacuuming settings](https://www.postgresql.org/docs/current/runtime-config-vacuum.html) — PostgreSQL 18 docs. Checked 2026-10-18. [^little]: [Little's law](https://en.wikipedia.org/wiki/Little%27s_law) — Wikipedia; original paper: J. D. C. Little, [A Proof for the Queuing Formula: L = λW](https://doi.org/10.1287/opre.9.3.383), *Operations Research* 9(3), 383–387, 1961 (bibliographic record via Crossref; full text not opened). Checked 2026-10-18. [^pg18-notes]: [Release 18](https://www.postgresql.org/docs/release/18.0/) — PostgreSQL release notes, 2025-09-25. Checked 2026-10-18. [^gh-checkout]: [actions/checkout releases](https://github.com/actions/checkout/releases) — GitHub (v7.0.1 latest). Checked 2026-10-18. [^gh-branch]: [About protected branches](https://docs.github.com/en/repositories/configuring-branches-and-merges-in-your-repository/managing-protected-branches/about-protected-branches) and [About rulesets](https://docs.github.com/en/repositories/configuring-branches-and-merges-in-your-repository/managing-rulesets/about-rulesets) — GitHub Docs. Checked 2026-10-18. [^pg-reindex]: [REINDEX](https://www.postgresql.org/docs/current/sql-reindex.html) — PostgreSQL 18 docs. Checked 2026-10-18. [^pg-repack]: [pg_repack 1.5.3](https://reorg.github.io/pg_repack/) — pg_repack project. Checked 2026-10-18. [^pg-partition]: [Table Partitioning](https://www.postgresql.org/docs/current/ddl-partitioning.html) — PostgreSQL 18 docs. Checked 2026-10-18. [^pgtap]: [pgTAP: Unit Testing for PostgreSQL](https://pgtap.org/) — pgTAP project. Checked 2026-10-18. [^mysql-repl]: [Replication](https://dev.mysql.com/doc/refman/8.4/en/replication.html), [Semisynchronous Replication](https://dev.mysql.com/doc/refman/8.4/en/replication-semisync.html) and [Single-Primary Mode](https://dev.mysql.com/doc/refman/8.4/en/group-replication-single-primary-mode.html) — MySQL 8.4 manual. Checked 2026-10-18. **Changelog.** 2026-10-18: first edition; labs, roles, triage queries and guard executed on PostgreSQL 13.13. r1: roles and migrations re-executed, keyset backfill, GitHub plan limits, PostgreSQL 18 NOT NULL, monthly PITR drill, vacuum, attributions. r2: review-gate preconditions, bloat remedies, partitioning, database tests, MySQL FAQ, `archive_timeout`, steps for dense passages, trust zones in the architecture figure, TOML parsed. 2026-10-18, post-score polish: "deployment-branch rules" and numbered steps in the review gate; the workflow also watches the runner config, guard, tests and itself; `test-db.sh` and triage query 7 (unused indexes) added and executed; undo wording matched to its sources; socket-path troubleshooting for the labs; shorter decision-tree labels; RDS PITR caveats as a list; date-proof PostgreSQL 14 line; Little's 1961 paper cited. Checker fixes: the lab hint now quotes the on-screen error and points to `primary.log`; pgTAP described as optional.