Skip to content

Supabase and PostgresArchitecture and comparisonsreference

Read/write split for Postgres at the Cloudflare edge

A Cloudflare Worker that splits reads and writes across a Supabase primary and its read replicas, under your control on your own domain. It mirrors the stock Data API load balancer’s routing, rewrites GraphQL reads so they reach replicas, and adds read-your-writes, health-checked replica failover, and an append-only ingest spool - each opt-in.

Every claim below comes from the pg-router source, which is a tested Cloudflare Worker published under MIT on a private repository (no public link). Claims from the Cloudflare and Supabase docs carry footnotes.

TL;DR:

  • Read/write split mirrors the stock Data API LB: GET/HEAD on /rest/v1/* is replica-eligible; POST/PUT/PATCH/DELETE and everything else goes to the primary. Behaviour is predictable and reversible: remove pg-router and clients still work against the primary or the stock LB.
  • GraphQL reads are rewritten to GET /rest/v1/rpc/<fn> so they balance to replicas. Classification is conservative: mutations, multi-op documents without operationName, and non-JSON bodies stay a POST to the primary - a possible mutation is never sent to a read-only replica.
  • Health-checked replica fan-out with failover: a passive circuit breaker ejects a replica after repeated 5xx/network failures; within a request, the router retries healthy replicas, then degrades to the primary (x-pg-router-degraded: 1). State is isolate-local - a courtesy model, not a global consensus.
  • Read-your-writes via a WriteTracker Durable Object: after a user write (identified by the JWT sub claim), that user’s reads pin to the primary for a configurable window. Anon/service keys have no sub, so RYW does not apply to them.
  • Promotion is execute-only: the router flips routing on an authenticated PUT /__admin/routing with acknowledgeFencing: true, after DB-layer promotion and fencing. It never decides a promotion from its own health detection - that is split-brain by false-positive.
  • The only edge-write path is an append-only, idempotency-keyed Queues spool. General writes are never buffered in D1 or KV: that path was rejected because it cannot validate against constraints, RLS, or triggers, and cannot produce real RETURNING values.

SupabaseCloudflare resourcesClientCloudflare(TLS / WAF / DDoS)HTTPSQueues(ingest spool)POST /spool/v1/*(opt-in)pg-router WorkerPrimaryPOST / PUT / DELETEGraphQL mutationsauth / storage / rpcReplica 1GET /rest/v1/*(round-robin)Replica 2WriteTracker DO(read-your-writes)markWrite /wroteWithinreplay(at-least-once)RoutingState DO(promotion control)

The pattern is Client -> Cloudflare -> pg-router Worker -> Supabase primary + replicas: the Worker owns the read path, the primary owns writes, and the replica set is under your control. Pooled Postgres connections (Supavisor :5432/:6543) and Realtime (WebSocket) bypass the Worker entirely - it only sees the HTTP Data API (/rest, /graphql).

You wantPathWhy
Standard Data API CRUD with RLSPoint GET/HEAD /rest/v1/* at replicas; everything else at the primarymirrors the stock LB exactly; predictable and reversible
GraphQL reads to reach replicasRewrite to GET /rest/v1/rpc/graphql_getGraphQL is a POST by protocol, so the stock LB never balances it
A user to see their own writes immediatelyEnable read-your-writes (READ_YOUR_WRITES_MS)pins that user’s reads to the primary for a window after their write
Reads to survive a replica outageCircuit breaker + degrade-to-primaryejects a dead replica and retries healthy ones; all replicas failed = short-circuit to primary
Safe writes during a primary blipEnable the ingest spool (SPOOL_ENABLED)append-only, idempotency-keyed inserts through Cloudflare Queues; queues buffer until the primary returns
Promotion after DB-layer failoverPUT /__admin/routing with acknowledgeFencing: truethe router executes the flip; a human or consensus orchestrator decides

The router mirrors the stock Data API load balancer exactly1:

  • GET/HEAD/OPTIONS on /rest/v1/* -> replica-eligible (round-robin).
  • POST/PUT/PATCH/DELETE on /rest/v1/* -> primary.
  • /auth/*, /storage/*, /functions/*, /graphql/v1 -> primary (by default; GraphQL gets its own rewrite path).
  • Any request already carrying x-sb-lb: 1 -> primary (loop guard: prevents a downstream LB from re-balancing back to a replica infinitely).

Every proxied response carries x-pg-router-via: primary|replica so the routing split is measurable at the edge.

GraphQL is a POST /graphql/v1 by protocol, so the stock LB sends every GraphQL request to the primary. The router rewrites a read query into a load-balanceable form:2

POST /graphql/v1
{ "query": "query { items { id name } }" }
->
GET /rest/v1/rpc/graphql_get?query=query+%7B+items+%7B+id+name+%7D+%7D

Classification is conservative, so a possible mutation is never sent to a replica:

  • Read: only query operations, no mutation or subscription fields.
  • Ambiguous/unknown: multi-operation documents without an operationName, non-JSON bodies, and anything the classifier cannot parse -> POST to the primary.
  • URL ceiling: GET has a length limit (~16 KB). A rewritten URL that exceeds GRAPHQL_MAX_GET_URL (default 6,000) falls back to a POST on the primary.
  • RPC gating: the rewrite depends on a graphql_get PostgREST RPC. On an older PostgREST without it, GRAPHQL_REPLICA_READS=false disables the replica path entirely and GraphQL stays on the primary. Even with it on, a PGRST202 (function not exposed) from the replica triggers a transparent fallback to a POST on the primary.

The router uses a passive per-replica circuit breaker (health.ts):

  • After CIRCUIT_FAILURE_THRESHOLD (default 3) consecutive 5xx or network failures, the replica is ejected for CIRCUIT_OPEN_MS (default 10 s), after which one half-open trial is allowed.
  • Within a single request, the router retries up to MAX_REPLICA_TRIES (default 2) healthy replicas.
  • If no replica succeeds, the read degrades to the primary and the response is tagged x-pg-router-degraded: 1.

State is isolate-local: each edge isolate learns independently, with no cross-isolate coordination. This is a courtesy health model - it matches the isolate-local round-robin and adds no I/O to the hot path. Lag-based ejection (a replica that is up but far behind) needs an active probe and is deferred to a future phase.


Async replicas mean a client that writes then immediately reads may read stale data. When READ_YOUR_WRITES_MS is set (default 0 = off), the router pins a user’s reads to the primary for that window after their write:

  • Identity is the JWT sub claim, parsed (not verified - upstream verifies) from Authorization: Bearer. Anon and service keys have no sub, so RYW does not apply to them (it would otherwise pin all shared-key traffic after any write).
  • State is a single lastWriteAt timestamp in a WriteTracker Durable Object keyed by sub:<id>. Because the write colo and a later read colo may differ, the state lives in a DO so all isolates see the same timestamp - in-memory only, so DO eviction simply degrades to replica reads (safe).
  • Cost: one DO round-trip on reads (to check wroteWithin) and a fire-and-forget ctx.waitUntil(markWrite()) on writes.

The only edge-write path is POST /spool/v1/<table>: with an Idempotency-Key header it enqueues the row to a Cloudflare Queue3 and returns 202 Accepted with no row and no RETURNING - the semantics are explicitly async. A consumer Worker replays each message to POST /rest/v1/<table> on the primary with Prefer: resolution=ignore-duplicates.

This:

  • tides over a primary blip for append-only inserts (the queue buffers; the consumer retries with backoff until the primary returns, up to the queue retention window);
  • is at-least-once, so the target table needs a unique constraint on the idempotency-key column;
  • creates no read-your-writes or RETURNING contract - callers must not expect the row back.

Only whitelisted tables (SPOOL_TABLES) are accepted; everything else is 403. The spool is off by default; the Queue binding is created deliberately.


pg-router can execute a promotion but never decide one. The decision and fencing happen at the DB layer first (a consensus orchestrator, a promote API, or a human); the edge routing flip is the last step.

When CONTROL_PLANE_ENABLED, the effective primary/replica set is held in a durable RoutingState DO (seeded from env on first use) and read through a short-TTL isolate cache (ROUTING_CACHE_TTL_MS, default 5 s). Authenticated endpoints (X-Admin-Key):

GET /__admin/routing -> current routing + per-replica circuit snapshot
PUT /__admin/routing -> flip routing to a newly-promoted primary
GET /__admin/metrics -> per-origin circuit counters (isolate-local)

The PUT is refused without "acknowledgeFencing": true, because flipping edge routing does not fence direct pooler/psql connections. Other isolates converge on the new routing within the cache TTL.


Rejected: edge write WAL (D1/KV as a durable log)

Section titled “Rejected: edge write WAL (D1/KV as a durable log)”

Buffering general writes at the edge (D1 or KV as a “WAL”) to keep writes flowing during a primary outage was considered and rejected. For a SQL/PostgREST/regulated workload it breaks correctness:

  • You would ACK writes you cannot validate against unique/FK/check constraints, RLS, or triggers - a rejection surfaces minutes later with no client to receive it (phantom success).
  • You cannot produce real RETURNING values (server-generated serial/uuid/timestamps); clients that use the returned id immediately break.
  • You cannot offer read-your-writes without reimplementing a query engine at the edge.
  • KV is last-write-wins with ~60 s propagation; a DO is single-instance at roughly 1,000 req/s and evictable. Neither gives the globally-ordered, fsync’d, replicated durability of the Postgres WAL you would be replacing - using either in place of the WAL trades durability for availability.

The correct answer to “keep writes flowing when the primary dies” is consensus-based promotion (synchronous replica -> primary). The append-only Queues spool above is the only edge-write carve-out, and only because it makes no durability/ordering/RETURNING promises it cannot keep.


Reads need to reachreplicas, writes theprimary?GraphQL reads alsoneed replicas?Deploy pg-router withPRIMARY_ORIGIN +REPLICA_ORIGINSyesUsers need to seetheir own writesimmediately?Enable GraphQL readrewrite (on by default)yesNeed writes to survivea primary blip?Set READ_YOUR_WRITES_MS> 0yesNeed to promote afterDB-layer failover?Enable the ingest spool(append-only)yesEnable the control plane(execute-only; neverauto-promote)yes

The same choices as a list:

  1. Reads to replicas, writes to the primary - deploy pg-router with PRIMARY_ORIGIN and a comma-separated REPLICA_ORIGINS. The circuit breaker is on by default. Leave REPLICA_ORIGINS empty and set LB_ORIGIN to defer to the stock Data API LB.
  2. GraphQL reads also need replicas - the rewrite is on by default. Create the graphql_get RPC on PostgREST and reload its schema cache. If the RPC cannot be deployed, set GRAPHQL_REPLICA_READS=false.
  3. Users need to see their own writes immediately - set READ_YOUR_WRITES_MS to the desired window. Anon/service keys are unaffected; only user JWTs with a sub claim are pinned.
  4. Need writes to survive a primary blip - enable the ingest spool (SPOOL_ENABLED, SPOOL_TABLES, create the queue). Only append-only, idempotency-keyed inserts qualify; general writes do not go through this path.
  5. Need to promote after DB-layer failover - enable the control plane, set ADMIN_KEY, and PUT /__admin/routing with acknowledgeFencing: true after DB-layer promotion and fencing. Never let edge detection trigger it automatically.

Claims below are marked by how they were checked: tested (an automated test in the repo exercises the behaviour) or design-only (the code implements the design, the decision is documented, but the behaviour has not been validated end-to-end against a live project with real replicas).

ClaimHow it was checkedSource
GET/HEAD /rest/v1/* routes to replicas; POST/PUT/DELETE go to primaryTested: test/worker.test.ts mocks upstreams and asserts routing by method and pathsrc/router.ts:8-25
GraphQL read query is rewritten to GET /rest/v1/rpc/<fn> and balanced to replicasTested: test/graphql.test.ts classifies operations and rewrites to GET URLssrc/graphql.ts:1-120
GraphQL mutation or ambiguous document stays a POST to primaryTested: same graphql test file, mutation/multi-op/unknown casessrc/graphql.ts:25-60
Circuit breaker ejects a replica after CIRCUIT_FAILURE_THRESHOLD consecutive 5xx/network failuresTested: test/health.test.ts feeds failure sequences and asserts circuit state transitionssrc/health.ts:51-86
Within a request, the router retries MAX_REPLICA_TRIES healthy replicas, then degrades to primaryTested: test/worker.test.ts simulates replica failures and asserts x-pg-router-degraded: 1src/router.ts:60-90
Read-your-writes pins a user’s reads to the primary after a writeTested: test/worker.test.ts writes as a JWT user, then reads as the same user (primary) and a different user (replica)src/write-tracker.ts:1-40, src/jwt.ts:1-30
Anon/service keys are unaffected by RYWTested: same test, anon write then read -> replicasrc/write-tracker.ts:25-30
Ingest spool enqueues a whitelisted table and returns 202; rejects unknown tables with 403Tested: test/spool.test.ts POSTs to /spool/v1/<table> and asserts response + queue message shapesrc/spool/producer.ts:1-50
Spool consumer replays to primary with Prefer: resolution=ignore-duplicates; acks on 2xx, retries on 5xxTested: same test file, mocks primary responses and asserts ack/retrysrc/spool/consumer.ts:1-80
PUT /__admin/routing is refused without acknowledgeFencing: trueTested: test/routing-state.test.ts sends PUT with and without the fieldsrc/admin.ts:40-55
PUT /__admin/routing flips routing and isolates converge within ROUTING_CACHE_TTL_MSTested: same test file, reads routing after a flip from a “different” isolatesrc/routing-state.ts:1-50, src/routing-cache.ts:1-40
Circuit breaker is isolate-local (no cross-isolate coordination)Design-only: UpstreamHealth is a plain class, no DO, no KV, no cross-isolate channelsrc/health.ts:1-20
Active lag-based replica ejection (probe-based)Design-only: deferred to Phase 4, not implementeddocs/PLAN.md:80-82
Edge-triggered auto-promotion from health detectionDesign-only: explicitly rejected by D8, never implemented, guarded by acknowledgeFencing gatedocs/DESIGN.md:115-135
Edge write buffer over D1/KV for general writesDesign-only: rejected in DESIGN.md, code path does not existdocs/DESIGN.md:137-155

  • Cloudflare Workers + Supabase: an architecture reference - every path a Worker uses to reach Supabase; pg-router is a deployment of the routing patterns that doc describes
  • Supabase read replicas - which requests a replica can serve through the stock load balancer, what it costs, and where each request lands; pg-router extends that read path with health-checked fan-out, GraphQL rewrite and read-your-writes
  1. Supabase, “Read replicas,” Supabase Docs. https://supabase.com/docs/guides/platform/read-replicas ↩

  2. Supabase, “Database Functions,” Supabase Docs. https://supabase.com/docs/guides/database/functions ↩

  3. Cloudflare, “Queues,” Cloudflare Docs. https://developers.cloudflare.com/queues/ ↩