31

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

I want to use Postgres for logging, as a sort of queue, where records (log events) older than two weeks are automatically deleted.

GG.
  • 145
  • 1
  • 8
Basil Bourque
  • 11,188
  • 20
  • 63
  • 96

2 Answers2

25

There is no feature built in to delete rows automatically on a time-based regime (that I would know of).

You could run a daily (you decide) cron-job to schedule simple DELETE commands or use pgAgent for the purpose.

Or you could use partitioning with weekly partitions. That makes deleting very cheap: just keep the latest two weeks and drop older partitions.

Before Postgres 10, partitioning was always based on inheritance. The new declarative partitioning has a lot of advantages and is improved further with every release. Inheritance still provides one key advantage, though. The manual:

Some operations require a stronger lock when using declarative partitioning than when using table inheritance. For example, adding or removing a partition to or from a partitioned table requires taking an ACCESS EXCLUSIVE lock on the parent table, whereas a SHARE UPDATE EXCLUSIVE lock is enough in the case of regular inheritance.

Meaning, adding or removing a partition does not interfere with readers and writers of other partitions with inheritance, while everything is blocked with declarative partitioning (when going through the parent table). It's a very fast operation, though.

With inheritance, create partitions that inherit from a "master" table, lets call it log. Create a RULE or a TRIGGER on the master table that redirects INSERTs to the partition of the current week based on system time.

Either way, you can always log to the master table log. Create partitions ahead of time. Make that several weeks ahead to be sure and run a weekly cron job that adds future child tables ...

There are code examples in the manual for inheritance and declarative partitioning..

Related answer with a plpgsql function creating tables for inheritance automatically:

The related solution recreates a RULE to redirect INSERTs. A trigger function could write to the current partition dynamically ...

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633
3

It sounds like you use Postgres as time-series database. In that case, you might want to consider TimescaleDB, which is a time-series DB based on Postgres and is thus fully compatible with Postgres.

In Timescale, you first need to define a hypertable (the primary Timescale data structure), then you can configure a retention policy using add_retention_policy.

theDmi
  • 131
  • 3