Postgres - 如何合并分区?

Question

我有一个每天累积数百万行的表。查询最新数据的频率最高，随着数据变旧，查询频率降低。

为了提高性能，我按月对数据进行了分区，表已经创建了这样的内容......

CREATE TABLE banana ( recTime TIMESTAMP, blah blah blah) PARTION BY RANGE (recTime);
CREATE TABLE banana_201704 PARTITION OF banana FOR VALUES FROM ('2017-04-01' ) TO (  '2017-05-01' ) ;
CREATE TABLE banana_201705 PARTITION OF banana FOR VALUES FROM ('2017-05-01' ) TO (  '2017-06-01' ) ;

我注意到，在月初，我从这个表中获得了非常好的性能，大概是因为在每月的那个时候，它几乎没有记录，随着月份的进展(并且表变得更大)，性能会下降。

出于这个原因，我认为如果我按天而不是按月分区，我会获得更好的性能。但是，我不想按天对整个表进行分区，我最终会有很多小分区需要管理。

是否有可能以某种方式将分区无缝合并在一起，以便在(假设)30 天后，所有单独的"日"分区可以合并到一个大的"月"分区中，而无需简单地从一个表中进行选择，插入到另一个表中然后删除原始记录的性能开销？

或者有更好的方法来管理它吗？

Answer 1

一个月结束后，您应该创建一个月度表，从基表中选择一个，删除每日分区并将月度表附加为分区。

为 2018 年 1 月创建每日分区：

do $$
declare d date;
begin
for d in 
select generate_series('2018-01-01'::date, '2018-01-31', '1d')::date
loop
execute format($ex$
create table banana_%s partition of banana for values from (%L) to (%L)
$ex$, replace(d::text, '-', ''), d, d+ 1);
end loop;
end;
$$;

为当月创建单个分区并删除每日分区：

create table banana_201801 as
select * from banana
where created_at between '2018-01-01' and '2018-01-31';
do $$
declare d date;
begin
for d in 
select generate_series('2018-01-01'::date, '2018-01-31', '1d')::date
loop
execute format($ex$
drop table banana_%s
$ex$, replace(d::text, '-', ''));
end loop;
end;
$$;
alter table banana 
attach partition banana_201801 for values from ('2018-01-01') to ('2018-02-01');