0

Case:

A temporary record is inserted for a specific period of time (eg now() for the next 5 minutes).

If "appropriate action" takes place within defined period (eg now() + 2 minutes) then the record gets deleted through a delete statement.

If period ends, the record should get automatically deleted.

At first sight, I would like to solve this within DB space.

Possible solutions:

I have found two tools which may help; pg_cron and pgAgent. I am not sure if they would be set dynamically in real time, wouldn't they?

For example: (expired_time) et = now() + 10 minutes

SELECT cron.schedule('minutes(et) hours(et) * * *', $$DELETE FROM ... $$);

Note: Above syntax is indicative!

Is there any other way or tool to do that, always within DB?

Tia

SONewbiee
  • 363
  • 2
  • 15
  • 1
    I would suggest that you look into Redis instead - it has TTL (time to live) option for most of the data structures so they will be automatically purged after the specified time period (if not explicitly removed before that). Redis is more suitable for things like that. And with a recent PostgreSQL you can utilize the power of FDW (foreign data-tables wrapper) to bring Redis inside PostgreSQL. – IVO GELOV Sep 25 '18 at 18:59
  • This is a relative [link](https://stackoverflow.com/questions/26046816/is-there-a-way-to-set-an-expiry-time-after-which-a-data-entry-is-automaticall) I have found **thanks to Ivo**'s comment. – SONewbiee Sep 26 '18 at 08:41

1 Answers1

0

After some time evaluating the available solutions, this is the current situation, for folks who want to use the Time-To-Live (TTL) feature within PostgreSQL.

  • MongoDB:

    The background task that removes expired documents runs every 60 seconds.

  • Redis:

    Since Redis 2.6 the expire error is from 0 to 1 milliseconds.

For Redis there are two fdws:

  • redis_fdw which:

    • is active, but
    • doesn't support the TTL feature, and

  • rw_redis_fdw which:

    • is not active,
    • does support the TTL feature, and
    • unfortunately doesn't work for Pg10.x

So, since I wanted to have high resolution (small error) TTL within Pg10.x I have used:

  • Pg9.6 with redis_fdw, and
  • Pg10.5 with postgres_fdw.

postgres_fdw redis_fdw PG10.5 ------------> PG9.6 ---------> Redis

PS: Data structure or any other feature was not important in this case. Only TTL.

UPDATE

Now rw_redis_fdw supports PostgreSQL10.x and 11.

Hence:

           redis_fdw
PG10.5/11  --------->  Redis
SONewbiee
  • 363
  • 2
  • 15