I need to update the "oldest" row in the database (for now, its limit 1, but I need to also be able to set it to n).

I'm essentially doing a constant stream of "update the oldest rows and retrieve them at the same time".

The column is indexed, it's called last_sent_at and I'm calling it ~20K times a minute.

The thing is, last_sent_at can be NULL and that should always be favored over ones with a filled in time. Once all of them are filled in it should choose the "oldest".

This works for me, the problem is I don't know if its efficient, and it also doesn't work when I need to update 5 rows at once. I guess I could use IN I'm just afraid it would be inefficient.

UPDATE subscribers
SET last_sent_at = '2018-11-17 00:02:27'
WHERE id = (
  FROM subscribers
  ORDER BY last_sent_at NULLS FIRST

1 Answer 1


If the table is big, the key to performance is a matching index on (last_sent_at NULLS FIRST).

Since you are updating so much, have aggressive VACUUM settings. This can be adjusted per table. See:

For only a single row, UPDATE with a subquery like you have is fine.

For LIMIT N with N > 1 a CTE is your safe bet. Like:

WITH cte AS (
   SELECT id
   FROM   subscribers
   ORDER  BY last_sent_at NULLS FIRST
   LIMIT  5
UPDATE subscribers s
SET    last_sent_at = '2018-11-17 00:02:27'
FROM   cte
WHERE  s.id = cte.id

If you do this under concurrent write load, add another expression to your ORDER BY to make the order deterministic. And add the same expression to your index to match! (There can be many rows with NULL, right?) And use the same order for all commands taking row locks in the same table. Else you might face deadlocks from multiple transaction taking locks in arbitrary order. Better yet, use FOR UPDATE SKIP LOCKED if you are not absolutely bound to the strict order. Closely related, with in-depth explanation:


  • There will definitely be very heavy write load but it will be all INSERT statements of new records, not modifying any existing records. Sorry I'm not A super DB expert, but are deadlocks possible which "stop" the entire database? or is it a temporary very quick issue. Thank you so much, I will read those
    – Tallboy
    Nov 18, 2018 at 3:16
  • I guess this is really stupid but I didn't think a deadlock was possible if I'm just writing "normal" queries. I thought postgres handled all of that in a sort of synchronous way
    – Tallboy
    Nov 18, 2018 at 3:17
  • 1
    Deadlocks in your case are resolved automatically by killing one or more of the contesting transactions. Details: dba.stackexchange.com/a/12984/3684 And, well, "a sort of synchronous way" is achieved by taking locks in a consistent order - the point above exactly. Nov 18, 2018 at 3:20
  • 2
    @Tallboy: By that I honor the possibility that SKIP LOCKED can skip over the "oldest" rows because it's currently locked - for whatever reason, even a concurrent transaction trying to do the same update (which might still fail, we don't know until it's committed). Nov 18, 2018 at 3:25
  • 1
    This should be instrumental understand deadlocks: stackoverflow.com/a/22776994/939860 Nov 18, 2018 at 3:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.