PostgreSQL pg_pathman: Partition Tables into Native Partition Tables

By digoal

Background

The performance of partition tables in PostgreSQL (PG) 12 is greatly improved. You can convert the partitions of pg_pathman into native partitions in PG 12 without data migration. The conversion only requires modification on the partition inheritance relationship.

You can convert the hash partition tables of pg_pathman into native list tables in PG. Also, you can directly covert Range partitions.

Example

Deploy pg_pathman.

yum install -y pg_pathman11*  
  
vi $PGDATA/postgresql.conf  
  
shared_preload_libraries = 'pg_pathman'   
pg_pathman.enable=on  
  
  
pg_ctl restart -m fast

Create extension plug-in.

CREATE EXTENSION pg_pathman;

Several common functions of pg_pathman for creating partition tables are as follows:

create_hash_partitions(parent_relid     REGCLASS,  
                       expression       TEXT,  
                       partitions_count INTEGER,  
                       partition_data   BOOLEAN DEFAULT TRUE,  
                       partition_names  TEXT[] DEFAULT NULL,  
                       tablespaces      TEXT[] DEFAULT NULL)

create_range_partitions(parent_relid    REGCLASS,  
                        expression      TEXT,  
                        start_value     ANYELEMENT,  
                        p_interval      ANYELEMENT,  
                        p_count         INTEGER DEFAULT NULL  
                        partition_data  BOOLEAN DEFAULT TRUE)

create_range_partitions(parent_relid    REGCLASS,  
                        expression      TEXT,  
                        start_value     ANYELEMENT,  
                        p_interval      INTERVAL,  
                        p_count         INTEGER DEFAULT NULL,  
                        partition_data  BOOLEAN DEFAULT TRUE)

create_range_partitions(parent_relid    REGCLASS,  
                        expression      TEXT,  
                        bounds          ANYARRAY,  
                        partition_names TEXT[] DEFAULT NULL,  
                        tablespaces     TEXT[] DEFAULT NULL,  
                        partition_data  BOOLEAN DEFAULT TRUE)

generate_range_bounds(p_start     ANYELEMENT,  
                      p_interval  INTERVAL,  
                      p_count     INTEGER)

generate_range_bounds(p_start     ANYELEMENT,  
                      p_interval  ANYELEMENT,  
                      p_count     INTEGER)  
Builds bounds array for create_range_partitions().

Create a pg_pathman partition table.

CREATE TABLE items (  
    id       SERIAL PRIMARY KEY,  
    name     TEXT,  
    code     BIGINT);  
  
INSERT INTO items (id, name, code)  
SELECT g, md5(g::text), random() * 100000  
FROM generate_series(1, 100000) as g;

Now, run the create_hash_partitions() function with appropriate arguments:

SELECT create_hash_partitions('items', 'id', 4);

View the definition of items partition.

postgres=# \d+ items  
                                                Table "public.items"  
 Column |  Type   | Collation | Nullable |              Default              | Storage  | Stats target | Description   
--------+---------+-----------+----------+-----------------------------------+----------+--------------+-------------  
 id     | integer |           | not null | nextval('items_id_seq'::regclass) | plain    |              |   
 name   | text    |           |          |                                   | extended |              |   
 code   | bigint  |           |          |                                   | plain    |              |   
Indexes:  
    "items_pkey" PRIMARY KEY, btree (id)  
Child tables: items_0,  
              items_1,  
              items_2,  
              items_3

The get_hash_part_idx(hashint4(id), 4) partition definition is used, which corresponds to the native list partition. Therefore, list partitions are needed to convert pg_pathman partitions into native partitions.

postgres=# \d items_0  
                           Table "public.items_0"  
 Column |  Type   | Collation | Nullable |              Default                
--------+---------+-----------+----------+-----------------------------------  
 id     | integer |           | not null | nextval('items_id_seq'::regclass)  
 name   | text    |           |          |   
 code   | bigint  |           |          |   
Indexes:  
    "items_0_pkey" PRIMARY KEY, btree (id)  
Check constraints:  
    "pathman_items_0_check" CHECK (get_hash_part_idx(hashint4(id), 4) = 0)  
Inherits: items

In a list partition, you must use the immutable function. You can modify the function attributes.

postgres=# \df+ get_hash_part_idx  
                                                                                     List of functions  
 Schema |       Name        | Result data type | Argument data types | Type | Volatility | Parallel |  Owner   | Security | Access privileges | Language |    Source code    | Description   
--------+-------------------+------------------+---------------------+------+------------+----------+----------+----------+-------------------+----------+-------------------+-------------  
 public | get_hash_part_idx | integer          | integer, integer    | func | volatile   | unsafe   | postgres | invoker  |                   | c        | get_hash_part_idx |   
(1 row)

Use the immutable function.

postgres=# alter function get_hash_part_idx(int,int) immutable;  
ALTER FUNCTION

Conversion Example

Create a pg_pathman partition table with 4,096 partitions and write 1 million data points into the table.

postgres=# drop table items cascade;  
  
  
postgres=# CREATE TABLE items (                              
    id       SERIAL PRIMARY KEY,  
    name     TEXT,  
    code     BIGINT);  
CREATE TABLE  
Time: 5.738 ms  
  
postgres=# SELECT create_hash_partitions('items', 'id', 4096);  
 create_hash_partitions   
------------------------  
                   4096  
(1 row)  
  
Time: 12132.819 ms (00:12.133)  
  
  
postgres=# insert into items select generate_series(1,1000000);  
INSERT 0 1000000  
Time: 6701.793 ms (00:06.702)

The conversion method is as follows:

no inherit
attach
do language plpgsql $$  
declare  
  p int := 4096;  
begin  
  execute format('create table pgitems (like items) partition by list (get_hash_part_idx(hashint4(id), %s))', p);  
  for i in 0..p-1 loop  
    execute format('alter table items_%s no inherit items;', i);  
    execute format('alter table pgitems ATTACH PARTITION items_%s FOR VALUES in (%s)', i, i);  
  end loop;  
end;  
$$;

Query the converted data.

postgres=# select count(*) from pgitems ;  
  count    
---------  
 1000000  
(1 row)

After conversion, the write to the native partition table is as fast as that to pg_pathman partition tables.

truncate pgitems;  
  
  
postgres=# insert into pgitems select generate_series(1,1000000);  
INSERT 0 1000000  
Time: 6545.695 ms (00:06.546)

References

https://github.com/postgrespro/pg_pathman/blob/master/README.md

Community

PostgreSQL pg_pathman: Partition Tables into Native Partition Tables

Background

Example

Conversion Example

References

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

PolarDB for PostgreSQL

Database Overview

AnalyticDB for PostgreSQL

Tablestore