On Wednesday, 26 September 2018 at 01:52:31 UTC, H. S. Teoh wrote:
What version of SQLite are you using? AFAIK, SQLite itself does support concurrent access. Though it does have to be explicitly compiled with that option, otherwise it will only issue a runtime error. Of course, locking is not as fine-grained, so if one request locks one table then it will block everything else.

Yep, well, it's not very good at it (as it wasn't designed for it). It locks the entire database when writing, and when the lock is held, you get an exception or have to retry on a timer. So, it's "supported" but not actually scalable.

I don't know what your schema looks like, so it's hard to give specifics,

I posted a link to the schema earlier in the thread.

but basically, any column used in a WHERE clause is a candidate for indexing.

Yep, I think we're past that already.

The last issue I ran into was subscriptions. Some people seem to be creating subscriptions to collect and email them frequently, sometimes on every post - not that those work well, because the forum stops emailing people as soon as they have unread messages in their subscriptions, but they still get saved to the queue. Still, the longer the forum was online, the more subscriptions have accumulated, and every new post resulted in all those subscriptions getting triggered. Now, every time a subscription with an email action was triggered, we had to check if there are any unread messages in their subscription queue, and there can be a lot of messages in there - thus, this caused something like an O(m*n) database operation (with the underlying database implementation also not having a constant execution time of course). I fixed this by limiting the check to the first unread post instead of reusing a function to count all unread messages in the subscription queue:

https://github.com/cybershadow/DFeed/commit/9cfcab2

Reply via email to