CODEWITHSUNDEEP
databasepostgresqlsql-delete
awfuldev2020
awfuldev2020

Reputation: 95

Does PostgreSQL have a built in feature to delete old records every minute or hour, like delete all records older than 2 years and check every hour

Does Postgres have any features to support aging-out old records?

Like the title says, I want to delete all records that are older than 2 years old, and have it check every few minutes or hours. I've seen posts that are years old saying no, but perhaps things have changed.

Upvotes: 1

Views: 8831

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247340

In addition to the good answer on DBA Stack Exchange, let me add this:

It is not necessary to delete the rows every couple of minutes. Rather, you hide them from queries.

Take this simple example:

CREATE TABLE realdata (
   id bigint PRIMARY KEY,
   payload text,
   create_time timestamp with time zone DEFAULT current_timestamp NOT NULL
) PARTITION BY RANGE (create_time);

CREATE VIEW visibledata AS
   SELECT * FROM realdata
   WHERE create_time > current_timestamp - INTERVAL '2 years';

The view is simple enough that you can INSERT, UPDATE and DELETE on it directly; no need for triggers.

Now all data will automagically vanish from visibledata after two years.

Occasionally you launch a clean-up job that simply drops all partitions older than two years.

Upvotes: 8

pifor
pifor

Reputation: 7892

The answer is no: things have not changed for this.

Upvotes: 0

Related Questions