4 ms·

The biggest one is redundancy. Architecting with Read replicas is much easier with Postgres than Sqlite because of it's server model. Sqlite on the server is a

The biggest one is redundancy. Architecting with Read replicas is much easier with Postgres than Sqlite because of it's server model. Sqlite on the server is a fantastic starter database. Dead simple to set up, highly performant and scales way higher (vertically) than anyone gives it credit for. But there certainly is a point you'll have to scale out instead of up, and while there are some great solutions for that (rqlite, litefs, dqlite, marmot) it's not inherent to Sqlite's design.

TekMol 2y ago
Should replication really be a concern of the DB layer? Replication means writing queries which alter the data to multiple machines, right? Shouldn't that be done by a software one level up? Which takes in the queries via some network protocol and then sends them to all machines. That would sound more logical to me.
- emseetech 2y ago
  Historically, yes. Databases were software that were concerned with both storage and networking. It's fine to want to separate those out, but it's not easy to do so and there are reasons they've been coupled for decades.
  - deleted 2y ago
    [deleted]
  - TekMol 2y ago
    What makes it hard? Having a single DB that takes write queries via a proxy which spreads them out to multiple read-only-DBs sounds easy at first.
    - abtinf 2y ago
      When do you consider the write/transaction to be completed? What do you do about out-of-sync read replicas? ACID gets real hard real fast when introducing replication.
      - TekMol 2y ago
        > When do you consider the write/transaction to be completed? Sending a UPDATE/INSERT/DELETE statement to SQLite is not blocking? I would think it is, because in my code I can read the number of affected rows right after I sent the query. > What do you do about out-of-sync read replicas? Delete them and replace them by uploading a checkpoint and replaying a log of the queries since then.
        Tostino 2y ago
        If you are doing statement level replication, you better make sure every query is run in the exact same order (and finishes in the same order). Without that you will have drift from your master database. With that, you have a whole new host of synchronization issues you need to deal with.
otoolep 2y ago
rqlite[1] creator here, happy to answer any questions about it. [1] https://rqlite.io https://rqlite.io

TekMol 2y ago

Should replication really be a concern of the DB layer? Replication means writing queries which alter the data to multiple machines, right? Shouldn't that be done by a software one level up? Which takes in the queries via some network protocol and then sends them to all machines. That would sound more logical to me.

emseetech 2y ago
Historically, yes. Databases were software that were concerned with both storage and networking. It's fine to want to separate those out, but it's not easy to do so and there are reasons they've been coupled for decades.
- deleted 2y ago
  [deleted]
- TekMol 2y ago
  What makes it hard? Having a single DB that takes write queries via a proxy which spreads them out to multiple read-only-DBs sounds easy at first.
  - abtinf 2y ago
    When do you consider the write/transaction to be completed? What do you do about out-of-sync read replicas? ACID gets real hard real fast when introducing replication.
    - TekMol 2y ago
      > When do you consider the write/transaction to be completed? Sending a UPDATE/INSERT/DELETE statement to SQLite is not blocking? I would think it is, because in my code I can read the number of affected rows right after I sent the query. > What do you do about out-of-sync read replicas? Delete them and replace them by uploading a checkpoint and replaying a log of the queries since then.
      - Tostino 2y ago
        If you are doing statement level replication, you better make sure every query is run in the exact same order (and finishes in the same order). Without that you will have drift from your master database. With that, you have a whole new host of synchronization issues you need to deal with.

otoolep 2y ago

rqlite[1] creator here, happy to answer any questions about it. [1] https://rqlite.io https://rqlite.io