Announcing pg_part extension, a table partitioning utility for PostgreSQL

I'm pleased to announce my first PostgreSQL extension, pg_part extension, to take advantage of table (range) partitioning on PostgreSQL.

• uptimejp/pg_part
  https://github.com/uptimejp/pg_part

In the PostgreSQL world, the table (range) partitioning (a.k.a. Constraint Exclusion) is one of the advantages of PostgreSQL for analytical workloads, but using lots of DDL commands for this purpose still requires some patience.

• PostgreSQL: Documentation: 9.1: Partitioning
  http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html

This extension provies several SQL functions to allow users manipulating table partitioning on PostgreSQL without executing DDL commands directly. So, pg_part would make it easier to take advantages of the PostgreSQL table partitioning.

This extension contains five SQL functions so far: add_partition(), merge_partition(), attach_partition(), detach_partition() and show_partition()

For example, once add_partition() function is called, it executes several DDL commands implecitly within the SQL function to create a partition from the parent user table.

dbt3=# SELECT pgpart.add_partition(
dbt3(#   'public',
dbt3(#   'orders',
dbt3(#   'orders_1992',
dbt3(#   ' ''1992-01-01'' <= o_orderdate AND o_orderdate < ''1993-01-01'' ',
dbt3(#   '/tmp/orders.tmp');
psql:add_part.sql:8: NOTICE:  add_partition: CREATE TABLE public.orders_1992( CONSTRAINT __orders_1992_check CHECK( '1992-01-01' <= o_orderdate AND o_orderdate < '1993-01-01' )) INHERITS (public.orders);
psql:add_part.sql:8: NOTICE:  add_partition: COPY ( SELECT * FROM public.orders WHERE  '1992-01-01' <= o_orderdate AND o_orderdate < '1993-01-01'  ) to '/tmp/orders.tmp';
psql:add_part.sql:8: NOTICE:  add_partition: DELETE FROM public.orders WHERE  '1992-01-01' <= o_orderdate AND o_orderdate < '1993-01-01' ;
psql:add_part.sql:8: NOTICE:  add_partition: COPY public.orders_1992 FROM '/tmp/orders.tmp';
psql:add_part.sql:8: NOTICE:  add_partition: ALTER TABLE public.orders_1992 ADD PRIMARY KEY (o_orderkey);
psql:add_part.sql:8: NOTICE:  add_partition: CREATE INDEX orders_1992_o_orderdate_idx ON public.orders_1992 USING btree (o_orderdate);
psql:add_part.sql:8: NOTICE:  add_partition: CREATE INDEX orders_1992_o_custkey_idx ON public.orders_1992 USING btree (o_custkey);
 add_partition
---------------
 t
(1 row)

dbt3=#

Please take a look, and if you have any question, comment, suggestion or request, feel free to tell me. Any feedback is welcome.

Enjoy!