11 ms·
SQLite-on-the-server is misunderstood: Better at hyper-scale than micro-scale
- NathanFlurry 2y agoAuthor here, happy to answer questions!
- abdullin 2y agoInteresting read, thank you! Do you use any special tools to manage all these separate databases, track performance and debug problems?
- NathanFlurry 2y agoThe folks over at StarbaseDB (https://starbasedb.com/ https://starbasedb.com/) are working on building tools for shareded SQLite. From the companies I've talked to, most developers using this architecture are building quick scripts to do this in-house. Both Turso and Durable Objects SQLite already a surprising amount of usage that people don't talk about much publicly yet, so I suspect some of this tooling will start to be published in the next year.
- robertlagrant 2y agoI love the idea of Durable Objects, and have thoughts about it in the health space (although having a single provider of them is probably a bad thing) but cross-cutting questions such as this seem to be quite difficult to achieve. From the chat logs example in your article: how do you cope with a requirement such as "I as a user want to see all my latest thread activity in one place, across all my chat rooms?"
- korkybuchek 2y ago> From the chat logs example in your article: how do you cope with a requirement such as "I as a user want to see all my latest thread activity in one place, across all my chat rooms?" create a copy, for example
- jmull 2y agoThat's an easy case: write to two places (one for the chat and one for the user's activity). In this case I think you can let them become inconsistent in the face of, e.g., write errors.
- NathanFlurry 2y agoThis. > In this case I think you can let them become inconsistent in the face of, e.g., write errors. For devs using CF Durable Objects, people frequently use CF Queues or CF Workflows to ensure that everything is eventually consistent without significant overhead. It's a similar pattern to what large cos already do at scale with keeping data up to date across multiple partitions with Cassandra/DynamoDB.
- neilpa 2y agoRandomly noticed your post is dated in the future - December 16, 2025
- NathanFlurry 2y agoGreat Scott!
- grounder 2y agoIn your chat channel example, you have a table for messages, and a table for participants. How do you join the participants in this chat channel database with whichever database the participants are actually defined in, so the application would be able to show participant details (name, avatar, etc.)?
- NathanFlurry 2y agoThe two tables are intended to be part of the same "chat" partition (ie SQLite database). You can join them with a native SQLite query. Seems I should make this more clear. Cheers
- grounder 2y agoI understand the two tables in your chat example, I think. I'm wondering how you get the rest of the user profile data (name, for example). Is that table in a totally different SQLite database? If so, can you join on that, or do you need to query it separately? Thanks!
- NathanFlurry 2y agoYep. Most designs I've seen put user profiles under a different table which requires a separate query.
- byoung2 2y agoLooks 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 agothere is no need in read replica, because every micro sqlite partition has very low workload (single digit queries per second)
- hinkley 2y agoOne 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 agoSuffers 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 agoA 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 agoI'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.
- jacktheturtle 2y agoSQLite is a very good technology.
- bob1029 2y ago> Benefits of SQLite-Per-Partition The obvious caveat here is any situation where you need global tables. I've never worked on a product where 100% of the schema could be partitioned like this. I've done a ton of SQLite-per-<entity type>, but there's always been a singleton SQLite database above this tracking the metadata. Looking up a user session via token is not an analytics task. Neither is checking if an email address is already registered. These are very pedestrian queries that demand global visibility, ideally with hard transactional guarantees.
- manmal 2y agoIs it even realistic to depend on transactional guarantees, with hundreds of services hammering the DB(s) more or less concurrently? Don’t they need to coordinate themselves outside of transactions?
- bob1029 2y ago> Is it even realistic to depend on transactional guarantees, with hundreds of services hammering the DB(s) more or less concurrently? If you have hundreds of services hammering the datastore concurrently, then the ability to offer transactional guarantees seems absolutely mandatory for me. > Don’t they need to coordinate themselves outside of transactions? I think we're back at the beginning of our journey here.
- manmal 2y agoDo you mean via distributed transactions? I‘m clueless in that area, and don’t understand how hundreds of self sufficient services would partake in transactions that span different sets of those services, without entangling them badly.
- refulgentis 2y agoI find this topic difficult to reason about because I'm not intimately familiar with DBs at scale. That being said: my understanding is we're always going to have something that needs to maintain its own state that's global, and you're naming that problem as well. For example, let's say we partition users based on the first letter of their email addresses. This works great for most user-specific queries (e.g., fetching a user profile). But what happens when someone registers a new account? At that point, we must ensure the email is globally unique. A purely partitioned approach won't help here—we'll need some kind of global database or service maintaining a single source of truth for email uniqueness checks. (then it gets complicated, because of the simple level at which I can understand and communicate about it. Why not just partition based on the first letter of an email? Well, yes, then we just have to deal with emails changing. Maybe a better example is session tokens, because they don't come with an email. But we could require that, or do some bespoke thing...there's security concerns there but they seem overrated...but to your point, you end up adding a ton of complexity just so you can fit a square peg in a round hole)
- lbutler 2y agoI’m building a local-first web app, and SQLite works well for my case since a single project can be contained in one database file, just like users are used to with existing desktop applications. What I’d really like is an easy way to sync the SQLite database state to a cloud service. Most existing options expect you to query against a remotely hosted database and charge per read/write. Since the database will have around 100,000 rows and you're typically working with all the data at once, streaming parts of it doesn’t make sense for my use case. The closest I’ve found is Turso, which has offline writes in private beta, and SQLite Cloud, which lists local-first and offline sync as "coming soon." The simplest approach might be letting users push to S3 storage with versioning. Ideally, it would also support point-in-time restores, tracking incremental updates alongside full snapshots. Even better, I’d manage minimal server-side infrastructure and just pull the SQLite database from a service that handles syncing and management.
- 0cf8612b2e1e 2y agoMaybe I am misunderstanding which part you want in the cloud, but that sounds like litestream. Let’s you transparently backup a live SQLite database to a remote destination. https://litestream.io/ https://litestream.io/
- adamtaylor_13 2y agoYeah, I was about to suggest litestream. Isn't it local-first-with-cloud-backups?
- gwking 2y agoI depend on litestream for production backups and as the months wear on without any releases I am getting more nervous. To be clear, I don’t feel entitled to anything with an open source project like this, but bug reports and fixes seem to be accumulating. I have flirted with the idea of building from main. I’ve also flirted with the idea of forking litestream and stripping it down dramatically. The reason why is that I don’t like the idea of the production server being in charge of rotation and deletion. It seems like the thing getting backed up shouldn’t have the privilege of deleting backups in case it gets compromised. I might even go so far as to propose that the “even liter stream” process merely writes to a different local volume and then some other process does the uploading but I haven’t gotten beyond the daydream stage.
- koakuma-chan 2y agoTDLib (Telegram Database library)[0] uses SQLite. In Telegram Bot API, each TDLib instance handles more than 24,000 active bots simultaneously.[1] [0]: https://github.com/tdlib/td https://github.com/tdlib/td [1]: https://core.telegram.org/tdlib https://core.telegram.org/tdlib
- akavi 2y agoHmm, this article is a little confusing. I'm not familiar with Vitess or Citus, but am familiar with "manually" sharded Postgres/Mysql, and I'm not sure I understand if there's any "interaction effects" of the decision to shard or not and the decision between MySQL/Postgres and Sqlite. Like, the article's three sections are: 1. The challenges of sharding 2. The benefits of these new sharded Sqlite solutions over conventional Sqlite 3. A list conflating the benefits of SQL databases generally with the benefits of Sqlite None of which answer the question of "Why should I use sharded Sqlite instead of, say, sharded Postgres, for hyperscale?".
- koakuma-chan 2y agoIf you think this is a good fit for your case, you should embed SQLite in your application and shard your application. An embedded SQLite is faster and uses less memory than a PostgreSQL running as a separate process and possibly on a different machine.
- NathanFlurry 2y agoAuthor here. Agreed — I think adding some comparisons to other database partitioning strategies would be helpful. My 2 cents, specifically about manually partitioning Postgres/MySQL (rather than using something like Citus or Vitess): SQLite-on-the-server works similarly to Cassandra/DynamoDB in how it partitions data. The number of partitions is decoupled from the number of databases you're running, since data is automatically rebalanced for you. If you're curious, Dagster has a good post on data rebalancing: https://dagster.io/glossary/data-rebalancing https://dagster.io/glossary/data-rebalancing. With manual partitioning, compared to automatic partitioning, you end up writing a lot of extra complex logic for: - Determining which database each piece of data lives on (as opposed to using partitioning keys which do that automatically) - Manually rebalancing data, which is often difficult and error-prone - Adding partitions manually as the system grows - (Anecdotally) Higher operational costs, since matching node count to workload is tricky Manual partitioning can work fine for companies like Notion, where teams are already invested in Postgres and its tooling. But overall, I think it introduces more long-term problems than using a more naturally partitioned system. To be clear: OLTP databases are great — you don’t always need to reach for Cassandra, DynamoDB, or SQLite-on-the-server depending on your workload. But I do think SQLite-on-the-server offers a really compelling blend of the developer experience of Postgres with the scalability of Cassandra.
- throwaway984393 2y ago[dead]
- EGreg 2y agoLately I've seen posts about DuckDB, which looks really cool, but Sqlite seems to be compileable with WASM so it can be used in some kind of container. How do the two compare?
- NathanFlurry 2y agoDuckDB crushes SQLite in heavy data workloads according to ClickBench by 915x. (Link below since it's looong.) DuckDB also has a WASM target: https://duckdb.org/docs/stable/clients/wasm/overview.html https://duckdb.org/docs/stable/clients/wasm/overview.html I don't know enough about DuckDB to understand the tradeoffs it made compared to SQLite to achieve this performance. https://benchmark.clickhouse.com/#eyJzeXN0ZW0iOnsiQWxsb3lEQiI6ZmFsc2UsIkFsbG95REIgKHR1bmVkKSI6ZmFsc2UsIkF0aGVuYSAocGFydGl0aW9uZWQpIjpmYWxzZSwiQXRoZW5hIChzaW5nbGUpIjpmYWxzZSwiQXVyb3JhIGZvciBNeVNRTCI6ZmFsc2UsIkF1cm9yYSBmb3IgUG9zdGdyZVNRTCI6ZmFsc2UsIkJ5Q29uaXR5IjpmYWxzZSwiQnl0ZUhvdXNlIjpmYWxzZSwiY2hEQiAoRGF0YUZyYW1lKSI6ZmFsc2UsImNoREIgKFBhcnF1ZXQsIHBhcnRpdGlvbmVkKSI6ZmFsc2UsImNoREIiOmZhbHNlLCJDaXR1cyI6ZmFsc2UsIkNsaWNrSG91c2UgQ2xvdWQgKGF3cykiOmZhbHNlLCJDbGlja0hvdXNlIENsb3VkIChhenVyZSkiOmZhbHNlLCJDbGlja0hvdXNlIENsb3VkIChnY3ApIjpmYWxzZSwiQ2xpY2tIb3VzZSAoZGF0YSBsYWtlLCBwYXJ0aXRpb25lZCkiOmZhbHNlLCJDbGlja0hvdXNlIChkYXRhIGxha2UsIHNpbmdsZSkiOmZhbHNlLCJDbGlja0hvdXNlIChQYXJxdWV0LCBwYXJ0aXRpb25lZCkiOmZhbHNlLCJDbGlja0hvdXNlIChQYXJxdWV0LCBzaW5nbGUpIjpmYWxzZSwiQ2xpY2tIb3VzZSAod2ViKSI6ZmFsc2UsIkNsaWNrSG91c2UiOmZhbHNlLCJDbGlja0hvdXNlICh0dW5lZCkiOmZhbHNlLCJDbGlja0hvdXNlICh0dW5lZCwgbWVtb3J5KSI6ZmFsc2UsIkNsb3VkYmVycnkiOmZhbHNlLCJDcmF0ZURCIjpmYWxzZSwiQ3J1bmNoeSBCcmlkZ2UgZm9yIEFuYWx5dGljcyAoUGFycXVldCkiOmZhbHNlLCJEYXRhYmVuZCI6ZmFsc2UsIkRhdGFGdXNpb24gKFBhcnF1ZXQsIHBhcnRpdGlvbmVkKSI6ZmFsc2UsIkRhdGFGdXNpb24gKFBhcnF1ZXQsIHNpbmdsZSkiOmZhbHNlLCJBcGFjaGUgRG9yaXMiOmZhbHNlLCJEcmlsbCI6ZmFsc2UsIkRydWlkIjpmYWxzZSwiRHVja0RCIChEYXRhRnJhbWUpIjp0cnVlLCJEdWNrREIgKG1lbW9yeSkiOnRydWUsIkR1Y2tEQiAoUGFycXVldCwgcGFydGl0aW9uZWQpIjpmYWxzZSwiRHVja0RCIjp0cnVlLCJFbGFzdGljc2VhcmNoIjpmYWxzZSwiRWxhc3RpY3NlYXJjaCAodHVuZWQpIjpmYWxzZSwiR2xhcmVEQiI6ZmFsc2UsIkdyZWVucGx1bSI6ZmFsc2UsIkhlYXZ5QUkiOmZhbHNlLCJIeWRyYSI6ZmFsc2UsIlNhbGVzZm9yY2UgSHlwZXIgKFBhcnF1ZXQpIjpmYWxzZSwiU2FsZXNmb3JjZSBIeXBlciI6ZmFsc2UsIkluZm9icmlnaHQiOmZhbHNlLCJLaW5ldGljYSI6ZmFsc2UsIk1hcmlhREIgQ29sdW1uU3RvcmUiOmZhbHNlLCJNYXJpYURCIjpmYWxzZSwiTW9uZXREQiI6ZmFsc2UsIk1vbmdvREIiOmZhbHNlLCJNb3RoZXJEdWNrIjp0cnVlLCJNeVNRTCAoTXlJU0FNKSI6ZmFsc2UsIk15U1FMIjpmYWxzZSwiT2N0b1NRTCI6ZmFsc2UsIk9wdGVyeXgiOmZhbHNlLCJPeGxhIjpmYWxzZSwiUGFuZGFzIChEYXRhRnJhbWUpIjpmYWxzZSwiUGFyYWRlREIgKFBhcnF1ZXQsIHBhcnRpdGlvbmVkKSI6ZmFsc2UsIlBhcmFkZURCIChQYXJxdWV0LCBzaW5nbGUpIjpmYWxzZSwicGdfZHVja2RiIChNb3RoZXJEdWNrIGVuYWJsZWQpIjpmYWxzZSwicGdfZHVja2RiIjpmYWxzZSwiUG9zdGdyZVNRTCB3aXRoIHBnX21vb25jYWtlIjpmYWxzZSwiUGlub3QiOmZhbHNlLCJQb2xhcnMgKERhdGFGcmFtZSkiOmZhbHNlLCJQb2xhcnMgKFBhcnF1ZXQpIjpmYWxzZSwiUG9zdGdyZVNRTCAodHVuZWQpIjpmYWxzZSwiUG9zdGdyZVNRTCI6ZmFsc2UsIlF1ZXN0REIiOmZhbHNlLCJSZWRzaGlmdCI6ZmFsc2UsIlNlbGVjdERCIjpmYWxzZSwiU2luZ2xlU3RvcmUiOmZhbHNlLCJTbm93Zmxha2UiOmZhbHNlLCJTcGFyayI6ZmFsc2UsIlNRTGl0ZSI6dHJ1ZSwiU3RhclJvY2tzIjpmYWxzZSwiVGFibGVzcGFjZSI6ZmFsc2UsIlRlbWJvIE9MQVAgKGNvbHVtbmFyKSI6ZmFsc2UsIlRpbWVzY2FsZSBDbG91ZCI6ZmFsc2UsIlRpbWVzY2FsZURCIChubyBjb2x1bW5zdG9yZSkiOmZhbHNlLCJUaW1lc2NhbGVEQiI6ZmFsc2UsIlRpbnliaXJkIChGcmVlIFRyaWFsKSI6ZmFsc2UsIlVtYnJhIjpmYWxzZSwiVmljdG9yaWFMb2dzIjpmYWxzZX0sInR5cGUiOnsiQyI6dHJ1ZSwiY29sdW1uLW9yaWVudGVkIjp0cnVlLCJQb3N0Z3JlU1FMIGNvbXBhdGlibGUiOnRydWUsIm1hbmFnZWQiOnRydWUsImdjcCI6dHJ1ZSwic3RhdGVsZXNzIjp0cnVlLCJKYXZhIjp0cnVlLCJDKysiOnRydWUsIk15U1FMIGNvbXBhdGlibGUiOnRydWUsInJvdy1vcmllbnRlZCI6dHJ1ZSwiQ2xpY2tIb3VzZSBkZXJpdmF0aXZlIjp0cnVlLCJlbWJlZGRlZCI6dHJ1ZSwic2VydmVybGVzcyI6dHJ1ZSwiZGF0YWZyYW1lIjp0cnVlLCJhd3MiOnRydWUsImF6dXJlIjp0cnVlLCJhbmFseXRpY2FsIjp0cnVlLCJSdXN0Ijp0cnVlLCJzZWFyY2giOnRydWUsImRvY3VtZW50Ijp0cnVlLCJHbyI6dHJ1ZSwic29tZXdoYXQgUG9zdGdyZVNRTCBjb21wYXRpYmxlIjp0cnVlLCJEYXRhRnJhbWUiOnRydWUsInBhcnF1ZXQiOnRydWUsInRpbWUtc2VyaWVzIjp0cnVlfSwibWFjaGluZSI6eyIxNiB2Q1BVIDEyOEdCIjp0cnVlLCI4IHZDUFUgNjRHQiI6dHJ1ZSwic2VydmVybGVzcyI6dHJ1ZSwiMTZhY3UiOnRydWUsImM2YS40eGxhcmdlLCA1MDBnYiBncDIiOnRydWUsIkwiOnRydWUsIk0iOnRydWUsIlMiOnRydWUsIlhTIjp0cnVlLCJjNmEubWV0YWwsIDUwMGdiIGdwMiI6dHJ1ZSwiMTJHaUIsIDEgcmVwbGljYShzKSI6dHJ1ZSwiOEdpQiwgMSByZXBsaWNhKHMpIjp0cnVlLCIxMkdpQiwgMiByZXBsaWNhKHMpIjp0cnVlLCIxMjBHaUIsIDIgcmVwbGljYShzKSI6dHJ1ZSwiMTZHaUIsIDIgcmVwbGljYShzKSI6dHJ1ZSwiMjM2R2lCLCAyIHJlcGxpY2EocykiOnRydWUsIjMyR2lCLCAyIHJlcGxpY2EocykiOnRydWUsIjY0R2lCLCAyIHJlcGxpY2EocykiOnRydWUsIjhHaUIsIDIgcmVwbGljYShzKSI6dHJ1ZSwiMTJHaUIsIDMgcmVwbGljYShzKSI6dHJ1ZSwiMTIwR2lCLCAzIHJlcGxpY2EocykiOnRydWUsIjE2R2lCLCAzIHJlcGxpY2EocykiOnRydWUsIjIzNkdpQiwgMyByZXBsaWNhKHMpIjp0cnVlLCIzMkdpQiwgMyByZXBsaWNhKHMpIjp0cnVlLCI2NEdpQiwgMyByZXBsaWNhKHMpIjp0cnVlLCI4R2lCLCAzIHJlcGxpY2EocykiOnRydWUsImM1bi40eGxhcmdlLCA1MDBnYiBncDIiOnRydWUsIkFuYWx5dGljcy0yNTZHQiAoNjQgdkNvcmVzLCAyNTYgR0IpIjp0cnVlLCJjNS40eGxhcmdlLCA1MDBnYiBncDIiOnRydWUsImM2YS40eGxhcmdlLCAxNTAwZ2IgZ3AyIjp0cnVlLCJjbG91ZCI6dHJ1ZSwiZGMyLjh4bGFyZ2UiOnRydWUsInJhMy4xNnhsYXJnZSI6dHJ1ZSwicmEzLjR4bGFyZ2UiOnRydWUsInJhMy54bHBsdXMiOnRydWUsIlMyIjp0cnVlLCJTMjQiOnRydWUsIjJYTCI6dHJ1ZSwiM1hMIjp0cnVlLCI0WEwiOnRydWUsIlhMIjp0cnVlLCJMMSAtIDE2Q1BVIDMyR0IiOnRydWUsImM2YS40eGxhcmdlLCA1MDBnYiBncDMiOnRydWUsIjE2IHZDUFUgNjRHQiI6dHJ1ZSwiNCB2Q1BVIDE2R0IiOnRydWUsIjggdkNQVSAzMkdCIjp0cnVlfSwiY2x1c3Rlcl9zaXplIjp7IjEiOnRydWUsIjIiOnRydWUsIjMiOnRydWUsIjQiOnRydWUsIjgiOnRydWUsIjE2Ijp0cnVlLCIzMiI6dHJ1ZSwiNjQiOnRydWUsIjEyOCI6dHJ1ZSwic2VydmVybGVzcyI6dHJ1ZSwidW5kZWZpbmVkIjp0cnVlfSwibWV0cmljIjoiaG90IiwicXVlcmllcyI6W3RydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWVdfQ== https://benchmark.clickhouse.com/#eyJzeXN0ZW0iOnsiQWxsb3lEQi...
- apitman 2y agoThis tidbit caught my eye: > Apple runs the world's largest known number of Cassandra/ScyllaDB instances with roughly 300,000 nodes as of 2022 The Big Tech approach to web services is like everyone piling on a cruise ship to commute to work. Certainly brings some conveniences, but the complexity is insane and overall it feels absurd. The relatively new DB-per-tenant type approaches described in this article are more like renting/leasing a car, or maybe riding the bus to work. Definitely a step in the right direction. The future will be a mix of these solutions, and selfhosting/indiehosting. Indiehosting is like owning a car. Once the software is as reliable as cars are, many more people will do it.
- NathanFlurry 2y agoAuthor here, love this take. I've chatted with a few medium-sized companies looking at Durable Objects for this reason. DB-per-tentant removes much of the need for another dedicated team to provision & maintain infrastructure for the services. It's almost like what microservices were trying to be but fell woefully short of achieving. It's disappointing (but understandable) that "serverless" received a bad rap. It's never going to fully replace traditional infrastructure, but it does solve a lot of problems.
- renegat0x0 2y agoMight be a little bit off topic. I created a web page, with data. I didn't want to host VPS, be charged for traffic. I do not want also to play with cloudflare and self-hosting. My solution? The app is a webpage, which reads SQLite. If user wants to use app, the database is downloaded, unpacked, and used on users device. Links: - https://github.com/rumca-js/Internet-Places-Database https://github.com/rumca-js/Internet-Places-Database - search.html provides a preview for my database file (but code supports also reading zip file). - https://rumca-js.github.io/search?file=top&page=1&search=neocities.org https://rumca-js.github.io/search?file=top&page=1&search=neo... - uses JSON files stored in a zip file. Will be replaced soon with zip + sqlite file - https://rumca-js.github.io/search?file=music&view_display_type=gallery&view_show_icons=true https://rumca-js.github.io/search?file=music&view_display_ty... - example showing my fav music. As above uses JSON files, in a zip file
- IshKebab 2y agoI mean if you only have a few thousand records you barely need a database at all.
- x-complexity 2y ago> I mean if you only have a few thousand records you barely need a database at all. Keyword being "barely". There are organization benefits if you can structure your data into a DB, instead of having each page redundantly hold the same header & metadata info.
- renegat0x0 2y agoPreviously I have been using JSON. However there are multiple structures with relations between them so... this seems to be a database. Extracting data from it also becomes really really easy with selects. Otherwise I would have to implement, or reuse some algorithms to filter JSON data, etc.
- billywhizz 2y agothis is nice. i like the idea which has been tried in a few places of running sqlite in the browser directly/locally. the only thing that is really missing to make this work at a bigger scale for read-heavy databases is a very cheap or free static hosting service which does range requests, allows you control of CORS and doesn't have the file size limitations of gist or github pages. maybe this exists already? S3 would do i guess? you can do kinda magic things like this and build websites that connect to multiple different databases around the web and... well, i'll leave the rest up to your imagination. go here: https://just.billywhizz.io/sqlite/squeel/ https://just.billywhizz.io/sqlite/squeel/ hit CTRL/CMD + Q on your keyboard. paste in this sql ``` attach database 'https://raw.githubusercontent.com/just-js/just.billywhizz.io/73c93bbaf6599cdf425f288d00dc4ab7f0f89800/sqlite/demo/db/chinook.db https://raw.githubusercontent.com/just-js/just.billywhizz.io...' as chinook ; select * from albums ; ``` and hit CTRL/CMD + g to run the queries.
- mickeyben 2y ago> No built-in cross-database querying, making complex analytics difficult without a dedicated data lake I've looked at Turso before and this is exactly what came to mind. I do see some use cases where it could work, but for most real-world applications, this is an absolute red flag that shouldn't be overlooked.
- NathanFlurry 2y agoThis seems to be the biggest hesitation I've heard over and over by far. There absolutely needs to be a good story here for both (a) ad-hoc cross-partition queries and (b) automatically building a datalake without having to know what ETL stands for. However, this isn't so much different from Cassandra/DynamoDB which have a similar problem. You _can_ query cross-partition, but it's strongly discouraged and will strain any reasonably sized cluster.
- c4pt0r 2y agoI'm curious if it matters if it's sqlite since it's already a serverless product
- NathanFlurry 2y agoIf you care only about serverless, databases like PlanetScale, CockroachDB Cloud, and DynamoDB work well. The biggest strength of using SQLite here is that it provides the benefits of a familiar SQL environment with the scaling benefits of Cassandra/DynamoDB.
- 0xbadcafebee 2y agoStill in the peak of inflated expectations, I see. Give it 2-3 more years to get to the trough of disillusionment.
- NathanFlurry 2y agoHopefully, it matures into a healthy open-source ecosystem that doesn’t rely on proprietary databases. More companies than people realize are already building and scaling with DO SQLite or Turso internally. Almost every company I've talked to that chooses Postgres hits scaling issues around Series A — these companies aren’t.
- 0xbadcafebee 2y agoIt's nice to have open source things. But not every open source thing is the right thing to use. You have to use systems design skills to pick a solution that is correct for your use case. "SQLite but distributed" is like NoSQL during its hype cycle: a good choice for one or two use cases, a bad choice for most every other use case, yet people are choosing it for every use case, because it's trendy and has lots of marketing hype. It appears to work at first, so people rejoice and make bold claims about it solving every problem in the universe. It's only later that the poor design choice bites them in the butt and they go looking for the next trendy thing.
- simonw 2y ago"Better At Hyper-Scale Than Micro-Scale" That's a slightly misleading headline, because it can be interpreted as implying SQLite is BAD at micro-scale. That's not an argument the rest of the piece makes - it's more about how SQLite is great at large scale and people who focus on the small-scale aspects might miss that.
- NathanFlurry 2y agoFair point, noted.
- eduction 2y agoThis is really bad. DBs other than sqlite need their own dedicated servers, it says. A good way to store online chats is db-per-chat, it says. Sqlite scales great with [fusilade of very specific proprietary cloud services], it says. Sqlite is great but companies like this seem determined to ruin it. MongoDB all over again.
- NathanFlurry 2y agoAuthor here. To clarify — is your concern that the only scaling options I listed are proprietary services? If so, I completely agree. This article was inspired by a tool we're building internally, based on the same architecture. We knew this was the right approach, but we refuse to rely on proprietary databases, so we built our own in-house. We’re planning to open-source it soon.
- rstuart4133 2y agoJust a single data point but... I am looking at replacing a custom database with SQL. The application will only ever run on one server, so Sqlite3 was a contender. The database is very read mostly, which makes it a strong contender. The custom database is extraordinary fast at some things, so it's a complex decision. I needed benchmarks. I chose something that mirrors how data is accessed in the custom database. The existing database does have several tables, but it's hard to use so most accesses are single table, indexed by rowid, with maybe 100,000,000 rows. So I benchmarked a single table, indexed on primary key only, 100,000,000 small rows. Since it was easy because I could use the same SQL I benchmarked both Sqlite3 and Postgresql. I expected Sqlite3 to beat Postgresql on single row access / updates, and Postgresql3 to get the upper hand on multi row access / updates. I was surprised to see Sqlite3 was about twice as fast as Postgresql on everything, including inserting the 100,000,000 records. It was always single writer. Had I tested multiple writers I expect I would have seen really flex its muscles. In case your wondering, the custom database is 100 to 1,000 times faster than Sqlite3 at accessing a single record. Getting that performance was achieved with some big tradeoffs, which make it so hard to code for it creates reliability problems.
- simlevesque 2y agoDid you try with duckdb ?
- rstuart4133 2y agoNot yet. It's on my list. Mostly out of curiosity, because it isn't an OLAP application. While it is read mostly there are single row updates, just not enough to make multiple writers a frequent event.
- remram 2y agoIs that SQLite with default settings? There are a few tunables for SQLite like mmap_size and page_size which help in most situations: https://sqlite.org/mmap.html https://sqlite.org/mmap.html (as well as journal_mode=wal)
- 2y ago
- PaulHoule 2y agoI rebuilt my home server a few weeks ago and the thing I was most anxious about getting working was the Ubiquiti controller which controls maybe 5 access points and managed hubs in my house. The damn thing depends on mongodb and, worse, an old version of mongodb. I found a script that installed it all but it seems a system like that Ubiquiti controller really ought to use sqlite for a "just works" experience. For a while I thought maybe mongo was necessary if you had a really big installation with 10,000+ access points but if this article is right, sqlite ought to be good for the biggest installations out there.
- briHass 2y agoBeen there, have the scars from googling mongo error log messages to prove it. I've been pleased at work to see some on-prem server apps switching to SQLite recently. Most recent that comes to mind is EFT Server (Windows SFTP/FTPS server), which switched from SQL Server Express to SQLite. SSE always makes me groan: huge install, pain to manage, etc. SQLite is just so easy to copy files around and make backups. I'm sure it's probably faster also.
- qingcharles 2y agoI've been switching all the projects I had running on SSE to SQLite. What a difference in ease of maintenance!
- z3ratul163071 2y agoUsing mongodb for the controller was a very poor decision. I went through similar issues migrating my controller :(
- talkingtab 2y agoI have a fair amount of PostgreSQL experience at this point. Enough so the advantages of using SQL and a database are very clear to me. But PostgreSQL is a bulldozer and sometimes I want the tactical benefits of an SQL db in situations where installation of PostgreSQL is not warranted. Throw away, small scale, experimental, etc. SQLite is doing very well for my use cases. Especially using the experimental Node.js version - all I have been using. So I would highly recommend using it when you get to the point of moving data around. On the other hand there is one big thing. And this may be only the Node.js version. But several times when I have been doing development work my file based database has become corrupted. I violated some constraint, the sql was poorly formed, etc. Always my fault. I simply remove the file that SQLite is using and create a new one or use a backup copy. The thing is that I have never seen a PostgreSQL DB become corrupt. No matter what horrendous code I wrote. Never. I have not really appreciated how completely bullet proof it has been for me. Just my 2 cents worth.
- MyOutfitIsVague 2y ago> But several times when I have been doing development work my file based database has become corrupted. I violated some constraint, the sql was poorly formed, etc. Always my fault. I simply remove the file that SQLite is using and create a new one or use a backup copy. That really shouldn't be able to happen unless you're using the API wrong or have weakened safety with pragmas or database configurations that trade off resilience for performance. Even with crashes, attempts to violate constraints, poorly formed SQL, etc, the file should never become corrupt. If it does, that's a bug with SQLite, not you. Corruption bugs are exceptionally rare in SQLite these days.
- SomeUserName432 2y ago> Corruption bugs are exceptionally rare in SQLite these days. I develop a third party software and I receive reports of corrupted SQLite databases roughly every 2 months. However, without any reproducibility, it might very well be due to hardware faults etc. Still beats having end-users install something like postgres.
- talkingtab 2y ago
- shipp02 2y agoIf anyone has heard Joe Armstrong's talk about how communication is limited by latency and data can only travel so fast. I think having smaller a partitions locally is an optimal point. If You want global consistency then you'll have to either spend some time at runtime to achieve it, Have complicated protocols, fast networking, synchronized clocks. Does this look like actor model (from Erlang) if you squint a bit?
- chasemp 2y agoI was thinking something very similar. Once you've accepted any need at all for global state the next move is to reorient to minimizing it with horizontally scalable point local state and a small targeting dataset and tiered caching system.
- NathanFlurry 2y agoAuthor here! I agree it's very similar to the actor model, but I kept the article's scope small, so I didn’t cover that. In fact – Durable Objects talks a bit about its parallels with the actor model here: https://developers.cloudflare.com/durable-objects/what-are-durable-objects/#actor-programming-model https://developers.cloudflare.com/durable-objects/what-are-d... You might also appreciate this talk on building a loosely related architecture using Erlang, though it doesn't implement an actor-per-database pattern – https://www.youtube.com/watch?v=huGVdGLBJEo https://www.youtube.com/watch?v=huGVdGLBJEo
- runako 2y agoNotable that Ruby on Rails recently shipped its 8.0 release, which had as one of its pillars extending SQLite support to replace cache and job queue components, and to make it production-ready for common types of Web apps. https://rubyonrails.org/2024/11/7/rails-8-no-paas-required https://rubyonrails.org/2024/11/7/rails-8-no-paas-required
- DeathArrow 2y agoHow you solve load balancing, availability, replication and backups with SQL lite? If running using an orchestration framework like Kubernetes, each container in a pod will have its own SQlite database. How can we assure the data is consistent across pods?
- nicky0 2y agoI think of SQLite on the server as something that you would use when running in a traditional server (VPS or dedicated host) setup rather than an orchestrated, container-based one.
- codethief 2y agoI remember there being an HN post a month or two ago about a solution to this exact problem and people using it seemed very happy with it.
- curtisszmania 2y ago[dead]
- vetrom 2y agoIn my experience, in a multiuser environment SQLite falls over pretty hard, due to a lack of anything like MVCC. I'm aware that there a bunch of sqlite-compatible implementations and extensions that add MVCC. How close are those to actually being SQLite? Are they just a totally different table storage and transaction engine sitting behind SQLite's SQL parser/compiler and VM?