[hot_gril]

Tbh I didn't know about SKIP LOCKED until now, but it looks like you have to hold a xact open the entire time the worker runs, which can be a problem. What I've done before is timestamp cols for start/end. A worker takes any job whose end time is null and start time is not too recent, which makes retries natural and flexible.

A pubsub pattern like pg_notify can definitely make sense depending on the requirements, but I wouldn't jump to it first. The few times I've used pubsub elsewhere, it was when subscribing to some other team's service, not via a shared DB.

[mcqueenjordan]

Yeah, you can avoid holding the xact with the means that you mentioned, e.g. SKIP LOCKED and set some value to PROCESSING, then do your processing, then update to DONE at the end. Or as you mentioned, timestamps.

I think the SKIP LOCKED part is really only useful to avoid contention between two workers querying for new work simultaneously.

[hot_gril]

Yeah, I can imagine SKIP LOCKED being faster if used that way. Just hasn't been an issue for me yet, so I haven't tested.