Setup TTL for Aurora Postgres Table

Question

Hi,

In a Aurora PostgreSQL table, we would like to setup TTL (Time-to-live) kind of thing, where rows will expire or will be deleted automatically after few hours/days.
What is the best way to do this?

Thanks,
Satya

Answer

Postgres does not have any out of the box solution to define TTL for a table. The recommended approach would be to convert the table as range partitioned table based of day/hour and schedule a purge script to drop the older partitions.. Here's a blog to convert a table into partitioned table : https://aws.amazon.com/blogs/database/partition-existing-tables-using-native-commands-in-amazon-rds-for-postgresql-and-amazon-aurora-postgresql/.

pg_partman extension can be used to create/maintain partitions as documented here : https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_Partitions.html

pg_partman extension can be used to create/maintain partitions as documented here :
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_Partitions.html

Setup TTL for Aurora Postgres Table

Relevant content