Yes for sniprss.com. Backend is written in Go and using litestream for replication. No issues at all.
How do you deploy your application? Is it possible to do without downtime?
Ask HN: Have you used SQLite as a primary database?
151–160 of 330 posts
Re: Ask HN: Have you used SQLite as a primary database?
#152I'm using SQLite for a small personal project that's live in production and so far I love it (both for its simplicity in development and for its performance).
But I've run into on prod that didn't exist in dev on my MacBook M1, and I'm curious if anyone has any suggestions:
My app is basically quiet and serves requests in the dozens (super easy to run on a tiny instance), but for a few hours a day it needs to run several million database transactions, N+1 queries, etc. Because of the high number of IOPS needed, a small instance falls down and runs suuuuuper sluggishly, so I've found myself needing to shut everything down, resize the instance to something with more CPUs, memory, and IOPS ($$$), doing the big batch, then scaling down again. That whole dance is a pain.
Were I using a more traditional postgres setup, I'd probably architect this differently -- the day-to-day stuff I'd run on Cloud Run and I'd spin up a separate beefy instance just for the daily batch job rather than resizing one instance up and down over and over again. The constraint here is that I have a 50GB+ sqlite db file that basically lives on local SSD.
Any thoughts?
Re: Ask HN: Have you used SQLite as a primary database?
#153Yes, I use it for builtwithdjango.com . I have not a single bad thing to say about it, not one!
It is so convenient to have it as a file, especially when you are just learning to do software development.
And the performance has not been an issue once.
One thing to note is that my site is not Facebook size. It only gets ~40 page views a day. And most of them are just for viewing, so no database opertaions.
So, I'm not going to be the most credible voice here. FWIW, I know that Pieter Levels, who runs multiple projects like nomadlist, remoteok, rebase, uses both SQLite and plain JSON files for storage.
Re: Ask HN: Have you used SQLite as a primary database?
#154With 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...
Ouch, that sounds like a nasty bug. But if I understand correctly it's more the driver/ORM's problem than sqlite's? One to look out/test for early if I go in this direction though. Thanks for the heads up!
> But if I understand correctly it's more the driver/ORM's problem than sqlite's?
I think so. sqlite is pretty reliable in my opinion and I never hat these issues anywhere else, but this bug made me switch my DMBS for a small side project from sqlite to postgres.
> One to look out/test for early if I go in this direction though.
You're welcome!
Re: Ask HN: Have you used SQLite as a primary database?
#155I maintain an 'older' codebase (2012) and am rebuilding it to a new version, but both use SQLite. It's a configuration management web application installed onto either bare metal or virtual machines. Generally only a handful of simultaneous users; I want to say performance isn't much of an issue or concern, but I've had to fix a bug that was caused by too many writes to the database where the system ran into IOPS lim…
I wrote a command line tool that automates that ALTER table flow (create new table, copy data across, rename and drop old table) - you might find it useful: https://simonwillison.net/2020/Sep/23/sqlite-advanced-alter-...
Re: Ask HN: Have you used SQLite as a primary database?
#156I think that iOS CoreData is just SQLite under the hood so I imagine a lot of iOS apps use it as the main data store
Re: Ask HN: Have you used SQLite as a primary database?
#157I'm using SQLite as the primary database for PlotPanel [1]! It's going great with no unpleasant surprises along the way. [1] https://plotpanel.com
I’d be curious to hear how you deploy new versions of your application with SQLite. Can it be done without downtime?
Re: Ask HN: Have you used SQLite as a primary database?
#158I'm using for a suite of commercial desktop products and it's working out really well. You'll need to figure your multiple reader/single write connection pools, and graceful shutdowns in your custom server to avoid a data file corruption. This stuff you wouldn't normally do with a db server, but the discovery has made for some great learning and provided food for all kinds of load balancing and distributed db designs. Also started using fossil, which I really love for my small team.
Sqlite is one of the greatest open source projects in history, with awesome docs, and really is a tribute to the art of programming. I'm happy and honored to use it for the appropriate use cases (which are a lot more than one would think).
Re: Ask HN: Have you used SQLite as a primary database?
#159Many of the replies here attest to the simplicity and fast performance of SQLite particularly for serving pages or data. But how well does SQLite fare in concurrent write/insert situations? Although SQLite is not designed for this type of scenario, this discussion higlights there's a strong demand for a concurrent client/server RDMS that is simple, performant and easy to deploy. PostgreSQL is powerful and feature-ric…
Most SQLite writes take less than a ms, so the concurrent writes scenario isn't actually a big problem - writes can be applied via a queue.
Does your forum accept more than 1,000 writes per second? If not, SQLite should be fine.
Re: Ask HN: Have you used SQLite as a primary database?
#160Earlier quoted context omitted.
What is the point of using SQLite under a web service? I thought people complained how MySQL sucks and PostgreSQL rocks for being right and SQLite was nowhere near being right or performant. (Things seem to be getting better with strict column types these days.) I've recently migrated a smallish service from MySQL to PostgreSQL and figured it's quite a work if you're not careful writing by the SQL standard which mean…
> So, why not use a safer choice to begin with? I know several people who build projects like that. It take them months to get a working product, just to discover it doesn't interest people or doesn't work like they expected. If for every piece of tooling you go for the "safe" and most performant one you gain bloat and complexity real quick. People underestimate "simple" tech performance, in 99% of projects by the ti…
Which part of running MySQL instead SQLite is over engineering?