Month based partitioning

As of now PostgreSQL 8.4, table partitioning is not provided out of the box. This an example of how to get partitioned tables.

The purpose is to have monthly table partitioning using PostgreSQL.

First we need a master table, then we can create partitions inheriting from the master table. A function is used to create the partitions for a given time range.

A timestamp will be used as a partitioning variable, and in this case it is assumed that it will never be updated.

A trigger must be created to intercept the insertions on the master table and redirect the data rows to the correct partition table. A trigger function is used to perform this.

In this specific example, it is assumed that the partitioning variable is a creation timestamp which will never change. This only leads to the need of redirecting insert operations from the master table to the appropriate partition. If the partitioning variable may be updated, then extra trigger and trigger functions would be needed for update operations. Updating the partitioning variable may imply that the updated row must be moved to another partition.

Performance Snippets

Month based partitioning

Works with PostgreSQL

8.4

Written in

pgplsql

Depends on

Nothing

Create the master table

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

Load language module

CREATE LANGUAGE plpgsql;

Create a function to create the partitions and indexes

-- 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;

Partition creation for a given time period

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

Trigger function creation

-- 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;

Trigger activation

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