Hacker News Reader

Comment by keewee7 | Hacker News Reader

I remember reading about a company that upgraded their production database from a JSON file to SQLite.

We really need this counter culture in software development that emphasis simplicity over the "Start with Kafka-on-k8s" madness.

View on HN

Tailscale https://tailscale.com/blog/database-for-2022/

But tailscale is a distributed, multi-region SaaS provider. It's easy for them to use SQLite.

What about a real-world workload? For example, I have 10 users a day on my new next.js app so I clearly need a RDS cluster for burst traffic.

Tailscale has some server components (account management etc.) that are powered with SQLite.

Can read more here: https://tailscale.com/blog/database-for-2022/

One of my websites has been using SQLite3 as a backend database since 2018ish and works perfectly fine.

I mean, aws makes it quite easy to run postgres so I don't see why not either, you don't need a dba more or less than with SQLite + rqlite. If you wanna run onprem you can usually do with one container for quite awhile too. I wouldn't call postgres a "complex dependency" anymore.

The existing replies to this comment of yours make it apparent why “/s” is still necessary :)

I find it hard to believe that someone would mistake the comment as not sarcastic if they read the second paragraph.

So if people comment based only on the first paragraph, would they even get to the /s in the end ?

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?

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.

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.

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?

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.

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.

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.

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

Context: I see a bunch of people here recommending SQLite. I have a suggestion, try out LMDB. It's kind of like noSQL SQLite. It's a simple in-process K/V store with a few features like compound keys that allow you to model relational data well enough.

I recently used lmdb for webhighlighter.com (specifically the wrapper: https://www.npmjs.com/package/node-lmdb), and it was a fantastic decision.

A lot of people here say "use SQLite for small projects". But even using SQLite can be significant over-engineering. Running migrations? Writing SQL? That's too much effort for me.

For example: In my application, people can leave comments on a (what is effectively) a post. A SQL-native solution might have a table for comments, with foreign keys to post IDs. That's 10x more engineering then I want to do for an MVP. I just store all the comments as an array on a post. This means reads read all the comments and writes require reading all the comments, appending, and then re-writing. That's totally fine, and will probably scale me to 100x my current traffic.

LMDB-JS is great. It allows you to serialize arbitrary JS objects to LMDB using the message pack encoding system. This makes for some super concise code.

Here's my entire data layer: - Interface: https://github.com/vedantroy/grape-juice/blob/main/site/app/... - Implementation: https://github.com/vedantroy/grape-juice/blob/main/site/app/...

TL;DR -- I won't use SQLite, for, I don't know, my first 10K users?

Flashback to when leveldb was hot in the node ecosystem

KV stores are fun but you end up writing a lot of queries and indexes manually

> But even using SQLite can be significant over-engineering. Running migrations? Writing SQL? That's too much effort for me.

Key-value stores are not a replacement for SQL, they solve a simpler problem. If you actually can do with a key value store I would argue a SQL table with two columns key and value with key being a primary key will do just fine. And using it needs simple select and 'insert on duplicate key update' statements. The effort for migrations won't be higher than whatever you had to configure for your key-value store.

However, if you can't actually do with a key-value store you will end up writing a lot of the features SQL provides in application code. Indices, joins, grouping, ordering, etc. You might not notice it at first, but you will blow up complexity in your business logic reinventing existing SQL features and chances are high you're doing it worse and less performant than what e.g. Postresql offers out of the box.

SQL is just such a powerful tool that is at the same time incredibly easy to use for simple scenarios.

Added benefit of using SQLite, you can migrate to a more powerful SQL database later without having to reengineer your whole data layer.

You can get pretty far using LMDB, specifically, its compound-key feature is quite good, and gets you a decent chunk of the relational model. Combined with Message Pack encoding, you can get an extremely concise data layer.

https://github.com/kriszyp/lmdb-js/discussions/170

I think there's a reason Tail Scale used a literal JSON file for up till 150 MB. of data: https://tailscale.com/blog/an-unlikely-database-migration/

So, I think the unconventional wisdom is correct.

Have you seen SQLightning and/or LumoSQL?

* https://github.com/LMDB/sqlightning

* https://github.com/LumoSQL/LumoSQL

SQLightning was the initial project combining SQLite3 with an LMDB backend. It seemed to be more an experimental/Proof-of-Concept thing, and isn't maintained.

LumoSQL is an alternative project (maintained), providing a SQLite3 front end with various optional storage backends. One of which is LMDB.

Note - I'm not affiliated with either project, I just remembered they exist. :)

Reply on news.ycombinator.com