4 ms·

I get that relational dbs like both Postgres and SQLite are extremely battletested and reliable, incredible pieces of tech, with tons of features. But what I’m

by klabb3 4y ago

I get that relational dbs like both Postgres and SQLite are extremely battletested and reliable, incredible pieces of tech, with tons of features. But what I’m reading here is a minefield of footguns, for fairly trivial logic, so I’m wondering if these tools were really designed for these use cases. Now, I don’t know if every server-side app NEEDS to run in 10 regions, auto-refresh session cookies, notify when someone starts typing a comment, report metrics every time the user moves the mouse pointer (or whatever else the kids do for fun these days). But let’s assume for a moment that's the common case. My understanding is that these relational dbs require a lot of config, awareness of the difficult distributed issues, and other layers such as caching, batching and so on, all of which is incredibly error-prone. In fact, the most important parts of the ground laws of data manipulation isn’t even part of a schema! I could worry less about enums and nulls, in a world with orders of magnitude more dangerous creatures: losing writes because I wrote to a replica, reading or writing inconsistent state, duplicate writes etc etc. It feels like we have a lot of structure around the easy stuff, but no structure around the hard parts. As a mere a dabbler with databases, can someone help calm my nerves?

paulryanrogers 4y ago
Core issue is usually multiple writable nodes, therefore conflicts and acting on outdated data. Then there are isolation level issues. More distributed solutions usually opt for eventual consistency. Enforcing a single writable nodes / primary can go a long way. Of course you've got a lot more options if your data naturally partitions, with few or no relationships among them.
benbjohnson 4y ago
LiteFS author here. I agree with you that most applications don't need to have all the distributed bells and whistles. Most projects that I run start out with just SQLite on a single node with Litestream and/or a cron backup. However, the number of applications that need to reduce latency in multiple regions is growing and the options tend to be pretty complicated or expensive. LiteFS aims to be a low-cost, self-hosted alternative to those options. > I could worry less about enums and nulls, in a world with orders of magnitude more dangerous creatures: losing writes because I wrote to a replica, reading or writing inconsistent state, duplicate writes etc etc LiteFS uses a primary/replica setup (as do many distributed databases) where the primary can perform writes and replicas are read-only. You won't be able to lose writes written to a replica because LiteFS doesn't allow that. As for inconsistent state, there is a transaction ID that lets you track your replication position[1]. You can implement strict serializability across your cluster using that. Then for duplicate writes, all writes go to the primary so it has the same serializable guarantees as regular SQLite. I hope that explanation helps. Let me know if you have any other questions. [1]: https://fly.io/docs/litefs/position/ https://fly.io/docs/litefs/position/
- klabb3 4y ago
  Very cool, and thanks for taking the time, it is indeed calming to a db novice like myself. In fact, I’m using Fly right now to try out the platform in general. While I have you, do you have any high level thoughts on reactivity within a db like sqlite? (My app needed some real-time features so I have mostly been looking at NATS.io plus websockets). Are there any real-time like features (reactivity, subscriptions, CDC, materialized views) that work well and ergonomically within that context, either today or in the future? If so it could be really, really useful for my app. Thanks again!
  - jacob019 4y ago
    I have never considered reacting to the database like that. If I need something to happen on db writes, then I cobble together some kind of event system, or delegate those writes to a function that takes care of the side effects. Things like websockets are app layer. There are many db features that I have never tried, have you used a reactive database in production?
  - yoz 4y ago
    Kent's recommended NodeJS module, `better-sqlite3`, has some very nifty features including the creation of JavaScript user-defined functions[0] that (if I understand this right) can be called from SQLite. Combined with TRIGGERs, I wonder if it might fire a function within the app when an UPDATE/INSERT happens from a different process? (This is me wondering out loud, I don't actually know.) I also recommend checking out Replicache[1] and alternatives, which may be a better way to handle the networking and database replication so that it doesn't rely on the underlying DB. [0] https://github.com/WiseLibs/better-sqlite3/blob/HEAD/docs/api.md#functionname-options-function---this https://github.com/WiseLibs/better-sqlite3/blob/HEAD/docs/ap... [1] https://replicache.dev/ https://replicache.dev/
    - justsomeuser 4y ago
      This will not work as SQLite triggers only work for writes within the same process; a trigger cannot execute code in another process. But you could use triggers to write to a table (as any process that writes will run that trigger), and then poll that table from another process.
roncesvalles 4y ago
>As a mere a dabbler with databases, can someone help calm my nerves? Just use one of the cloud or SaaS database options which are battle tested, have well-defined guarantees and 24/7 support. Running your own database seems to be a fool's errand if data loss or corruption worries you at all.

by klabb3 4y ago

paulryanrogers 4y ago

Core issue is usually multiple writable nodes, therefore conflicts and acting on outdated data. Then there are isolation level issues. More distributed solutions usually opt for eventual consistency. Enforcing a single writable nodes / primary can go a long way. Of course you've got a lot more options if your data naturally partitions, with few or no relationships among them.

benbjohnson 4y ago

LiteFS author here. I agree with you that most applications don't need to have all the distributed bells and whistles. Most projects that I run start out with just SQLite on a single node with Litestream and/or a cron backup. However, the number of applications that need to reduce latency in multiple regions is growing and the options tend to be pretty complicated or expensive. LiteFS aims to be a low-cost, self-hosted alternative to those options. > I could worry less about enums and nulls, in a world with orders of magnitude more dangerous creatures: losing writes because I wrote to a replica, reading or writing inconsistent state, duplicate writes etc etc LiteFS uses a primary/replica setup (as do many distributed databases) where the primary can perform writes and replicas are read-only. You won't be able to lose writes written to a replica because LiteFS doesn't allow that. As for inconsistent state, there is a transaction ID that lets you track your replication position[1]. You can implement strict serializability across your cluster using that. Then for duplicate writes, all writes go to the primary so it has the same serializable guarantees as regular SQLite. I hope that explanation helps. Let me know if you have any other questions. [1]: https://fly.io/docs/litefs/position/ https://fly.io/docs/litefs/position/

klabb3 4y ago
Very cool, and thanks for taking the time, it is indeed calming to a db novice like myself. In fact, I’m using Fly right now to try out the platform in general. While I have you, do you have any high level thoughts on reactivity within a db like sqlite? (My app needed some real-time features so I have mostly been looking at NATS.io plus websockets). Are there any real-time like features (reactivity, subscriptions, CDC, materialized views) that work well and ergonomically within that context, either today or in the future? If so it could be really, really useful for my app. Thanks again!
- jacob019 4y ago
  I have never considered reacting to the database like that. If I need something to happen on db writes, then I cobble together some kind of event system, or delegate those writes to a function that takes care of the side effects. Things like websockets are app layer. There are many db features that I have never tried, have you used a reactive database in production?
- yoz 4y ago
  Kent's recommended NodeJS module, `better-sqlite3`, has some very nifty features including the creation of JavaScript user-defined functions[0] that (if I understand this right) can be called from SQLite. Combined with TRIGGERs, I wonder if it might fire a function within the app when an UPDATE/INSERT happens from a different process? (This is me wondering out loud, I don't actually know.) I also recommend checking out Replicache[1] and alternatives, which may be a better way to handle the networking and database replication so that it doesn't rely on the underlying DB. [0] https://github.com/WiseLibs/better-sqlite3/blob/HEAD/docs/api.md#functionname-options-function---this https://github.com/WiseLibs/better-sqlite3/blob/HEAD/docs/ap... [1] https://replicache.dev/ https://replicache.dev/
  - justsomeuser 4y ago
    This will not work as SQLite triggers only work for writes within the same process; a trigger cannot execute code in another process. But you could use triggers to write to a table (as any process that writes will run that trigger), and then poll that table from another process.

roncesvalles 4y ago

>As a mere a dabbler with databases, can someone help calm my nerves? Just use one of the cloud or SaaS database options which are battle tested, have well-defined guarantees and 24/7 support. Running your own database seems to be a fool's errand if data loss or corruption worries you at all.