Automatic aging-out (deletion) of old records in Postgres

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.

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:

• Drop certain tables in a function managing partitions

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

StackExchange Database Administrators Q#106827, answer score: 25