Update oldest record

by Tallboy   Last Updated November 17, 2018 01:06 AM

I need to update the oldest record 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 records and retrieve them at the same time"

The column is indexed, its 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 dont know if its efficient, and it also doesnt work when I need to update 5 records at once. I guess I could use IN im just afraid it would be inefficient.

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


Related Questions


PostgreSQL query very slow when subquery added

Updated December 05, 2017 10:06 AM


Postgresql: A better way to write this query

Updated November 05, 2017 12:06 PM

How to avoid a subquery in FILTER clause?

Updated July 12, 2017 08:06 AM