oid2name\

pageinspect\

passwordcheck\

pathman\

pg_buffercache\

pg_freespacemap\

pg_prewarm\

Performs HASH partitioning for`relation` by integer key`attribute`. Creates`partitions_count` partitions and trigger on INSERT. Data doesn't automatically copied from parent table to partitions. Use`partition_data()` function (see below) to migrate data.

Performs RANGE partitioning for`relation` by partitioning key`attribute`.`start_value` argument specifies initial value,`interval` sets the range of values in a single partition,`premake` is the number of premade partitions (the only one partition will be created if`premake` is 0).

Copies data from parent table to its partitions.

Splits RANGE`partition` in two by`value`.

Merge two adjacent RANGE partitions. Data from`partition2` is copied to`partition1`. Then the`partition2` is removed.

Appends new partition with the range equal to the range of the previous partition.

Prepends new partition with the range equal to the range of the first partition.

Disables`pathman` partitioning mechanism for the specified parent table and removes an insert trigger. Partitions itself remain unchanged.

&ltree;

&pageinspect;

&passwordcheck;

&pathman;

&pgbuffercache;

&pgcrypto;

&pgfreespacemap;

<!ENTITY oid2name SYSTEM "oid2name.sgml">

<!ENTITY pageinspect SYSTEM "pageinspect.sgml">

<!ENTITY passwordcheck SYSTEM "passwordcheck.sgml">

<!ENTITY pathman SYSTEM "pathman.sgml">

<!ENTITY pgbuffercache SYSTEM "pgbuffercache.sgml">

<!ENTITY pgcrypto SYSTEM "pgcrypto.sgml">

<!ENTITY pgfreespacemap SYSTEM "pgfreespacemap.sgml">

<sect1 id="pathman">

<title>pathman</title>

<para>

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

partitioning mechanism and functions to manage partitions.

</para>

<sect2 id="pathman-concepts">

<title>pathman Concepts</title>

<para>

Partitioning refers to splitting one large table into smaller

pieces. Each row in such table assigns to a single partition based

on partitioning key. Common partitioning strategies are:

</para>

<itemizedlist spacing="compact">

<listitem>

<para>

HASH - maps rows to partitions based on hash function values;

</para>

</listitem>

<listitem>

<para>

RANGE - maps data to partitions based on ranges that you

establish for each partition;

</para>

</listitem>

<listitem>

<para>

LIST - maps data to partitions based on explicitly specified

values of partitioning key for each partition.

</para>

</listitem>

</itemizedlist>

<para>

&productname; supports partitioning via table inheritance. Each

partition must be created as child table with CHECK CONSTRAINT.

For example:

</para>

<programlisting>

CHECK ( id &gt;= 100 AND id &lt; 200 )

CHECK ( id &gt;= 200 AND id &lt; 300 )

</programlisting>

<para>

Despite the flexibility of this approach it has weakness. If query

uses filtering the optimizer forced to perform an exhaustive

search and check constraints for each partition to determine

partitions from which it should select data. If the number of

partitions is large the overhead may be significant.

</para>

<para>

The <literal>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>pathman</literal> module caches information about child

partitions in shared memory in form convenient to perform rapid

search. When user executes SELECT query pathman analyzes

conditions tree looking for conditions like:

</para>

<programlisting>

VARIABLE OP CONST

</programlisting>

<para>

where <literal>VARIABLE</literal> is partitioning key,

<literal>OP</literal> is comparison operator (supported operators

are =, &lt;, &lt;=, &gt;, &gt;=), <literal>CONST</literal> is

scalar value. For example:

</para>

<programlisting>

WHERE id = 150

</programlisting>

<para>

Based on partitioning type and operator the

<literal>pathman</literal> searches corresponding partitions and

builds the plan.

</para>

</sect2>

<sect2 id="pathman-installation">

<title>Installation</title>

<para>

To install pathman run:

</para>

<programlisting>

CREATE EXTENSION pathman;

</programlisting>

<para>

in your database Then modify shared_preload_libraries parameter

in postgresql.conf as following:

</para>

<programlisting>

shared_preload_libraries = 'pathman'

</programlisting>

<para>

Then restart the &productname; instance.

</para>

</sect2>

<sect2 id="pathman-functions">

<title>FUNCTIONS</title>

<sect3 id="partitions-creation">

<title>Partitions Creation</title>

<programlisting>

create_hash_partitions(

relation TEXT,

attribute TEXT,

partitions_count INTEGER)

</programlisting>

<para>

Performs HASH partitioning for <literal>relation</literal> by

integer key <literal>attribute</literal>. Creates

<literal>partitions_count</literal> partitions and trigger on

INSERT. Data doesn't automatically copied from parent table to

partitions. Use <literal>partition_data()</literal> function

(see below) to migrate data.

</para>

<programlisting>

create_range_partitions(

relation TEXT,

attribute TEXT,

start_value ANYELEMENT,

interval ANYELEMENT,

premake INTEGER)

</programlisting>

<para>

Performs RANGE partitioning for <literal>relation</literal> by

partitioning key <literal>attribute</literal>.

<literal>start_value</literal> argument specifies initial value,

<literal>interval</literal> sets the range of values in a single

partition, <literal>premake</literal> is the number of premade

partitions (the only one partition will be created if

<literal>premake</literal> is 0).

</para>

<programlisting>

create_range_partitions(

relation TEXT,

attribute TEXT,

start_value ANYELEMENT,

interval INTERVAL,

premake INTEGER)

</programlisting>

<para>

Same as above but suitable for <literal>DATE</literal> and

<literal>TIMESTAMP</literal> partitioning keys.

</para>

</sect3>

<sect3 id="data-migration">

<title>Data migration</title>

<programlisting>

partition_data(parent text)

</programlisting>

<para>

Copies data from parent table to its partitions.

</para>

</sect3>

<sect3 id="partitions-management">

<title>Partitions management</title>

<programlisting>

split_range_partition(partition TEXT, value ANYELEMENT)

</programlisting>

<para>

Splits RANGE <literal>partition</literal> in two by

<literal>value</literal>.

</para>

<programlisting>

merge_range_partitions(partition1 TEXT, partition2 TEXT)

</programlisting>

<para>

Merge two adjacent RANGE partitions. Data from

<literal>partition2</literal> is copied to

<literal>partition1</literal>. Then the

<literal>partition2</literal> is removed.

</para>

<programlisting>

append_partition(p_relation TEXT)

</programlisting>

<para>

Appends new partition with the range equal to the range of the

previous partition.

</para>

<programlisting>

prepend_partition(p_relation TEXT)

</programlisting>

<para>

Prepends new partition with the range equal to the range of the

first partition.

</para>

<programlisting>

disable_partitioning(relation TEXT)

</programlisting>

<para>

Disables <literal>pathman</literal> partitioning mechanism for

the specified parent table and removes an insert trigger.

Partitions itself remain unchanged.

</para>

</sect3>

</sect2>

<sect2 id="examples">

<title>Examples</title>

<sect3 id="hash">

<title>HASH</title>

<para>

Consider an example of HASH partitioning. First create a table

with some integer column:

</para>

<programlisting>

CREATE TABLE hash_rel (

id SERIAL PRIMARY KEY,

value INTEGER);

INSERT INTO hash_rel (value) SELECT g FROM generate_series(1, 10000) as g;

</programlisting>

<para>

Then run create_hash_partitions() function with appropriate

arguments:

</para>

<programlisting>

SELECT create_hash_partitions('hash_rel', 'value', 100);

</programlisting>

<para>

This will create new partitions but data will still be in the

parent table. To move data to the corresponding partitions use

partition_data() function:

</para>

<programlisting>

SELECT partition_data('hash_rel');

</programlisting>

</sect3>

<sect3 id="range">

<title>RANGE</title>

<para>

Consider an example of RANGE partitioning. Create a table with

numerical or date or timestamp column:

</para>

<programlisting>

CREATE TABLE range_rel (

id SERIAL PRIMARY KEY,

dt TIMESTAMP);

INSERT INTO range_rel (dt) SELECT g FROM generate_series('2010-01-01'::date, '2015-12-31'::date, '1 day') as g;

</programlisting>

<para>

Run create_range_partitions() function to create partitions so

that each partition would contain data for one month:

</para>

<programlisting>

SELECT create_range_partitions('range_rel', 'dt', '2010-01-01'::date, '1 month'::interval, 59);

</programlisting>

<para>

It will create 60 partitions (one partition is created

regardless of <literal>premake</literal> parameter). Now move

data from the parent to partitions.

</para>

<programlisting>

SELECT partition_data('range_rel');

</programlisting>

<para>

To merge to adjacent partitions run merge_range_partitions()

function:

</para>

<programlisting>

SELECT merge_range_partitions('range_rel_1', 'range_rel_2');

</programlisting>

<para>

To split partition use split_range_partition() function:

</para>

<programlisting>

SELECT split_range_partition('range_rel_1', '2010-02-15'::date);

</programlisting>

<para>

Now let's create new partition. You can use append_partition()

or prepend_partition() functions:

</para>

<programlisting>

SELECT append_partition('range_rel');

SELECT append_partition('range_rel');

</programlisting>

</sect3>

</sect2>

<sect2 id="author">

<title>Author</title>

<para>

Ildar Musin <email>i.musin@postgrespro.ru</email> Postgres

Professional Ltd., Russia

</para>

</sect2>

</sect1>