3 ms·

> SQLite is absolutely useless for multiple writers. I don't really quite agree with this, but would say that with SQLite it's more important be be aware of ho

> SQLite is absolutely useless for multiple writers. I don't really quite agree with this, but would say that with SQLite it's more important be be aware of how concurrency is being used with regards to the database, in comparison to something like MySQL or PostgreSQL. As far as IRC logs, I think the important thing (that would almost certainly hamstring any SQL database that's actually being ACID) is to not use autocommit on inserts, and to not use a transaction for every single insert (which is possibly what's happening). I wrote a plugin for ZNC to do exactly what you're doing above, and by batching my writes (either waiting 15 seconds or until n (where n is maybe 1000 or more) messages are queued), I've gotten superb performance out of SQLite3 running with a WAL journal, with the trade off being a little bit of data might be lost if your bouncer happens to fall down in an unhandled way. I was also able to get really good read performance without hurting raw write performance by leveraging partial indices, which something like MySQL doesn't give you.

kuschku 9y ago
Well, with PostgreSQL everything works just fine. Read and write performance is amazing, and full text search returns instantaneous results. With SQLite, it’s basically useless.
- tracker1 9y ago
  It really depends... I've worked on systems that PostgreSQL (or any SQL rdbms) wouldn't be able to keep up with. I've seen many others that would do fine with SQLite. It depends. In the end, it's a matter of testing. Also, with faster drive options (Optane and whatever is next), it could very well be a decent option. It all depends. Frankly, for logging, I'd lean towards elasticsearch (ELK) these days, which is about the most write heavy situation you're likely to see. Depending on the scaling Mongo, RethinkDB, ElasticSearch and Cassandra can all be better solutions than RDBMS. I'd still reach for SQLite or PostgreSQL first depending on the situation though.
  - kuschku 9y ago
    Sure, with optane it may work — but the usual usecase for SQLite is on the slowest SD cards or spinning HDDs you can imagine, where the user didn't want to spend time configuring postgres

kuschku 9y ago

Well, with PostgreSQL everything works just fine. Read and write performance is amazing, and full text search returns instantaneous results. With SQLite, it’s basically useless.

tracker1 9y ago
It really depends... I've worked on systems that PostgreSQL (or any SQL rdbms) wouldn't be able to keep up with. I've seen many others that would do fine with SQLite. It depends. In the end, it's a matter of testing. Also, with faster drive options (Optane and whatever is next), it could very well be a decent option. It all depends. Frankly, for logging, I'd lean towards elasticsearch (ELK) these days, which is about the most write heavy situation you're likely to see. Depending on the scaling Mongo, RethinkDB, ElasticSearch and Cassandra can all be better solutions than RDBMS. I'd still reach for SQLite or PostgreSQL first depending on the situation though.
- kuschku 9y ago
  Sure, with optane it may work — but the usual usecase for SQLite is on the slowest SD cards or spinning HDDs you can imagine, where the user didn't want to spend time configuring postgres