The idea, I think, is you wouldn't delete the job from the queue until the processing was done.

Of course, this relies on the jobs being something that can be retried.

But you will have marked it as in progress by setting the "expire" to a non-zero value, preventing any other workers from trying to work on it. How will they know that the worker which marked it actually crashed and will never finish?

By using SELECT ... FOR UPDATE SKIP LOCKED, the record will automatically get unlocked if the worker crashes.

Not sure. Maybe something else is responsible for identifying "stuck" jobs and kicking them out to humans to decide what to do (assuming worker crashes are rare)

If we're talking PostgreSQL specifically, and newer-ish Postgres (9.5+ I think), then you can leverage its abilities to do all this in one atomic query:

  UPDATE jobs
  SET status='working'
  WHERE id = (SELECT id
              FROM jobs
              WHERE status=NULL
              LIMIT 1
              FOR UPDATE
              SKIP LOCKED)
  RETURNING id

If you are willing to use SELECT ... FOR UPDATE SKIP LOCKED, then you can implement the queue without any UPDATE query or status column at all. Just lock the record to mark it as in progress, that is how you get the benefit that when the worker crashes, it will automatically be returned to the queue.

This approach has its own gottchas.

Looking at the code, some steps are missing in the description:

3. When processing is complete the matching table entry is deleted.

4. There is a "reaper" process that checks for stale jobs ( based on time in the future ) and resets them to 0.

This of ouffcourse raises more questions.

I think they want to select expire < now instead of expire = 0. Delete the row on completion.