4 ms·

Looks interesting. How would you approach write and multiple read replicas with this setup? My holy grail would be allowing users to get data from read replica,

Looks interesting. How would you approach write and multiple read replicas with this setup? My holy grail would be allowing users to get data from read replica, but also writing to the read replica which then forwards those writes to the primary write DB

slt2021 2y ago
there is no need in read replica, because every micro sqlite partition has very low workload (single digit queries per second)
hinkley 2y ago
One of the classic solutions to this was to put the data behind a cluster and route by verb. All the GET requests go to the replicas and everything else goes to the writeable instance.
- datadrivenangel 2y ago
  Suffers from dirty reads: Write to the primary, get confirmation of write-success, reload page, user sees 'stale' data from the read-replica and thinks you lost their data.
  - yurishimo 2y ago
    A temporary header that forwards the next X requests to the main write database is a possible solution. Requires a bit more ooomfph to handle the traffic and probably isn’t good for a write heavy app, but for a generally read heavy CRUD app, it’s a decent compromise.
    - hinkley 2y ago
      I've also seen a writeup of someone who used clocks. The write is transaction 1234, so the client has to read from a replica that is caught up to 1234 in the WAL, or get bounced to another that does. And of course there's the Phoenix LiveView solution where the write contains the new data, gets injected back into the page that contained the edit link. That doesn't quite cover the save-reload loop that some users with well-earned trust issues follow.
NathanFlurry 2y ago
I think the other comments have the application-level approaches covered. However, I suspect the infrastructure will provide this natively as it matures: - Cloudflare will probably eventually add read replicas for Durable Objects. They're already rolling it out for D1 (their other SQLite database offering). [1] - Turso has their own story for read replicas. [2] [1] https://blog.cloudflare.com/building-d1-a-global-database/#sneak-preview-bringing-read-replication-to-d1-with-sessions https://blog.cloudflare.com/building-d1-a-global-database/#s... [2] https://docs.turso.tech/features/embedded-replicas/introduction https://docs.turso.tech/features/embedded-replicas/introduct...

slt2021 2y ago

there is no need in read replica, because every micro sqlite partition has very low workload (single digit queries per second)

hinkley 2y ago

One of the classic solutions to this was to put the data behind a cluster and route by verb. All the GET requests go to the replicas and everything else goes to the writeable instance.

datadrivenangel 2y ago
Suffers from dirty reads: Write to the primary, get confirmation of write-success, reload page, user sees 'stale' data from the read-replica and thinks you lost their data.
- yurishimo 2y ago
  A temporary header that forwards the next X requests to the main write database is a possible solution. Requires a bit more ooomfph to handle the traffic and probably isn’t good for a write heavy app, but for a generally read heavy CRUD app, it’s a decent compromise.
  - hinkley 2y ago
    I've also seen a writeup of someone who used clocks. The write is transaction 1234, so the client has to read from a replica that is caught up to 1234 in the WAL, or get bounced to another that does. And of course there's the Phoenix LiveView solution where the write contains the new data, gets injected back into the page that contained the edit link. That doesn't quite cover the save-reload loop that some users with well-earned trust issues follow.

NathanFlurry 2y ago

I think the other comments have the application-level approaches covered. However, I suspect the infrastructure will provide this natively as it matures: - Cloudflare will probably eventually add read replicas for Durable Objects. They're already rolling it out for D1 (their other SQLite database offering). [1] - Turso has their own story for read replicas. [2] [1] https://blog.cloudflare.com/building-d1-a-global-database/#sneak-preview-bringing-read-replication-to-d1-with-sessions https://blog.cloudflare.com/building-d1-a-global-database/#s... [2] https://docs.turso.tech/features/embedded-replicas/introduction https://docs.turso.tech/features/embedded-replicas/introduct...