基于月份的分区

截至目前，PostgreSQL 8.4 并未提供开箱即用的表格分区功能。这是一个如何获取分区表的示例。

目的是使用 PostgreSQL 进行每月表格分区。

首先，我们需要一个主表，然后可以创建从主表继承的分区。使用一个函数来创建指定时间范围内的分区。

时间戳将用作分区变量，在本例中假设它永远不会被更新。

必须创建一个触发器来拦截主表上的插入操作，并将数据行重定向到正确的分区表。使用一个触发器函数来执行此操作。

在本例中，假设分区变量是创建时间戳，并且永远不会改变。这只需要将主表的插入操作重定向到适当的分区。如果分区变量可能会被更新，则需要额外的触发器和触发器函数来处理更新操作。更新分区变量可能意味着更新后的行必须移动到另一个分区。

性能片段

基于月份的分区

适用于 PostgreSQL

8.4

用

pgplsql

依赖于

无

创建主表

-- drop table test cascade;
create table test (
	id 	bigserial primary key,
	time	timestamp
);

加载语言模块

CREATE LANGUAGE plpgsql;

创建一个函数来创建分区和索引

-- drop function test_partition_creation();
create or replace function test_partition_creation( date, date )
returns void as $$
declare
	create_query text;
	index_query text;
begin
	for create_query, index_query in select
			'create table test_'
			|| to_char( d, 'YYYY_MM' )
			|| ' ( check( time >= date '''
			|| to_char( d, 'YYYY-MM-DD' )
			|| ''' and time < date '''
			|| to_char( d + interval '1 month', 'YYYY-MM-DD' )
			|| ''' ) ) inherits ( test );',
			'create index test_'
			|| to_char( d, 'YYYY_MM' )
			|| '_time on test_' 
			|| to_char( d, 'YYYY_MM' )
			|| ' ( time );'
		from generate_series( $1, $2, '1 month' ) as d
	loop
		execute create_query;
		execute index_query;
	end loop;
end;
$$
language plpgsql;

指定时间段的分区创建

select test_partition_creation( '2010-01-01', '2012-01-01' ) ;

触发器函数创建

-- drop function test_partition_function();
create or replace function test_partition_function()
returns trigger as $$
begin
	execute 'insert into test_'
		|| to_char( NEW.time, 'YYYY_MM' )
		|| ' values ( $1, $2 )' using NEW.id, NEW.time ;
	return null;
end;
$$
language plpgsql;

触发器激活

-- drop trigger test_partition_trigger;
create trigger test_partition_trigger
	before insert
	on test
	for each row
	execute procedure test_partition_function() ;