Practice: Replication and partitioning
Generated by Claude through GigAI (karthik446/gigai-learning-center 4c0cb514) on 2026-10-13.
Main tool: PostgreSQL · PostgreSQL exposes every replication mode in this lesson as plain configuration you can run as several instances on one laptop, so you see each durability and sharding tradeoff directly instead of behind a managed layer. · Alternatives: Patroni automates PostgreSQL failover and leader election, PgBouncer pools and reroutes connections but replicates nothing, Citus adds distributed tables and shard rebalancing as a PostgreSQL extension, Vitess does the same for MySQL, Apache Cassandra is leaderless with tunable consistency and hash partitioning built in, CockroachDB shards by range and replicates by consensus automatically, and Amazon Aurora replicates at a shared storage layer as a paid cloud service.
Every step links into the tool's own documentation; links were checked on 2026-10-13 · version 1
Setup (one time)
1. Install PostgreSQL 18 with your package installer, or pull the official container image, and start one instance that will act as the primary.
Every later step needs a running primary to replicate from. The chapter overview shows which replication features ship with the server, so you know nothing extra is needed.
Explained in: PostgreSQL: Documentation: 18: Chapter 26. High Availability, Load Balancing, and Replication (www.postgresql.org), checked 2026-10-13
Done when: You can connect with psql and a version query reports major version 18.
2. Prepare two more empty instances on the same laptop, each with its own data directory and port, or as two more containers on one network.
Replication and sharding only make sense with more than one server. Separate ports or containers stand in for separate machines.
Explained in: PostgreSQL: Documentation: 18: 26.2. Log-Shipping Standby Servers (www.postgresql.org), checked 2026-10-13
Done when: Three instances are listed as running, each reachable on its own port.
3. Set wal_level to logical on the primary, create a role with the replication attribute, allow it in the host based authentication file, and restart.
Logical replication needs extra detail in the write-ahead log, and both physical and logical replication connect through a replication role.
Explained in: PostgreSQL: Documentation: 18: Chapter 29. Logical Replication (www.postgresql.org), checked 2026-10-13
Done when: A show command for wal_level returns logical and the new role can log in from the other instances.
Steps
1. Create an orders table with a primary key and a tenant id column on the primary, load a few thousand rows, and create a publication for that table.
A publication is the unit that decides which tables and which operations leave a node. It is the first object in the logical replication model.
Explained in: PostgreSQL: Documentation: 18: 29.1. Publication (www.postgresql.org), checked 2026-10-13
Done when: The publication tables catalog view lists the orders table under your publication.
2. Create the same table definition on the second instance and create a subscription that points at the publication on the primary.
A subscription is the receiving side: it creates a replication slot on the publisher, copies the existing rows, then applies changes. This completes a working logical replication pair.
Explained in: PostgreSQL: Documentation: 18: 29.2. Subscription (www.postgresql.org), checked 2026-10-13
Done when: The row count on the second instance matches the primary, and a row inserted on the primary appears there within a second.
3. Query the activity view on both nodes to find the walsender process and the apply worker, and query the replication slots view to see the slot the subscription created.
Knowing which process decodes the log, which one applies it, and how the initial snapshot hands over to streaming is the model you debug with later.
Explained in: PostgreSQL: Documentation: 18: 29.9. Architecture (www.postgresql.org), checked 2026-10-13
Done when: You can name the walsender on the publisher, the apply worker on the subscriber, and the active logical slot that links them.
4. Create two publications with row filters on the tenant id shard key, one per half of the key space, and subscribe the second and third instances to one each, first with a range split and then with a modulo split.
Row filters let you route rows by key to different nodes, which is a small hand-built version of sharding. Trying range and hash-style splits shows how the shard key choice decides data balance and which queries stay on one node.
Explained in: PostgreSQL: Documentation: 18: 29.4. Row Filters (www.postgresql.org), checked 2026-10-13
Done when: Each shard instance holds only the rows for its key range, the two counts add up to the primary total, and you can compare how evenly each strategy split the data.
5. Write a row directly on a shard instance with a primary key that the primary then inserts, and watch replication stop on the conflict, then resolve it.
Subscribers are writable, so stray writes on a shard break the apply worker. Recognising and clearing a conflict is a routine operating task on sharded data.
Explained in: PostgreSQL: Documentation: 18: 29.7. Conflicts (www.postgresql.org), checked 2026-10-13
Done when: The subscriber log shows the conflict, and after you remove the offending row or skip the transaction new changes flow again.
6. Run a migration rehearsal: add a column and a sequence-backed id on the primary, check what does and does not reach the shards, then write the cutover checklist that the restrictions demand.
Moving live data onto shards by logical replication only works if you handle what it does not carry, such as schema changes and sequence values. Finding these gaps in a rehearsal is cheaper than at cutover.
Explained in: PostgreSQL: Documentation: 18: 29.8. Restrictions (www.postgresql.org), checked 2026-10-13
Done when: You have seen apply fail or lag after the schema change, fixed it by applying the change on the shard, and listed the manual steps for a real cutover.
7. Query the subscription statistics view on each shard and the replication statistics view on the primary while a bulk load runs.
Lag and apply errors are the signals that tell you whether a shard is safe to read from or cut over to.
Explained in: PostgreSQL: Documentation: 18: 29.10. Monitoring (www.postgresql.org), checked 2026-10-13
Done when: You can state the current lag for each shard and see the error counters that rose during the earlier conflict.
8. Create a physical standby from a base backup of the primary and start it with streaming replication, and write the whole sequence as a script or a compose file pinned to one PostgreSQL version.
Streaming replication ships the write-ahead log continuously to an identical copy, which is the base of availability and read scaling. Scripting it against a pinned version means you or a CI job can rebuild the same pair from nothing.
Explained in: PostgreSQL: Documentation: 18: 26.2. Log-Shipping Standby Servers (www.postgresql.org), checked 2026-10-13
Done when: Deleting the standby and rerunning the script brings back a standby that reports it is in recovery and shows as streaming in the replication view on the primary.
9. Configure synchronous_standby_names on the primary to name the standby, then compare commit behaviour with the standby running and stopped, and again with the setting cleared.
Synchronous commit waits for the standby and so loses no confirmed transaction, but a commit blocks when the standby is gone. Asynchronous commit stays fast and available but can lose the last moments of writes.
Explained in: PostgreSQL: Documentation: 18: 26.2. Log-Shipping Standby Servers (www.postgresql.org), checked 2026-10-13
Done when: A commit hangs while the synchronous standby is stopped and returns at once after you restart it or switch back to asynchronous.
10. Run a failover drill: stop the primary, promote the standby, write to it, and decide how the old primary rejoins.
Failover is the reason the standby exists, and PostgreSQL itself does not detect failure or fence the old primary. Doing it by hand shows what a tool like Patroni automates.
Explained in: PostgreSQL: Documentation: 18: 26.2. Log-Shipping Standby Servers (www.postgresql.org), checked 2026-10-13
Done when: The promoted server accepts writes and reports it is no longer in recovery, and the old primary stays down until rebuilt as a standby.
11. Enable failover on the shard subscriptions and slot synchronisation on the standby, rebuild the pair, repeat the failover drill, and repoint the subscriptions at the promoted server.
In production the publisher itself must survive a node loss, so logical slots have to exist on the standby before it is promoted. This combines physical and logical replication into the topology a real sharded deployment runs.
Explained in: PostgreSQL: Documentation: 18: 29.3. Logical Replication Failover (www.postgresql.org), checked 2026-10-13
Done when: After promotion the shards keep receiving new rows from the new primary with no resync and no missing changes.