When inside of a resolver for a subscription, allow `selectGraphQLResultFromTable` to use the read only connection string

[johnhaley81]

Feature description

When using subscriptions through GraphQL, you usually want to read from the main DB instance so you can get the freshest data available. However as an application scales this can put significant stress on the main DB.

Currently, PostGraphile allows you to provide a readOnlyConnection parameter that will send all query requests to it which is just fantastic! If the underlying query in a subscription could be also directed towards a read replica that would really help scale this out for larger applications.

I was thinking something along these lines:

• Before running the subscription resolver, we can wait until select pg_last_xact_replay_timestamp(); returns a timestamp that is >= when the subscription triggered.
• We'll use some sort of backoff for getting pg_last_xact_replay_timestamp (maybe exponential backoff?)
• If we've waited over some sort of threshold that the user has set, we'll stop querying the RR and just grab the data from the main DB

Motivating example

Currently in prod, we have clients that are being streamed updated data from the API via subscriptions. As the number of the clients grows more reads are coming from the main DB than the RR which is causing significant performance issues.

Supporting development

I [tick all that apply]:

• [ ] am interested in building this feature myself
• [x] am interested in collaborating on building this feature
• [x] am willing to help testing this feature before it's released
• [ ] am willing to write a test-driven test suite for this feature (before it exists)
• [x] am a Graphile sponsor ❤️
• [x] have an active support or consultancy contract with Graphile

[ben-pr-p]

What method of subscriptions are you using? Purely custom subscriptions, with a custom trigger (not simple subscriptions or using LISTEN/NOTIFY or anything?)

[johnhaley81]

Custom subscriptions as defined at https://www.graphile.org/postgraphile/subscriptions/#custom-subscriptions including the usage of the function (slightly modified) that fires off the pg_notify

[ben-pr-p]

I don't think you can do pg_notify from a primary to a replica

[johnhaley81]

I think you're correct. The notification comes from the primary but I was wondering if it would be at all possible for the code that's passing selectGraphQLResultFromTable into the callback function to perhaps have some logic to allow that function to point to the replica when it's called. Not for the actual subscription to be emitted from a replica.

[benjie]

graphile/postgraphile#1522 fixes an issue in PostGraphile that's preventing this from working currently; but we still need to consider something like pg_last_xact_replay_timestamp to avoid race conditions.

[benjie]

The current work-around for this is to delay announcing the event until you believe all replicas have caught up. We cannot solve this in pro using pg_last_xact_replay_timestamp without using transactions to ensure that the node that we request this data from and the node that we run the actual query through are the same node if you have readonlyConnectionString pointing at a pool of replicas. There's not really a suitable way right now of integrating this kind of requirement with PostGraphile - especially releasing and retrying the transaction if the pg_last_xact_replay_timestamp check fails.