I'm using SQLite as my main database. Is it a bad idea to build queues on top of SQLite?
Choose Postgres queue technology
211–220 of 369 posts
Re: Choose Postgres queue technology
#212I'm using SQLite as my main database. Is it a bad idea to build queues on top of SQLite?
No
Re: Choose Postgres queue technology
#213For several projects I’ve opted for the even dumber approach, that works out of the box with every ORM/Query DSL framework in every language: using a normal table with SELECT FOR UPDATE SKIP LOCKED https://www.pgcasts.com/episodes/the-skip-locked-feature-in-... It’s not “web scale” but it easily extends to several thousand background jobs in my experience
In my experience, a queue system is the worst thing to find out isn't scaling properly because once you find out your queue system can't architecturally scale, there's no easy fix to avoid data loss. You talk about "several thousand background jobs" but generally, queues are measured in terms of Little's Law [1] for which you need to be talking about rates; according to Little's Law namely average task enqueue rate p…
You are going to have the same scaling issues with your datastore. I don't really understand why you say that your dequeue queries will throttle each others locks and grind it to a half? Isn't that the whole point of SKIP LOCKED?
Re: Choose Postgres queue technology
#214One of the biggest benefits imo of using Postgres as your application queue, is that any async work you schedule benefits from transactionality. That is, say you have a relatively complex backend mutation that needs to schedule some async work (eg sending an email after signup). With a Postgres queue, if you insert the job to send the email and then in a later part of the transaction, something fails and the transact…
> With a Postgres queue, if you insert the job to send the email and then in a later part of the transaction, something fails and the transaction rollbacks, the email is never queued to be sent.
An option could be use a second connection and a separate transaction to insert data in the queue table.
Re: Choose Postgres queue technology
#215The official JDBC driver for PostgreSQL provides no way for the database to push events to the client. So you need a dedicated connection to continuously poll the database to see if there are any events available. This seems half-baked and does not give me the warm and fuzzy feeling I crave when making architectural choices. Not to mention, it causes undesirable latency in event delivery unless I flood the database with queries.
Re: Choose Postgres queue technology
#216We have our own queue, because it was easy, fun and has been exceedingly reliable above all else. Far moreso than other things we had tried. Cough Gearman cough SQS cough One endpoint accepts work to a named queue, writes it to a file in an XFS directory. Another locks a mutex, moves the file to an in progress directory and unlocks the mutex before passing the content to the reader. A third and final endpoint deletes…
Built very similar but on S3. Jobs have statuses, land in /jobs, indexed by status at /indexes-jobs/PENDING, etc. Scheduler polls for jobs in PENDING index, acquire lock, pass job to processor, change its status to COMPLETE or DEAD.
300~ LOC or so and fairly easy to test. Wouldn't take that approach every time, but definitely worth it when you're aiming for a simple architecture.
Re: Choose Postgres queue technology
#217Earlier quoted context omitted.
> and are confident you'll be working at tens of tasks per second forever. It's more like a few thousand per second, and enqueues win, not dequeues like you say... on very small hardware without tuning. If you're at tens of tasks per second, you have a whole lot of breathing room: don't build for 100x current requirements. https://chbussler.medium.com/implementing-queues-in-postgres... > eventually your dequeue queri…
> https://chbussler.medium.com/implementing-queues-in-postgres... This link is simply raw enqueue/dequeue performance. Factor in workers that perform work or execute remote calls and the numbers change. Also, I find when your jobs have high variance in times, performance degrades significantly. > This doesn't really make sense to me. To me, the main problem seems to be that you end up with having a lot of snapshots a…
> Factor in workers that perform work or execute remote calls and the numbers change.
These don't occur on the database server, though... This merely affects the number of rows currently claimed.
> The dequeuer needs to know which tasks to "claim", so this requires some form of locking. Eventually this becomes a bottleneck.
These are just try locks, though-- the row locks are not contended. The big thing you run into is having lots of snapshots around and having to skip a lot of claimed rows for each dequeue.
> What happens if you get 100x traffic? Popularity spikes can do it, so can attacks.
If you get 100x the queueing activity for batch jobs, you're going to have stuff break well before the queue. It's probably not too easy to get 100x the drain rate, even if your queue system can handle it.
This scales well beyond 100M batch tasks per day, which gets you to 1M users with 100 tasks/day each.
Re: Choose Postgres queue technology
#218Earlier quoted context omitted.
> and are confident you'll be working at tens of tasks per second forever. It's more like a few thousand per second, and enqueues win, not dequeues like you say... on very small hardware without tuning. If you're at tens of tasks per second, you have a whole lot of breathing room: don't build for 100x current requirements. https://chbussler.medium.com/implementing-queues-in-postgres... > eventually your dequeue queri…
> https://chbussler.medium.com/implementing-queues-in-postgres... This link is simply raw enqueue/dequeue performance. Factor in workers that perform work or execute remote calls and the numbers change. Also, I find when your jobs have high variance in times, performance degrades significantly. > This doesn't really make sense to me. To me, the main problem seems to be that you end up with having a lot of snapshots a…
NOTIFY/LISTEN isn't a queue it has broadcast semantics. Postgres queueing is really just the SELECT FOR UPDATE SKIP LOCKED, the NOTIFY/LISTEN allows you to reduce the latency a bit but not essential.
Re: Choose Postgres queue technology
#219We have our own queue, because it was easy, fun and has been exceedingly reliable above all else. Far moreso than other things we had tried. Cough Gearman cough SQS cough One endpoint accepts work to a named queue, writes it to a file in an XFS directory. Another locks a mutex, moves the file to an in progress directory and unlocks the mutex before passing the content to the reader. A third and final endpoint deletes…
Why files though, and why move them into different directories? You said billions a day. With files, the physical drive must be taking a beating. Not to mention potential issues with directory file limitations(based on OS and file system). Why not use some kvdb?
As I understand (correct me if I'm wrong, it's been forever since I've worked with filesystems) - file renames are very cheap as the actual data does not get moved, simply a journal gets updated
Re: Choose Postgres queue technology
#220We have our own queue, because it was easy, fun and has been exceedingly reliable above all else. Far moreso than other things we had tried. Cough Gearman cough SQS cough One endpoint accepts work to a named queue, writes it to a file in an XFS directory. Another locks a mutex, moves the file to an in progress directory and unlocks the mutex before passing the content to the reader. A third and final endpoint deletes…
[deleted]