Sometimes it is good to have only the most recent data in database and to remove outdated rows as new data comes (e.g., user activity logs or data from hardware sensors). This can be easily achieved using pg_pathman’s features. Let’s say we have a temperature sensor and we need to store readings from it for the last few days.
As you may know pg_pathman automatically creates new partitions on INSERT when new data exceeds existing partitions. Now the interesting part. Since version 1.1 you can add custom callback to your partitioned table which would trigger every time new partition is created. So if we want to keep only recent data and remove old partitions we could use a callback like following:
Note that callback must meet certain requirements:
it has a single JSONB parameter;
it returns VOID.
Few comments on the code above. pathman_partition_list is a view that contains all partitions that are managed by pg_pathman. We sort it by range_min field so that the newest partitions come first, then skip first ten and drop the rest. The set_init_callback() function installs callback into pg_pathman’s config.
Now every time we insert data that exceeds the range covered by partitions a new partition is created and the oldest one is automatically removed: