12 ms·
TL;DR: "just use an MySQL or Postgres or SQLite"
by frik 9y ago
TL;DR: "just use an MySQL or Postgres or SQLite"
- StavrosK 9y agoDon'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.
- codazoda 9y agoIf 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.
- StavrosK 9y agoI agree, I run a production (very write-light) site on SQLite and it has been great, but the site is pretty much almost static.
- alkonaut 9y agoIt'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.
- emodendroket 9y agoBut in all those scenarios the downsides of just writing JSON to disk or something are smaller too.
- beagle3 9y agoConcurrent 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.
- StavrosK 9y agoAgreed, SQLite is a fantastic piece of software. Thanks for the WAL tip, I didn't know it wasn't enabled by default.
- logicallee 9y agohave 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.
- StavrosK 9y agoIt'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.
- logicallee 9y agoSo 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.
- StavrosK 9y agoYes, it happened. I could do lots of things, and "apt-get install postgres" is the simplest
- logicallee 9y agoThat'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?
- StavrosK 9y agoNo, no noticeable hit. SQLite is a proper concurrent database as well, so I don't think I would have.
- 9y ago
- cheapsteak 9y agoFrom the article > I hope I have helped you choose PostgreSQL for your next business
- maephisto 9y agoTL;DR: Trust me. I should know.