Hacker News Reader

Comment by bazmattaz | Hacker News Reader

I thought that SQLlite databases are not suitable for large multi user websites. Something got to do with only being able to handles one transaction at a time. Isn’t that right?

View on HN

Sure, but you can have raised millions with large numbers of customers before this is an issue. Having multiple customers using your platform at the same time to the extent that their transactions actually end up having to wait a meaningful amount of time requires a lot of usage that you often don’t reach for a long long time.

Or just having any long OLAP queries mixed into your OLTP workload. A readers-writer lock can often end up with tons of writers waiting on one last long read to drain.

SQLite WAL mode means readers and writer don't block each other: https://www.sqlite.org/wal.html

Since it's SQLite, it's also trivial to just copy the database file as a snapshot for separate OLAP queries if necessary.

Not all OLAP queries are for reporting. Sometimes they drive OLTP processes — really complex, separate-statements but same-transaction equivalents to "INSERT/UPDATE/DELETE ... WHERE" queries.

Of course, you can still drive those using a separate snapshot for the read side, to generate an ID set for a follow-up write on the business layer. But now you're doing CQRS — less trivial. You're not getting any of the ACID benefits of doing the OLAP and OLTP queries together in a single transaction; so your data model has to be designed for that (by e.g. having all tables be temporal tables.)

This is sounds pretty great. Is there though that much of a dev ramp up increase from using SQLite over MySQL/PGSQL though?

SQLlite can do handle concurrent reads, writes are a problem.

A site like this which is mostly a read-only archive is a perfect use case for SQLite.

With the amount of memory these days, the whole SQLite db would be cached in memory. Read heavy application is not a problem at all.

Yeah at that point you're basically as fast as redis or any other good binary tree datastructure store.

Define large. I'd guess sqlite can do 10-100K TPS, no problem.

You'll want a read cache to avoid repeatedly rendering the same HTML, etc in the frontend, where most CPU is spent. That'll accidentally reduce database load.

Assuming 10% writes (which is really, really write heavy), that'll get you to 1M page views per second. After that, you'll need to rearchitect.

(MySQL and Postgres are probably better choices, but I'd evaluate all three if I was setting this up for real.)

Just because you can use something doesn’t mean you should use it.

If you have 10k writes a second and 90k reads a second then SQLite is an awful choice.

Have you benchmarked it?

Given modern commodity servers and no network overhead I’d buy it not being a problem.

My intuition is you are right but given the operational simplicity improvements it would be worth exploring.

This. Like I said, postgres or mysql are probably better choices. However, modern hardware (especially small sync disk writes) is really fast,and sqlite has its own strong points.

I think this is kind of their point. If you're starting out, you may not need to meet the same considerations as a "large multi user website". So you don't get stuck trying to do that - you start with what you need (appropriately) and adjust down the line.

It is hard to do right.

The problem is you have SQLite which works some of the time and Postgres which works all of the time. Postgres is a great option for websites of all sizes. There is vanishingly few reasons to ever pick SQLite for a website. When the only real advantage is not having to run a second process which only really matters for local apps.

SQLite only allows one writer at a time. Attempts at multiple DML logins will not block until successful, but simply fail.

The allowed writer can also impact readers, depending on the WAL mode setting.

For DSS uses, where a data store is published once a day, SQLite is wonderful. For OLTP, look elsewhere.

> Attempts at multiple DML logins will not block until successful, but simply fail.

sqlite has a "busy handler" mechanism which tells it how to respond to blocks and most apps use the builtin one with which they can tell it "wait for X milliseconds before failing on a lock."

The Fossil SCM (https://fossil-scm.org), the SCM in which sqlite itself is hosted, is an sqlite application which serves thousands of users per day, many of them in write transactions. Its forum is a separate sqlite3 instance. sqlite3's own forum is another... all of those serve many users, many of whom are writing, and in 14 years of using/contributing to fossil-scm.org, almost on a daily basis, i've hit exactly two locking errors.

The Single Writer Principle is probably the most important principle for concurrent systems design. Often it’s best to design wholly around it.

In this case, I doubt there are any writers at all.

I haven't gotten much past the planning phase but I'm thinking it would be a pretty good fit to use Erlang (Elixir in my case) and sqlite together, which should make SWP pretty straightforward, which in addition to offloading some of the concurrency work off of sqlite, also should make it fairly straightforward to swap out for postgres. If in fact I ever hit that point.

Phoenix now has sqlite3 as an option when running phx.new. I just played with it yesterday to also checkout litespeed syncing the database to backblaze. It was very easy to setup and test by running phx.gen.auth and monitoring the data being synced to b2. There is a blog post about running SQLite in dev and Postgres in production using Phoenix, much like rails does. It’s worth spending a few minutes checking it all out. I have a project in mind where I think it would be a good fit.

Somewhere along the way, we seem to have forgotten that we have processors that can do 3 billion mathematical operations per second.

I'm currently trying to shame some people at work into acting on the fact that they wrote some code that should take about 1µs per call that is instead taking over 100. If you're stupid with cycles then you get to be stupid with cores too, and then you get to be stupid with locking semantics.

Unless you are working in aerospace, military, or similar shaming people seems uncouth and perhaps not the best way to inspire meaningful change?

Shame is somewhere around #5 on a list, and never for subordinates.

Also I’ve worked on commercial aviation software, and maybe three of us even knew the meaning of the word, so I’m curious what domain you saw it in.

If its mostly read then it's fine

Reply on news.ycombinator.com