pathman_version()

This function returns a complete version number of the loaded lt_pathman module in MAJOR.MINOR.PATCH format.

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)

Performs HASH partitioning for relation by partitioning expression expr. The partitions_count parameter specifies the number of partitions to create; it cannot be changed afterwards. If partition_data is true then all the data will be automatically copied from the parent table to partitions. Note that data migration may took a while to finish and the table will be locked until transaction commits. See partition_table_concurrently() for a lock-free way to migrate data. Partition creation callback is invoked for each partition if set beforehand (see set_init_callback()).

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)

Performs RANGE partitioning for relation by partitioning expression expr, start_value argument specifies initial value, p_interval sets the default range for auto created partitions or partitions created with append_range_partition() or prepend_range_partition() (if NULL then auto partition creation feature won’t work), p_count is the number of premade partitions (if not set then lt_pathman tries to determine it based on expression’s values). The bounds array can be built using generate_range_bounds(). Partition creation callback is invoked for each partition if set beforehand.

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().

partition_table_concurrently(relation   REGCLASS,
                             batch_size INTEGER DEFAULT 1000,
                             sleep_time FLOAT8 DEFAULT 1.0)

Starts a background worker to move data from parent table to partitions. The worker utilizes short transactions to copy small batches of data (up to 10K rows per transaction) and thus doesn’t significantly interfere with user’s activity. If the worker is unable to lock rows of a batch, it sleeps for sleep_time seconds before the next attempt and tries again up to 60 times, and quits if it’s still unable to lock the batch.

stop_concurrent_part_task(relation REGCLASS)

Stops a background worker performing a concurrent partitioning task. Note: worker will exit after it finishes relocating a current batch.

Triggers are no longer required nor for INSERTs, neither for cross-partition UPDATEs. However, user-supplied triggers are supported:

replace_hash_partition(old_partition REGCLASS,
                       new_partition REGCLASS,
                       lock_parent   BOOLEAN DEFAULT TRUE)

Replaces specified partition of HASH-partitioned table with another table. The lock_parent parameter will prevent any INSERT/UPDATE/ALTER TABLE queries to parent table.

split_range_partition(partition_relid REGCLASS,
                      split_value     ANYELEMENT,
                      partition_name  TEXT DEFAULT NULL,
                      tablespace      TEXT DEFAULT NULL)

Split RANGE partition in two by split_value. Partition creation callback is invoked for a new partition if available.

merge_range_partitions(variadic partitions REGCLASS[])

Merge several adjacent RANGE partitions. Partitions are automatically ordered by increasing bounds; all the data will be accumulated in the first partition.

append_range_partition(parent_relid   REGCLASS,
                       partition_name TEXT DEFAULT NULL,
                       tablespace     TEXT DEFAULT NULL)

Append new RANGE partition with pathman_config.range_interval as interval.

prepend_range_partition(parent_relid   REGCLASS,
                        partition_name TEXT DEFAULT NULL,
                        tablespace     TEXT DEFAULT NULL)

Prepend new RANGE partition with pathman_config.range_interval as interval.

add_range_partition(parent_relid   REGCLASS,
                    start_value    ANYELEMENT,
                    end_value      ANYELEMENT,
                    partition_name TEXT DEFAULT NULL,
                    tablespace     TEXT DEFAULT NULL)

Create new RANGE partition for relation with specified range bounds. If start_value or end_value are NULL then corresponding range bound will be infinite.

drop_range_partition(partition TEXT, delete_data BOOLEAN DEFAULT TRUE)

Drop RANGE partition and all of its data if delete_data is true.

attach_range_partition(parent_relid    REGCLASS,
                       partition_relid REGCLASS,
                       start_value     ANYELEMENT,
                       end_value       ANYELEMENT)

Attach partition to the existing RANGE-partitioned relation. The attached table must have exactly the same structure as the parent table, including the dropped columns. Partition creation callback is invoked if set (see pathman_config_params).

detach_range_partition(partition_relid REGCLASS)

Detach partition from the existing RANGE-partitioned relation.

disable_pathman_for(parent_relid REGCLASS)

Permanently disable lt_pathman partitioning mechanism for the specified parent table and remove the insert trigger if it exists. All partitions and data remain unchanged.

drop_partitions(parent_relid REGCLASS,
                delete_data  BOOLEAN DEFAULT FALSE)

Drop partitions of the parent table (both foreign and local relations). If delete_data is false, the data is copied to the parent table first. Default is false.

To remove partitioned table along with all partitions fully, use conventional DROP TABLE relation CASCADE. However, care should be taken in somewhat rare case when you are running logical replication and DROP was executed by replication apply worker, e.g. via trigger on replicated table. lt_pathman uses pathman_ddl_trigger event trigger to remove the record about dropped table from pathman_config, and this trigger by default won’t fire on replica, leading to inconsistent state when lt_pathman thinks that the table still exists, but in fact it doesn’t. If this is the case, configure this trigger to fire on replica too:

ALTER EVENT TRIGGER pathman_ddl_trigger ENABLE ALWAYS;

Physical replication doesn’t have this problem since DDL as well as pathman_config table is replicated too; master and slave LightDB instances are basically identical, and it is only harmful to keep this trigger in ALWAYS mode.

set_interval(relation REGCLASS, value ANYELEMENT)

Update RANGE partitioned table interval. Note that interval must not be negative and it must not be trivial, i.e. its value should be greater than zero for numeric types, at least 1 microsecond for TIMESTAMP and at least 1 day for DATE.

set_enable_parent(relation REGCLASS, value BOOLEAN)

Include/exclude parent table into/from query plan. In original LightDB planner parent table is always included into query plan even if it’s empty which can lead to additional overhead. You can use disable_parent() if you are never going to use parent table as a storage. Default value depends on the partition_data parameter that was specified during initial partitioning in create_range_partitions() function. If the partition_data parameter was true then all data have already been migrated to partitions and parent table disabled. Otherwise it is enabled.

set_auto(relation REGCLASS, value BOOLEAN)

Enable/disable auto partition propagation (only for RANGE partitioning). It is enabled by default.

set_init_callback(relation REGCLASS, callback REGPROC DEFAULT 0)

Set partition creation callback to be invoked for each attached or created partition (both HASH and RANGE). If callback is marked with SECURITY INVOKER, it’s executed with the privileges of the user that produced a statement which has led to creation of a new partition (e.g. INSERT INTO partitioned_table VALUES (-5)). The callback must have the following signature: part_init_callback(args JSONB) RETURNS VOID. Parameter arg consists of several fields whose presence depends on partitioning type:

/* 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"
}

set_set_spawn_using_bgw(relation REGCLASS, value BOOLEAN)

When INSERTing new data beyond the partitioning range, use SpawnPartitionsWorker to create new partitions in a separate transaction.

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);

This table stores a list of partitioned tables.

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);

This table stores optional parameters which override standard behavior.

-- 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 'lt_pathman', 'show_concurrent_part_tasks_internal'
LANGUAGE C STRICT;

CREATE OR REPLACE VIEW pathman_concurrent_part_tasks
AS SELECT * FROM show_concurrent_part_tasks();

This view lists all currently running concurrent partitioning tasks.

-- 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 'lt_pathman', 'show_partition_list_internal'
LANGUAGE C STRICT;

CREATE OR REPLACE VIEW pathman_partition_list
AS SELECT * FROM show_partition_list();

This view lists all existing partitions, as well as their parents and range boundaries (NULL for HASH partitions).

-- helper SRF function
CREATE OR REPLACE FUNCTION @extschema@.show_cache_stats()
RETURNS TABLE (
    context     TEXT,
    size        INT8,
    used        INT8,
    entries     INT8)
AS 'lt_pathman', 'show_cache_stats_internal'
LANGUAGE C STRICT;

CREATE OR REPLACE VIEW @extschema@.pathman_cache_stats
AS SELECT * FROM @extschema@.show_cache_stats();

Shows memory consumption of various caches.

SELECT tableoid::regclass AS partition, * FROM partitioned_table;

SELECT * FROM pathman_concurrent_part_tasks;
 userid | pid  | dbid  | relid | processed | status
--------+------+-------+-------+-----------+---------
 dmitry | 7367 | 16384 | test  |    472000 | working
(1 row)

SELECT drop_range_partition(partition, false) /* move data to parent */
FROM pathman_partition_list
WHERE parent = 'part_test'::regclass AND range_min::int < 500;
NOTICE:  1 rows copied from part_test_11
NOTICE:  100 rows copied from part_test_1
NOTICE:  100 rows copied from part_test_2
 drop_range_partition
----------------------
 dummy_test_11
 dummy_test_1
 dummy_test_2
(3 rows)

Consider an example of HASH partitioning. First create a table with some integer column:

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', 100);

This will create new partitions and move the data from parent to partitions.

Here’s an example of the query performing filtering by partitioning key:

SELECT * FROM items WHERE id = 1234;
  id  |               name               | code
------+----------------------------------+------
 1234 | 81dc9bdb52d04dc20036dbd8313ed055 | 1855
(1 row)

EXPLAIN SELECT * FROM items WHERE id = 1234;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Append  (cost=0.28..8.29 rows=0 width=0)
   ->  Index Scan using items_34_pkey on items_34  (cost=0.28..8.29 rows=0 width=0)
         Index Cond: (id = 1234)

Notice that the Append node contains only one child scan which corresponds to the WHERE clause.

To access parent table use ONLY modifier:

EXPLAIN SELECT * FROM ONLY items;
                      QUERY PLAN
------------------------------------------------------
 Seq Scan on items  (cost=0.00..0.00 rows=1 width=45)

Consider an example of RANGE partitioning. Let’s create a table containing some dummy logs:

CREATE TABLE journal (
    id      SERIAL,
    dt      TIMESTAMP NOT NULL,
    level   INTEGER,
    msg     TEXT);

-- similar index will also be created for each partition
CREATE INDEX ON journal(dt);

-- generate some data
INSERT INTO journal (dt, level, msg)
SELECT g, random() * 6, md5(g::text)
FROM generate_series('2015-01-01'::date, '2015-12-31'::date, '1 minute') as g;

Run the create_range_partitions() function to create partitions so that each partition would contain the data for one day:

SELECT create_range_partitions('journal', 'dt', '2015-01-01'::date, '1 day'::interval);

It will create 365 partitions and move the data from parent to partitions.

New partitions are appended automaticaly by insert trigger, but it can be done manually with the following functions:

-- add new partition with specified range
SELECT add_range_partition('journal', '2016-01-01'::date, '2016-01-07'::date);

-- append new partition with default range
SELECT append_range_partition('journal');

The first one creates a partition with specified range. The second one creates a partition with default interval and appends it to the partition list. It is also possible to attach an existing table as partition. For example, we may want to attach an archive table (or even foreign table from another server) for some outdated data:

CREATE FOREIGN TABLE journal_archive (
    id      INTEGER NOT NULL,
    dt      TIMESTAMP NOT NULL,
    level   INTEGER,
    msg     TEXT)
SERVER archive_server;

SELECT attach_range_partition('journal', 'journal_archive', '2014-01-01'::date, '2015-01-01'::date);

To merge to adjacent partitions, use the merge_range_partitions() function:

SELECT merge_range_partitions('journal_archive', 'journal_1');

To split partition by value, use the split_range_partition() function:

SELECT split_range_partition('journal_366', '2016-01-03'::date);

To detach partition, use the detach_range_partition() function:

SELECT detach_range_partition('journal_archive');

Here’s an example of the query performing filtering by partitioning key:

SELECT * FROM journal WHERE dt >= '2015-06-01' AND dt < '2015-06-03';
   id   |         dt          | level |               msg
--------+---------------------+-------+----------------------------------
 217441 | 2015-06-01 00:00:00 |     2 | 15053892d993ce19f580a128f87e3dbf
 217442 | 2015-06-01 00:01:00 |     1 | 3a7c46f18a952d62ce5418ac2056010c
 217443 | 2015-06-01 00:02:00 |     0 | 92c8de8f82faf0b139a3d99f2792311d
 ...
(2880 rows)

EXPLAIN SELECT * FROM journal WHERE dt >= '2015-06-01' AND dt < '2015-06-03';
                            QUERY PLAN
------------------------------------------------------------------
 Append  (cost=0.00..58.80 rows=0 width=0)
   ->  Seq Scan on journal_152  (cost=0.00..29.40 rows=0 width=0)
   ->  Seq Scan on journal_153  (cost=0.00..29.40 rows=0 width=0)
(3 rows)

There are several user-accessible GUC variables designed to toggle the whole module or specific custom nodes on and off:

To permanently disable lt_pathman for some previously partitioned table, use the disable_pathman_for() function:

SELECT disable_pathman_for('range_rel');

All sections and data will remain unchanged and will be handled by the standard LightDB inheritance mechanism.