9 ms·

For 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 tab

by aduffy 3y ago

For 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-postgres-9-5 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

Klonoar 3y ago
I’ve used this for a queue with millions of items and some indexes. It “just works”.
johnthescott 3y ago
skip lock works well on many Ks/sec message queues.
- boruto 3y ago
  Not if you have groups in those thousands and want to maintain order on those groups
Karrot_Kream 3y ago
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 per second and average task duration per second. Raw numbers don't mean that much. In the beginning you can do a naive UPDATE ... SET, which locks way too much. While you can make your locking more efficient, doing UPDATE with SELECT subqueries for dequeues and SELECT FOR UPDATE SKIP LOCKED, eventually your dequeue queries will throttle each other's locks and your queue will grind to a halt. You can try to disable enqueues at that point to give your DB more breathing room but you'll have data loss on lost enqueues and it'll mostly be your dequeues locking each other out. You can try very quickly to shard out your task tables to avoid locking and that may work but it's brittle to roll out across multiple workers and can result in data loss. You can of course drop a random subset of tasks but this will cause data loss. Any of these options is not only highly stressful in a production scenario but also very hard to recover from without a ground-up rearchitecture. Is this kind of a nightmare production scenario really worth choosing Boring Technology? Maybe if you have a handful of customers and are confident you'll be working at tens of tasks per second forever. Having been in the hot seat for one of these I will always choose a real queue technology over a database when possible. [1]: https://en.wikipedia.org/wiki/Little%27s_law https://en.wikipedia.org/wiki/Little%27s_law
- mlyle 3y ago
  > 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-postgresql-3f6e9ab724fa https://chbussler.medium.com/implementing-queues-in-postgres... > eventually your dequeue queries will throttle each other's locks a 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 around.
  - Karrot_Kream 3y ago
    > https://chbussler.medium.com/implementing-queues-in-postgresql-3f6e9ab724fa 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 around. The dequeuer needs to know which tasks to "claim", so this requires some form of locking. Eventually this becomes a bottleneck. > don't build for 100x current requirements What happens if you get 100x traffic? Popularity spikes can do it, so can attacks. Is the answer to just accept data loss in those situations? Queue systems are super simple to use. I'm counting "NOTIFY/LISTEN" on Postgres as a queue, because it is a queue from the bottom up.
    - mlyle 3y ago
      > 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.
boruto 3y ago
Skip locked is useful till you have to maintain order for a group of messages with some "group_id", so that set of related messages are sent one after the other. Then you probably have to write complicated queries or use partitions in some sort. Or Just stick to one thread polling the messages.
theK 3y ago
Ditto. Also, postgres partial indexes can be quite helpful in situations where you want to persist and query intermediate job lifecycle state and don't want multiple rows or tables to track one type of job queue
mvdtnz 3y ago
How is this "an even dumber approach"? It's literally the one thing this article is advocating for. Did you read it?
matsemann 3y ago
I've done even simpler without locks (as no transaction logic), where I select a row, and then try to update a field about it being taken. If 1 row is affected, it's mine. If 0, someone else did it before me and I select a new row. I've used this for tasks at big organizations without issue. No need for any special deployments or new infra. Just spin up a few worker threads in your app. Perhaps a thread to reset abandoned tasks. But in three years this never actually happened, as everything was contained in try/catch that would add it back to the queue, and our java app was damn stable.
- andrelaszlo 3y ago
  I guess you update it with the assigned worker id, where the "taken by" field is currently null? Does it mean that workers have persistent identities, something like an index? How do you deal with workers being replaced, scaled down, etc? Just curious. We maintained a custom background processing system for years but recently replaced it with off the shelf stuff, so I'm really interested in how others are doing similar stuff.
  - matsemann 3y ago
    No, just update set taken=1. If it was a change to the row, you updated it. If it wasn't, someone updated before you. Our tasks were quick enough so that all fetched tasks would always be able to be completed before a scale down / new deploy etc, but we stopped fetching new ones when the signal came so it just finished what it had. I updated above, we did have logic to monitor if a task got taken but never got a finished status, but I can't remember it ever actually reporting on anything.
    - SahAssar 3y ago
      That is the sort of thing that bites you hard when it bites. It might run perfectly for years but that one period of flappy downtime at a third party or slightly misconfigured DNS will bite you hard.
      - 3y ago
ricardobeat 3y ago
That's what's in the article.
orangepanda 3y ago
As I understand, with SKIP LOCKED rows would no longer be processed in-order?
- riku_iki 3y ago
  article says he also uses "order by" clause, but I am wondering if it will severely limit throughput since all messages will need to be sorted on each lookup, but this probably can be solved by introducing index.
  - vore 3y ago
    It seems strictly worse to use ORDER BY in this case, since if you're using SKIP LOCKED you should be doing parallel processing anyway, and if you're doing parallel processing, ordering is already going out the window.
    - nsonha 3y ago
      Parallel or not, the order is of importance in any queue system.
      - vore 3y ago
        You have no ordering guarantees, so how can order be important? If 4 work items are scheduled on 4 independent workers, you have no guarantee which will start first or finish first.
        nsonha 3y ago
        The order matters in the sense that the 5th jobs should not be atempted before those 4.
        riku_iki 3y ago
        I think the order matter at least because you want to have some FIFO approximation, otherwise some tasks can forever stuck in queue and never be picked up.
qaq 3y ago
batch inserts process tasks in batches and it is pretty much webscale
somsak2 3y ago
Fourth paragraph of the post: >Applied to job records, this feature enables simple queue processing queries, e.g. SELECT * FROM jobs ORDER BY created_at FOR UPDATE SKIP LOCKED LIMIT 1.
surprisetalk 3y ago
I recently published a manifesto and code snippets for exactly this in Postgres! delete from task where task_id in ( select task_id from task order by random() -- use tablesample for better performance for update skip locked limit 1 ) returning task_id, task_type, params::jsonb as params [1] https://taylor.town/pg-task https://taylor.town/pg-task
- thom 3y ago
  Presumably it's okay that this loses work if your task runner has an error?
  - muti 3y ago
    From the linked article > The task row will not be deleted if sendEmail fails. The PG transaction will be rolled back. The row and sendEmail will be retried.
  - surprisetalk 3y ago
    If you read my guide, you’ll see that I embed it in a transaction that doesn’t COMMIT until the companion code is complete :) For example, I run the above query to grab a queued email, send it using mailgun, then COMMIT. Nothing is changed in the DB unless the email is sent.
    - maxbond 3y ago
      Holding a transaction open for the duration of a request to an external service makes me nervous. I've seen similar code lock up the database and bring down production. Are you using timeouts and circuit breakers to control the length of the transactions?
      - lomereiter 3y ago
        Yes, you absolutely need to set a reasonable idle transaction timeout to avoid a disaster (bugs in the code happen) - this can also be done globally in the database settings.
adatta02 3y ago
This is more or less how graphile, https://github.com/graphile/worker https://github.com/graphile/worker is implemented.

by aduffy 3y ago

Klonoar 3y ago

I’ve used this for a queue with millions of items and some indexes. It “just works”.

johnthescott 3y ago

skip lock works well on many Ks/sec message queues.

boruto 3y ago
Not if you have groups in those thousands and want to maintain order on those groups

Karrot_Kream 3y ago

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 per second and average task duration per second. Raw numbers don't mean that much. In the beginning you can do a naive UPDATE ... SET, which locks way too much. While you can make your locking more efficient, doing UPDATE with SELECT subqueries for dequeues and SELECT FOR UPDATE SKIP LOCKED, eventually your dequeue queries will throttle each other's locks and your queue will grind to a halt. You can try to disable enqueues at that point to give your DB more breathing room but you'll have data loss on lost enqueues and it'll mostly be your dequeues locking each other out. You can try very quickly to shard out your task tables to avoid locking and that may work but it's brittle to roll out across multiple workers and can result in data loss. You can of course drop a random subset of tasks but this will cause data loss. Any of these options is not only highly stressful in a production scenario but also very hard to recover from without a ground-up rearchitecture. Is this kind of a nightmare production scenario really worth choosing Boring Technology? Maybe if you have a handful of customers and are confident you'll be working at tens of tasks per second forever. Having been in the hot seat for one of these I will always choose a real queue technology over a database when possible. [1]: https://en.wikipedia.org/wiki/Little%27s_law https://en.wikipedia.org/wiki/Little%27s_law

mlyle 3y ago
> 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-postgresql-3f6e9ab724fa https://chbussler.medium.com/implementing-queues-in-postgres... > eventually your dequeue queries will throttle each other's locks a 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 around.
- Karrot_Kream 3y ago
  > https://chbussler.medium.com/implementing-queues-in-postgresql-3f6e9ab724fa 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 around. The dequeuer needs to know which tasks to "claim", so this requires some form of locking. Eventually this becomes a bottleneck. > don't build for 100x current requirements What happens if you get 100x traffic? Popularity spikes can do it, so can attacks. Is the answer to just accept data loss in those situations? Queue systems are super simple to use. I'm counting "NOTIFY/LISTEN" on Postgres as a queue, because it is a queue from the bottom up.
  - mlyle 3y ago
    > 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.

boruto 3y ago

Skip locked is useful till you have to maintain order for a group of messages with some "group_id", so that set of related messages are sent one after the other. Then you probably have to write complicated queries or use partitions in some sort. Or Just stick to one thread polling the messages.

theK 3y ago

Ditto. Also, postgres partial indexes can be quite helpful in situations where you want to persist and query intermediate job lifecycle state and don't want multiple rows or tables to track one type of job queue

mvdtnz 3y ago

How is this "an even dumber approach"? It's literally the one thing this article is advocating for. Did you read it?

matsemann 3y ago

I've done even simpler without locks (as no transaction logic), where I select a row, and then try to update a field about it being taken. If 1 row is affected, it's mine. If 0, someone else did it before me and I select a new row. I've used this for tasks at big organizations without issue. No need for any special deployments or new infra. Just spin up a few worker threads in your app. Perhaps a thread to reset abandoned tasks. But in three years this never actually happened, as everything was contained in try/catch that would add it back to the queue, and our java app was damn stable.

andrelaszlo 3y ago
I guess you update it with the assigned worker id, where the "taken by" field is currently null? Does it mean that workers have persistent identities, something like an index? How do you deal with workers being replaced, scaled down, etc? Just curious. We maintained a custom background processing system for years but recently replaced it with off the shelf stuff, so I'm really interested in how others are doing similar stuff.
- matsemann 3y ago
  No, just update set taken=1. If it was a change to the row, you updated it. If it wasn't, someone updated before you. Our tasks were quick enough so that all fetched tasks would always be able to be completed before a scale down / new deploy etc, but we stopped fetching new ones when the signal came so it just finished what it had. I updated above, we did have logic to monitor if a task got taken but never got a finished status, but I can't remember it ever actually reporting on anything.
  - SahAssar 3y ago
    That is the sort of thing that bites you hard when it bites. It might run perfectly for years but that one period of flappy downtime at a third party or slightly misconfigured DNS will bite you hard.
    - 3y ago

ricardobeat 3y ago

That's what's in the article.

orangepanda 3y ago

As I understand, with SKIP LOCKED rows would no longer be processed in-order?

riku_iki 3y ago
article says he also uses "order by" clause, but I am wondering if it will severely limit throughput since all messages will need to be sorted on each lookup, but this probably can be solved by introducing index.
- vore 3y ago
  It seems strictly worse to use ORDER BY in this case, since if you're using SKIP LOCKED you should be doing parallel processing anyway, and if you're doing parallel processing, ordering is already going out the window.
  - nsonha 3y ago
    Parallel or not, the order is of importance in any queue system.
    - vore 3y ago
      You have no ordering guarantees, so how can order be important? If 4 work items are scheduled on 4 independent workers, you have no guarantee which will start first or finish first.
      - nsonha 3y ago
        The order matters in the sense that the 5th jobs should not be atempted before those 4.
        riku_iki 3y ago
        I think the order matter at least because you want to have some FIFO approximation, otherwise some tasks can forever stuck in queue and never be picked up.

qaq 3y ago

batch inserts process tasks in batches and it is pretty much webscale

somsak2 3y ago

Fourth paragraph of the post: >Applied to job records, this feature enables simple queue processing queries, e.g. SELECT * FROM jobs ORDER BY created_at FOR UPDATE SKIP LOCKED LIMIT 1.

surprisetalk 3y ago

I recently published a manifesto and code snippets for exactly this in Postgres! delete from task where task_id in ( select task_id from task order by random() -- use tablesample for better performance for update skip locked limit 1 ) returning task_id, task_type, params::jsonb as params [1] https://taylor.town/pg-task https://taylor.town/pg-task

thom 3y ago
Presumably it's okay that this loses work if your task runner has an error?
- muti 3y ago
  From the linked article > The task row will not be deleted if sendEmail fails. The PG transaction will be rolled back. The row and sendEmail will be retried.
- surprisetalk 3y ago
  If you read my guide, you’ll see that I embed it in a transaction that doesn’t COMMIT until the companion code is complete :) For example, I run the above query to grab a queued email, send it using mailgun, then COMMIT. Nothing is changed in the DB unless the email is sent.
  - maxbond 3y ago
    Holding a transaction open for the duration of a request to an external service makes me nervous. I've seen similar code lock up the database and bring down production. Are you using timeouts and circuit breakers to control the length of the transactions?
    - lomereiter 3y ago
      Yes, you absolutely need to set a reasonable idle transaction timeout to avoid a disaster (bugs in the code happen) - this can also be done globally in the database settings.

adatta02 3y ago

This is more or less how graphile, https://github.com/graphile/worker https://github.com/graphile/worker is implemented.