31–40 of 330 posts
With C# (Entity Framework) I've tried and failed using sqlite in a production scenario because of concurrency issues... Recently I stumbled over a potential fix[1], which I will try in my next project. [1] https://ja.nsommer.dk/articles/thread-safe-async-sqlite3-ent...
One to look out/test for early if I go in this direction though. Thanks for the heads up!
I use SQLite in production for my SaaS[1]. It's really great — saves me money, required basically no setup/configuration/management, and has had no scaling issues whatsoever with a few million hits a month. SQLite is really blazing fast for typical SaaS workloads. And will be easy to scale by vertically scaling the vm it's hosted on.
Litestream was the final piece of the missing puzzle that helped me use it in production — continuous backups for SQLite like other database servers have: https://litestream.io/ With Litestream, I pay literally $0 to back up customer data and have confidence nothing will be lost. And it took like 5 minutes to set up.
I'm so on-board the SQLite train you guys.
[1] https://extensionpay.com — Lets developers take payments in their browser extensions.
> Given the complexity
Which complexity? It is the simplest possible widespread, reliable and effective solution. Which makes it a primary choice.
> it seems like there are use cases or needs here that I'm not seeing
On the contrary, the use cases for the traditional Relational DB engines are defined: when you need a concurrency manager better than filesystem access. (Or maybe some unimplemented SQL function; or special features.) Otherwise, SQLite would be the natural primary candidate, given the above.
Edit:
I concur about https://blog.wesleyac.com/posts/consider-sqlite being a close to essential read if one has the poster's doubt.
To its "So, what's the catch?" section, I would add: SQLite does not implement the whole of SQL (things that come to mind on the spot are variables; the possibility of recursion was implemented only recently, etc).
I'm using SQLite in Notion Backups (most of the workload happens in background jobs; the web app itself doesn't get that many visits) Except for some rare exceptions, it's been doing pretty great. I don't have any plans to migrate from SQLite any time soon.
Also they're where the real insights are.
[1] https://www.gaia-gis.it/fossil/libspatialite/index
[2] http://web.archive.org/web/20190224100905/https://core.ac.uk...
I also do backups periodically with ActiveJob using `.backup` on the sqlite3 client. It's simple and nice because I just have to worry about running the app, and nothing else.
We replaced SSMS + SQL Server with Python + SQLite run in AWS Lambda. The jobs fetch the database from S3, update with the latest deltas and write out the database and some CSV files to S3. The CSV files drive some Tableau dashboards through Athena.
The SQL usually needs a bit of a rework to make this work, but for the volumes of data we were looking at (we're talking less than a million rows, jobs run once per day) we've seen good performance at low cost. We used DuckDB for a couple of workloads which needed more complicated queries, it's stupid quick.
Yes, I've used it for a side project of mine. It processed like 5 financial transactions in total, so I'm glad I never invested the time to build anything more robust :) It's also powering another one and I really like the fact that I can just commit the whole DB to the GIT repo.
I've never (deliberately) considered committing a DB to git. Although there was that one time when I was straight out of college...
Pro tip: surprising your colleagues in the morning with a 40 minute wait to pull master (because you committed a ???GB db) is a good way to feel like a right eegit.
When I first came and saw it, it...did not sound right. But I didn't want to be the guy who comes in and says "you are doing it wrong" month 1. So I went along with it.
Of course, eventually problems started to pop up. I distinctly remember that the ingestion (happening via a lot of Kafka consumers) throughput was high enough that SQLite started to crumble and even saw WAL overruns, data loss etc. Fortunately, it wasn't "real" production yet.
I suggested we move to Postgres and was eventually able to convince everyone from engineers to leadership. We moved to a custom sharded Postgres (9.6 at the time). This was in 2016. I spoke to people at the place last month, and it's still humming along nicely.
This isn't to illustrate anything bad about SQLite, to be clear! I like it for what it does. Just to show at least 1 use case where it was a bad fit.
SQLite was a tempting first answer, but what solved it was Postgres, and we eventually offloaded a lot of aggregation tables to Clickhouse and turned the whole thing into a warehouse where the events got logged.
I no longer work there, but an enterprise facial recognition system used by NGOs, and 3-lettered government agencies has SQLite as the sole datastore. I wrote a portion of the SQLite runtime logic, a simply key/value store used all over the software. SQLite proved to be phenomenal. We spec'ed hardware with enough RAM to hold the FR DB in memory, and damn SQLite is fast enough to keep up with the optimized FR system p…
Out of interest, were you running on bare metal/cloud? And what kind of CPU was behind those 24M face compares per second?