Hacker News Reader

Comment by williamstein | Hacker News Reader

TL;DR: "just use Postgres"

After doing "big data" for the past 5+ years, I always tell people: use postgres, unless you have a proven, compelling reason not to. It disappoints almost everyone that asks, but it's the truth.

TL;DR: "just use an MySQL or Postgres or SQLite"

Don't use SQLite for production, for all the love I have for it, the client libraries are usually locking accesses and don't work properly with concurrent reads/writes.

If your site is light on writes and always will be, I think SQLite is a good choice. Especially if it reduces the complexity of the system up front. I use it for a lot of personal projects that never gain more than a few thousand impressions a month.

SQLite is also not too difficult to switch to a more advanced SQL in the future.

I agree, I run a production (very write-light) site on SQLite and it has been great, but the site is pretty much almost static.

It's designed for single client, and works very well in production for single client. Mobile apps, desktop apps, and anywhere you can serialize access it works great.

Let's say "don't use it for production for a multiuser server app" then I agree. But that isn't really a supported scenario at all.

But in all those scenarios the downsides of just writing JSON to disk or something are smaller too.

Concurrent writes block each other, but following one-time "pragma journal=wal", read and writes can work concurrently, and you can expect x2-x5 performance (or ~2% degradation, depending on use pattern, but most people see x2 performance).

The cost is that access through network e.g. NFS or SMB in wal mode is impossible (but you shouldn't have done that anyway), and that you can't just ship the sqlite file - ship a dump/backup instead, or you'll have to do recovery on the wal file you sent.

Of course, it's a good idea to use pgsql from the get-go; but SQLite deserves more credit than it gets, and is much more capable than it is usually assumed to be.

Agreed, SQLite is a fantastic piece of software. Thanks for the WAL tip, I didn't know it wasn't enabled by default.

have you ever had an issue with that?

There are only 7 billion people, most of whom don't need your database updated faster than their ping time...

seems like a non-issue.

It's an issue when two people try to write something at the same time and one of them gets a crash because acquiring the lock failed.

So that's an actual issue for you? This happened?

Can't you just idle in a loop waiting for the lock, perhaps waiting a random number of milliseconds (200-800)?

(So that rather than crash, the client just "hangs" waiting for the lock, in a busy loop.)

It just seems like this should not be an issue.

Yes, it happened. I could do lots of things, and "apt-get install postgres" is the simplest

That's fair. As an aside, while it solved your crash issue and obviously scales, when you switched did you get an immediate measurable (and noticeable) performance hit due to the overhead of a proper concurrent database?

No, no noticeable hit. SQLite is a proper concurrent database as well, so I don't think I would have.

I don't mean to be dense but why did you just say "SQLite is a proper concurrent database as well" after sharing a story to the contrary and advising "Don't use SQLite for production".

I'm just trying to understand your advice and learn from your experience, I am just confused by this followup.

It's not an issue with SQLite itself, the python bindings don't work for concurrent access (and I think some other languages' bindings as well). If you're going to embed SQLite yourself, I think it works fine (I haven't tried it).

In theory, SQLite works quite well with concurrent accesses, I have just never tried it because the libraries in the languages I've used didn't work well for that.

ohhhhh, I get it. Yes, you mentioned this in your original comment ("the client libraries are usually locking accesses") but I guess I read too quickly.

Though unless you did a truly comprehensive shootout it might be fairer to write: "Be careful using sqlite in production, the client libraries I tried in Python did not handle concurrent reads/writes, whereas clients for postgresql handle it just fine."

Anyway, thanks for the clarification!

From the article

> I hope I have helped you choose PostgreSQL for your next business

TL;DR: Trust me. I should know.

Reply on news.ycombinator.com