5 ms·
Uhm... experience from a large project that used SQLite was that we where hit with SQLite only allowing one write transaction at a time. That is madness for any
by dsincl12 4y ago
Uhm... experience from a large project that used SQLite was that we where hit with SQLite only allowing one write transaction at a time. That is madness for any web app really.
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?
- dagw 4y agoWhat am I missing? 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.
- unicornporn 4y agoSpeaking of this, I really wish there was SQLite support in WordPress...
- samwillis 4y agoA blog is the perfect example of where SQLite should be used other a DB server.
- quickthrower2 4y agoIf you chuck Varnish in front of it, does it matter what you use? 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.
- deleted 4y ago[deleted]
- beberlei 4y agouse more than one SQLite file? we have one per day and project for example.
- smt88 4y agoI don't know if you're joking or not, but this would just be reinventing the Postgres/SQL Server/Oracle/MySQL wheel using duct tape and wishes. If you're doing something that multiple systems have had millions of hours of development to do, just use one of those.
- daliusd 4y agoYou are wrong. Using multiple SQLite files solves quite many problems, e.g. let's say your user can have multiple products where each product has its own properties. This can be presented as 3 tables in SQL database: users, products, properties. Now with SQLite you can drop one table (users). This simplifies queries, depending on your situation you can use simpler indexes and etc. Extra benefit: you can allow your user to download all his SQLite db as per GDPR requirement.
- hu3 4y agoHow do you join data across hundreds of databases? And how is the query plan for indexes when doing so?
- kevingadd 4y agoMy understanding is that it just works. They're still all in the same process, and SQLite has all the information it needs to do joins. Transactions get more complex but are still atomic.
- daliusd 4y agoYou don’t.
- samatman 4y agoUsing ATTACH. Works a treat. SQLite query planner knows about ATTACH.
- jbverschoor 4y ago- Most transactions are read-only - "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/begin_concurrent.md 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.
- tidenly 4y agoWhy would I bake all of those assumptions and limitations into my system though just on the hope it won't ever become a problem
- whiddershins 4y agoHow hard is it to migrate if/when.
- danuker 4y agoDepending on your expected lifecycle of the app, you should use an adapter between the DB and the rest of your app. 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.
- ignoramous 4y ago> Depending on your expected lifecycle of the app, you should use an adapter between the DB and the rest of your app. A sane design, but realise that limitations tend to leak through abstractions.
- _fizz_buzz_ 4y agoThis questions goes both ways: Why put effort into something that might never even be a problem?
- samwillis 4y agoQuite right it’s not one size fits all but for any site that’s mostly read only it’s a brilliant solution. Simon Willison has written about it and coined the term “baked data”: https://simonwillison.net/2021/Jul/28/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.
- quickthrower2 4y agoThe confusion is probably a lot of us work at smaller companies that serve a wide solution to a niche customer, and that kind of app has a lot of reads and writes but doesn't need to scale. This app might be doing the invoicing/shipments/specialist parts of a business for example. 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.
- samwillis 4y agoEven for those small niche apps for businesses there are a huge number that are unlikely to be doing more than the 10-20 write transactions / second that SQLite can support.
- quickthrower2 4y agoProbably on average correct, but there can be bursts.
- masklinn 4y ago> Uhm... experience from a large project that used SQLite was that we where hit with SQLite only allowing one write transaction at a time. That is madness for any web app really. "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).
- pc86 4y agoIs there something you can point to that explains this "flush them to the durable DB once in a while" pattern in more detail?
- onelesd 4y agoInstead of running each small write query as the event occurs (a vote), collect them in memory and at some time interval (1m, 5m, or whatever your UX will tolerate) write them to the DB in batch with one (or a few) writes.
- jiux 4y agoThank you. This fits a current need of mine perfectly. Do you have any other helpful tips you’d like to share? Any thoughts between Redis and Memcache?
- hnfong 4y agoNot the GP, but if the GP described a scenario that is useful to you, redis can #1 act as the memory cache if the processes themselves don't last long enough for the optimization to be useful, and #2 can act as a cheap message queue to funnel similar requests into a single (or small number of) processes for aggregation before writing to disk.
- phaedrus 4y agoThe default settings of SQLite are very conservative and essentially enforce serial writes. With tuning and loosening that enforcement, you can go from 50 writes per second to 50,000. 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.
- andai 4y agohttps://www.sqlite.org/faq.html#q19 https://www.sqlite.org/faq.html#q19 >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.