Why do everyone seem so hyped on this when it can't really work properly IRL? If you have large amounts of data that need to be stored the app would die instantly, or leave all your users waiting for their changes to be saved.
What am I missing?
"Lots of readers few writers" is an extremely common application property tho. Your average HN has significantly more reads than it has writes, especially if you bump the "worthless" writes (e.g. vote counts) out of using the DB and only flush them to the durable DB one in a while, for instance.
And with SQLite's WAL support it's supported even better: while it still has a singular writer, the writer doesn't block the readers anymore, which is a workload issue in the exclusive case (as the single writer would bring read concurrency down to 0).
Do you have any other helpful tips you’d like to share? Any thoughts between Redis and Memcache?
- "Large" applications can usually be sharded by account. This means 1 file per account, and can easily be put on the most optimal geolocation of the account
- If you defer locking until commit, allowing multiple writers ( https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begi... ). This is good enough for most applications anayway.
- Sqlite simple, fast enough for almost anything, supports a good set of features and datatypes, is very easy to embed.
Have an interface made up of clear methods which you do all your business through. That way, if you want to change the DB, you only need to rewrite the adapter.
I believe SQLite is a great, low-insallation-needs portable DB you can start your project with. If it turns out your startup gets millions of users, you can afford to switch.
A sane design, but realise that limitations tend to leak through abstractions.
Why do you assume you'll run into problems? The moment you're running into problems, you better have a good plan with any RDBMS.
such a rewrite is a lot more predictable endeavor, then building the initial solution, that it's a great problem to have :)
meanwhile, your UI don't have to change and a lot of your other glue code or business-logic code don't have to change either, IF you haven't hardcoded direct calls to SQLite everywhere in your program :)
eg. I used HoneySQL with great success! My queries are safely assembled from Clojure data structures and I had a single function, which I used to format them to the desired SQL dialect H2DB/SQLite/MySQL/Postgres, execute them and parse the results back into Clojure data structures and even take care of lazily paginating through long result sets, without burdening the consumer of the data with such details.
Simon Willison has written about it and coined the term “baked data”: https://simonwillison.net/2021/Jul/28/baked-data/
Mozilla.org uses this architecture, Django app running off SQLite with the db rsync’ed to each application server.
Whereas there is another different kind of Engineering which I probably will never be a part of (simply due to mathematics of available positions doing it) where you are scaling something up for millions of users but the app is much simpler like a Twitter or Reddit, and the challenge is in the scaling.
Edit: forgot to mention that yes a major part of that is batching writes into fewer, bigger transactions; AFAIK you can't really get around that.
>INSERT is really slow - I can only do few dozen INSERTs per second
>Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second.
>By default, each INSERT statement is its own transaction. But if you surround multiple INSERT statements with BEGIN...COMMIT then all the inserts are grouped into a single transaction.
>Another option is to run PRAGMA synchronous=OFF. This command will cause SQLite to not wait on data to reach the disk surface, which will make write operations appear to be much faster. But if you lose power in the middle of a transaction, your database file might go corrupt.
Many sites are Read (almost) Only. For sites where users interactively query/view/explore the data, but (almost) never write their own, it works great.
Edit: was being serious: if your data is that static you can statically generate it. But I get that CMS is convenient so with that caching is where you get the performance win. A blog post either never updates or gets 1 or 2 edits max.
If you're doing something that multiple systems have had millions of hours of development to do, just use one of those.
Extra benefit: you can allow your user to download all his SQLite db as per GDPR requirement.
And how is the query plan for indexes when doing so?
Having several areas of concern managed by distinct databases, with some occasions which warrant joins across the domains, this is the more natural use pattern. ATTACH loses foreign key constraints and triggers, among other things.