<sect1 id="pg-pathman">

<title>pg_pathman</title>

The <literal>pg_pathman</literal> module provides optimized

<sect2 id="pg-pathman-concepts">

<title>pg_pathman Concepts</title>

CREATE TABLE test (id SERIAL PRIMARY KEY, title TEXT);

CREATE TABLE test_1 (CHECK ( id &gt;= 100 AND id &lt; 200 )) INHERITS (test);

CREATE TABLE test_2 (CHECK ( id &gt;= 200 AND id &lt; 300 )) INHERITS (test);

The <literal>pg_pathman</literal> module provides functions to

manage partitions and partitioning mechanism optimized based on

knowledge of the partitions structure. It stores partitioning

configuration in the <literal>pathman_config</literal> table, each

row of which contains single entry for partitioned table (relation

name, partitioning key and type). During initialization the

<literal>pg_pathman</literal> module caches information about

child partitions in shared memory in form convenient to perform

rapid search. When user executes SELECT query pg_pathman analyzes

<literal>pg_pathman</literal> searches corresponding partitions

and builds the plan.

<sect2 id="pg-pathman-installation">

CREATE SCHEMA pathman;

CREATE EXTENSION pg_pathman SCHEMA pathman;

Then modify <literal>shared_preload_libraries</>

parameter in postgresql.conf as

following:

shared_preload_libraries = 'pg_pathman'

It will require to restart of the &productname; instance.

<sect2 id="pg-pathman-functions">

<sect3 id="pg-pathman-partitions-creation">

<sect3 id="pg-pathman-data-migration">

<programlisting>

create_hash_update_trigger(parent TEXT)

</programlisting>

<para>

Creates the trigger on UPDATE for HASH partitions. The UPDATE

trigger isn't created by default because of overhead. It is

useful in cases when key attribute could be changed.

</para>

<programlisting>

create_hash_update_trigger(parent TEXT)

</programlisting>

<para>

Same as above for RANGE sections.

</para>

<sect3 id="pg-pathman-partitions-management">

Disables <literal>pg_pathman</literal> partitioning mechanism

for the specified parent table and removes an insert trigger.

<sect2 id="pg-pathman-examples">

<sect3 id="pg-pathman-example-hash">

</programlisting>

<para>

Here is an example of the query with filtering by partitioning

key and its plan:

</para>

<programlisting>

SELECT * FROM hash_rel WHERE value = 1234;

id | value

------+-------

1234 | 1234

EXPLAIN SELECT * FROM hash_rel WHERE value = 1234;

QUERY PLAN

-----------------------------------------------------------------

Append (cost=0.00..2.00 rows=0 width=0)

-&gt; Seq Scan on hash_rel_34 (cost=0.00..2.00 rows=0 width=0)

Filter: (value = 1234)

</programlisting>

<para>

Note that pg_pathman exludes parent table from the query plan.

To access parent table use ONLY modifier:

</para>

<programlisting>

EXPLAIN SELECT * FROM ONLY hash_rel;

QUERY PLAN

--------------------------------------------------------

Seq Scan on hash_rel (cost=0.00..0.00 rows=1 width=8)

<sect3 id="pg-pathman-example-range">

</programlisting>

<para>

Here is an example of the query with filtering by partitioning

key and its plan:

</para>

<programlisting>

SELECT * FROM range_rel WHERE dt &gt;= '2012-04-30' AND dt &lt;= '2012-05-01';

id | dt

-----+---------------------

851 | 2012-04-30 00:00:00

852 | 2012-05-01 00:00:00

EXPLAIN SELECT * FROM range_rel WHERE dt &gt;= '2012-04-30' AND dt &lt;= '2012-05-01';

QUERY PLAN

----------------------------------------------------------------------------

Append (cost=0.00..60.80 rows=0 width=0)

-&gt; Seq Scan on range_rel_28 (cost=0.00..30.40 rows=0 width=0)

Filter: (dt &gt;= '2012-04-30 00:00:00'::timestamp without time zone)

-&gt; Seq Scan on range_rel_29 (cost=0.00..30.40 rows=0 width=0)

Filter: (dt &lt;= '2012-05-01 00:00:00'::timestamp without time zone)

</programlisting>

</sect3>

<sect3 id="disable-pg-pathman">

<title>Disable pg_pathman</title>

<para>

To disable pg_pathman for some previously partitioned table use

disable_partitioning() function:

</para>

<programlisting>

SELECT disable_partitioning('range_rel');

</programlisting>

<para>

All sections and data will stay available and will be handled by

standard PostgreSQL partitioning mechanism. ### Manual

partitions management It is possible to manage partitions

manually. After creating or removing child tables it's necessary

to invoke function:

</para>

<programlisting>

on_update_partitions(oid),

</programlisting>

<para>

which updates internal structures in memory of

<literal>pg_pathman module</literal>. For example, let's create

new section for the <literal>range_rel</literal> from above:

</para>

<programlisting>

CREATE TABLE range_rel_archive (CHECK (dt &gt;= '2000-01-01' AND dt &lt; '2010-01-01')) INHERITS (range_rel);

SELECT on_update_partitions('range_rel'::regclass::oid);

</programlisting>

<para>

CHECK CONSTRAINT must have the exact format: * (VARIABLE &gt;=

CONST AND VARIABLE &lt; CONST) for RANGE partitioned tables; *

(VARIABLE % CONST = CONST) for HASH partitioned tables.

</para>

<para>

It is possible to create partition from foreign table as well:

</para>

<programlisting>

CREATE FOREIGN TABLE range_rel_archive (

id INTEGER NOT NULL,

dt TIMESTAMP)

SERVER archive_server;

ALTER TABLE range_rel_archive INHERIT range_rel;

ALTER TABLE range_rel_archive ADD CHECK (dt &gt;= '2000-01-01' AND dt &lt; '2010-01-01');

SELECT on_update_partitions('range_rel'::regclass::oid);

</programlisting>

<para>

Foreign table structure must exactly match the parent table.

</para>

<para>

In case when parent table is being dropped by DROP TABLE, you

should invoke on_remove_partitions() function and delete

particular entry from <literal>pathman_config</literal> table:

</para>

<programlisting>

SELECT on_remove_partitions('range_rel'::regclass::oid);

DROP TABLE range_rel CASCADE;

DELETE FROM pathman_config WHERE relname = 'public.range_rel';

<sect2 id="pg-pathman-author">

Professional Ltd., Russia