I’m planning on having a read counter on my blog posts where once I’ve determined someone’s read a post I increment a number for the post in the DB. My concern is that if I have a multi-region deployment of my postgres DB, that could lead to conflicts if multiple people finish reading the same post at the same time. Does Fly handle this so it “just works” or should I be worried?
If you send something like this to your primary DB, it just works:
UPDATE posts SET read_count = read_count + 1
We just run vanilla pg so all the transactions still work as you’d expect.
The trick is going to be doing reads from the local replica and writes to the primary instance. If you can issue the write without blocking the response it should be very fast for users!
1 Like
So if I do the write to the primary but then a read from a non-primary then will the user see the wrong count because it has not been propagated yet?
Yes. Depending on how far you are from the primary database, the read replica can take a few hundred milliseconds to catch up.
The normal technique is to write to the primary and then run the query over the same connection. Rails, for example, does this transparently when it detects a write.
I don’t think you’re going to want to wait for the entire write roundtrip before you show someone a count though.
Gotcha. I’ll change to try it out and we’ll see what prisma does for me.
How are you doing read replicas in Prisma out of curiosity? This is something we want to build a library for, it seems like there’s a reasonable read replica setup possible: Support for Read Replicas (Postgres, MySQL) or setting Client in read-only mode · Issue #172 · prisma/prisma · GitHub
1 Like
I haven’t done multi-regional deployments of postgres yet so I’m not sure how it will work. I just want to make sure I don’t paint myself into a corner.
1 Like