The following versions are currently supported:
PostgreSQL 9.5, 9.6, 10, 11, 12, 13
characteristic
Support range and hash partition
You can partition by expression and composite keys
Partition management can be done automatically or manually
Supports integer, floating point, date and other types, including domains
The partition table JOIN and sub query filter partition are realized
Dynamic partition selection is realized by using RuntimeAppend and RuntimeMergeAppend custom plan nodes.
Partition filter: replace insert trigger to improve performance
PartitionRouter and PartitionOverseer for cross partition update queries (instead of using triggers)
Automatically create partitions for newly inserted data (only for range partitions). It is recommended to close. Inserting a large value will create many partition tables
COPY can directly operate partitions to improve efficiency
User defined callback for partition creation event handling
Through partition_ table_ Concurrent non blocking and concurrent data migration
Support FDW
From version 10, some partition commands are supported, such as ATTACH PARTITION, DETACH PARTITION and CREATE TABLE... PARTITION OF
install
git clone https://github.com/postgrespro/pg_pathman.git make install USE_PGXS=1
PG profile add:
shared_preload_libraries = 'pg_pathman'
correlation function
Partition creation function:
hash partition
create_hash_partitions(parent_relid REGCLASS, --Main table OID
expression TEXT, --Specify the partition key, which can be a column or an expression
partitions_count INTEGER, --Number of partitions
partition_data BOOLEAN DEFAULT TRUE, --Whether to migrate data from the parent table to the child table immediately. It is recommended to close because the table will be locked until the end of data migration
partition_names TEXT[] DEFAULT NULL, --You can specify a partition name
tablespaces TEXT[] DEFAULT NULL) --Tablespaces can be specified
Range partition
create_range_partitions(parent_relid REGCLASS, -- Main table OID
expression TEXT, -- Partition column name or expression
start_value ANYELEMENT, --Start value
p_interval ANYELEMENT, --Specify interval
p_count INTEGER DEFAULT NULL --Specify the number of partitions
partition_data BOOLEAN DEFAULT TRUE) --Whether to migrate data from the parent table to the child table immediately. It is recommended to close because the table will be locked until the end of data migration
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)
In create_ range_ When using partitions(), you can construct boundaries through the following functions
generate_range_bounds(p_start ANYELEMENT,
p_interval INTERVAL,
p_count INTEGER)
generate_range_bounds(p_start ANYELEMENT,
p_interval ANYELEMENT,
p_count INTEGER)
Non blocking migration data: migrate data from the parent table in batches through short transactions. If batch cannot be obtained_ The lock of the size related row will sleep_time, after 60 loop attempts, if the lock cannot be obtained, exit for a long time.
partition_table_concurrently(relation REGCLASS,
batch_size INTEGER DEFAULT 1000,
sleep_time FLOAT8 DEFAULT 1.0)
Stop non blocking concurrent data migration task
stop_concurrent_part_task(relation REGCLASS)
Replace the specified partition, lock_ The parent parameter prevents the operation (INSERT/UPDATE/ALTER) of the parent table
replace_hash_partition(old_partition REGCLASS,
new_partition REGCLASS,
lock_parent BOOLEAN DEFAULT TRUE)
Detach a range partition
split_range_partition(partition_relid REGCLASS,
split_value ANYELEMENT,
partition_name TEXT DEFAULT NULL,
tablespace TEXT DEFAULT NULL)
Merge multiple adjacent partitions, and merge all data into the first partition
merge_range_partitions(variadic partitions REGCLASS[])
Press pathman_config.range_interval setting, append a new range partition at the end
append_range_partition(parent_relid REGCLASS,
partition_name TEXT DEFAULT NULL,
tablespace TEXT DEFAULT NULL)
Press pathman_ config. range_ Set interval to add a new range partition in the header partition
prepend_range_partition(parent_relid REGCLASS,
partition_name TEXT DEFAULT NULL,
tablespace TEXT DEFAULT NULL)
Specify a range boundary and add a range partition
add_range_partition(parent_relid REGCLASS,
start_value ANYELEMENT,
end_value ANYELEMENT,
partition_name TEXT DEFAULT NULL,
tablespace TEXT DEFAULT NULL)
Delete range partition
drop_range_partition(partition TEXT, delete_data BOOLEAN DEFAULT TRUE)
Bind existing table to partition parent table
attach_range_partition(parent_relid REGCLASS,
partition_relid REGCLASS,
start_value ANYELEMENT,
end_value ANYELEMENT)
Unbind table from parent table
detach_range_partition(partition_relid REGCLASS)
Disable the partitioned table plug-in for a single primary table
disable_pathman_for(parent_relid REGCLASS)
Delete all partitions and specify whether the data is migrated to the main table
drop_partitions(parent_relid REGCLASS,
delete_data BOOLEAN DEFAULT FALSE)
To completely delete the partition table and all partitions, use DROP table cascade. However, in the logical replication environment, the publisher sends the drop table through pathman_ ddl_ The trigger event trigger is triggered, but the default subscriber will not take effect. You need to configure the trigger at the subscriber:
ALTER EVENT TRIGGER pathman_ddl_trigger ENABLE ALWAYS;
Other parameters:
Update time range interval. The minimum time of TIMESTAMP type is 1 ms, and the DATE is at least 1 day
set_interval(relation REGCLASS, value ANYELEMENT)
Whether to disable the main table in the execution plan during query
set_enable_parent(relation REGCLASS, value BOOLEAN)
Whether to automatically expand the partition. Note that only the range partition is allowed. For example, if the newly inserted data is not within the existing partition, the partition will be created automatically
set_auto(relation REGCLASS, value BOOLEAN)
The callback function is triggered when creating or binding partitions
set_init_callback(relation REGCLASS, callback REGPROC DEFAULT 0)
/* RANGE-partitioned table abc (child abc_4) */
{
"parent": "abc",
"parent_schema": "public",
"parttype": "2",
"partition": "abc_4",
"partition_schema": "public",
"range_max": "401",
"range_min": "301"
}
/* HASH-partitioned table abc (child abc_0) */
{
"parent": "abc",
"parent_schema": "public",
"parttype": "1",
"partition": "abc_0",
"partition_schema": "public"
}
When the new data of INSERT exceeds the partition scope, whether to use spawnpartitions worker to create a new partition in a separate transaction
set_set_spawn_using_bgw(relation REGCLASS, value BOOLEAN)
Related tables and views
pathman_config storage partition table list
CREATE TABLE IF NOT EXISTS pathman_config (
partrel REGCLASS NOT NULL PRIMARY KEY,
expr TEXT NOT NULL,
parttype INTEGER NOT NULL,
range_interval TEXT,
cooked_expr TEXT);
pathman_config_params the parameter settings stored in this table will override the standard configuration
CREATE TABLE IF NOT EXISTS pathman_config_params (
partrel REGCLASS NOT NULL PRIMARY KEY,
enable_parent BOOLEAN NOT NULL DEFAULT TRUE,
auto BOOLEAN NOT NULL DEFAULT TRUE,
init_callback TEXT DEFAULT NULL,
spawn_using_bgw BOOLEAN NOT NULL DEFAULT FALSE);
pathman_concurrent_part_tasks data migration tasks currently running
-- helper SRF function
CREATE OR REPLACE FUNCTION show_concurrent_part_tasks()
RETURNS TABLE (
userid REGROLE,
pid INT,
dbid OID,
relid REGCLASS,
processed INT,
status TEXT)
AS 'pg_pathman', 'show_concurrent_part_tasks_internal'
LANGUAGE C STRICT;
CREATE OR REPLACE VIEW pathman_concurrent_part_tasks
AS SELECT * FROM show_concurrent_part_tasks();
pathman_partition_list lists all partitioned tables, including their parent tables and scope boundaries
-- helper SRF function
CREATE OR REPLACE FUNCTION show_partition_list()
RETURNS TABLE (
parent REGCLASS,
partition REGCLASS,
parttype INT4,
expr TEXT,
range_min TEXT,
range_max TEXT)
AS 'pg_pathman', 'show_partition_list_internal'
LANGUAGE C STRICT;
CREATE OR REPLACE VIEW pathman_partition_list
AS SELECT * FROM show_partition_list();
pathman_cache_stats displays the usage of cache
-- helper SRF function CREATE OR REPLACE FUNCTION @extschema@.show_cache_stats() RETURNS TABLE ( context TEXT, size INT8, used INT8, entries INT8) AS 'pg_pathman', 'show_cache_stats_internal' LANGUAGE C STRICT; CREATE OR REPLACE VIEW @extschema@.pathman_cache_stats AS SELECT * FROM @extschema@.show_cache_stats();
https://github.com/postgrespro/pg_pathman#custom-plan-nodes
https://github.com/digoal/blog/blob/master/201610/20161024_01.md