200: Partitioning, Replication Concepts, Backup, Restore, Vacuum, and Operational SQL

Learning outcomes

By the end of this lesson, you should be able to:

explain and apply declarative partitioning in a realistic implementation;
explain and apply replication basics in a realistic implementation;
explain and apply backups in a realistic implementation;
explain and apply RPO and RTO in a realistic implementation;
explain and apply vacuum and bloat in a realistic implementation.

These outcomes are deliberately practical. The goal is not just to define five PostgreSQL terms, but to use them when a transactional system has large tables, availability requirements, recovery constraints, and ongoing maintenance work.

Prerequisites and retrieval

This lesson assumes the 01–06 foundation and the preceding lessons in this module. Before you read, retrieve one concrete example from an earlier project where one of these concerns appeared. Perhaps a table grew faster than expected, a report needed a read replica, a deployment required a schema change, or a backup had never been restored in a real test.

Use that example as a reference point while you work through the lesson. The purpose is not to memorize terminology. It is to make a defensible decision inside a PostgreSQL-backed transactional application, where schema design, correctness, query plans, and concurrency all matter at the same time.

Terminology

Declarative partitioning: A table can be partitioned by range, list, or hash. The technique is useful when partition pruning, lifecycle management, or operational scale justifies the added schema and maintenance complexity.
Replication basics: Streaming or logical replication copies changes so another PostgreSQL instance can support availability, read scaling, migrations, or integrations. A copy of the data is not automatically a copy of the primary's read-after-write behavior.
Backups: A backup strategy includes retention, encryption, isolation from site or account failure, and periodic restore tests. A job that reports success has created an artifact; it has not yet demonstrated that the application can recover.
RPO and RTO: Recovery Point Objective bounds the amount of data loss that is acceptable. Recovery Time Objective bounds the amount of outage time that is acceptable. Both are service requirements, not merely database settings.
VACUUM and bloat: PostgreSQL's MVCC behavior creates dead row versions after updates and deletes. Vacuuming is the maintenance process that helps clean up those versions and maintain visibility information; bloat is the wasted or inefficiently reusable space that can result when cleanup cannot keep up.
Operational locks: DDL, index builds, and maintenance operations can acquire locks. The lock mode and duration affect whether application traffic is delayed or blocked.

Mental model

Treat Partitioning, Replication Concepts, Backup, Restore, Vacuum, and Operational SQL as one design problem with observable inputs, outputs, invariants, and failure modes. Production SQL knowledge includes the lifecycle around queries and tables. Large tables, replication lag, incomplete backups, long transactions, vacuum behavior, and schema maintenance can affect correctness just as directly as query syntax can.

A strong implementation makes its assumptions visible, narrows uncertainty at system boundaries, and leaves enough evidence to justify the design. That evidence may be tests, types, constraints, metrics, restore logs, lock observations, query plans, or diagrams. For example, “reads go to the replica” is not a complete contract until the design says how much lag is acceptable and what happens immediately after a write.

A useful interview and production sequence is:

requirement -> constraints -> model -> implementation -> failure analysis -> verification

Start by stating what must remain true. Then identify the constraints that may prevent the obvious solution: table size, retention, recovery targets, write volume, consistency expectations, maintenance windows, and lock tolerance. Only after that should you choose a PostgreSQL mechanism. Jumping from a requirement directly to a library call or a configuration flag usually hides the assumption that will matter during an incident.

Deep dive

1. Declarative partitioning

When a table becomes very large, queries and maintenance may need to operate on a smaller, well-defined part of it. Declarative partitioning divides the logical table into partitions selected by a partition key. With range partitioning, for example, events can be separated by time so a query restricted to one month can avoid inspecting unrelated partitions. That behavior is called partition pruning.

Partition a table by range, list, or hash when pruning, lifecycle management, or operational scale justifies it. Time-based partitions can make retention work more targeted because old data can be detached or dropped as a unit, but they also require a process for creating future partitions and handling values outside the expected range. List and hash partitioning solve different distribution problems; neither is a universal answer to slow queries.

Partitioning is not a universal speed feature. It can complicate primary and unique keys, index design, foreign-key relationships, migrations, and application assumptions about one physical table. The partition key must also line up with useful access patterns. Adding partitions without checking plans and maintenance behavior only moves complexity around.

Decision rule: Use declarative partitioning deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design. Be able to explain the partition key, the lifecycle policy, what happens at a boundary, and how the design will be observed in production.

2. Replication basics

Replication keeps another PostgreSQL instance or logical target updated from a source. Streaming replication generally follows the database's change stream at the physical level, while logical replication publishes row-level changes for selected tables or other integration and migration use cases. The exact operational behavior depends on the setup, but the application-level question is the same: which reads are allowed to be stale, and how will the system respond when a replica falls behind?

Streaming or logical replication can support availability, read scaling, migrations, or integrations. A replica can lag because of network delay, a busy subscriber, replay contention, or a replication slot retaining changes. Sending a read to a replica therefore changes the consistency contract. If a request writes data and then immediately reads it, routing the second operation to a lagging replica may return an older state.

Replicas also do not remove the need to monitor the primary. Track lag and replication health, and define what happens when the replica is unavailable or too stale. Failover requires more than having a second server: the system needs a promotion procedure, connection routing, data-loss expectations, and a way to verify that clients are no longer using the old primary.

Decision rule: Use replication basics deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design. State whether each read needs primary consistency, bounded staleness, or eventual consistency, and test the behavior rather than assuming replication is instantaneous.

3. Backups

A backup is useful only if it can be found, trusted, and restored within the service's constraints. A backup strategy needs retention, encryption, offsite failure isolation, and periodic restore tests. Retention answers how far back recovery must reach; encryption protects the backup artifact; offsite or independently controlled storage limits the blast radius of a database host, region, or account failure.

Backups may be physical, logical, full, incremental, or coordinated with an ongoing archive of changes, depending on the recovery design. The implementation must document what each artifact contains and what sequence is required to restore it. A successful backup job is not proof that recovery works. Restoration can fail because of corrupted files, missing keys, unavailable dependencies, incompatible versions, incorrect permissions, or a procedure that nobody has rehearsed.

Periodic restore tests should verify both the technical result and the application's result. Can the database start? Are the expected tables, indexes, and permissions present? Can the application connect and serve a representative request? Record the restore duration and compare it with the RTO instead of treating “the backup exists” as the end of the process.

Decision rule: Use backups deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design. Define retention, encryption, isolation, restore ownership, and test frequency before relying on the backup in an incident.

4. RPO and RTO

Recovery requirements become much clearer when they are stated as two separate limits. Recovery Point Objective bounds acceptable data loss: if the RPO is fifteen minutes, the service must be designed so that losing more than fifteen minutes of committed work is not an expected recovery outcome. Recovery Time Objective bounds acceptable outage duration: if the RTO is one hour, recovery must bring the service back within that hour.

RPO and RTO determine backup frequency, replication, automation, and cost. A low RPO may require frequent change archiving or synchronous behavior rather than an occasional backup. A low RTO may require a prepared standby, tested automation, and fast connection reconfiguration. Neither target is achieved by labeling a database “high availability”; it must be demonstrated with measurements and failure exercises.

There is a trade-off between recovery targets, operational complexity, performance, and cost. Ask who owns the requirement and what counts as recovered: a running database, a functioning API, or a fully usable customer workflow. Also document what data may be unavailable or reconstructed after recovery. Those details prevent a technically successful restore from being declared a business failure.

Decision rule: Use RPO and RTO deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design. Choose mechanisms that can meet measured targets, and revisit the design when data volume, traffic, or business impact changes.

5. VACUUM and bloat

An update or delete in an MVCC database does not simply overwrite a row in place for every concurrent reader. PostgreSQL creates row versions and later needs to determine when old versions are no longer visible to any transaction. Those obsolete versions are dead row versions. If they remain too long, tables and indexes can consume more space and scans may do more work. This condition is commonly described as bloat.

Autovacuum reclaims or reuses space and maintains visibility metadata. Its work can be delayed by long-running transactions, prepared transactions, replication behavior, or a workload with heavy update and delete churn. More storage is not the only symptom: stale visibility information, excessive dead tuples, and delayed cleanup can contribute to degraded query and maintenance behavior.

Inspect transaction age, table statistics, dead-tuple estimates, vacuum and analyze activity, and workload patterns before changing settings. Do not treat manual vacuum as a universal cure. The right response may involve ending or fixing long transactions, adjusting per-table autovacuum thresholds, changing the write pattern, rebuilding an index, or scheduling a more invasive operation. Measure the result after the change.

Decision rule: Use vacuum and bloat deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design. Define which maintenance signals matter, who responds to them, and how cleanup interacts with active traffic.

6. Operational locks

SQL that changes the schema is still part of the production request path, even when it is run from a migration tool. DDL, index builds, and maintenance can acquire locks. Some operations briefly block writers or readers; others can hold a lock while scanning or rewriting a table. On a hot table, the same statement that completed quickly in development can create a queue of blocked application requests.

Use concurrent or index-safe strategies where supported, set sensible timeouts, and monitor blocking before deploying changes to hot tables. A migration plan should account for transaction scope, lock acquisition order, existing long-running work, rollback behavior, and what happens if the operation is interrupted. “It is only one ALTER TABLE” is not a concurrency analysis.

Decision rule: Use operational locks deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design. Inspect the lock behavior in a production-like environment and deploy changes in a way that gives operators a clear stop or recovery path.

Worked example

Consider a PostgreSQL-backed transactional application where schema design, correctness, query plans, and concurrency all matter. Start by writing the requirement in one sentence. Then list the input and output contracts and identify which concept above owns each failure mode. For instance, a retention requirement may point toward partitioning, a read-scaling requirement toward replication, and a recovery promise toward backups plus explicit RPO and RTO targets.

The important move is separation. Parsing or validation belongs at the boundary; domain rules belong in the domain or service layer; persistence rules belong in the database or repository; presentation rules belong in the client. Mixing these concerns can make a happy-path demo look shorter, but it makes retries, malformed values, concurrent requests, and partial failures much harder to reason about.

Here is a small query that reports every active customer, including active customers with no orders:

SELECT c.id, c.name, COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE c.status = 'active'
GROUP BY c.id, c.name
ORDER BY order_count DESC;

The LEFT JOIN is the key detail: a customer with no matching order still appears, and COUNT(o.id) produces zero for that customer because the joined order id is null. The WHERE clause filters customers before the grouping result is returned. Grouping by both selected customer columns keeps the aggregate well-defined, and the alias makes the ordering intent readable. In a real system, inspect the execution plan and verify the indexes and row counts rather than assuming the query will remain inexpensive as the tables grow.

Walk the example with at least four cases: the normal path, an empty or missing value, a duplicate/retry/concurrent path where relevant, and a dependency failure. For each case, state which layer detects the problem and what the caller observes. Also ask whether a read after a write can use a lagging replica, whether a schema change can block this query, and whether the required data can be recovered within the stated RPO and RTO. This is the level of explanation expected in a senior code review or technical interview.

Production perspective

Production correctness is broader than “the code works on my machine.” Ask how the design behaves during deploys, retries, partial failure, stale clients, concurrent requests, malformed data, schema changes, and high cardinality. For this topic, extend those questions to table growth, replica lag, failed restores, long transactions, dead tuples, and lock queues. Prefer explicit contracts, bounded resource usage, structured errors, and measurable behavior. Optimize only after you can identify the bottleneck or risk with evidence.

When the topic involves an external dependency, define a timeout and cancellation strategy. When it involves persistence, define transaction and consistency expectations. When it involves user-visible state, define loading, empty, error, stale, and success states. When it involves security, assume the client can be modified and the network input is untrusted. Database credentials, backup files, replication endpoints, and operational logs deserve the same care as application secrets; do not expose them to clients or commit them to source control.

Guided lab

Partition an event table by month, document the retention policy and partition-creation process, simulate a replica-stale read decision, and write a backup/restore checklist with explicit RPO and RTO. Inspect long transactions and vacuum statistics. For each activity, record what you observed, what a healthy result looks like, and what evidence would indicate a failure.

Complete the lab with this discipline:

Write the requirement and two non-requirements.
List input, output, and error contracts before implementation.
Implement the smallest correct vertical slice.
Add at least one invalid-input test and one edge-case test.
Instrument or inspect the behavior instead of guessing.
Refactor one hidden assumption into an explicit type, constraint, function, or configuration.
Explain one alternative design and why you did not choose it.
Record a short “what would break at 10× scale?” note.

For the partitioning portion, include a boundary-date case and explain what happens when the next month's partition has not been created. For replication, state what the caller sees when lag exceeds the allowed threshold. For recovery, time the restore rather than estimating it. For vacuum, connect the observed statistics to transaction age and write churn. These additions keep the lab focused on operational reasoning rather than on copying commands.

Edge cases and failure modes

Declarative partitioning: Test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Also test partition-key boundaries, missing future partitions, and retention operations while application traffic is active.
Replication basics: Test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Include lag, subscriber or network failure, failover routing, and read-after-write behavior.
Backups: Test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Verify missing or invalid artifacts, encryption-key access, retention expiry, and an actual restore.
RPO and RTO: Test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Check that measured recovery loss and recovery time are within the stated targets, not merely that a procedure exists.
VACUUM and bloat: Test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Include long transactions, sustained churn, delayed autovacuum, dead tuples, and the effect of maintenance on active queries.

Common mistakes and debugging

Solving the example instead of the requirement: a copied pattern can be syntactically correct but architecturally wrong.
Hiding uncertainty with assertions, broad exception handlers, permissive schemas, or “temporary” any values.
Testing only the happy path and therefore discovering contracts only after integration.
Optimizing before measuring, or selecting a scalable mechanism without a scale requirement.
Letting client-side behavior stand in for server-side authorization, validation, or persistence guarantees.
Treating a replica as synchronous without measuring lag or declaring a staleness policy.
Treating a successful backup job as a restore test, or setting RPO and RTO without measuring recovery.
Changing autovacuum or running maintenance without checking transaction age, locks, workload, and the resulting statistics.

For debugging, reproduce the smallest failing case, inspect the actual value or execution plan, and trace the boundary where the invariant first becomes false. For operational SQL, inspect active sessions, transaction age, lock holders, and waiters. For replication, inspect lag and replay state. For recovery, inspect the artifact and run the documented restore. Then fix the owning layer rather than adding a downstream patch.

Interview questions

What problem does Declarative partitioning solve, and what trade-off or failure mode would make you choose a different approach?
What problem does Replication basics solve, and what trade-off or failure mode would make you choose a different approach?
What problem does Backups solve, and what trade-off or failure mode would make you choose a different approach?
What problem does RPO and RTO solve, and what trade-off or failure mode would make you choose a different approach?
What problem does VACUUM and bloat solve, and what trade-off or failure mode would make you choose a different approach?

Answer each question with a requirement, a consistency or recovery invariant, one operational failure mode, and the evidence you would inspect. A vocabulary-only answer is not enough: the design choice should follow from the constraint.

Checkpoint

Without notes, explain Partitioning, Replication Concepts, Backup, Restore, Vacuum, and Operational SQL to another developer in five minutes. Your explanation must include one invariant, one edge case, one production failure mode, and one alternative design. Then implement a small example without copying the lesson code.

Mastery checklist

I can define the core terms precisely.
I can choose a design from requirements instead of from habit.
I can implement and test the normal path and edge cases.
I can explain the runtime, storage, or complexity cost.
I can identify which layer owns validation, errors, and recovery.
I can compare at least two reasonable alternatives.
I can explain how the design changes at larger scale or stricter reliability.