4 ms·
> Sqlite smokes postgres on the same machine even with domain sockets [1]. SQLite on the same machine is akin to calling fwrite. That's fine. This is also a sy
by locknitpicker 6mo ago
> Sqlite smokes postgres on the same machine even with domain sockets [1].
SQLite on the same machine is akin to calling fwrite. That's fine. This is also a system constraint as it forces a one-database-per-instance design, with no data shared across nodes. This is fine if you're putting together a site for your neighborhood's mom and pop shop, but once you need to handle a request baseline beyond a few hundreds TPS and you need to serve traffic beyond your local region then you have no alternative other than to have more than one instance of your service running in parallel. You can continue to shoehorn your one-database-per-service pattern onto the design, but you're now compelled to find "clever" strategies to sync state across nodes.
Those who know better to not do "clever" simply slap a Postgres node and call it a day.
- rpdillon 6mo agoI wonder what percentage of services run on the Internet exceed a few hundred transactions per second.
- egwor 6mo agoI think the better question to ask is what services peak at a few hundred transactions per second?
- icedchai 6mo agoI’ve seen multimillion dollar “enterprise” projects get no where close to that. Of course, they all run on scalable, cloud native infrastructure costing at least a few grand a month.
- not_kurt_godel 6mo ago> a few grand a month. A negligible cost for a successful tech business that also works when your requirements exceed the capabilities of a single VPS.
- icedchai 6mo agoI agree. But these are projects that barely get any requests.
- andersmurphy 6mo ago> SQLite on the same machine is akin to calling fwrite. Actually 35% faster than fwrite [1]. > This is also a system constraint as it forces a one-database-per-instance design You can scale incredibly far on a single node and have much better up time than github or anthropic. At this rate maybe even AWS/cloudflare. > you need to serve traffic beyond your local region Postgres still has a single node that can write. So most of the time you end up region sharding anyway. Sharding SQLite is straight forward. > This is fine if you're putting together a site for your neighborhood's mom and pop shop, but once you need to handle a request baseline beyond a few hundreds TPS It's actually pretty good for running a real time multiplayer app with a billion datapoints on a 5$ VPS [2]. There's nothing clever going on here, all the state is on the server and the backend is fast. > but you're now compelled to find "clever" strategies to sync state across nodes. That's the neat part you don't. Because, for most things that are not uplink limited (being a CDN, Netflix, Dropbox) a single node is all you need. - [1] https://sqlite.org/fasterthanfs.html https://sqlite.org/fasterthanfs.html - [2] https://checkboxes.andersmurphy.com https://checkboxes.andersmurphy.com
- wookmaster 6mo agoHow do you manage HA?
- rovr138 6mo agoNo offense, you wait. Like everyone's been doing for years in the internet and still do - When AWS/GCP goes down, how do most handle HA? - When a database server goes down, how do most handle HA? - When Cloudflare goes down, how do most handle HA? The down time here is the server crashed, routing failed or some other issue with the host. You wait. One may run pingdom or something to alert you.
- locknitpicker 6mo ago> When AWS/GCP goes down, how do most handle HA? This is a disingenuous scenario. SQLite doesn't buy you uptime if you deploy your app to AWS/GCP, and you can just as easily deploy a proper RDBMS such as postgres to a small provider/self-host. Do you actually have any concrete scenario that supports your belief?
- tl 6mo agohttps://antonz.org/sqlite-is-not-a-toy-database/ https://antonz.org/sqlite-is-not-a-toy-database/ — 240K inserts per second on a single machine in 2021. The problem you describe is real, but the TPS ceiling is wrong by three orders of magnitude on modern hardware.
- pdhborges 6mo agoDo you know why it is a toy? Because in a real prod environment after inserting 240k rows per second for a while you have to deal with the fact that schema evolution is required. Good luck migrating those huge tables with Sqlite ALTER table implementation
- devmor 6mo agoTry doing that on a “real” DB with hundreds of millions of rows too. Anything more than adding a column is a massive risk, especially once you’ve started sharding.
- pdhborges 6mo agoYes it might be risky. But most schema evolution changes can be done with no or minimal downtime even if you have to do then in multiple steps. When is a simple ALTER going to be totally unacetable if youare using Sqlite?
- shimman 6mo agoThis doesn't seem like a toy but you know... realizing different systems will have different constraints. Not everyone needs monopolistic tech to do their work. There's probably less than 10,000 companies on earth that truly need to write 240k rows/second. For everyone else, we can focus on better things.
- pdhborges 6mo ago> realizing different systems will have different constraints. I realize that. There are a few comments already that present use cases where I can totally see using Sqlite as a good option. > Not everyone needs monopolistic tech to do their work We are talking about localhost Postgres vs SQLite here. Both are open source.
- darkwater 6mo agoI mean, your "This is fine for" is almost literally the whole point of TFA, that you can go a long way, MRR-wise, with a simpler architecture.