Hacker News Reader

Comment by nalgeon | Hacker News Reader

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.

View on HN

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).

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 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.

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?

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

Reply on news.ycombinator.com