📘 Replication, Sharding and Operations

Writing SQL is half of the job. The other half is keeping the database available, fast, recoverable, and safe as it grows. This page follows the usual growth path: replicate, pool, partition, shard, and then covers backups, migrations, monitoring and security. Design-level treatment of the same ideas is in Building Blocks and Distributed Systems.

Table of Contents

1. The Scaling Path
2. Replication
3. High Availability and Failover
4. Connection Pooling
5. Partitioning
6. Sharding
7. Backups and Recovery
8. Schema Migrations Without Downtime
9. Monitoring and Maintenance
10. Security
11. Managed Services and Choosing
12. Questions and Answers

1. The Scaling Path

Scale up before you scale out, and do the cheap things first.

A single modern primary with fast NVMe and hundreds of gigabytes of RAM serves a very large workload. Most companies never reach step 7. Each step up the ladder adds operational and consistency cost, so move only when measurements demand it.

2. Replication

Replication copies changes from a primary (leader) to replicas (followers). Reasons: high availability, read scaling, geographic locality, backups without load, and feeding other systems.

2.1 Kinds of Replication

2.2 Synchronous vs Asynchronous

PostgreSQL: synchronous_standby_names = 'ANY 1 (replica_a, replica_b)' gives quorum commit. MySQL: semi-synchronous replication plugin, or Group Replication (Paxos-based).

2.3 Replication Lag

Lag is how far a replica is behind the primary. Causes: heavy write bursts, long-running queries on the replica that conflict with replay, slow disks or network, large transactions, single-threaded apply.

Consequences for the application:

• Read-your-writes breaks: a user posts, then a page load reads from a lagging replica and the post is missing.
• Non-monotonic reads: a refresh goes to a different replica and time appears to go backward.

Mitigations:

• Route reads that must be fresh (right after a write, or in the same session for a short window) to the primary.
• Track the write's log position (LSN or GTID) and only read from a replica that has passed it.
• Pin a user session to one replica.
• Alert on lag, and stop routing traffic to replicas that fall behind a threshold.
• Watch pg_stat_replication (byte and time lag) or SHOW REPLICA STATUS.

Delayed replicas (intentionally an hour behind) are cheap insurance against a bad DELETE.

2.4 Multi-Primary and Active-Active

Multiple writers need conflict resolution. Options: single-writer per row or region (home region), last-write-wins, CRDTs, or a consensus database (Spanner, CockroachDB). Plain PostgreSQL and MySQL are single-primary, and multi-primary setups (Galera, BDR, Group Replication in multi-primary mode) come with restrictions and conflict handling that you must understand before choosing them.

3. High Availability and Failover

A failure of the primary should be survived in seconds to minutes, without data loss beyond the agreed RPO.

Failover steps:

1. Detect failure: missed heartbeats or lease expiry in the consensus store.
2. Elect the most caught-up standby.
3. Fence the old primary so it cannot accept writes if it returns (split brain is the worst outcome). Leases in the consensus store, STONITH, or storage fencing.
4. Promote the standby, repoint clients (virtual IP, proxy, DNS with a short TTL), and reconfigure other standbys to follow.
5. Rebuild the old primary as a standby (pg_rewind or a fresh base backup).

Targets to state in interviews: RPO (how much data you can lose, zero with synchronous replication) and RTO (how long you can be down, automatic failover gives seconds to minutes). Test failover regularly, an untested standby is an assumption.

4. Connection Pooling

A database connection is expensive: PostgreSQL uses a process per connection (several megabytes each), and MySQL a thread per connection. Thousands of direct connections thrash memory and the scheduler. More connections do not mean more throughput past a small number.

Sizing rule of thumb (from the HikariCP guidance): about (CPU cores x 2) + effective disk spindles active connections per database server. Small pools with queueing outperform huge pools.

PgBouncer pooling modes:

Serverless functions are the classic connection-storm source (each cold start opens a connection), so put a pooler or a serverless-friendly proxy in front. See Backend performance.

5. Partitioning

Partitioning splits one big table into smaller physical pieces inside one database. The table still looks like one table to queries.

Benefits:

• Partition pruning: the planner skips partitions that cannot match the WHERE, so a query for one month reads one partition.
• Cheap retention: DROP or detach an old partition instead of deleting millions of rows (no bloat, no long transaction).
• Maintenance: vacuum, reindex, and backup operate per partition.
• Smaller indexes per partition fit in memory.

Pitfalls:

• The partition key must be part of primary and unique keys in PostgreSQL, which affects design.
• Queries without the partition key scan all partitions.
• Thousands of partitions slow planning. Keep the count to hundreds.
• Foreign keys to and from partitioned tables have limits in some versions.

PostgreSQL declarative partitioning (illustrative):

CREATE TABLE events (
  id         bigint GENERATED ALWAYS AS IDENTITY,
  created_at timestamptz NOT NULL,
  payload    jsonb,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_01 PARTITION OF events FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE events_2026_02 PARTITION OF events FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- retention: detach and drop, instantly, no DELETE
ALTER TABLE events DETACH PARTITION events_2026_01;
DROP TABLE events_2026_01;

The pg_partman extension automates creating and dropping time partitions.

6. Sharding

Sharding splits data across multiple independent databases (shards), each holding a subset. It scales writes and storage beyond one machine, at the price of complexity.

6.1 When and How

Shard only when a single primary cannot handle the write rate or data size after the earlier steps. The design decisions:

6.2 Tools and Options

For multi-tenant SaaS, tenant-per-shard is the natural design: it keeps transactions local and lets you move a noisy tenant to its own shard.

7. Backups and Recovery

A backup you have not restored is a hypothesis.

7.1 Kinds

7.2 Point-in-Time Recovery (PITR)

PITR answers "someone dropped the table at 14:32, restore to 14:31". It is what saves you from bad deploys and human error, which replicas do not (they replicate the mistake instantly).

7.3 Practice

• 3-2-1 rule: three copies, two media, one offsite (another account and region).
• Immutable and access-separated backups so ransomware or a compromised admin cannot delete them.
• Test restores on a schedule, and measure real RTO. Automate it.
• Encrypt backups, and protect the keys separately.
• Define retention (legal and business), and monitor backup and WAL archive success.
• Managed services provide automated backups and PITR windows, learn the limits (retention days, cross-region copy).

8. Schema Migrations Without Downtime

Code and schema deploy at different moments, so every change must work with both the old and new code.

8.1 Expand, Migrate, Contract

-- runnable
CREATE TABLE users (id INTEGER PRIMARY KEY, full_name TEXT NOT NULL);
INSERT INTO users VALUES (1, 'Ana Bell'), (2, 'Ben Cruz'), (3, 'Cy');

-- 1. EXPAND: add nullable columns, old code keeps working
ALTER TABLE users ADD COLUMN first_name TEXT;
ALTER TABLE users ADD COLUMN last_name TEXT;

-- 2. DUAL WRITE: new rows fill both shapes (in real systems the application does this, a trigger is shown for a self-contained demo)
CREATE TRIGGER users_sync AFTER INSERT ON users
BEGIN
  UPDATE users
  SET first_name = substr(NEW.full_name, 1, instr(NEW.full_name || ' ', ' ') - 1),
      last_name  = NULLIF(trim(substr(NEW.full_name, instr(NEW.full_name || ' ', ' '))), '')
  WHERE id = NEW.id;
END;

-- 3. BACKFILL old rows in small id ranges, so each statement is short and locks little
UPDATE users SET first_name = substr(full_name, 1, instr(full_name || ' ', ' ') - 1),
                 last_name  = NULLIF(trim(substr(full_name, instr(full_name || ' ', ' '))), '')
WHERE first_name IS NULL AND id BETWEEN 1 AND 2;
UPDATE users SET first_name = substr(full_name, 1, instr(full_name || ' ', ' ') - 1),
                 last_name  = NULLIF(trim(substr(full_name, instr(full_name || ' ', ' '))), '')
WHERE first_name IS NULL AND id BETWEEN 3 AND 4;

INSERT INTO users (id, full_name) VALUES (4, 'Dee Ford');           -- a new row after the migration started
SELECT id, first_name, last_name FROM users ORDER BY id;

-- 4. VERIFY nothing is left before switching reads
SELECT COUNT(*) AS unmigrated FROM users WHERE first_name IS NULL;

-- 5. CONTRACT after every reader uses the new columns
DROP TRIGGER users_sync;
ALTER TABLE users DROP COLUMN full_name;
SELECT * FROM users ORDER BY id;

8.2 Dangerous Operations and Safer Versions (PostgreSQL)

Always set lock_timeout (for example 3 seconds) and retry. Otherwise an ALTER waiting for a lock queues behind a long transaction and blocks every query behind it, causing an outage.

MySQL: online DDL handles many changes, and gh-ost or pt-online-schema-change copy the table in the background for the rest.

Migration tools: Flyway, Liquibase, Alembic, Prisma Migrate, Rails and Django migrations, Atlas. Keep migrations versioned in source control, run in CI against a copy of production-like data, and make them forward-only with tested restores rather than relying on down-migrations.

9. Monitoring and Maintenance

9.1 What to Watch

Alert on symptoms first (latency, errors, replication lag, disk full within N hours), and investigate causes on dashboards. See Reliability and Operations for SLO-based alerting.

9.2 Routine Maintenance

• Autovacuum and analyze: keep them healthy. Tune per table for high-churn tables (lower scale factors). Watch for blocked vacuum caused by long transactions or abandoned replication slots.
• Bloat management: pg_repack, REINDEX CONCURRENTLY.
• Statistics targets for skewed columns.
• Upgrades: minor versions regularly (security fixes). Major upgrades via pg_upgrade, logical replication with a cutover, or the managed service's blue-green feature. Rehearse them.
• Capacity planning: track growth of data, connections and IOPS, and forecast when the next scaling step is needed.
• Extensions and versions: pg_stat_statements, auto_explain, pgaudit, pg_partman, pg_trgm, pgvector.

10. Security

Multi-tenant isolation options, compared in Data Modeling Patterns: shared tables with a tenant_id and RLS, schema per tenant, or database per tenant.

11. Managed Services and Choosing

Choosing:

• Team skills and operational load matter more than benchmarks. A managed service you understand beats a self-managed cluster nobody wants to page for.
• Check the limits: maximum connections, storage size, IOPS ceilings, failover time, backup retention, extension support, and version upgrade policy.
• Watch cost drivers: provisioned IOPS, cross-zone and cross-region transfer, replicas, backup storage, and idle serverless minimums.
• Avoid deep lock-in where cheap: stay on standard SQL and extensions that exist elsewhere.

12. Questions and Answers

Q1. How would you scale a read-heavy PostgreSQL database? Fix queries and indexes, add caching, add read replicas with lag-aware routing, pool connections, and only then consider partitioning or sharding.

Q2. What is replication lag and how do you deal with it? Delay between the primary and a replica. Route fresh reads to the primary, track the write LSN, pin sessions, alert on lag, and avoid long conflicting queries on replicas.

Q3. Synchronous vs asynchronous replication? Synchronous waits for replica acknowledgement, giving zero data loss with higher latency. Asynchronous is fast but can lose recent commits on failover. Quorum or semi-sync is a compromise.

Q4. How does automatic failover work and what can go wrong? Failure detection, election of the most caught-up standby, fencing of the old primary, promotion and re-pointing clients. Risks: split brain, lost writes with async replication, flapping from bad timeouts.

Q5. Why use a connection pooler? Connections are heavyweight, and thousands of them thrash the server. A pooler multiplexes many clients over few server connections, with transaction pooling giving the best density.

Q6. Partitioning vs sharding? Partitioning splits a table inside one database for pruning and maintenance. Sharding splits data across multiple databases to scale writes and storage beyond one machine.

Q7. How do you choose a shard key? High cardinality, even distribution, and alignment with the dominant query so most requests touch one shard. tenant_id or user_id typically, not time.

Q8. How do you restore a database to just before an accidental delete? Point-in-time recovery: restore the latest base backup and replay archived WAL up to the moment before the mistake, into a new instance, then extract or swap.

Q9. How do you add a column and backfill on a billion-row table safely? Expand, migrate, contract: add a nullable column (metadata-only), dual write, backfill in small batches with pauses, verify, switch reads, then drop the old column later.

Q10. What migration mistake takes sites down? A schema change that waits behind a long transaction while holding or queueing for a strong lock, blocking all traffic behind it. Set lock_timeout, use concurrent or online variants, and run during low traffic.

Q11. What would you monitor on a production database? Latency and errors, connections, cache hit ratio, replication lag, locks and long transactions, disk and WAL growth, bloat, vacuum health, and backup success.

Q12. How do you isolate tenants in a multi-tenant SaaS database? Shared tables with tenant_id and row-level security for scale and cost efficiency, schema-per-tenant for stronger isolation with moderate scale, or database-per-tenant for strict isolation and easy per-tenant moves.

Next: NoSQL Databases.