Multi-Region PostgreSQL on GCP: Three Writable Regions with pglogical

This is the final post in our series on self-hosting Supabase on GCP. Throughout this series, we've covered:

1. Why we self-host
2. Infrastructure as Code with Pulumi
3. Cloud SQL Enterprise Plus
4. Security with VPN & Cloud Armor
5. Auto-scaling Supabase Studio

We first published this post in January as a plan: one primary database in London with read replicas in the US and Australia. We've since built it, and it came out different. Each region now has its own PostgreSQL node that takes reads and writes, and pglogical keeps all three in sync. This is the rewrite, covering what we run today and what we learned getting there.

The problem: latency across continents

Our client's application was growing internationally, and users in the US and Australia saw much higher latency than users in Europe, where the database lived (europe-west2, London). Typical round trips for a single query:

A page load with 5 to 10 queries meant 400ms+ of database time alone for Australian users.

Why we dropped the read-replica plan

Read replicas fix reads. They don't fix writes. With replicas, every write from Sydney still travels to London and back, and this app's users write all day: they log meals, and their training data syncs in from wearables.

Replicas also bring read-after-write bugs. Someone logs a meal, the next read hits a replica that hasn't caught up, and the meal is missing. We'd have needed routing rules for every one of those paths.

So we flipped it. The app runs in all three regions, each region has its own writable database, and the databases replicate to each other.

What we run

                ┌───────────────────────────┐
                │ EU · Cloud SQL            │
                │ europe-west2 (London)     │
                │ reads + writes            │
                └─────▲───────────────▲─────┘
                      │               │
                      ▼               ▼
┌───────────────────────────┐   ┌───────────────────────────┐
│ US · Compute Engine       │◄─►│ AU · Compute Engine       │
│ us-central1 (Iowa)        │   │ australia-southeast1      │
│ reads + writes            │   │ (Sydney) · reads + writes │
└───────────────────────────┘   └───────────────────────────┘

Every node publishes its changes to the other two
and subscribes to theirs.

Running the US and Australia nodes ourselves keeps them cheap: about $120 to $140 a month each with disk, against the $400 to $500 a month we'd estimated per Cloud SQL replica in January. The tradeoff is that we own their upgrades and backups.

The app follows the same shape. One Docker image runs as a managed instance group in each region, behind a single global HTTPS load balancer that sends each user to the closest healthy region. Inside each container, HAProxy points the app at its local database first and fails over to the other two:

Caches stay local too: each region has its own Valkey instance (Memorystore).

Replication with pglogical

On Cloud SQL, pglogical comes down to a few database flags. In our Pulumi code:

databaseFlags: [
  { name: "cloudsql.enable_pglogical", value: "on" },
  { name: "cloudsql.logical_decoding", value: "on" },
  { name: "track_commit_timestamp", value: "on" }, // needed for last_update_wins
  { name: "pglogical.conflict_resolution", value: "last_update_wins" },
  { name: "max_replication_slots", value: "10" },
  { name: "max_wal_senders", value: "10" },
  { name: "max_worker_processes", value: "16" },
],

The self-hosted nodes build a PostgreSQL 18 image with pglogical on first boot, set wal_level = logical, turn on track_commit_timestamp, use the same replication limits, and create their pglogical node automatically.

Then each node subscribes to the other two, six subscriptions in all:

-- On the EU node: subscribe to the US node
SELECT pglogical.create_subscription(
  subscription_name := 'sub_eu_from_us',
  provider_dsn      := 'host=<us-private-ip> port=5432 dbname=<db> user=<replication-role>',
  synchronize_data  := false,
  forward_origins   := '{}'
);

Two settings in there matter more than they look:

• forward_origins := '{}': a node only sends on changes that started on it. Without this, a row written in London would arrive in Iowa, get forwarded to Sydney, and come back around.
• synchronize_data := false: with Cloud SQL as the provider, true failed with a permission error on pg_replication_origin_session_setup and left the subscription stuck. Every subscription is created with false, so the nodes have to start from the same data.

One more Cloud SQL detail: the role that replicates needs the REPLICATION attribute, or subscribing fails with "insufficient privileges to start walsender".

Conflicts

Any two regions can update the same row at nearly the same moment, so pglogical needs a rule for which change wins. That's a per-node setting, pglogical.conflict_resolution. We chose last_update_wins: the change with the later commit timestamp wins (that's what track_commit_timestamp is for), and pglogical logs each conflict it resolves. Set it on every node, not just the managed one. pglogical's default is apply_remote, and two nodes that resolve the same conflict differently can drift apart.

Conflicts need two regions writing the same row at once. Most writes come from a user's own requests, and the load balancer keeps those in their nearest region.

Schema changes are the hard part

pglogical replicates rows, not DDL. A Prisma migration has to land on all three nodes, and while their schemas differ, replicated rows can fail to apply. There's a subtler trap too: pglogical decodes queued changes with the schema that existed when they were written, so a change waiting in the queue can refer to a column you just dropped.

We run every schema change through one script, triggered by GitHub Actions whenever prisma/schema.prisma changes on our development or production branch. In order, it:

1. Generates Prisma's SQL diff against each of the three nodes and stops if they don't match. It also stops on anything destructive (dropped columns, type changes, new unique constraints, SET NOT NULL) unless data loss was explicitly approved.
2. Refuses to run if any subscription is already down. A schema push is never a way to repair replication.
3. Freezes writes: sets the application's database login to NOLOGIN on every node and ends its client sessions, leaving pglogical's own workers alone.
4. Drains: waits until every replication slot has consumed a WAL position captured after the freeze.
5. Disables the subscriptions, applies the DDL on all three nodes and refreshes the replication sets.
6. Re-enables the subscriptions, waits until all six report replicating, and drains again.
7. Only then restores the application login.

If a step fails once the schema change has started, the script leaves writes frozen instead of letting the app write into mismatched schemas. Pausing writes is better than three databases quietly drifting apart.

The mental model that keeps us out of trouble: a subscription is a cursor into the provider's WAL. Disabling it keeps the cursor and the queued changes. Dropping it throws them away, and recreating it won't bring them back. So during maintenance we disable subscriptions, and never drop them.

Side effects belong on one node

Database triggers that talk to the outside world need care. We send webhooks from triggers with pg_net, and if every node sent them, the same change could go out more than once. So one node, Iowa, is the webhook gateway: pg_net runs only there, the webhook triggers live only there, and pg_net's queue tables are unlogged, so pglogical never copies them.

Connection pooling

The app connects through a pooler on every node: Cloud SQL's managed connection pooling in London and a PgBouncer sidecar on the self-hosted nodes, both in transaction mode on port 6432, with 5432 kept for direct connections. Prisma uses the pooled URL with pgbouncer=true and a direct URL for migrations:

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL") // pooled, port 6432
  directUrl = env("DIRECT_URL")   // direct, port 5432
}

Locking it down

None of the database nodes has a public IP. They talk over private networking, authenticate with scram-sha-256, and we reach them through our VPN and a bastion (more on that in our VPN post).

Watching it

Each node reports on its subscriptions:

SELECT subscription_name, status, provider_node
FROM pglogical.show_subscription_status();

We also watch the replication slots on each provider, because an inactive slot holds on to WAL and can fill the disk:

SELECT slot_name, active, confirmed_flush_lsn
FROM pg_replication_slots;

A Grafana dashboard shows all three nodes side by side.

What we'd tell anyone doing this

1. Decide how you'll handle writes first. Replicas only help reads.
2. Build the schema-change process before you go live. It's most of the work.
3. Never forward what you didn't write. forward_origins := '{}' prevents loops.
4. Keep side effects on one node. Webhooks, emails, anything that leaves the database.
5. Disable subscriptions, don't drop them. A dropped subscription loses its queue.

Multi-master PostgreSQL has a reputation for being more trouble than it's worth, and for a lot of apps it is. For an app whose users write from three continents all day, it's been worth it.

Building a global application and need help with multi-region architecture? Get in touch. We love solving these kinds of infrastructure challenges.