Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Choose Postgres queue technology

adriano.fyi

Post reply on HN
Print view View original

11–20 of 369 posts

Re: Choose Postgres queue technology

#11

Post by fbdab103 »

matsemann wrote: post #8
andrelaszlo wrote: post #6

Earlier quoted context omitted.

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 simil…

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 fini…

I would set the taken field to a timestamp. Then you could have a cleanup job that looks for any lingering jobs aged past a reasonable timeout and null out the field.

Re: Choose Postgres queue technology

#12

Post by levkk »

Running this exact implementation with 47M jobs processed and counting. SKIP LOCKED is great for VACUUM, and having durable storage with indexes make otherwise expensive patterns like delayed jobs, retries, status updates, "at least once", etc. really easy to implement.

Re: Choose Postgres queue technology

#13

Post by tylergetsay »

fbdab103 wrote: post #11
matsemann wrote: post #8

Earlier quoted context omitted.

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 fini…

I would set the taken field to a timestamp. Then you could have a cleanup job that looks for any lingering jobs aged past a reasonable timeout and null out the field.

it wont work with a timestamp because each write will have an affected row of 1 beacuse the writes happen at different times. setting a boolean is static

Re: Choose Postgres queue technology

#14

Post by Scubabear68 »

pphysch wrote: post #9

Another point is that task queue technology is highly fungible. There's nothing stopping you from starting with cron, adding in Postgres/Redis, then graduating to Kafka or something as need arises. And running all three in parallel, with different jobs in each. I would be surprised if the average Kafka shop didn't also have a bunch of random cron jobs doing things that could be implemented on Kafka or vice versa. At…

I agree with all of this except for the part about “cron”. Cron jobs in my experience quickly become hard to manage and effectively invisible over time.

Use almost anything else to manage job scheduling….

Re: Choose Postgres queue technology

#15

Post by dharmab »

Scubabear68 wrote: post #14
pphysch wrote: post #9

Another point is that task queue technology is highly fungible. There's nothing stopping you from starting with cron, adding in Postgres/Redis, then graduating to Kafka or something as need arises. And running all three in parallel, with different jobs in each. I would be surprised if the average Kafka shop didn't also have a bunch of random cron jobs doing things that could be implemented on Kafka or vice versa. At…

I agree with all of this except for the part about “cron”. Cron jobs in my experience quickly become hard to manage and effectively invisible over time. Use almost anything else to manage job scheduling….

I'm not sure if they literally mean crond, or something vaguely cron-like but easier to manage like systemd timers.

Re: Choose Postgres queue technology

#16

Post by fsniper »

matsemann wrote: post #8
andrelaszlo wrote: post #6

Earlier quoted context omitted.

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 simil…

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 fini…

You can combine this "update" with a "where taken = 0" to directly skip taken rows.

Re: Choose Postgres queue technology

#17

Post by ricardobeat »

aduffy wrote: post #2

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-... It’s not “web scale” but it easily extends to several thousand background jobs in my experience

That's what's in the article.

Re: Choose Postgres queue technology

#18

Post by jayd16 »

tylergetsay wrote: post #13
fbdab103 wrote: post #11

Earlier quoted context omitted.

I would set the taken field to a timestamp. Then you could have a cleanup job that looks for any lingering jobs aged past a reasonable timeout and null out the field.

it wont work with a timestamp because each write will have an affected row of 1 beacuse the writes happen at different times. setting a boolean is static

You can do something like UPDATE row SET timeout = NOW() WHERE NOW() - taskTimeout > row.timestamp. You're not stuck with comparing bools.

Re: Choose Postgres queue technology

#20

Post by SahAssar »

matsemann wrote: post #8
andrelaszlo wrote: post #6

Earlier quoted context omitted.

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 simil…

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 fini…

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.
Post reply on HN