9 ms·

How do any writes end up on other horizontally scaled machines though? To me the whole point of a database on another machine is that it is the single point of

• nicoburns 5y ago
  > If you don't need that, you might as well read the entire dataset into memory and be done with it. Over in-memory data structures,SQLite gives you: - Persistence - Crash tolerance - Extremely powerful declarative querying capabilities > if you have read-only traffic you don't need sqlite replication. I agree with you that the main use-case here is backup and data durability for small apps. Which is pretty big deal, as a database server is often the most expensive part of running a small app. That said, there are definitely systems where latency of returning a snapshot of the data is important, but which snapshot isn't (if updates take a while to percolate that's fine).
  • mbreese 5y ago
    I’d argue that persistence also includes data portability. It’s very handy to be able to just copy your data around in a SQLite file. That’s not really a feature that’s terribly useful in a remote deployed application, but very handy if you have multiple applications all reading the same data.
    • mrkurt 5y ago
      It does! "Get a to-go database" is already on the roadmap. :)
  • jbergens 4y ago
    There are in-memory db's that also write to disk. They can offer both persistence and crash tolerance. I tried o e that even had a better solution to the object-relational mismatch, you just got objects, which made development very easy.
• mrkurt 5y ago
  Litestream does a couple of things. It started as a way to continuously back sqlite files up to s3. Then Ben added read replicas – you can configure Litestream to replicate from a "primary" litestream server. It's still limited to a single writer, but there's no s3 in play. You get async replication to other VMs: https://github.com/fly-apps/litestream-base https://github.com/fly-apps/litestream-base We have a feature for redirecting HTTP requests that perform writes to a single VM. This makes Litestream + replicas workable for most fullstack apps: https://fly.io/blog/globally-distributed-postgres/ https://fly.io/blog/globally-distributed-postgres/ It's not a perfect setup, though. You have to take the writer down to do a deploy. The next big Litestream release should solve that, and is part of what's teased in the post.
  • throwoutway 5y ago
    > We have a feature for redirecting HTTP requests that perform writes to a single VM. This makes Litestream + replicas workable for most fullstack apps: https://fly.io/blog/globally-distributed-postgres/ https://fly.io/blog/globally-distributed-postgres/ Thereby making it a constraint and (without failover) a single point of failover? What’s the upper limit here?
    • tptacek 5y ago
      This constraint is common to most n-tier architectures (with Postgres or MySQL) as well. Obviously, part of what's interesting about Litestream is that it simplifies fail-over with SQLite.
      • pipe_connector 5y ago
        Is simplified failover referring to the currently-in-beta async replicas? Or is there something else on the way that will make it easier to failover?
        • mtremsal 5y ago
          I _think_ he's referring to the ease of use of streaming the WAL to S3 and reloading from it upon starting a new write "leader".
• bob1029 5y ago
  What if, due to ridiculous latency reductions, your business no longer requires more than 1 machine to function at scale? I'm talking more about sqlite itself than any given product around it at this point, but I still think it's an interesting thought experiment in this context.
  • WJW 5y ago
    I'll point out that the ridiculous latency reductions don't apply to replicating the writes to S3 and/or any replica servers, that still takes as long as it would to any other server across a network. The latency reductions are only for pure read traffic. Also, every company I ever worked at had a policy to run at least two instances of a service in case of hardware failure. (Is this reasonable to extrapolate this policy to a company which might want to run on a single sqlite instance? I don't know, but just as a datapoint I don't think any business should strive to run on a single instance) This write latency might be fine, although more than one backend app I know renewed the expiry time of a user session on every hit and would thus do at least one DB write per HTTP call. I don't think this is optimal, but it does happen and simply going "well don't do write traffic then" does not always line up with how apps are actually built. Replicated sqlite over litestream is very cool, but definitely you need to build your app around and also definitely something that costs you one of your innovation tokens.
    • tptacek 5y ago
      There's no magic here (that there is no magic is part of the point). You have the same phenomenon in n-tier Postgres deployments: to be highly available, you need multiple instances; you're going to have a write leader, because you're not realistically want to run a Raft consensus for every write; etc. The point of the post is just that if you can get rid of most of the big operational problems with using server-side SQLite in a distributed application --- most notably, failing over and snapshotting --- then SQLite can occupy a much more interesting role in your stack than it's conventionally been assigned. SQLite has some very attractive properties that have been largely ignored because people assume they won't be able to scale it out and manage it. Well, you can scale it out and manage it. Now you've got an extremely simple database layer that's easy to reason about, doesn't require you to run a database server (or even a cache server) next to all your app instances, and happens to be extraordinarily fast. Maybe it doesn't make sense for your app? There are probably lots of apps that really want Postgres and not SQLite. But the architecture we're proposing is one people historically haven't even considered. Now, they should. I'm not sure "litestream replicate <file>" really costs a whole innovation token. It's just SQLite. You should get an innovation rebate for using it. :)
• ok_dad 5y ago
  With Postgres, you might have one server, or one cluster of servers that are coordinated, and then inside there you have tables with users and the users' data with foreign keys tying them together. With SQLite, you would instead have one database (one file) per user as close to the user as possible that has all of the user's data and you would just read/write to that database. If your application needs to aggregate multiple user's data, then you use something like Litestream to routinely back it up to S3, then when you need to aggregate data you can just access it all there and use a distributed system to do the aggregation on the SQLite database files.
  • danappelxx 5y ago
    Hold on, doesn’t one-database-per-user totally absolve all ACID guarantees? You can’t do cross-database transactions (to my knowledge), which means you can end up with corrupted data during aggregations. What am I missing?
    • mwcampbell 5y ago
      One database per tenant only makes sense in multi-tenant applications that don't have any cross-tenant actions. I imagine there are many B2B applications that fall into this category.
    • ok_dad 5y ago
      If you have a use case with data that is extremely partitionable (like controlling individual industrial devices and collecting data to improve their performance or monitor the processes), then SQLite and Litestream could be a great option, but if you can't reasonably partition the data then it's probably better to use a centralized database server.
    • imtringued 4y ago
      >Hold on, doesn’t one-database-per-user totally absolve all ACID guarantees? No it doesn't. What gave you that idea? You still have all "ACID guarantees" within each database. >You can’t do cross-database transactions (to my knowledge), That's true of most databases. If you have two apps and they use two different databases you won't have transactions across those two apps. >which means you can end up with corrupted data during aggregations. No, aggregations within each database work as you would expect. >What am I missing? As others have said, you use this pattern only if you don't intend to cross databases. By the way, in NoSQL databases like MongoDB, every document is its own little database so having a per user database is a massive upgrade over NoSQL.
• jolux 5y ago
  S3 is strongly consistent now: https://aws.amazon.com/s3/consistency/ https://aws.amazon.com/s3/consistency/
  • fizwhiz 5y ago
    What a ridiculous marketing term. This is a RYW (Read your writes) level of consistency which is a far cry from Strong consistency (see https://jepsen.io/consistency https://jepsen.io/consistency). Seems like eventual consistency with some affinity bolted on.
    • jolux 5y ago
      That page does not give a hard definition for strong consistency, it says that it uses them informally as relative terms. AWS is not claiming serializability, they call it "strong read-after-write consistency." I don't see the problem here? S3 famously wasn't guaranteed to read data you had just written for a long time, and now it is. That's significant. Here's more about the specifics: https://docs.aws.amazon.com/AmazonS3/latest/userguide/Welcome.html#ConsistencyModel https://docs.aws.amazon.com/AmazonS3/latest/userguide/Welcom... In particular: > Any read (GET or LIST request) that is initiated following the receipt of a successful PUT response will return the data written by the PUT request. So this is stronger than RYW (emphasis mine).
      • couchand 5y ago
        > > Any read (GET or LIST request) that is initiated following the receipt of a successful PUT response will return the data written by the PUT request. > So this is stronger than RYW. I'm not sure that it is? The examples listed below that description only specify making an update and then immediately reading it back from the same process.
        • jolux 5y ago
          Look at the graphics in the section "Concurrent applications," specifically the first one. At T0 Client 1 writes 'color = red.' Write 1 completes at T1. At T2 Client 2 writes 'color = ruby.' Write 2 completes at T3. At T4 Client 1 reads 'color = ruby,' the result of Write 2 from Client 2. The explanation above says "Because S3 is strongly consistent, R1 and R2 both return color = ruby." There are clearly some subtleties (as explained further down the page) but I don't think Amazon are really being deceptive here.
• nine_k 5y ago
  I do understand the point of running SQLite in-process to speed up reads. I do not understand why SQLite must also handle intense write load with HA, failover, etc. I would rather have the best of both worlds: a proper DB server (say, Postgres) replicated to super-fast and simple read replicas in SQLite on every node. (My ideal case would be some kind of natural sharding where each node keeps its own updates, or just a highly available data browsing app, with data in SQLite files updated as entire files, like a deploymen.)
  • jethro_tell 5y ago
    I've worked on an 'eventually consistent' system with read/write SQLite dbs on each host with a background worker that replayed the log into a central source of truth db and workers that made updates across each host instance of SQLite. It could have been made a lot faster, I think the replication sla for the service was 10 minutes usually done in seconds. But our specific workflow only progressed in one direction, so replaying a step wasn't a huge issue now and again though that was quite rare. If you were to put a little more effort than we did into replication layer and tuning your master db, it could be a really effective setup. One of the best parts is that when instances are stopped or isolated, they were also almost isolated from everything that used the service so if you go into a black box with your clients, you work as normal and when connection or other hosts are brought back up they replay the db before accepting connections. We could take entire availability zones offline and the workers and clients would keep humming and update neighbors later.
  • jrochkind1 4y ago
    > a proper DB server (say, Postgres) replicated to super-fast and simple read replicas in SQLite on every node. Having your replicas be different database software than your master seems to me like asking for, at least, weird edge case bugs. Is this something anyone does? Interested to hear experiences if so!
    • JohnBooty 4y ago
      I've not done this but it's intriguing; potentially a best-of-all-worlds solution. I think "proper" automatic replication is not possible given the mismatch between Postgres and SQLite - not everything in Postgres maps to a thing that is possible in SQLite. That said, there are a variety of ways to get data out of Postgres, and a variety of ways to get things into SQLite. You could periodically export CSVs or whatever from Postgres and periodically import them into SQLite. Or you could do a more realtime sync by using a Postgres foreign data wrapper like this one: https://github.com/pgspider/sqlite_fdw https://github.com/pgspider/sqlite_fdw which would let you write directly to SQLite. Combine that with database triggers on the Postgres side of things and you've got something pretty close to realtime replication. Those sorts of solutions wouldn't be as robust as "real" replication (specifically, what happens when one of the SQLite replicas is unavailable? do you catch things up later?) but could be very useful for a lot of scenarios. You could have Postgres importing gobs of data, and "replicating" it over to your read-only reporting server which uses SQLite as a data source.
• samatman 4y ago
  A lot depends on your consistency requirements and data model here. I use SQLite heavily, and have evaluated litestream and rqlite but not deployed them, so bear that in mind. If the application is set up so that it serves a user for a session, so a given session ID is reading and writing from the same SQLite database, there are many opportunities to replicate that data optimistically, so that you won't lose it if a meteor hits the server, but it might not live in all the replicas right away, since applying patchsets off the gossip network happens in downtime. If concerns can't be isolated like this then yes, dedicated swarms of database servers are the way to go. Frequently they can be, and using SQLite punches way above its weight here.
• hinkley 4y ago
  There are many systems that have much higher read to write traffic and so writes only need logarithmic scaling or perhaps with the square root of the system size. Waiting for faster hardware worked for these system for a long time, and to a small extent, still does. The dirty secret is that a lot of systems that require very high write traffic are essentially systems built for narcissists. "Social websites" have higher write traffic than simpler consumption based systems, but we've gone beyond those initial steps into very aggressive systems that are based on recording every interaction with the user and providing them instant gratification for many of those. These applications don't scale in a way that others do, easily. And maybe it's a feature, not a bug, if the tools I use discourage me from jumping into the maelstrom by making it difficult to even consider doing so. Constraints are where creativity comes from, not possibility.