Hacker News Reader

Comment by creshal | Hacker News Reader

> Always choose extremely boring technology. Just use python/Django/Postgres for everything.

Hell, think twice before you consider postgres. Sqlite scales further than most people would expect it to, especially for local development / spinning up isolated CI instances. And for small apps it tends to be good enough for production too.

View on HN

Sqlite is mostly boring but I've found that there's just slightly more risk of something going wrong because of the way it handles locking between threads. It has tended to misbehave under unexpected load and been difficult to fix in a way that Postgres hasn't.

I'm particularly thinking of workers running tasks, here. It's possible to lock up everything with write transactions or cause a spate of unhandled SQLITE_BUSY errors in cases where Postgres would just keep chugging along.

> especially for local development / spinning up isolated CI instances

I really don't consider it a good idea to use different databases on your production vs development vs CI instances. Just use PostgreSQL everywhere, it doesn't cost anything, scales down to almost nothing, and scales up to whatever you're likely to need in the future.

I have had some bad experiences with Sqlite for local desktop apps with regards to memory usage, especially on MacOs. Insert and delete a few thousands rows per hour, and over a few days your memory usage has ballooned. It seems to cause a lot of fragmentation.

Curious to hear more about your experience, since my impression from hacking around in native Apple software is that pretty much a bunch of it is based on top of sqlite3. The Photos app is a case in point I remember off the cuff.

I think we might be using it in a slightly unusual way: collect data for a while, do one big query once in a while to aggregate/transform the data and clean everything. Rinse and repeat as it's a background app.

So lots of allocations/deallocations. If you're only storing a few key/value pairs long term, you won't have any issues.

What kind of memory usage are you talking about? That sounds wild.

Are you keeping the entire database in memory, or are you flushing to disk?

It seems wild that such a critical and pervasive piece of software would behave like that.

Keeping it in memory. After a few days, the memory usage reported by the Activity Monitor (so not the actual resident memory, but the one customers see and complain about) grows from maybe a few 10s MB to a few hundreds MBs.

But as far as I can tell, it's more an OS issue than really a Sqlite issue, simply doing malloc/free in a loop results in a similar behaviour. And Sqlite does a lot of allocations. We see a similar problem on Windows, but not as pronounced, and there we can force the OS to reclaim memory.

It's probably solvable by using a custom allocator, but at this point it's no longer plug and play the way the GP meant.

Sqlite is not a very good choice for a typical CRUD app running on a web server. MySQL/Postgres/MariaDB will be much better. You can connect to it from remote and use GUI tools etc. Also much more flexible from an architectural point of view.

Sqlite seems to be the hip new thing to use where MySQL should have been used in the first place. Sqlite is great for many things, but not for the classic CRUD web app.

I have worked on a database engine core team, and I really like the simplicity of SQLite and would love to use SQLite more. I am fully aware of benefits of the simplicity it brings to operations and I also know that the scalability of SQLite is more than sufficient for most of my use cases.

But I am running two major side projects I while still working full time. One of my side projects is a site with 2M page views per month. Additionally, depending on how you count (whether apps are included in the major projects or not), I have 3-4 smaller side projects, two of which use SQLite database.

To be able to run all of this, I have an extreme cut-throat approach to technology. SQLite is almost there, but ultimately Postgres is still more mature all-around solution. As an example, I believe (have not tested this though) it is quicker to set up full-text search for Postgres using online resources. The developer experience is better, because Postgres full-text search has been around for longer and has wider adoption.

Local Postgres is easy. If you're deploying to some cloud service, Postgres is still probably easier than SQLite, since persistent local storage is a bit more special there. I'd also be worried that SQLite doesn't provide something I need out of the box.

This isn't to complain about SQLite overall, it's perfect for local DBs like you'd see in app frontends or OS services.

It's easier to spin up a RDS instance or equivalent than to set up EBS / EFS just to host a tiny sqlite file in a pvc for me personally

hello,

as always: imho. (!)

while i personally really love SQLite for a lot of use-cases, i wouldn't recommend / use it "in serious production" for a django-application which does more than a simple "hello world".

why!? concurrency ... especially if your application attracts user or you just want to scale your deployment horizontally etc. ;))

so in my opinion:

* why not use sqlite for development and functionality testing

* postgresql or mariadb/mysql for (serious) production-instances :)

just my 0.02€

Yeah, I've also found that foregoing Postgres is one step too far. It's just too useful, especially with Listen/Notify making it a good task queue broker. SQLite is great, but Postgres is definitely worth the extra dependency.

You distilled the point well. SQLite is great, but Postgres still hits the sweet spot.

while i'm currently using sqlite, since it has only one write transaction at a time, if I understand correctly, opening a tx and doing outside requests while in the transaction could potentially block your whole application if the outside requests keep timing out... so you kinda need to watch out for that

Treat transactions like mutexes has always been the prevailing wisdom has not not? Keep them as short as possible and do not make blocking calls within one.

This would be true for any database, something read / written during a transaction would block at least that table universally until the transaction is finalised.

What do you save by going with sqlite vs how much pain are you going to endure in the future?

I save having to deal with a full-blown DBMS daemon accessed through a network boundary unless I need this, and I have approximately zero pain, because I read the documentation and set up my database schemas to work on both solutions (and use WALs so sqlite is rock stable). I also know how to open a file on the command line if I need raw database access for some reason (very rare with django anyway, django's console is just too good not to use it). I also design my apps to not deadlock on unnecessarily long transaction and don't turn every request into a database write, so I can scale out pretty far before I have to worry about write performance. And if I do, I can still use postgres. Until then, I can do unified, consistent backups of all state by snapshotting the filesystem containing uploads and sqlite files.

So I dunno why people insist on spreading so much FUD.

It's not FUD. For all the trouble you claim to have with Postgres I experienced 0 of it in the last 4 years. The only thing extra for a simple setup is a couple of lines in your docker compose files which is completely amortised because you already have a multi process architecture with Python anyway (proxy + webserver + web server works). The upfront cost is so small that for me the expected total cost will rarely make sense even if you assume that your application has 1% of chance of scaling beyond what you can do with sqlite.

Reply on news.ycombinator.com