pg_graphql and read replicas

[artema]

Is it planned to be supported, at least for queries? This blog post mentions that it is available, but calling /graphql/v1 on a replica gives an error.

[DYNOSuprovo]

The error when calling /graphql/v1 directly against a read replica stems from how pg_graphql manages its internal schema cache:

1. Root Cause: Internal Schema Cache Transactions

pg_graphql resolves queries in-database via graphql.resolve(...). When initializing or refreshing its schema reflection, pg_graphql writes metadata and cache state into its internal schema tables (under the graphql schema namespace).

Because Supabase read replicas operate under PostgreSQL physical streaming replication with default_transaction_read_only = on, any implicit schema synchronization or cache write attempt immediately triggers:

ERROR: cannot execute INSERT / UPDATE in a read-only transaction

2. Current Architecture & Roadmap

The Supabase database and pg_graphql teams have been working on decoupling schema cache materialization from query execution (specifically serving schema reflection purely in-memory on standby nodes rather than writing to disk). Until the pure in-memory cache mode is fully rolled out across all hosted read-replica endpoints, /graphql/v1 is constrained to the primary database instance where write transactions are permitted.

3. Recommended Workarounds for Replica Offloading

If you need to offload read-heavy query traffic to replicas today:

• PostgREST (/rest/v1/): PostgREST fully supports read replicas. You can direct read traffic using the sb-lb-routing-mode: read-replica header or by configuring the replica URL.
• Direct SQL / ORM via Read Replica Pooler: Point read queries directly to your read replica connection pooler string (port 6543 / 5432) from your application backend.