[Load balancer] Sticky reads

[levkk]

Stick to the primary for a read from a table that just received a write from the same connection. This can help with the following:

user = User.create email: "test@test.com"
user.reload

[ats1999]

Curious about how it can be achieved? Read your own write consistency is scoped to user session, pgdog will have no idea about user.

[mediprtl]

We hit this on the replica side rather than the primary. Our consumers read right after a write whose commit LSN they already know (it rides on an async message), but PgDog routes reads per transaction with no replica affinity, so a read can land on a replica that hasn't replayed the write yet. (#823 is the same problem from a real user, missing ~30% of reads; the only fix today is routing whole tables to the primary, which gives up replica offload.)

We prototyped a per-read freshness floor that keeps reads on replicas. Full implementation + tests here: mediprtl#4 (design notes in mediprtl#3).

• pgdog.min_lsn param (startup options=-c or SET pgdog.min_lsn = 'X/Y'), intercepted like pgdog.role. The client passes the LSN it must observe.
• The load balancer routes the read only to a replica whose replayed LSN (already tracked by the LSN monitor) is >= min_lsn; unmet errors recoverably (client retries), with opt-in primary fallback.

Would you welcome this upstream?

[levkk]

Have you guys tested your patch in prod yet and did it have a meaningful impact? A few concerns:

• This leaks the LSN abstraction to the client: clients that don't know how to poll for LSN can't use it
• Read after write will require a primary read 9 times out of 10. Replication delay in production systems is typically sub-second, but even then, that's enough to cause a race. So, while you're polling for the LSN (1-2ms roundtrip), the replicas have already caught up...usually at least. So you should be able to just get away with /* pgdog_role: primary */ SELECT [...] for those specific read after write queries
• The lsn monitor can run very frequently, but the typical cadence is once per second, to avoid spamming the databases. We could run it more frequently (it's just a config chage), but either way, it won't update the LSN often enough for a significant amount of query traffic to hit the replica where it matters (the <1s time it takes for the replica to catch up). The clients that read after write might as well just put a sleep(1s) in their code and that might have the same effect

I think the right solution tracks row changes for each table and listens on the replication stream coming from each replica to figure out when those rows actually synced. Then, each query it receives is checked against the row change map - if it's still there, send the query to the primary, otherwise send it to a replica. This is pretty complex to build, but would "just work". But also maybe a hack is good enough and we could abstract it by creating a plugin for each Postgres driver out there (Node, ActiveRecord, etc.) and hide that we poll for the LSN periodically. Food for thought.

[alexnick-glow]

Thanks for the detailed reply.

One clarification reframes most of this: in our case the client doesn't poll for the LSN - it's stamped on the async message that triggers the read, so the consumer already has the floor before it ever talks to PgDog. No roundtrip to lose the race in.

On your points:

• Leaking the LSN to the client: it's opt-in - no param, today's behavior. And the ecosystem is heading this way: PG 19 will ship WAIT FOR LSN, making LSN a first-class client primitive. Possible synergy too: instead of erroring when a replica is behind, PgDog could WAIT FOR LSN on it with a bounded timeout.
• "Just route to the primary": that's the [Plugins] Add plugin for primary-only tables #823 workaround - it gives up replica offload for exactly the high-read async consumers where offload matters. With min_lsn, a caught-up replica still serves the read; only the not-yet-replayed case is special-cased.
• Monitor cadence / "might as well sleep(1s)": cadence costs offload opportunity, not correctness - a stale view errs conservative, never stale. A sleep can't promise that, and it assumes sub-second lag. Ours is usually over a second and swings with cluster load, so a hard-coded sleep is a guess; an LSN floor adapts.

Your row-change-tracking approach is the ideal transparent fix, and the per-driver plugin idea is great - both would sit on top of this same primitive. We see min_lsn as a small opt-in complement, not a competitor.

We'll test it on our sandbox shortly and report back with numbers.

[alexnick-glow]

Quick update, @levkk: we tested the min_lsn approach on both our sandbox and production, and the results were good - reads land on caught-up replicas as expected, and the conservative fallback held with no stale reads observed.

Given that, would you be open to merging this upstream? If there's anything that would help on your end - more numbers, a writeup of the prod setup, API/default tweaks, or extra tests - just let us know and we're happy to put in the work to get it into mergeable shape.

[levkk]

Could you submit a PR? We can take a look.

[alexnick-glow]

#1156