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