3 ms·

I went from thinking “SQLite is a toy product, not reliable for real data" to "lets use SQLite for almost everything" SQLite is very good if you can fit into t

I went from thinking “SQLite is a toy product, not reliable for real data" to "lets use SQLite for almost everything" SQLite is very good if you can fit into the single writer, multiple readers pattern; you'll never lose data if you use the correct settings, which takes a minute of Google search to figure out. Today, most of my apps are simply go binary + SQLite + systemd service file. I've yet to lose data. Performance is great and plenty for most apps

michaelchisari 5mo ago
The single writer is less of an issue in practice than it's made out to be. Modern nvme drives are incredible and it's trivial to get 5k writes per second in an optimized WAL setup. Way more than most apps could ever dream. And even then, I've used a batch writer pattern to get 180k writes per second on a commodity vps.
- Ringz 5mo ago
  I usually try to explain it like this: “Single writer” is rarely a real problem, because a writer is not slow. It writes exclusively, but very quickly. "Batch writer pattern" is a good idea to get rid of expensive commits.
- 0123456789ABCDE 5mo ago
  all* of that + sharding -> https://sqlite.org/lang_attach.html https://sqlite.org/lang_attach.html ex: main.db + fts.db. reading and writing to main.db is always available; updating the fts index can be done without blocking the main database — it only needs to read, the reads can be chunked, and delayed. fts.db keeps the index + a cursor table — an id or last change ts could also use a shard to handle tables for metrics, or simply move old data out of main.db * some examples: conn = sqlite3.connect("data.db") conn.execute("PRAGMA journal_mode=WAL") # concurrent reads (see above) conn.execute("PRAGMA synchronous=NORMAL") # fsync at checkpoint, not every commit conn.execute("PRAGMA cache_size=-62500") # ~61 MB page cache (negative = KB) conn.execute("PRAGMA temp_store=MEMORY") # temp tables and indexes in RAM conn.execute("PRAGMA busy_timeout=5000") # wait 5s on lock instead of failing edit: orms will obliterate your performance — use raw queries instead. just make sure to run static analysis on your code base to catch sqli bugs. my replies are being ratelimited, so let me add this the heavy duty server other databases have is doing that load bearing work that folks tend to complain about sqlite can't do the real dmbs's are doing mostly the same work that sqlite does, you just don't have to think about it once they're set up. behind that chunky server process the database is still dealing with writing your data to a filesystem, handling transaction locks, etc. by default sqlite gives you a stable database file, that when you see the transaction complete, it means the changes have been committed to storage, and cannot be lost if the machine were to crash exactly after that. you can decide to wave some, or all of those guaranties in exchange for performance, and this doesn't even have to be an all or nothing situation.
  - hparadiz 5mo ago
    Oh fun something I have some metrics on. I just made this benchmark for every php orm a few weeks ago for fun. https://the-php-bench.technex.us/ https://the-php-bench.technex.us/ There's a huge performance difference between memory and file storage within sqlite itself. Not even getting into tuning specifics.
    - deleted 5mo ago
      [deleted]
ashellunts 5mo ago
Do you use multiple backend nodes? If yes, how do you access sqlite files from different nodes?
- faangguyindia 5mo ago
  I use it for apps which don't need multiple backend nodes. When i actually have something that requires multi nodes, i just use postgres (with replica) or mongo (with replica). But it's for those apps which are in autoscaler. For bulk data refresh I use build artifact and hotreload memort mapped files, by checking a manifest on object storage then only getting update if newer. I've used this pattern everywhere and never really needed anything more, occasionally i might use redis if something required shared state across multiple nodes and fast.
- xenadu02 5mo ago
  You can always route writes to a writer node with streaming WAL replication to all the reader nodes. Works for some workloads and systems, not for others. For that matter if you write your system with the correct abstraction you can switch to Postgres _later_ if it becomes necessary. For every system that really did need to scale 10,000 are pointlessly overbuilt - worrying about scale when it just didn't matter.
beoberha 5mo ago
For me, the concern about SQLite has never been if the database engine itself is “reliable for real data”, but that storing data on a single node is not “reliable for real data”. Performance aside, what you are positing is no different than dumping everything to a text file on disk. What happens if that VM dies?
- aperrien 5mo ago
  If the file is that important, it shouldn't be stored in the VM, but on some sort of more robust storage system.
  - ohnei 5mo ago
    The standard for pretty much any multiuser app of a reasonable size is a quorum of SQL or noSQL DBs preferably as a single source of truth for all retainable state. Personally, I think foundationDB is the closest to an attempt to make the minimal viable base layer that I've encountered. But C/C++ based and then owned by apple make it not suitable for the role.
- hellcow 5mo ago
  It's trivial to set up WAL-based streaming backups. Same thing as you'd have with Postgres on GCP. Restore from your latest backup.
- ramblurr 5mo ago
  Postgres, MySQL, all of them, they write to files (binary, not text) on your disk. What happens if your postgres VM dies? (Hint: whatever your answer is it'll apply to SQLite too)

michaelchisari 5mo ago

The single writer is less of an issue in practice than it's made out to be. Modern nvme drives are incredible and it's trivial to get 5k writes per second in an optimized WAL setup. Way more than most apps could ever dream. And even then, I've used a batch writer pattern to get 180k writes per second on a commodity vps.

Ringz 5mo ago
I usually try to explain it like this: “Single writer” is rarely a real problem, because a writer is not slow. It writes exclusively, but very quickly. "Batch writer pattern" is a good idea to get rid of expensive commits.
0123456789ABCDE 5mo ago
all* of that + sharding -> https://sqlite.org/lang_attach.html https://sqlite.org/lang_attach.html ex: main.db + fts.db. reading and writing to main.db is always available; updating the fts index can be done without blocking the main database — it only needs to read, the reads can be chunked, and delayed. fts.db keeps the index + a cursor table — an id or last change ts could also use a shard to handle tables for metrics, or simply move old data out of main.db * some examples: conn = sqlite3.connect("data.db") conn.execute("PRAGMA journal_mode=WAL") # concurrent reads (see above) conn.execute("PRAGMA synchronous=NORMAL") # fsync at checkpoint, not every commit conn.execute("PRAGMA cache_size=-62500") # ~61 MB page cache (negative = KB) conn.execute("PRAGMA temp_store=MEMORY") # temp tables and indexes in RAM conn.execute("PRAGMA busy_timeout=5000") # wait 5s on lock instead of failing edit: orms will obliterate your performance — use raw queries instead. just make sure to run static analysis on your code base to catch sqli bugs. my replies are being ratelimited, so let me add this the heavy duty server other databases have is doing that load bearing work that folks tend to complain about sqlite can't do the real dmbs's are doing mostly the same work that sqlite does, you just don't have to think about it once they're set up. behind that chunky server process the database is still dealing with writing your data to a filesystem, handling transaction locks, etc. by default sqlite gives you a stable database file, that when you see the transaction complete, it means the changes have been committed to storage, and cannot be lost if the machine were to crash exactly after that. you can decide to wave some, or all of those guaranties in exchange for performance, and this doesn't even have to be an all or nothing situation.
- hparadiz 5mo ago
  Oh fun something I have some metrics on. I just made this benchmark for every php orm a few weeks ago for fun. https://the-php-bench.technex.us/ https://the-php-bench.technex.us/ There's a huge performance difference between memory and file storage within sqlite itself. Not even getting into tuning specifics.
  - deleted 5mo ago
    [deleted]

ashellunts 5mo ago

Do you use multiple backend nodes? If yes, how do you access sqlite files from different nodes?

faangguyindia 5mo ago
I use it for apps which don't need multiple backend nodes. When i actually have something that requires multi nodes, i just use postgres (with replica) or mongo (with replica). But it's for those apps which are in autoscaler. For bulk data refresh I use build artifact and hotreload memort mapped files, by checking a manifest on object storage then only getting update if newer. I've used this pattern everywhere and never really needed anything more, occasionally i might use redis if something required shared state across multiple nodes and fast.
xenadu02 5mo ago
You can always route writes to a writer node with streaming WAL replication to all the reader nodes. Works for some workloads and systems, not for others. For that matter if you write your system with the correct abstraction you can switch to Postgres _later_ if it becomes necessary. For every system that really did need to scale 10,000 are pointlessly overbuilt - worrying about scale when it just didn't matter.

beoberha 5mo ago

For me, the concern about SQLite has never been if the database engine itself is “reliable for real data”, but that storing data on a single node is not “reliable for real data”. Performance aside, what you are positing is no different than dumping everything to a text file on disk. What happens if that VM dies?

aperrien 5mo ago
If the file is that important, it shouldn't be stored in the VM, but on some sort of more robust storage system.
- ohnei 5mo ago
  The standard for pretty much any multiuser app of a reasonable size is a quorum of SQL or noSQL DBs preferably as a single source of truth for all retainable state. Personally, I think foundationDB is the closest to an attempt to make the minimal viable base layer that I've encountered. But C/C++ based and then owned by apple make it not suitable for the role.
hellcow 5mo ago
It's trivial to set up WAL-based streaming backups. Same thing as you'd have with Postgres on GCP. Restore from your latest backup.
ramblurr 5mo ago
Postgres, MySQL, all of them, they write to files (binary, not text) on your disk. What happens if your postgres VM dies? (Hint: whatever your answer is it'll apply to SQLite too)