0Pricing
SQL Academy · Lesson

Logical Replication for Sharding

Use PUBLICATION/SUBSCRIPTION logical replication to move tables between clusters and support major-version upgrades.

Logical Replication for Sharding is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Logical vs Physical Replication

Physical replicates BYTES of WAL — whole cluster, same version. Logical replicates ROWS — selected tables, possibly across PG versions:

  • Subscribe to specific tables
  • Replicate from PG 14 → PG 16 (upgrades!)
  • Two-way / multi-master with care
  • Different schemas allowed

Setting Up: Publisher

Mark the primary as a publisher:

-- postgresql.conf
wal_level = logical

-- Create a publication:
CREATE PUBLICATION pub_orders FOR TABLE orders, order_items;

-- Or all tables in the database:
CREATE PUBLICATION pub_all FOR ALL TABLES;

Setting Up: Subscriber

On the target database:

CREATE SUBSCRIPTION sub_orders
  CONNECTION 'host=primary port=5432 user=repl dbname=mydb'
  PUBLICATION pub_orders;

Initial Sync

The subscription performs an initial copy of all matching tables, then streams changes. The target schema must match the source.

Use Case: Major Version Upgrade

Logical replication is the modern way to upgrade Postgres with minimal downtime:

  1. Build PG16 cluster
  2. Logical-replicate from PG14 to PG16
  3. Cut over apps when PG16 has caught up
  4. Shut down PG14

Use Case: Splitting a Database

To break a giant DB into smaller ones:

  1. Publish only the tables a new service needs
  2. Subscribe in the new DB
  3. Migrate writes after the catch-up

Use Case: Heterogeneous Replicas

Replicate to a read-replica with different indexes, or to a data-warehouse Postgres with extra reporting columns.

Conflicts

Logical replication doesn't auto-resolve conflicts. If the same row is updated on both sides, the subscriber stops with an error. Avoid by:

  • Writing to only one side
  • Using REPLICA IDENTITY FULL for better diagnostics
  • Resolving conflicts manually and restarting the subscription

Schema Changes

DDL is NOT replicated. Apply schema changes manually on both sides — typically subscriber first, then publisher. Or use the pglogical extension which can replicate DDL.

Partial Replication

Modern PG allows row filters and column lists per publication:

CREATE PUBLICATION us_only FOR TABLE customers
  WHERE (country = 'US');

CREATE PUBLICATION compact FOR TABLE users (id, email);

Sharding with Logical Replication

Move tenants to a dedicated shard:

  1. Publication on source filtered to one tenant
  2. Subscribe from the destination
  3. Switch app to new shard
  4. Drop tenant data on source

Performance and Lag

Logical replication is single-threaded per subscription (mostly). High-volume sources may benefit from multiple publications/subscriptions or third-party tools like pglogical / Debezium.

Recap

Logical replication enables row-level streaming.

  • Cross-version, partial-table
  • Major upgrades with minimal downtime
  • Conflict resolution is YOUR problem
  • DDL is not replicated

Quick Check

You need to upgrade from PostgreSQL 14 to 16 with minimal downtime. Which replication helps?

Frequently asked questions

Is the “Logical Replication for Sharding” lesson free?

Yes — the full text of “Logical Replication for Sharding” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Logical Replication for Sharding”?

Use PUBLICATION/SUBSCRIPTION logical replication to move tables between clusters and support major-version upgrades. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Logical Replication for Sharding” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Streaming Replication and WAL
  2. Logical Replication for Sharding
  3. Failover and Leader Election (Patroni, Stolon)
  4. Read Replicas and Connection Routing
← Back to SQL Academy