⬇️ This is a nice write-up about using SQLite in production, with pitfalls and open questions.
😓 I cringe whenever I see some Django/Python luminary recommending people use SQLite in production. I don’t care how good you are, you won’t get it right even if you think you did. Then, someone will read your advice and contact @djangonews to ask why their production database disappears every time they deploy their website or unexpectedly. https://fosstodon.org/@anze3db/111765707787892711
@webology@djangonews “not many” is the usual answer. Assuming you’re building an app you have to think about that is.
Ah, yes. (Glances at backlog 😅). The boring tech idea is the famous talk. But choosing SQLite specifically (or why still not really) is something I’ve been thinking about.
@_chrismay@webology You’d need to be committed to the claim that “SQLite is the/a standard choice for web application database backends” for it to be “Boring Tech” in the sense in question.
I am not recommending SQLite but I am using it in my read-heavy projects and I am quite happy with it.
It is straightforward, easy to backup and restore. Postgres is quite intimidating and last time I did Ubuntu upgrade I had to somehow upgrade the Postgres cluster and I had no idea what was happening.
Managed DB is different story but the cost is quite high for hobby projects.
@nemecek_f@djangonews I think read-only projects are a valid use-case for SQLite and I'm not saying to never use it.
I see companies like Fly.io encouraging people to use it (they aren't alone), but they don't even have clear docs for how to set the persistent volume.
I use a Managed DB after years of running my own Postgres boxes and I totally agree with your point. I use a few Managed Postgres boxes for a few dozen projects and it's quite cheap for this scale.
@nemecek_f@djangonews Thankfully, with Postgres everything you mentioned there is plenty of docs on the internet and forms of free support to walk people through.
There really aren't any good guides to getting Django + SQLite running in production that can actually withstand a strong breeze, let alone an orange website visit.
@nemecek_f I'm happy with DO's managed service even if the company makes me grumpy. Every few years, I half my bill with new droplets, and they somehow creep back up in price until I half them again.
I use two managed DBs mostly because I have one in an org and the other is in my account. They handle backups, upgrades, and everything is painless so far. I need to spend an hour and write some "copy from production" scripts so I have those in case they ever go belly up.
@webology@nemecek_f I should write this up (too 😅) but a mini AWS RDS is like €8/month. If you prepay a year you can get half off that. You can run multiple projects on the same server (hobby or not) and scale up if that ever becomes needed.
I don’t see that as pricey if we’re honest.
It’s a few bucks a month for a DB admin to set it up and run it for you, plus they throw in a DB, and backups, and updates. 🤷♀️
@carlton@nemecek_f This is another article I would read. Nodding along because I have similar thoughts with so much value for such little cost. (I think mine are $15 a month)
I use DO because my droplets are already there, but I plan to eventually move over to Crunchy Data (good people there)
After a decade of running RDS instances I've never had issues with the service. Only minor annoyance with the ability to set config bits, but then you just learn to live with them. Their serverless stuff is very neat as well if you can afford the performance vs cost you get from scaling to zero.
@carlton@webology my thinking was that DB by Digital Ocean costs 15$ at a minimum, so per project that would be perhaps 60$ month + the droplets.
I hope to pay this once my projects are bit further along, but I also pay Sentry, domains, backup space so it unfortunately isn't insignificant cost for me.
@carlton@nemecek_f DO's Managed DB has its own connection pooler/proxy/PgBouncer-like tool, which takes the edge off those connection handshakes. That's good enough.
Plus, DO's data centers are all linked over a fast, low-latency backbone.
Think of it this way: their data centers have a lower latency over their backbone than we have from our homes to any of their datacenters.
@carlton@nemecek_f One feature that Crunchy Data has that I haven't seen anyone else support is really good Tailscale support.
I run Tailscale on my droplets with only port 443 open (or even no ports open) and that avoids 99.9...% of most security issues My web droplets can connect to my db droplet over Tailscale and I can Tailscale SSH from a trusted device.
@nemecek_f@webology also though, for essentially read-only workloads SQLite IS totally fine. It’s not you can’t use it. You’re just spending a token doing so.
@carlton@webology I will wait for the blog articles and then see what it entails 😆
One of the nice things about SQLite is that I can just copy it to my machine in seconds and have the same data to work on.
So I think I would be spending more tokens with the Postgres but since you both have tons more experince with this than me, I think give it a few months and I will be like "you were right! How to migrate SQLite do managed Postgres?" 😜
Django Ninja has hit a sweet spot for me, mostly for quickly copying data from one machine to another. I might draft how I'm doing that for Django News Jobs and an occasional client project.
@nemecek_f For your sites, I very much imagine they’re almost entirely read-driven, so you might not change at all. Multiple users doing multiple writes? Machine driven (i.e. fast) writes? Access from multiple servers? … — Any of those reasons. Anyhoo…. 😗
Add comment