Postgres: Identifying partitions older than N days to drop them

Question

I have a table partitioned by a timestamp field in a postgres database (v 13.11). I create new partitions every day. How do I look for partitions older than N days so I can drop then?

CREATE TABLE events
(
    record_id       BIGSERIAL NOT NULL,
    events          TEXT,
    status          int DEFAULT 0 NOT NULL,
    insert_time     bigint DEFAULT date_part('epoch'::text, timezone('utc'::text, now())),
    event_source    varchar(255),
    row_insert_time timestamp not null default current_timestamp
) PARTITION BY RANGE (row_insert_time);


create table IF NOT EXISTS events_2023_12_21 PARTITION OF events FOR VALUES FROM ('2023-12-21 00:00:00') TO ('2023-12-21 23:59:59')
create table IF NOT EXISTS events_2023_12_22 PARTITION OF events FOR VALUES FROM ('2023-12-22 00:00:00') TO ('2023-12-22 23:59:59')
create table IF NOT EXISTS events_2023_12_23 PARTITION OF events FOR VALUES FROM ('2023-12-23 00:00:00') TO ('2023-12-23 23:59:59')
create table IF NOT EXISTS events_2023_12_24 PARTITION OF events FOR VALUES FROM ('2023-12-24 00:00:00') TO ('2023-12-24 23:59:59')
create table IF NOT EXISTS events_2023_12_25 PARTITION OF events FOR VALUES FROM ('2023-12-25 00:00:00') TO ('2023-12-25 23:59:59')
create table IF NOT EXISTS events_2023_12_26 PARTITION OF events FOR VALUES FROM ('2023-12-26 00:00:00') TO ('2023-12-26 23:59:59')

Answer 1

You could use a query like

SELECT t.oid::regclass
FROM pg_class AS t
   JOIN pg_inherits AS i ON t.oid = i.inhrelid
WHERE (regexp_match(
          pg_get_expr(t.relpartbound, oid),
          '\(''([^'']*)''\)'
       )
      )[1]::timestamp < localtimestamp - INTERVAL '3 days'
   AND i.inhparent = 'events'::regclass
   AND t.relkind = 'r';

The way you declare the partitions, there is no partition for a timestamp like 2023-12-21 23:59:59, because the upper end is always excluded from the range. You should create your partitions like this:

... FOR VALUES FROM ('2023-12-21 00:00:00') TO ('2023-12-22 00:00:00')