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

Turning PostgreSQL into a queue serving 10k jobs per second (2013)

gist.github.com

Post reply on HN
Print view View original

131–140 of 146 posts

Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)

#131

Post by dang »

dimino wrote: post #129
dang wrote: post #128

Earlier quoted context omitted.

Would you please stop posting flamewar comments and crossing into personal attack? This breaks the site guidelines and is not ok here. https://news.ycombinator.com/newsguidelines.html

I'm not doing anything like that, but please, pretend that I am, seems like your go-to move here anyway.

We cut you much more slack than we give to most accounts who break the site guidelines, so I'm not really feeling your complaints.

Would you please pick one account to post with? Using multiple accounts to work around moderation restrictions is obviously abusive.

Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)

#132

Post by shrimpx »

jimktrains2 wrote: post #32
z92 wrote: post #25

Earlier quoted context omitted.

// One problem with using PostgreSQL in this way (using either advisory locks or LOCK FOR UPDATE) is that it requires you to keep an open connection to the database whilst the job is being worked on. // Not necessarily. You lock the row for an instant update to a field, for example called "status" into "running" and then disconnect from the database within milliseconds. Finish your job taking as much time as you want…

What happens when the worker processing the job dies and never updates the status?

An easy way to implement this is to have a "heartbeat" column that the worker updates every N seconds in a thread. A periodic cron-like job reaps jobs who missed M heartbeats. It's possible a worker was alive but unable to update the heartbeat due to, e.g., a temporary network partition. It's also possible the worker's job execution thread crashed while the heartbeat thread is still chugging, causing the job to remain unfinished indefinitely. You can minimize the probability of these failures with various client side logic though. If the worker can't update the heartbeat due to network errors it cancels itself, and you can structure workers such that you can detect stuck/crashed job threads and put the job in "failed" state or just exit and allow the job to be reaped.

But come to think of it, these two classes of problems also occur with systems that hold a db lock for the duration of the job. If the worker loses its connection it needs to somehow cancel itself unless the work is idempotent and computation waste doesn't matter. And if the job crashes you need to make sure you release the lock.

Btw to add another related point, databases do have a lock timeout that you have to worry about if you hold a lock for the duration of the job. Your job execution time cannot exceed the lock timeout.

Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)

#133

Post by dimino »

dang wrote: post #131
dimino wrote: post #129

Earlier quoted context omitted.

I'm not doing anything like that, but please, pretend that I am, seems like your go-to move here anyway.

We cut you much more slack than we give to most accounts who break the site guidelines, so I'm not really feeling your complaints. Would you please pick one account to post with? Using multiple accounts to work around moderation restrictions is obviously abusive.

You know moderation is at an IP level, we've had multiple discussions about it, and I'm clearly still getting hit with the rate limit, so when you say "to work around restrictions" it's obviously disingenuous on your part. Someone/multiple people are auto-downvoting everything I comment on with my other account, so I switched to see if it kept happening. Unsurprisingly, it didn't until I mentioned it again, and it started up immediately after.

As for the other comment you made (can't reply directly, rate limit), it isn't a low information post, I'm showing a screenshot of the message I get when I hit the rate limit.

You're literally gas lighting right now, it's insane.

You also know if you actually do anything else beyond rate limiting I'll just go dark, which is even "worse" for you, so this so-called "leniency" is nonsense. Just stop.

Edit: You can't use multiple accounts to get around rate limits, as you and I have discussed multiple times, so I'm not "doing" anything that constitutes a ban, but feel free to ban at your leisure. Over the years you've established yourself firmly as an unreasonable person, so there's no point trying to treat you like one.

Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)

#134

Post by shrimpx »

drabinowitz wrote: post #34
jimktrains2 wrote: post #32

Earlier quoted context omitted.

What happens when the worker processing the job dies and never updates the status?

You can include a locked_at field and have your update query be for not_started rows and started rows where locked_at is older than the job timeout

A global job timeout might be unreasonable with high variance in workload. Eg some jobs taking 0.5 seconds and others 30 seconds. You might set a global timeout of say 60s but it sucks to wait 59.5s to reap that short job whose worker crashed. A better system is to make workers update a timestamp on an interval and you reap any jobs that haven't been updated in N seconds.

Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)

#135

Post by dang »

dimino wrote: post #133
dang wrote: post #131

Earlier quoted context omitted.

We cut you much more slack than we give to most accounts who break the site guidelines, so I'm not really feeling your complaints. Would you please pick one account to post with? Using multiple accounts to work around moderation restrictions is obviously abusive.

You know moderation is at an IP level, we've had multiple discussions about it, and I'm clearly still getting hit with the rate limit, so when you say "to work around restrictions" it's obviously disingenuous on your part. Someone/multiple people are auto-downvoting everything I comment on with my other account, so I switched to see if it kept happening. Unsurprisingly, it didn't until I mentioned it again, and it st…

I'm afraid I don't really follow what you've written here.

Rate limits are meaningless if people can just use multiple accounts to get around them. If you keep doing that, we're going to have to ban at least one of your accounts—but more likely all of them. After several years of trying to persuade you to use HN as intended, I'm beginning to lose patience.

Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)

#136

Post by pas »

shrimpx wrote: post #134
drabinowitz wrote: post #34

Earlier quoted context omitted.

You can include a locked_at field and have your update query be for not_started rows and started rows where locked_at is older than the job timeout

A global job timeout might be unreasonable with high variance in workload. Eg some jobs taking 0.5 seconds and others 30 seconds. You might set a global timeout of say 60s but it sucks to wait 59.5s to reap that short job whose worker crashed. A better system is to make workers update a timestamp on an interval and you reap any jobs that haven't been updated in N seconds.

It's a trade off between updates per sec and latency.

Maybe simply using a timeout per job type is a better way. (That of course trades off simplicity.)

Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)

#137

Post by adverbly »

balfirevic wrote: post #102
adverbly wrote: post #85

Earlier quoted context omitted.

Actually, redis makes several guarantees. Just not as acid as rds. Source: https://redis.io/topics/transactions

Enqueueing jobs in Redis is not transactional with regard to your primary data store.

Oh I see what you mean. So ya its not transactional across systems. Ya good point. I usually manage that by just updating the primary data store in the job, but I can see what you mean, and how that might not always be an option.

Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)

#138

Post by pas »

oftenwrong wrote: post #68

Why use a RDBMS as a queue? Because you already have it, and it works well. One day you may need a purpose-built component, but today YAGNI. http://boringtechnology.club/

Breaking up the monolith is hard. Yes YAGNI is terrible, over-engineering makes everything brittle and hard to test and so on, but at the same time I burnt myself with spaghetti obelisks more than with the too many docker containers way.

That said, using a common persistence store initially makes sense, but trying to compartmentalize the jobqueue/batch-processing stuff never hurts.

Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)

#139

Post by zentiggr »

dimino wrote: post #129
dang wrote: post #128

Earlier quoted context omitted.

Would you please stop posting flamewar comments and crossing into personal attack? This breaks the site guidelines and is not ok here. https://news.ycombinator.com/newsguidelines.html

I'm not doing anything like that, but please, pretend that I am, seems like your go-to move here anyway.

> You should not be employed in this industry if what you wrote here is how you think, period.

That seems pretty personal. Not that I'm hopping on dang's bandwagon, but you can't post that sort of attack and say you're not doing anything like that.

I've wound up seeing a lot of your recent posts and I can understand where a lot of your responders are coming from... you are very strongly opinionated and have no problem crossing the line from "I hold this opinion" to "you're wrong for not agreeing and therefore you ".

THAT's the behavior that's getting you the pushback. Be more open to discussing things and comparing experiences and maybe you'll see more cordial response back.

Re: Turning PostgreSQL into a queue serving 10k jobs per second (2013)

#140

Post by aldoushuxley001 »

subleq wrote: post #88
aldoushuxley001 wrote: post #37

Earlier quoted context omitted.

Wish I could use that in a Django app. Doesn't seem to be a viable python queueing library that allows using postgresql

I wrote django-postgres-queue for this purpose. It uses postgres transactions to keep queue and application state in sync. It also uses SKIP LOCKED to avoid some of the typical issues with using a database as a queue. https://github.com/gavinwahl/django-postgres-queue

That actually does look like exactly what I need. Beautiful. Thank you! Great work.
Post reply on HN