postgresql删除分区表

Question

我是Postgresql的新手，不知道如何管理分区表
我有基于日期的分区表。在插入数据之前，触发器检查日期并将其放入相应的子表中
例如
2014年11月15日-插入名为11-15-2014_log的表格
2014年11月16日-插入名为11-16-2014_log的表格
现在我想创建一个函数来删除旧的子表，例如，超过90天的表。我应该根据表名查找并删除子表（因为它包含创建日期），还是应该在主表中查找超过90天的记录<任何建议或暗示都将不胜感激。谢谢

Answer 1

关于删除分区，下面的示例将删除sales表的一个分区。使用以下命令创建销售表：

CREATE TABLE sales
(
  dept_no     number,   
  part_no     varchar2,
  country     varchar2(20),
  date    date,
  amount  number
)
PARTITION BY LIST(country)
(
  PARTITION europe VALUES('FRANCE', 'ITALY'),
  PARTITION asia VALUES('INDIA', 'PAKISTAN'),
  PARTITION americas VALUES('US', 'CANADA')
);

查询ALL_选项卡_PARTITIONS视图将显示分区名称：

acctg=# SELECT partition_name, server_name, high_value FROM ALL_TAB_PARTITIONS;
 partition_name | server_name |     high_value      
----------------+-------------+---------------------
 europe         | seattle     | 'FRANCE', 'ITALY'
 asia           | chicago     | 'INDIA', 'PAKISTAN'
 americas       | boston      | 'US', 'CANADA'
(3 rows)

要从sales表中删除americas分区，请调用以下命令：

ALTER TABLE sales DROP PARTITION americas;

查询ALL_选项卡_PARTITIONS视图表明该分区已被成功删除：

acctg=# SELECT partition_name, server_name, high_value FROM ALL_TAB_PARTITIONS;
 partition_name |     high_value      
----------------+---------------------
 asia           | 'INDIA', 'PAKISTAN'
 europe         | 'FRANCE', 'ITALY'
(2 rows)

Answer 2

我将从主表中删除较旧的记录，例如：

DELETE FROM master_table WHERE creation_date < 'today' - INTERVAL '90 days';

然而，我的建议是让这个查询在服务器最昏昏欲睡的时候运行，比如早上5点

Answer 3

对于那些在这里寻找如何在PostgreSQL中删除表分区的人来说，这里有一个现代的答案。

解决方案是不使用DELETE语句，因为这将删除数据，而不会删除保存数据的相应表分区。OP询问如何管理分区表，而不是删除记录，因此任何使用DELETE语句的解决方案都会增加删除记录的不必要的数据库开销，保留空分区表，并且完全忽略了使用分区的主要好处之一；当桌子不再有用时把它放下。

在这种情况下，解决方案必须是删除不再需要的分区表。我已经在我的生产环境中编写了解决这个问题的解决方案，答案在这里。

为了解决OP的问题，我编写的函数可以使用两个轻微的修改来修改fullTablename变量和日期格式。fullTablename变量行必须从

fullTablename := base_table_name || '_' || to_char(startTime, dateFormat);

像这样的YYYY-MM-DD_日志格式

fullTablename := to_char(startTime, dateFormat) || '_' || base_table_name;

然后需要稍微修改日期格式

WHEN partition_plan='day'   THEN 'YYYYDDD'

到声明的表命名约定

WHEN partition_plan='day'   THEN 'YYYY-MM-DD'

然后，可以从以下日常维护脚本调用调用调用函数以执行清理的SQL查询：

SELECT public.drop_partitions(current_date-180, 'public', 'log', 5, 'day');

这将删除比180天前早5天的YYY-MM-DD_log表。对于删除几十或几百个旧表分区的初始运行，可以将5设置为更高的值，以达到预期的效果。