Ask HN: Have you used SQLite as a primary database?

Litestream is indeed a missing piece of the puzzle. But it also defeats some of the purpose of using an embedded database library in the first place. Now you're back to juggling separate processes once again.

Tenable.SC ( https://www.tenable.com/products/tenable-sc ) uses SQLite as the only type primary db.

I had a great use case for SQLite last year.

Client (incident response dept at megacorp) had a problem: their quarterly exercises of switching network storage devices from live servers to disaster recovery (DR) servers was a manual operation of reconciling about 8 Excel spreadsheets and setting up ACLs before (luckily) an automated process would switch the storage mounts from live to DR.

We modeled and matched up all the hosts, servers, and ACLs and did a daily write to a single SQLite database. (We redundantly sent all the data to Splunk.) Now the DR employees are automating a daily diff of servers, hosts, ACLs etc to further automate the switch.

To echo a bunch of comments here, we decided on SQLite for a few reasons:

- only one user would write to the DB - only a few users need to access the data - besides standard retention policies, the data could be considered ephemeral and easily recompiled - the script we wrote to compile the data runs in 5 minutes, so if we lose the db, we can easily recompile it.

SQLite (and SQLalchemy) is useful for inexpensive data.

I’ve worked on several projects with sqlite, both read and write heavy, all with high concurrency, with databases in the few hundred MB with 400k server clients, and 100 bare-metal servers running at capacity. The sqlite part of our system is never the problem. In our case sqlite has been an alternative to custom files on disk or replacing a spaghetti of hashmaps in memory. we also replaced a single postgresql instance with all customers into many sqlites per customer. Performance and reliability is why I always reach for it first. At this point I’m a zealot and would argue your first ‘data structure’ of choice should be an sqlite database :)

It blew up big time. I would have saved myself lots of trouble if I had just gone with postgres from the getgo.

The workload was simple (single node work tracking) and I didn't expect it to become a bottleneck. Unfortunately, there were some default settings in the storage backend (tiny page size or WAL or something) that caused severe thrashing and a dearth of tooling to track down the issue. After making a custom build with custom instrumentation and figuring out the problem, I found an email thread where the sqlite community was arguing about this exact issue and the default settings in question. A couple of people had forseen the exact problem I had run into and suggested a fix. Their concerns were dismissed on the grounds that the problem could be configured away, and their concerns about discoverability of configuration were ignored completely. I wasn't thrilled with the crummy defaults, but seeing that the consequences had been forseen, considered, and dismissed despite what seemed like widespread consensus on the fix being simple... it really damaged my trust. How many more landmines did SQLite have?

Lack of perf tooling + bad defaults = recipe for pain.