PostgreSQL：识别并删除早于N天的分区

针对你按row_insert_time每日分区的events表，下面提供两种可靠的方法来定位早于N天的分区，以及对应的删除操作步骤：

方法1：通过系统元数据查询（不依赖分区命名，准确性高） #

PostgreSQL的系统表存储了分区的边界信息，可以直接通过这些数据判断分区的时间范围是否符合要求：

SELECT 
    child.relname AS partition_name,
    pg_get_expr(child.relpartbound, child.oid) AS partition_bound
FROM 
    pg_partitioned_table pt
JOIN 
    pg_class parent ON pt.partrelid = parent.oid
JOIN 
    pg_inherits inh ON parent.oid = inh.inhparent
JOIN 
    pg_class child ON inh.inhrelid = child.oid
WHERE 
    parent.relname = 'events'
    -- 提取分区的上限时间，判断是否早于N天前的0点
    AND (regexp_match(pg_get_expr(child.relpartbound, child.oid), 'TO\s*\(''([^'']+)''\)'))[1]::timestamp < current_date - interval 'N days';

将语句中的N替换为具体天数（比如30代表早于30天），执行后就能得到所有符合条件的分区及其边界信息。这个方法不受分区命名规则影响，哪怕后续分区命名调整也能正常工作。

方法2：利用固定分区命名规则（简洁高效） #

你的分区命名遵循events_YYYY_MM_DD的格式，可以直接从表名提取日期进行筛选：

SELECT 
    tablename AS partition_name
FROM 
    pg_tables
WHERE 
    schemaname = 'public' -- 替换为你的表所在的schema
    AND tablename LIKE 'events\_%\_%\_%'
    AND to_date(substring(tablename from 'events_(\d{4}_\d{2}_\d{2})'), 'YYYY_MM_DD') < current_date - interval 'N days';

同样替换N为目标天数，这个方法执行速度更快，但依赖分区命名的一致性，如果后续命名规则改变需要同步调整语句。

删除目标分区 #

确认要删除的分区后，建议先生成删除语句进行核对，避免误删：

生成批量删除语句（以方法2为例） #

SELECT 
    'DROP TABLE IF EXISTS ' || quote_ident(tablename) || ';' AS drop_statement
FROM 
    pg_tables
WHERE 
    schemaname = 'public'
    AND tablename LIKE 'events\_%\_%\_%'
    AND to_date(substring(tablename from 'events_(\d{4}_\d{2}_\d{2})'), 'YYYY_MM_DD') < current_date - interval 'N days';

执行后会得到一系列DROP TABLE语句，复制这些语句执行即可完成删除。

注意事项：

• PostgreSQL中删除分区表时，会自动将其从主分区表中分离，无需手动执行DETACH PARTITION。
• 删除前务必确认分区内的数据已不再需要，或已完成备份。
• 生产环境建议在低负载时段操作，减少对业务的影响。

内容的提问来源于stack exchange，提问作者Ravi M