How to implement timer events?

Question

Software design

There is a DB (PostgreSQL). There are many entities in the database, let's say a million or several. I would like to implement the ability to do something with them on a timer. For example - the essence is rotten, something needs to be done with it. Or periodic events that occur with the entity, for example - status reminders.
I don’t want to make SQL selections with a query, comparing dates, I suppose this will require a lot of resources. Yes, and I want the event to occur on time, and not when the next cron search works there.

There was an idea to implement through queues of expired messages on RabbitMQ (we put a message with the required date of delay in the queue and it will appear in the queue of expired messages just when necessary) or something similar to Redis. But now we do not have Redis or Rabbit, but only Tarantool, Kafka, Java and Postgres. Maybe something similar can be implemented in Tarantool? Prompt please - where to dig.

Answer 1

do something with them on a timer.

Yes, and I want the event to occur on time, and not when the next cron search works there.

So by the timer, or by expiration? If you are talking about some kind of event like "overdue" or "remind", just make a selection taking into account the condition when requesting data .
If you feel like changing the state of entities directly, you can also do this
a) upon request from the client. That is, suppose you select some messages for a user that 1) are not stale by time, 2) do not have the "rotten" status, and 3) belong to that user. Before that, you can make an update with these conditions, just select the old ones without the "rotten" status and write down the status for them. Suitable if frequent small selections are made, then most requests will be cheap in terms of resources, and some will never change their status at all, which means the data is too cold and you can safely hammer a bolt on it. The downside is that there will be a delay in updating the database before fetching the client, most likely it was a mosquito sneezing the load, but as a fact...
b) also crowns, but here you will have mass updates, although if they are tied to dates, then the parties will also not be very large, in addition, 1 mass request will be a little faster than a bunch of small ones, by the way, this is why it’s not very good to pull through a rabbit . Plus - the script is executed in the background, the load does not fall on the current user. Minus - requests can produce inconsistent data if you do not specify an additional restriction directly in the select request.
There are still a lot of options, including storing keys for updates in radish and pulling them when requested from the user, or some other exotic, but in general - something like that.