SQLite Forum

Building a queue based on SQLite
Login

Building a queue based on SQLite

(1) By anonymous on 2021-05-22 16:59:34 [link] [source]

I am building a message queue based on SQLite. Anyone here done the same, do you mind sharing your learnings?

My use case is, I need short lived queues, but a lot of them. At any point I need about 40-50K (might go upto 100K during spikes). Each queues get about 30-40 messages in the duration of 8-10 mins. After that the queue needs to be deleted.

Right now I am deciding between having all the messages in a single table or create each table per queue. I will be using WAL mode.

  1. I believe SQLite locks entire table during writes. Is that correct?
  2. If I create each table per queue, then my code will be simpler and also my writes per queue will be faster if #1 is true. My book keeping code also becomes easier
  3. on the other hand, if I keep all the messages in a single table, then my code becomes slightly complex like deleting each queues after they expire.

any other better ways to do it? I am open to suggestions, thank you!

(2) By Warren Young (wyoung) on 2021-05-22 17:38:54 in reply to 1 [link] [source]

Why have you rejected the pre-built and battle-tested message queuing systems? There are many to choose from.

I've built queues in SQLite before, but on the scale of one insert per several minutes, with dequeueing on the order of a few per hour, the queue providing this low-level buffering that lets the sender avoid spamming the receiver.

When you get to the scale you talk about, rolling your own atop a tool not made to do this sort of thing seems like madness to me.

(3) By anonymous on 2021-05-22 19:46:08 in reply to 1 [source]

SQLite locks the entire database per write, so, everything else being equal, multiple tables and a single table should perform roughly the same. But given a single table design will probably require an index (for fast access to entries of a particular queue) a multi table design will be faster in that regard. Though the number of queues you mention is pretty high and it could have other implications on the system that I am not aware of.

Some of the questions that come to mind, what is the level of durability that you require? And are you more concerned about latency or throughput? I have myself built a queue in SQlite before and for my particular use case it was quite a success.

SQLite is a very flexible, fast and reliable data management tool, but you need to understand/define your requirements and see if they fit SQLite's design tradeoffs.

(4) By Wout Mertens (wmertens) on 2021-05-22 20:06:48 in reply to 1 [link] [source]

I made a queue with sqlite in JS, it's reasonably straightforward, just make sure to keep transactions short so inserting doesn't block.

https://github.com/StratoKit/strato-db/blob/master/src/EventQueue.js

I would definitely use a db file per queue. A modern filesystem won't have issues with 300k files in a single directory, but you can use some subdirectories if you want.

Sqlite should perform fine IMHO, but you'll probably need to use multiple processes.