Hacker News Reader

Comment by ben509 | Hacker News Reader

> There is a popular opinion among developers that SQLite is not suitable for the web, because it doesn’t support concurrent access.

No, the issue is it doesn't have high availability features: failover, snapshots, concurrent backups, etc. (Edit: oops, comment pointed out it does have concurrent backups.)

SQLite isn't a toy DBMS, it's an extremely capable embedded DBMS. An embedded DBMS is geared towards serving a single purpose-built client, which is great for a desktop application that wants a reliable way to store user data.

Once you have multiple clients being developed and running concurrently, and you have production data (customer accounts that are effectively legal documents that must be preserved at all times) you want that DBMS to be an independent component. It's not principally about the concurrent performance, rather it's the administrative tasks.

That requires a level of configuration and control that is contrary to the mission of SQLite to be embedded. They don't, and shouldn't, add that kind of functionality.

View on HN

I think that the absence of 'high availability' is not an issue for small websites or web apps. Transactions are ACID, concurrent readers are fully supported. Backups and administrative tasks are super-easy.

Also if you really need HA there are nice projects like https://dqlite.io/

Looking at https://dqlite.io/docs/protocol it seems like it does not allow for embedded usage though. Doesn't this turn it into more of a traditional client-server RDBMS?

From the home page:

> Dqlite is a fast, embedded, persistent SQL database with Raft consensus that is perfect for fault-tolerant IoT and Edge devices.

Looking at the archtecture page [0] on that same website the second heading is "Embedding".

[0] https://dqlite.io/docs/architecture

Ah, right, I should have looked deeper!

I just looked, and the entire database for my webapp (which typically serves around ~50 people) is 139k. As you say, you can do a data dump quickly and safely; at that size, you could afford to just dump the entire DB every hour and keep the last year's worth of snapshots if you wanted.

https://litestream.io/ That's exactly what litestream accomplishes.

That's close. Litestream takes a snapshot of the database and then continuously replicates out WAL frames. On restore, it grabs the snapshot and replays all those WAL frames (or all the frames up to a certain time if you want point-in-time recovery).

I see availability as orthogonal issue to scale/size. You might be processing one transaction per day but it can still be super important that the service is available for that one transaction.

How do backups work? Is there a locking mechanism to prevent file corruption if the file is copied during writing?

It supports multiple readers, and writes are atomic - no reader should ever get corrupt data just because there is a write happening in parallel. You probably should not use the OS to copy the live db file, rather, have an in-process thread that reads the db and writes to a backup location periodically.

Exactly, many forget the little detail that they should use the backup feature, rather than just copy the database file.

You can also combine file system snapshots to get versioning which would allow you to pull the db file out of the latest snapshot (right after taking it) and send it somewhere

So if you're saying everything does support a web database... then what's the reason people aren't using it for websites?

Why do you say it works for "small" websites but presumably not large ones? If it's not transactions, concurrent reading, backups, or administrative tasks... then what's the issue you run into?

Genuinely curious... I'm wondering if everything I've heard about "don't use SQLite for websites" is wrong, or when it's right?

If writes are infrequent I think it might be fine. Otherwise I’d be wary.

I think one valid fear is that a web application might, for multiple reasons, scale beyond a single process on a single server. This is common enough that using an embedded database can be problematic, compared to a separate RDBMS process that can be shared between processes. Some web applications are just never going to scale out for any reason, and for those SQLite might be appropriate.

Agreed, but it’s not only a question of scaling. Any app with high uptime requirements will need more than a single process from day 1.

It depends on what you consider high uptime. You can achieve 99.95% uptime with 4h of downtime a year. A lot of downtime occurs because of overly complicated systems so running a single process on a single server can give you relatively high uptime.

I just want to be able to deploy updates during business hours without downtime. Hard to do with a single process.

Most web servers support greaceful restarts, i.e. reloading code and configuration without downtime. Usually you just send a pre-specified and documented signal to the process to do it.

For a web server serving from the filesystem, sure. That doesn't help you deploy a new version of a web application you're making code changes to (which is the kind of thing that would need a database).

I was actually talking about both. For example for python, which is what I usually use, both uwsgi and gunicorn support graceful restarts. Admittedly I don't have much experience with other languages, but it's technically possible. If your application doesn't support it out of the box, you can still achieve it with something like systemd's socket activation.

> then what's the reason people aren't using it for websites?

I'd guess the reason to be that people keep hearing things like "don't use SQLite for websites" and thus don't even try.

> Why do you say it works for "small" websites but presumably not large ones?

Not the GP, but the main reason I wouldn't use SQLite for a large website is that SQLite itself doesn't offer much re: failover/replication (i.e. multiple servers, one database), and I haven't used RQLite enough (or at all; I should fix that) to be comfortable with it in production. Because of that, I'm more likely to reach for / recommend PostgreSQL instead.

That being said, if your website has crazy "web scale" FAANGesque needs and you're at the point where you need to write your own replicated datastore, using SQLite as a base and building your own replication layer on top of it (or using RQLite and maybe adjusting it for your needs) seems like a reasonable way to go.

> That being said, if your website has crazy "web scale" FAANGesque needs and you're at the point where you need to write your own replicated datastore, using SQLite as a base and building your own replication layer on top of it (or using RQLite and maybe adjusting it for your needs) seems like a reasonable way to go.

Exactly what Bloomberg did with Comdb2: https://github.com/bloomberg/comdb2

I'm in the process of adding read replication to Litestream[1] so folks can scale out the read-side of their SQLite applications to multiple nodes (or replicate to edge nodes for low-latency).

[1]: https://litestream.io/

Amazing. This was missing for my use-case and I gave up on LiteStream because of it but now it seems I have to revisit!

Wonderful job.

Neat. I'll have to give that a whirl.

Would this enable any node to be a writer (i.e. would it lock the DB across all nodes)? Or would I have to designate some "master" server with exclusive write access and have any other servers forward write requests to that server?

It would be the latter. You'd need to have a single primary and replicas would need to redirect writes to that node.

Crazy web scale folks flip from Mongo (NoSQL) to Postgres: https://www.theguardian.com/info/2018/nov/30/bye-bye-mongo-h...

Great article. It took them 10 months to successfully migrate! No mention of how many man-hours, but probably a lot.

Depending on the use case (SaaS offerings with per customer shards) you can actually scale sqlite quite high. Expensify scaled to 4 mio qps (queries per second) [1], so if one of your customers is exceeding that, you better be looking at another DBMS but below that - well sqlite is an option.

[1] https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...

The primary reason for me is that Postgres has stronger default constraints. If you care about keeping your data logically consistent then Postgres has more of that out of the box.

SQLite just makes the tradeoff to be simpler since often it doesn't matter. But don't make the mistake that it doesn't matter. Since PG helps avoid data problems and you might need to scale out web servers that is why Django for instance recommends switching to Postgres (or whatever you're actually going to use) ASAP cause there are differences. You may end up relying on PG to reject things SQLite doesn't care about by default. SQLite might let you get away with inserting data which PG refuses to handle.

Not to mention the DB specific features can differ. Like PG's JSON field types or etc.

Not sure why you were downvoted because that is a legitimate concern and it's my only beef with sqlite3. I will kill for an embedded PostgreSQL. If sqlite3 becomes that I'll absolutely pay for a license if they require it.

Check out DuckDB! It is designed for OLAP instead of OLTP, but it uses Postgres syntax and types! It's columnar and lightning fast for big queries.

Been looking for an excuse to use it, this might just be the one. :)

I would use SQLite on a single-machine web server. However, I prefer to build my web stuff as stateless machine images that connect to a separate database instance, because it lets me scale them independently.

I should revisit this policy now that you can run a truly huge site off a 1U slot (I work for an alexa top10k, and our compute would fit comfortably in 1U); computers are so fast that vertical scaling is probably a viable option.

That's exactly what I am leaning to in my current job. Looking at the load of our servers I can comfortably put all our software on my gaming machine (which is mid-range!) -- with the DB included -- and I bet no request ever will be above 100ms.

IMO a lot of organizations should start re-investing in on-premise as well. Having a mid-range AMD EPYC server can serve most businesses out there without ever having more than 40% CPU usage.

That, plus scaling down Kubernetes clusters. Most companies absolutely didn't need them in the first place.

I don’t know about on-prem for serving external workloads; getting a reliable, redundant, high-speed connection can cost far more than paying for colocation at a DC that’ll do at least as good a job. If you’re lucky they’ll even install fire suppression systems.

Yep, can't argue with that.

Still, for the cloud I started preferring going for Rust (where applicable!) and optimizing the hell out of the performance hot-spots. So far the results have only been crushing successes. Horizontal scaling has been employed only as a means of backup instances with a load balancer in front of them. And for blue/green deployments.

Horizontal scaling hasn't been at all necessary otherwise. A $25 instance never got north of 15% CPU for one of the services I rewrote. I/O utilization never got beyond 60% and is usually 3-5%.

But I can agree that the cloud has unquestionable benefits. It's just that I feel that their number is gradually dwindling.

Sqlite is catching up quickly with SQL support, but like with any fast moving technology - unless they're really interested in it - most of devs are lagging few years behind with their knowledge on what can be done with it.

For instance I remember that at some point sqlite didn't have foreign keys, so I couldn't run my existing migrations on it (without rewriting everything) which was a big issue for me at the time. Now I see that they've added the support for referential integrity in the meanwhile, but I had no idea about it because simply there's so many other libs and technologies to follow - one just can't keep track of every single tool in the world obviously - so some great tools just fall out of focus.

My guess is the word will slowly get out and in a few years people will probably shift to using it more in a web world - but it will take some time.

I ran it once on an NFS disk. There must be some file locking happening I think. But I could read from multiple nodes, but for some reason the delete was failing (could not delete a database). Did not test the write as my app at that time was writing from one location only. Anyone tested such HA scenario?

It also doesn't have strong/static typing (it's dynamically typed) so you have to typecheck your inputs or do type coercion upon read.

And it doesn't have a native date type. Date handling has to be handled at the application layer. It can be tricky to do massive time-series calculations or date-based aggregations.

You can use integers or text types to represent dates, but this open-endedness means you can't share your db because everyone implements their own datetime representation.

This. I'm working on a hobby project that is essentially a web app for personal use. I started with sqlite because it was the simplest to start with, but I'm about to reluctantly migrate to postgres. Why? Constraints, data types, and programmability.

In terms of scale, sqlite is just fine. But I am tired of fiddling with the dates, it's too easy for bugs to sneak into my code, and I want to use table valued functions to essentially parameterize views instead of having to build complex queries in the app layer.

If your web app is mostly reading and writing single rows, yeah, sqlite is just fine. But if there's substantial and complex logic involved, it has its limits.

Datetimes really are the achilles heel of SQLite.

The JSON extension library is amazing and works well. If SQLite were to grow a first-rate RFC 3339 library, one which could read from tz when available and do the things which strftime cant, acting on your choice of Unix timestamp and valid RFC 3339 date string, this would be a real boon to the ecosystem.

I haven't found typechecking inputs to be a real barrier. Sure, `val INTEGER CHECK (val = 0 or val = 1)` is a long way to spell `val BOOLEAN` but `CHECK json(metadata)` is a reasonable way to spell `metadata JSON`, and a similar function would surely exist for a SQLite datetime extension. You can do it now by coercing the string through an expected format, but that doesn't generalize well.

> If SQLite were to grow a first-rate RFC 3339 library, one which could read from tz when available and do the things which strftime cant, acting on your choice of Unix timestamp and valid RFC 3339 date string, this would be a real boon to the ecosystem.

OTOH, as an embedded database with freeform advisory typing and easy extensibility (for functions, etc.) in most host languages, it's not hard at all to get whatever you need for datetimes if you are using a host language that has a decent datetime library itself (and, as a bonus, you then don't have to worry about subtle differences between manipulations of datetimes through SQL and manipulations through other mechanisms in the app.)

Check out rqlite (https://github.com/rqlite/rqlite) for that functionality - "rqlite is a lightweight, distributed relational database, which uses SQLite as its storage engine. Forming a cluster is very straightforward, it gracefully handles leader elections, and tolerates failures of machines, including the leader. rqlite is available for Linux, macOS, and Microsoft Windows."

To me this is obviating a lot of the advantage of SQLite over any other RDBMS.

I thought the same, but maybe it's useful if you start with SQLite and decide to scale up to a distributed RDBMS without having to rewrite too much?

Two thoughts:

SQLAlchemy, at least, lets you switch from SQLite to Postgres in a configuration file.

Isn't there a useful SQL subset which allows you to switch from one database to another without rewriting? There seems to be such a subset for C, for example, which multiple compilers all interpret the same way, and SQL is a standardized language, too.

Yes, both of those are alternative solutions.

Using a wrapper like SQLAlchemy is probably an improvement for most medium and large projects, but it has costs of its own. Using portable ANSI SQL - above the Hello World level - is something basically nobody does unless they make a serious effort, lean on linting tools and forego some of the most useful features of their DBMS. sh is perhaps a closer comparison than C here: it's at least possible to write portable C accidentally.

And neither of these help you if you started with a SQLite database - an excellent choice for most - and decide that you need to support a dozen or a hundred concurrent users.

> Isn't there a useful SQL subset which allows you to switch from one database to another without rewriting?

Only if you're happy throwing away a lot of the features, which is taking away from what makes SQL an attractive solution in the first place. Plus, even some basic things aren't the same between different implementations (e.g., SELECT TOP 1 * FROM t vs SELECT * FROM t LIMIT 1), so you'd really have to be testing with different RDBMSes the whole way through to make sure you didn't accidentally break compatibility.

rqlite is a separate daemon written in Go, negating most of the reasons to choose Sqlite in the first place.

It absolutely has good use cases, but those are rather niche. You'll mostly be better off with the traditional postgres etc.

For web applications, having a separate daemon for sqlite is best practice anyway. SQLite doesn't play nice with multiple concurrent connections (costly locking, the possibility of data corruption). You typically need to offload transactions to a queue in a daemon thread.

I agree that high availability features are outside of the goals of an embedded database. There's an ecosystem of tools SQLite to provide these benefits though. There's dqlite & rqlite for providing HA over SQLite. I'm the author of Litestream[1] which provides streaming replication to S3 for SQLite databases so folks can safely run a single node instance.

[1]: https://litestream.io/

One could, for instance, run “static” sites with the data stored in a SQLite database. Update on write apps often can do okay with 90% uptime. It’s the “generate everything in every request” crowd that needs an HA solution and why do we keep doing this to ourselves?

Yes, static generation is a great way to go if that works for your use case. Throw it up on a CDN and you'll likely have five 9s of uptime. I don't think that's a viable solution for a lot of applications though.

Half of arguments on the Internet are someone trying to win an argument they lost elsewhere. If I could get some people I work with to actually run the numbers on how much it costs us to make everything dynamic, versus how much we earn versus static, I think we’d quickly arrive at an agreement that most of it should be static with a little AJAX thrown in for the 10% that needs to be special.

SQLite itself does not have those features, but at the disk level you can get those features:

On GCP:

- A regionally replicated disk will replicate writes synchronously to another zone (another data centre 100km away). This means when the SQLite write call returns, the data will be geographically replicated, and the second disk can be used as a fail over. This is all transparent to app/db.

- Disk snapshots are incremental, and are stored in Cloud Storage, which is geo replicated across regions (E.g. Europe and US).

In a way, this gets you even better failover and backups than traditional server DBMS's that have these features built in, and often need custom administration to ensure they are working.

> "No, the issue is it doesn't have high availability features: failover, snapshots, concurrent backups, etc."

I think it has concurrent backups via the backup api: https://www.sqlite.org/backup.html

I'd expect this to be unnecessary in WAL mode (which you should use, when possible), since WAL allows concurrent readers while permitting up to one writer. In the old undo-log mode (which remains the default for compatibility) writing excluded readers.

I don't think you can do naive file copy backups even in WAL mode. WAL will checkpoint the log into the main database file from time to time. You need to use the SQLite backup API to let you make a backup while blocking any potential checkpointing.

Sorry, I was unclear. I meant the "copy a few pages then sleep for some time to release the database lock" part. With WAL mode you should just be able to copy as fast as you can without disturbing other readers or the writer. SQLite also has a similar SQL built in, VACUUM INTO. That's nice because the backup API just copies all pages (iirc).

I think the biggest issue can be distilled to "lack of concurrent network access". You start getting into a lot hairy management problems with distributed file systems/copying files around.

Imo part of that is just web applications tend to be not very efficient when compared to something like unix cli utilities so to get performance you end up with potentially massive amounts of horizontal scaling

On the other hand, the lack of performance /usually/ buys you higher productivity so you can make product changes faster (you let a GC manage the memory to save time coding but introduce GC overhead)

Right, and Expensify plausibly solved this by wrapping SQLite to produce a different DB, BedrockDb. But that shouldn't imply at all that SQLite alone could satisfy main stream web applications.

> Imo part of that is just web applications tend to be not very efficient when compared to something like unix cli utilities so to get performance you end up with potentially massive amounts of horizontal scaling

It's less about performance than about resilience IMO. Of course if you don't have a proper HA datastore (master-master) then you kind of undermine that, but a datastore SPOF is better than the whole application being a SPOF.

>Once you have multiple clients being developed and running concurrently, and you have production data (customer accounts that are effectively legal documents that must be preserved at all times) you want that DBMS to be an independent component. It's not principally about the concurrent performance, rather it's the administrative tasks.

For my reporting/read-only use case https://datasette.io/ solves the above beautifully.

>No, the issue is it doesn't have high availability features: failover, snapshots, concurrent backups, etc. (Edit: oops, comment pointed out it does have concurrent backups.)

Actually, I think it's network access that is the issue. SQLite isn't really designed to have 4-5 network connected apps using it as a shared data store. In most shared hosting setups, database is treated as a network service, so you can have 200 different sites leveraging the same db server... and sometimes the db server isn't on the same hardware as the http server.

Not to misunderstand. It fully supports concurrent reads. And also concurrent reading while some other process is writing the same database.

What it doesn't support is concurrent writes only.

For replication of SQLite there are some options:

http://litereplica.io - single-master replication

http://litesync.io - multi-master replication

https://aergolite.aergo.io - highest security replication, using a small footprint blockchain

Have you used litereplica or litesync? It doesn't inspire confidence that their websites haven't been updated since 2016 and 2017 respectively.

Most things happen in the forums:

http://litesync.io/forum/

http://litereplica.io/forum/

A kludge---I forget from where---for low traffic sites is one SQLite DB per user.

Not sure I understand: sqlite is file based, so snapshots and concurrent backups are literally just file copies/backups.

I'd much rather SQLite not waste its time on implementing features they're not good at, leaving that to the tools we already have available for rolling file backups, instead spending their time and effort on offering the best file-based database system they can.

(Heck, even failover is just a file copy initiated "when your health check sees there's a problem")

Unfortunately backing up with a simply file copy operation isn't guaranteed to work if you have write traffic at the same time.

Instead you need to use the .backup mechanism or the VACCUM INTO command, both of which safely create a backup copy of your database in another file - which you can then move anywhere you like.

Depends on the file system. On Windows you can use shadow copy for example.

Oh man, I didn't know about VACCUM INTO, thanks!

Not really, it still works exactly as you should expect: writes are handled atomically, using a separate file, so if you copy the database file "mid-write" you're not actually copying a database in flux, you're copying the database before the write operation gets applied, and SQLite applies the write after the copy lock is gone.

The main issue here is time: because copying temporarily locks the db out of further changes, it will be "out of sync" if you naively believe that performing a write means your next call will see that data, and you bake that assumption into your code. While there's a wait, on modern hardware using SQLite for its intended purposes (any volume of reads, but low volume of writes), that's just not an issue.

There are other problems associated with file-based backup, of course, such as missing out on in-memory data, or corruptions caused by power outages, but those problems only exist if we were to copy a db once. Backups run regularly, and any data we miss out on the current pass, we'll get on the next pass.

Basically: when used for the purpose that SQLite was created for, file copies are a perfectly fine backup strategy that really only shows its limitations when you start to push your project into "SQLite isn't really appropriate here anymore" territory.

(having said that: the fact that VACUUM can be run into a new file is super nice, and everyone should know that it exists)

SQLite uses advisory locks but the `cp` command won't adhere to those so you can get a copy that contains only some of the writes from a transaction. If you're lucky, you just get some wonky data. If you're unlucky, you can get a corrupted copy.

Transactions are only atomic from the perspective of other transactions. Other processes or commands on the system can see partial state. This applies to both the rollback journal & the WAL modes.

File copies are also covered in their "How to Corrupt a SQLite Database File" web page[1].

[1]: https://www.sqlite.org/howtocorrupt.html#_backup_or_restore_...

Every database is ultimately on the file system but there’s a reason that method of backup is rarely used.

Postgres lets you write directly to block. In practice, nobody does this though.

Except not every database was designed from the ground up to BE a file, that's one of Sqlite's benefits. A database is by definition a single, fully self-contained file. Unless you go out of your way to make Postgres, MySQL, MariaDb, etc. do this, that is very much not the case.

For SQLite, "copying files", rolling file backups, shadow volumes etc. are by definition valid strategies when it comes to SQLite.

From the dbadmin POV and understandable. From the dev side, if you want to take a stab at your own failover, shards, concurrents and snaps, there may be no better platform to learn on. Writing a custom backend to do tricks with sqlite dlls is extremely satisfying.

I learned this the hard way when I stuck some app containers down on a few RPis and mapped the folder of stateful stuff (including a SQLite database) to an NFS share on my NAS.

It's....not great.

Can't remember where did I read this now but about a year ago I have found material saying that sqlite3 over NFS is a very bad idea and even though it's supported it's also strongly advised against as a use-case.

The FAQ suggests that NFS is problematic for concurrent access:

> But use caution: this locking mechanism might not work correctly if the database file is kept on an NFS filesystem. This is because fcntl() file locking is broken on many NFS implementations. You should avoid putting SQLite database files on NFS if multiple processes might try to access the file at the same time.

https://www.sqlite.org/faq.html#q5

Oh yeah, I read all that after I started seeing problems, I know I'm in the wrong here. It works just good enough that I haven't addressed it yet (I just kick over the process when things get weird, and the db usually recovers just fine).

no, those things have never stopped me. What has stopped me is that the default configuration for a Rails app throws intermittent exceptions when I'm trying to save, because concurrent writes fail and Rails (at least as of a couple years ago) does the wrong thing by default

Reply on news.ycombinator.com