Is there a way to set an "expiry" time, after which a data entry is automatically deleted in PostgreSQL?

Question

Is there any way to set some sort of "expiry" time on data entries in PostgreSQL? I'm thinking about something equivalent to EXPIRE in Redis.

I'm not looking to store a timestamp and then manually code some sort of cron job to check what entries have expired.

I'm trying to find out if there's any native feature in PostgreSQL that would provide this kind of functionality, or if it would make sense to request such feature for future releases.

There's was discussion on the postgresql mailing list http://www.postgresql.org/message-id/CAKe+-77Vu=wzMYSX5d06b2+q6Fi77ZwkO0aYVf8+cewVEca-iQ@mail.gmail.com — vonPetrushev, Jun 30 '15 at 15:00
I know this isn't really an answer, so I'll leave it as a comment. Postgres is not meant to be used for ephemeral data in this way. Use Redis. No reason to make Postgres into the tool you need when the tool you need exists already and works well. — aeskreis, Feb 02 '22 at 20:53

score 159 · Answer 1 · answered Sep 26 '14 at 15:42

159

There is no built in expiration feature but if your goal is to automatically expire fields and have the logic contained within your database (and thus no outside dependency like a cron job) then you can always write a trigger. Below is an example of a trigger that deletes rows from a table that have a timestamp of older than 1 minute. It is executed whenever a new row is inserted into that same table. You can obviously set the trigger to execute on other conditions and for various expiration dates as need be. I used the following website as a basis for this: http://www.the-art-of-web.com/sql/trigger-delete-old/

CREATE TABLE expire_table (
    timestamp timestamp NOT NULL DEFAULT NOW(),
    name TEXT NOT NULL
);

INSERT INTO expire_table (name) VALUES ('a');
INSERT INTO expire_table (name) VALUES ('b');
INSERT INTO expire_table (name) VALUES ('c');

select * from expire_table;
         timestamp          | name 
----------------------------+------
 2014-09-26 15:33:43.243356 | a
 2014-09-26 15:33:45.222202 | b
 2014-09-26 15:33:47.347131 | c
(3 rows)

CREATE FUNCTION expire_table_delete_old_rows() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
  DELETE FROM expire_table WHERE timestamp < NOW() - INTERVAL '1 minute';
  RETURN NEW;
END;
$$;

CREATE TRIGGER expire_table_delete_old_rows_trigger
    AFTER INSERT ON expire_table
    EXECUTE PROCEDURE expire_table_delete_old_rows();

INSERT INTO expire_table (name) VALUES ('d');

select * from expire_table;
         timestamp          | name 
----------------------------+------
 2014-09-26 15:36:56.132596 | d
(1 row)

answered Sep 26 '14 at 15:42

Brett DiDonato

1,794
1
10
9

1

@caeus probably depends on caching and indexing – Nimrod Mar 21 '17 at 20:34
118

-1. Imho, triggers are not the way you should deal with missing database features, because triggers are hard to test, difficult to maintain and just a pain in the ass. Be honest and implement it in your application. :) – Bastian Voigt Apr 20 '17 at 15:08
2

Agree, I think that check old records and delete it on each insert is really terrible solution in terms of performance. It's not that hard to setup even something like CRON job script which executes require SQL, for example. – zarkone Feb 25 '18 at 12:01
1

perfprmance should be fairly good if there's an index on expiry time. – Jasen Feb 22 '20 at 02:09
This should either be managed application level or run as a separate SQL job, the query for data should also use a where clause to limit as it's business logic and it should rather be made clear that the information is from within a limit. – Marco May 04 '20 at 05:43
11

+1 to Brett's solution. For something like a session table where you'd only ever want a user to have a single session, I think a trigger on any INSERT to the session table, to make sure that each user only has one session, is a perfectly valid use case. People obsess over if something is "testable" so they write more complex solutions (which then _need_ heavy testing) rather than some simple function that they can be confident won't break. – corysimmons May 10 '20 at 18:34
@corysimmons Can't you just do `INSERT ... ON CONFLICT DO ...` with a unique constraint on the user ID column for this use case, rather than use triggers? Or just perform a DELETE before the INSERT in your application code... – Maxime Rossini Jan 23 '23 at 09:25
@Jasen Fairly good is not always good enough. Plus your assumption can be wrong, sometimes indexed range deletes can take a large amount of time for other reasons. Like managing the TOAST table if your entries have large fields. – Maxime Rossini Jan 23 '23 at 09:27
@MaximeRossini So. about the same cost or less than inserting the records, it doesn't seem like a big deal to me. – Jasen Jan 23 '23 at 10:09
@MaximeRossini If you can do something at the root of it, do that and comment it—even if it's a bit more complicated at first. If you are relying on devs (including your future self) to remember to write something at the application code level, you're setting yourself up to have to deal with annoying bugs in the future. This is especially important at the data integrity level since it can introduce sooooo many other bugs. – corysimmons Jan 24 '23 at 02:35
@corysimmons I agree with the general principle, but IMHO in this case it's the wrong way to do it. Just add a UNIQUE constraint on the user ID column, this will prevent developers from inserting duplicate rows, and they will get an explicit error when trying to do so. Silentely replacing rows on inserts causes more problems than it solves. Not just testability, but also difficulty to track the behavior, hidden performance overheads... – Maxime Rossini Jan 25 '23 at 09:13
for those wondering how to test triggers here is a simple easy-to-do process. within code - start a transaction, perform DML that triggers the trigger, perform read that asserts before and after the DML the expected state of the table/row under test, rollback transaction. of course complexity may vary with number of interacting triggers but that's the case in general with all state management, application code or not. scales better when using randomized property tests which can catch more complex edge cases. – Awesome-o Mar 28 '23 at 19:48

score 13 · Answer 2 · answered Sep 25 '14 at 21:13

13

No. There is no such feature.

I can't see what it does more than either (1) just an "expired" timestamp does or (2) timestamp + cron-job/pgAgent.

It doesn't sound like a general feature that would be added to the core. You could quite simply code an extension to handle this sort of thing, with either a tick called from a cron-job or perhaps a background-worker process.

I don't see anything on pgxn, so presumably there's not been much demand for it yet.

answered Sep 25 '14 at 21:13

Richard Huxton

21,516
3
39
51

it would require lots of work to add this feature to postgresql, eg, foreign key creation would need different rules... – Jasen Feb 22 '20 at 02:18

score -1 · Answer 3 · answered Aug 01 '22 at 19:30

-1

Nope, PG does not. But you do get these with Google Cloud Spanner. It does have an SQL interface and currently support postgres dialect too.

answered Aug 01 '22 at 19:30

user6880399

105
2
3

Is there a way to set an "expiry" time, after which a data entry is automatically deleted in PostgreSQL?

3 Answers3

Linked