Casos de uso comuns da extensão pg_pathman - PolarDB: Central de Documentação do Alibaba Cloud

Este tópico descreve os casos de uso comuns da extensão pg_pathman.

Informações básicas

O PolarDB for PostgreSQL inclui a extensão pg_pathman para melhorar o desempenho de tabelas particionadas. Essa extensão oferece um mecanismo de gerenciamento e otimização de partições.

Crie a extensão pg_pathman

Nota

Para usar os recursos de gerenciamento de partições da extensão pg_pathman, entre em contato conosco.

CREATE EXTENSION IF NOT EXISTS pg_pathman;

Após criar a extensão, execute a seguinte instrução SQL para visualizar sua versão:

SELECT extname,extversion FROM pg_extension WHERE extname = 'pg_pathman';

O resultado retornado é semelhante ao seguinte:

  extname   | extversion 
------------+------------
 pg_pathman | 1.5
(1 row)

Atualize a extensão

O PolarDB for PostgreSQL atualiza regularmente seus plugins para oferecer serviços de banco de dados aprimorados. É necessário atualizar o cluster para a versão mais recente antes de atualizar um plugin.

Recursos da extensão

Oferece suporte a particionamento por hash e por intervalo.
Permite o gerenciamento automático de partições, usando funções para criar partições e migrar dados da tabela primária. Também suporta gerenciamento manual, possibilitando anexar ou desanexar tabelas existentes por meio de funções.
Compatível com tipos de dados comuns para colunas de chave de partição, como int, float e date, incluindo domínios personalizados.
Gera planos de consulta eficientes para tabelas particionadas, inclusive para JOINs e subconsultas.
Usa nós de plano personalizados, RuntimeAppend e RuntimeMergeAppend, para implementar a seleção dinâmica de partições.
O PartitionFilter fornece uma alternativa eficiente aos gatilhos de inserção.
Suporta a criação automática de novas partições. Atualmente, esse recurso está disponível apenas para tabelas particionadas por intervalo.
Permite operações diretas de leitura e escrita em tabelas particionadas usando copy from/to para maior eficiência.
Aceita atualizações nas colunas de chave de partição por meio de um gatilho. Para evitar possíveis impactos no desempenho, não adicione esse gatilho se não houver necessidade de atualizar as colunas de chave de partição.
Possibilita a definição de funções de callback personalizadas, acionadas automaticamente quando uma partição é criada.
Cria tabelas particionadas e migra dados da tabela primária para as partições em segundo plano, sem bloquear operações.
Compatível com Foreign Data Wrappers (FDWs). Configure o parâmetro pg_pathman.insert_into_fdw=(disabled | postgres | any_fdw) para dar suporte ao postgres_fdw ou a qualquer outro FDW.

Para obter mais informações, consulte o projeto no GitHub.

Views e tabelas relacionadas

A extensão pg_pathman usa funções para manter tabelas particionadas e fornece diversas views para verificar o status dessas tabelas. As views são as seguintes:

pathman_config

CREATE TABLE IF NOT EXISTS pathman_config (
    partrel         REGCLASS NOT NULL PRIMARY KEY,  -- OID of the primary table
    attname         TEXT NOT NULL,  -- Name of the partition key column
    parttype        INTEGER NOT NULL,  -- Partitioning type (hash or range)
    range_interval  TEXT,  -- Interval for range partitions

    CHECK (parttype IN (1, 2)) /* check for allowed part types */ );

pathman_config_params

CREATE TABLE IF NOT EXISTS pathman_config_params (
    partrel        REGCLASS NOT NULL PRIMARY KEY,  -- OID of the primary table
    enable_parent  BOOLEAN NOT NULL DEFAULT TRUE,  -- Specifies whether to filter the primary table in the optimizer
    auto           BOOLEAN NOT NULL DEFAULT TRUE,  -- Specifies whether to automatically create a new partition if it does not exist during an insert operation
    init_callback  REGPROCEDURE NOT NULL DEFAULT 0);  -- OID of the callback function for partition creation

pathman_concurrent_part_tasks

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

-- helper SRF function
CREATE OR REPLACE FUNCTION show_partition_list()
RETURNS TABLE (
    parent     REGCLASS,
    partition  REGCLASS,
    parttype   INT4,
    partattr   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();

Gerenciamento de partições

Particionamento por intervalo

Quatro funções de gerenciamento estão disponíveis para criar partições por intervalo. Duas delas permitem especificar um valor inicial, um intervalo e a quantidade de partições. Suas definições são as seguintes:

create_range_partitions(relation       REGCLASS,  -- OID of the primary table
                        attribute      TEXT,      -- Name of the partition key column
                        start_value    ANYELEMENT,  -- Start value
                        p_interval     ANYELEMENT,  -- Interval. Can be of any data type suitable for any type of partitioned table.
                        p_count        INTEGER DEFAULT NULL,   -- Number of partitions to create
                        partition_data BOOLEAN DEFAULT TRUE)   -- Specifies whether to immediately migrate data from the primary table to partitions. This is not recommended. Use the non-blocking migration function partition_table_concurrently() instead.

create_range_partitions(relation       REGCLASS,  -- OID of the primary table
                        attribute      TEXT,      -- Name of the partition key column
                        start_value    ANYELEMENT,  -- Start value
                        p_interval     INTERVAL,    -- Interval. The interval data type is used for time-based partitioned tables.
                        p_count        INTEGER DEFAULT NULL,   -- Number of partitions to create
                        partition_data BOOLEAN DEFAULT TRUE)   -- Specifies whether to immediately migrate data from the primary table to partitions. This is not recommended. Use the non-blocking migration function partition_table_concurrently() instead.

As outras duas funções permitem especificar um valor inicial, um valor final e um intervalo. Suas definições são as seguintes:

create_partitions_from_range(relation       REGCLASS,  -- OID of the primary table
                             attribute      TEXT,      -- Name of the partition key column
                             start_value    ANYELEMENT,  -- Start value
                             end_value      ANYELEMENT,  -- End value
                             p_interval     ANYELEMENT,  -- Interval. Can be of any data type suitable for any type of partitioned table.
                             partition_data BOOLEAN DEFAULT TRUE)   -- Specifies whether to immediately migrate data from the primary table to partitions. This is not recommended. Use the non-blocking migration function partition_table_concurrently() instead.

create_partitions_from_range(relation       REGCLASS,  -- OID of the primary table
                             attribute      TEXT,      -- Name of the partition key column
                             start_value    ANYELEMENT,  -- Start value
                             end_value      ANYELEMENT,  -- End value
                             p_interval     INTERVAL,    -- Interval. The interval data type is used for time-based partitioned tables.
                             partition_data BOOLEAN DEFAULT TRUE)   -- Specifies whether to immediately migrate data from the primary table to partitions. This is not recommended. Use the non-blocking migration function partition_table_concurrently() instead.

Exemplo:

Crie uma tabela primária a ser particionada e insira dados de teste.

--- Create the primary table to be partitioned
CREATE TABLE part_test(id int, info text, crt_time timestamp not null);  -- The partition key column must have a NOT NULL constraint  

--- Insert test data to simulate a primary table that already contains data
INSERT INTO part_test SELECT id,md5(random()::text),clock_timestamp() + (id||' hour')::interval from generate_series(1,10000) t(id);

Consulte os dados na tabela primária:

SELECT * FROM part_test limit 10;

O resultado retornado é semelhante ao seguinte:

 id |               info               |          crt_time          
----+----------------------------------+----------------------------
  1 | 36fe1adedaa5b848caec4941f87d443a | 2016-10-25 10:27:13.206713
  2 | c7d7358e196a9180efb4d0a10269c889 | 2016-10-25 11:27:13.206893
  3 | 005bdb063550579333264b895df5b75e | 2016-10-25 12:27:13.206904
  4 | 6c900a0fc50c6e4da1ae95447c89dd55 | 2016-10-25 13:27:13.20691
  5 | 857214d8999348ed3cb0469b520dc8e5 | 2016-10-25 14:27:13.206916
  6 | 4495875013e96e625afbf2698124ef5b | 2016-10-25 15:27:13.206921
  7 | 82488cf7e44f87d9b879c70a9ed407d4 | 2016-10-25 16:27:13.20693
  8 | a0b92547c8f17f79814dfbb12b8694a0 | 2016-10-25 17:27:13.206936
  9 | 2ca09e0b85042b476fc235e75326b41b | 2016-10-25 18:27:13.206942
 10 | 7eb762e1ef7dca65faf413f236dff93d | 2016-10-25 19:27:13.206947
(10 rows)

Crie as partições. Cada partição conterá dados referentes a um período de um mês.

--- Create partitions. Each partition contains data for a one-month span.  
SELECT                                             
create_range_partitions('part_test'::regclass,             -- OID of the primary table
                        'crt_time',                        -- Name of the partition key column
                        '2016-10-25 00:00:00'::timestamp,  -- Start value
                        interval '1 month',                -- Interval. The interval data type is used for time-based partitioned tables.
                        24,                                -- Number of partitions to create
                        false) ;                           -- Do not migrate data

Use a função de migração sem bloqueio para migrar os dados da tabela primária.

--- Before data migration, the data is still in the primary table
SELECT count(*) FROM ONLY part_test;
 count 
-------
 10000
(1 row)

--- Non-blocking migration function  
partition_table_concurrently(relation   REGCLASS,              -- OID of the primary table
                             batch_size INTEGER DEFAULT 1000,  -- Number of records to migrate in a single transaction batch
                             sleep_time FLOAT8 DEFAULT 1.0)    -- The duration to sleep before retrying if acquiring row locks fails. The task exits after 60 retries.

--- Use the non-blocking migration function to migrate data from the primary table
SELECT partition_table_concurrently('part_test'::regclass,
                             10000,
                             1.0);

--- After migration, the primary table is empty. All data has been moved to the partitions.
SELECT count(*) FROM ONLY part_test;
 count 
-------
     0
(1 row)

Após concluir a migração de dados, desative a tabela primária para evitar que ela apareça nos planos de execução.

--- Disable the primary table
SELECT set_enable_parent('part_test'::regclass, false);

--- Verification
EXPLAIN SELECT * FROM part_test WHERE crt_time = '2016-10-25 00:00:00'::timestamp;
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Append  (cost=0.00..16.18 rows=1 width=45)
   ->  Seq Scan on part_test_1  (cost=0.00..16.18 rows=1 width=45)
         Filter: (crt_time = '2016-10-25 00:00:00'::timestamp without time zone)
(3 rows)

Nota

Ao usar tabelas particionadas por intervalo, siga estas práticas recomendadas:

A coluna de chave de partição deve ter uma restrição NOT NULL.
A quantidade de partições deve ser suficiente para cobrir todos os registros existentes.
Use a função de migração sem bloqueio.
Desative a tabela primária após a conclusão da migração de dados.

Particionamento por hash

Uma função de gerenciamento permite criar partições por hash especificando o valor inicial, o intervalo e a quantidade de partições, conforme mostrado abaixo:

create_hash_partitions(relation         REGCLASS,  -- OID of the primary table
                       attribute        TEXT,      -- Name of the partition key column
                       partitions_count INTEGER,   -- Number of partitions to create
                       partition_data   BOOLEAN DEFAULT TRUE)   -- Specifies whether to immediately migrate data from the primary table to partitions. This is not recommended. Use the non-blocking migration function partition_table_concurrently() instead.

Exemplo:

Crie uma tabela primária a ser particionada e insira dados de teste.

--- Create the primary table to be partitioned
CREATE TABLE part_test(id int, info text, crt_time timestamp not null);    -- The partition key column must have a NOT NULL constraint  

--- Insert test data to simulate a primary table that already contains data
INSERT INTO part_test SELECT id,md5(random()::text),clock_timestamp() + (id||' hour')::interval FROM generate_series(1,10000) t(id);

Consulte os dados na tabela primária:

SELECT * FROM part_test limit 10;

O resultado retornado é semelhante ao seguinte:

 id |               info               |          crt_time          
----+----------------------------------+----------------------------
  1 | 29ce4edc70dbfbe78912beb7c4cc95c2 | 2016-10-25 10:47:32.873879
  2 | e0990a6fb5826409667c9eb150fef386 | 2016-10-25 11:47:32.874048
  3 | d25f577a01013925c203910e34470695 | 2016-10-25 12:47:32.874059
  4 | 501419c3f7c218e562b324a1bebfe0ad | 2016-10-25 13:47:32.874065
  5 | 5e5e22bdf110d66a5224a657955ba158 | 2016-10-25 14:47:32.87407
  6 | 55d2d4fd5229a6595e0dd56e13d32be4 | 2016-10-25 15:47:32.874076
  7 | 1dfb9a783af55b123c7a888afe1eb950 | 2016-10-25 16:47:32.874081
  8 | 41eeb0bf395a4ab1e08691125ae74bff | 2016-10-25 17:47:32.874087
  9 | 83783d69cc4f9bb41a3978fe9e13d7fa | 2016-10-25 18:47:32.874092
 10 | affc9406d5b3412ae31f7d7283cda0dd | 2016-10-25 19:47:32.874097
(10 rows)

Crie as partições.

--- Create 128 partitions
SELECT                                              
create_hash_partitions('part_test'::regclass,              -- OID of the primary table
                        'crt_time',                        -- Name of the partition key column
                        128,                               -- Number of partitions to create
                        false) ;                           -- Do not migrate data

Use a função de migração sem bloqueio para migrar os dados da tabela primária.

--- Before data migration, the data is still in the primary table
SELECT count(*) FROM ONLY part_test;
 count 
-------
 10000
(1 row)

--- Non-blocking migration function  
partition_table_concurrently(relation   REGCLASS,              -- OID of the primary table
                             batch_size INTEGER DEFAULT 1000,  -- Number of records to migrate in a single transaction batch
                             sleep_time FLOAT8 DEFAULT 1.0)    -- The duration to sleep before retrying if acquiring row locks fails. The task exits after 60 retries.

--- Use the non-blocking migration function to migrate data from the primary table
SELECT partition_table_concurrently('part_test'::regclass,
                             10000,
                             1.0);

--- After migration, the primary table is empty. All data has been moved to the partitions.
SELECT count(*) FROM ONLY part_test;
 count 
-------
     0
(1 row)

Após concluir a migração de dados, desative a tabela primária para evitar que ela apareça nos planos de execução.

--- Disable the primary table
SELECT set_enable_parent('part_test'::regclass, false);

Verifique o plano de execução:

--- Query a single partition
EXPLAIN SELECT * FROM part_test WHERE crt_time = '2016-10-25 00:00:00'::timestamp;
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Append  (cost=0.00..1.91 rows=1 width=45)
   ->  Seq Scan on part_test_122  (cost=0.00..1.91 rows=1 width=45)
         Filter: (crt_time = '2016-10-25 00:00:00'::timestamp without time zone)
(3 rows)

A restrição a seguir na tabela particionada demonstra que o pg_pathman realiza a conversão automaticamente. Em contraste, a herança tradicional não consegue realizar a poda de partições para uma instrução como SELECT * FROM part_test WHERE crt_time = '2016-10-25 00:00:00'::timestamp;.

\d+ part_test_122
                                Table "public.part_test_122"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_122_3_check" CHECK (get_hash_part_idx(timestamp_hash(crt_time), 128) = 122)
Inherits: part_test

Nota

Ao usar tabelas particionadas por hash, siga estas práticas recomendadas:

A coluna de chave de partição deve ter uma restrição NOT NULL.
Use a função de migração sem bloqueio.
Desative a tabela primária após a conclusão da migração de dados.
O pg_pathman não é limitado pelo formato da expressão. Portanto, uma instrução como select * from part_test where crt_time = '2016-10-25 00:00:00'::timestamp; também pode ser usada para particionamento por hash.
A coluna de chave de partição para particionamento por hash não se limita ao tipo de dados int. Uma função de hash é usada para a conversão automática.

Migrar dados para partições

Caso não tenha migrado os dados da tabela primária durante a criação da tabela particionada, use a função de migração sem bloqueio para realizar essa tarefa.

WITH tmp AS (DELETE FROM primary_table limit xx nowait returning *) INSERT INTO partition SELECT * FROM tmp;

Alternativamente, use a seguinte instrução para marcar as linhas e depois executar as operações DELETE e INSERT.

SELECT array_agg(ctid) FROM primary_table limit xx FOR UPDATE nowait;

A função é definida da seguinte forma:

partition_table_concurrently(relation   REGCLASS,              -- OID of the primary table
                             batch_size INTEGER DEFAULT 1000,  -- Number of records to migrate in a single transaction batch
                             sleep_time FLOAT8 DEFAULT 1.0)    -- The duration to sleep before retrying if acquiring row locks fails. The task exits after 60 retries.

Exemplo:

SELECT partition_table_concurrently('part_test'::regclass,
                             10000,
                             1.0);

Visualize a tarefa de migração de dados em segundo plano.

SELECT * FROM pathman_concurrent_part_tasks;

Dividir uma partição por intervalo

Se uma partição ficar muito grande, divida-a em duas partições. Esse recurso está disponível atualmente apenas para tabelas particionadas por intervalo. Use a seguinte função:

split_range_partition(partition      REGCLASS,            -- OID of the partition
                      split_value    ANYELEMENT,          -- Split value
                      partition_name TEXT DEFAULT NULL)   -- Name of the new partition table created after the split

Exemplo:

Use a tabela particionada do exemplo em Exemplo de particionamento por intervalo. O esquema da tabela é o seguinte.

\d+ part_test
                                  Table "public.part_test"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Child tables: part_test_1,
              part_test_10,
              part_test_11,
              part_test_12,
              part_test_13,
              part_test_14,
              part_test_15,
              part_test_16,
              part_test_17,
              part_test_18,
              part_test_19,
              part_test_2,
              part_test_20,
              part_test_21,
              part_test_22,
              part_test_23,
              part_test_24,
              part_test_3,
              part_test_4,
              part_test_5,
              part_test_6,
              part_test_7,
              part_test_8,
              part_test_9

\d+ part_test_1
                                 Table "public.part_test_1"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_1_3_check" CHECK (crt_time >= '2016-10-25 00:00:00'::timestamp without time zone AND crt_time < '2016-11-25 00:00:00'::timestamp without time zone)
Inherits: part_test

Divida a partição.

SELECT split_range_partition('part_test_1'::regclass,              -- OID of the partition
                      '2016-11-10 00:00:00'::timestamp,     -- Split value
                      'part_test_1_2');                     -- Name of the partition table

As duas tabelas resultantes da divisão são as seguintes:

\d+ part_test_1
                                 Table "public.part_test_1"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_1_3_check" CHECK (crt_time >= '2016-10-25 00:00:00'::timestamp without time zone AND crt_time < '2016-11-10 00:00:00'::timestamp without time zone)
Inherits: part_test

\d+ part_test_1_2 
                                Table "public.part_test_1_2"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_1_2_3_check" CHECK (crt_time >= '2016-11-10 00:00:00'::timestamp without time zone AND crt_time < '2016-11-25 00:00:00'::timestamp without time zone)
Inherits: part_test

Os dados são migrados automaticamente para as novas partições.

SELECT count(*) FROM part_test_1;
 count 
-------
   373
(1 row)

SELECT count(*) FROM part_test_1_2;
 count 
-------
   360
(1 row)

A relação de herança fica da seguinte forma:

\d+ part_test
                                  Table "public.part_test"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Child tables: part_test_1,
              part_test_10,
              part_test_11,
              part_test_12,
              part_test_13,
              part_test_14,
              part_test_15,
              part_test_16,
              part_test_17,
              part_test_18,
              part_test_19,
              part_test_1_2,    -- New table
              part_test_2,
              part_test_20,
              part_test_21,
              part_test_22,
              part_test_23,
              part_test_24,
              part_test_3,
              part_test_4,
              part_test_5,
              part_test_6,
              part_test_7,
              part_test_8,
              part_test_9

Mesclar partições por intervalo

Esse recurso está disponível atualmente apenas para particionamento por intervalo, e as partições devem ser adjacentes. Chame a seguinte função:

--- Specify the two partitions to merge  
merge_range_partitions(partition1 REGCLASS, partition2 REGCLASS)

Veja um exemplo:

Use as tabelas do exemplo em Exemplo de divisão de partição por intervalo para realizar uma operação de mesclagem.

SELECT merge_range_partitions('part_test_1'::regclass, 'part_test_1_2'::regclass);

Nota

Se você tentar mesclar partições não adjacentes, um erro será reportado.

SELECT merge_range_partitions('part_test_2'::regclass, 'part_test_12'::regclass) ;
ERROR:  merge failed, partitions must be adjacent
CONTEXT:  PL/pgSQL function merge_range_partitions_internal(regclass,regclass,regclass,anyelement) line 27 at RAISE
SQL statement "SELECT public.merge_range_partitions_internal($1, $2, $3, NULL::timestamp without time zone)"
PL/pgSQL function merge_range_partitions(regclass,regclass) line 44 at EXECUTE

Após a mesclagem, uma das partições originais é excluída.

\d part_test_1_2
Did not find any relation named "part_test_1_2".

\d part_test_1
             Table "public.part_test_1"
  Column  |            Type             | Modifiers 
----------+-----------------------------+-----------
 id       | integer                     | 
 info     | text                        | 
 crt_time | timestamp without time zone | not null
Check constraints:
    "pathman_part_test_1_3_check" CHECK (crt_time >= '2016-10-25 00:00:00'::timestamp without time zone AND crt_time < '2016-11-25 00:00:00'::timestamp without time zone)
Inherits: part_test

SELECT count(*) FROM part_test_1;
 count 
-------
   733
(1 row)

Adicionar uma partição por intervalo

Se uma tabela primária já estiver particionada, adicione novas partições de várias maneiras. Esta seção descreve três métodos: anexar uma partição por intervalo ao final, adicionar uma partição por intervalo no início e adicionar uma partição por intervalo com um valor inicial especificado.

Anexar uma partição por intervalo ao final

Ao anexar uma partição por intervalo (adicionar uma partição ao final), o intervalo especificado durante a criação inicial da tabela particionada é usado. Consulte a view pathman_config para encontrar o intervalo inicial de cada tabela particionada, conforme mostrado abaixo:

SELECT * FROM pathman_config;
  partrel  | attname  | parttype | range_interval 
-----------+----------+----------+----------------
 part_test | crt_time |        2 | 1 mon
(1 row)

A função para adicionar uma partição é a seguinte. Atualmente, não há suporte para especificar um tablespace.

append_range_partition(parent         REGCLASS,            -- OID of the primary table
                       partition_name TEXT DEFAULT NULL,   -- Name of the new partition table. This is optional.
                       tablespace     TEXT DEFAULT NULL)   -- Tablespace for the new partition table. This is optional.

Veja um exemplo:

SELECT append_range_partition('part_test'::regclass);

\d+ part_test_25
                                Table "public.part_test_25"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_25_3_check" CHECK (crt_time >= '2018-10-25 00:00:00'::timestamp without time zone AND crt_time < '2018-11-25 00:00:00'::timestamp without time zone)
Inherits: part_test

\d+ part_test_24
                                Table "public.part_test_24"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_24_3_check" CHECK (crt_time >= '2018-09-25 00:00:00'::timestamp without time zone AND crt_time < '2018-10-25 00:00:00'::timestamp without time zone)
Inherits: part_test

Adicionar uma partição por intervalo no início

Para adicionar uma partição por intervalo no início, use a seguinte função:

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

Exemplo:

SELECT prepend_range_partition('part_test'::regclass);

\d+ part_test_26
                                Table "public.part_test_26"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_26_3_check" CHECK (crt_time >= '2016-09-25 00:00:00'::timestamp without time zone AND crt_time < '2016-10-25 00:00:00'::timestamp without time zone)
Inherits: part_test

\d+ part_test_1
                                 Table "public.part_test_1"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_1_3_check" CHECK (crt_time >= '2016-10-25 00:00:00'::timestamp without time zone AND crt_time < '2016-11-25 00:00:00'::timestamp without time zone)
Inherits: part_test

Adicionar uma partição com um valor inicial especificado

Adicione uma partição por intervalo especificando seus valores inicial e final. A partição será criada desde que não haja sobreposição com as partições existentes. Esse método não exige a criação de partições contíguas. Por exemplo, se as partições existentes cobrem o período de 2010 a 2015, crie diretamente uma partição para o ano de 2020 sem precisar cobrir o intervalo de 2015 a 2020. A função é a seguinte:

add_range_partition(relation       REGCLASS,    -- OID of the primary table
                    start_value    ANYELEMENT,  -- Start value
                    end_value      ANYELEMENT,  -- End value
                    partition_name TEXT DEFAULT NULL,  -- Name of the partition
                    tablespace     TEXT DEFAULT NULL)  -- Tablespace where the partition is created

Exemplo:

SELECT add_range_partition('part_test'::regclass,    -- OID of the primary table
                    '2020-01-01 00:00:00'::timestamp,  -- Start value
                    '2020-02-01 00:00:00'::timestamp); -- End value

\d+ part_test_27
                                Table "public.part_test_27"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_27_3_check" CHECK (crt_time >= '2020-01-01 00:00:00'::timestamp without time zone AND crt_time < '2020-02-01 00:00:00'::timestamp without time zone)
Inherits: part_test

Exclua uma partição

Para excluir uma única partição por intervalo, use a seguinte função:

drop_range_partition(partition TEXT,   -- Name of the partition
                    delete_data BOOLEAN DEFAULT TRUE)  -- Specifies whether to delete the partition data. If false, the data is migrated to the primary table.  

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

Para excluir todas as partições e especificar se os dados devem ser migrados para a tabela primária, use a seguinte função:

drop_partitions(parent      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.

Exemplo:

Exclua uma partição e migre seus dados para a tabela primária.

SELECT drop_range_partition('part_test_1',false);
SELECT drop_range_partition('part_test_2',false);

Consulte o volume atual de dados da tabela primária:

SELECT count(*) FROM part_test;
 count 
-------
 10000
(1 row)

Exclua uma partição e seus dados. Os dados não serão migrados para a tabela primária.

SELECT drop_range_partition('part_test_3',true);

Consulte o volume atual de dados da tabela primária:

SELECT count(*) FROM part_test;
 count 
-------
  9256
(1 row)

SELECT count(*) FROM ONLY part_test;
 count 
-------
  1453
(1 row)

Exclua todas as partições.

SELECT drop_partitions('part_test'::regclass, false);  -- Delete all partition tables and migrate the data to the primary table

Consulte os dados na tabela primária:

SELECT count(*) FROM part_test;
 count 
-------
  9256
(1 row)

Anexar uma partição

Anexe uma tabela existente a uma tabela primária particionada. A tabela existente deve ter o mesmo esquema da tabela primária, incluindo colunas removidas. Verifique a consistência na view pg_attribute. A função é a seguinte:

attach_range_partition(relation    REGCLASS,    -- OID of the primary table
                       partition   REGCLASS,    -- OID of the partition table
                       start_value ANYELEMENT,  -- Start value
                       end_value   ANYELEMENT)  -- End value

Veja um exemplo:

Crie uma tabela para ser usada como partição.

CREATE TABLE part_test_1 (like part_test including all);

Anexe a tabela existente à tabela primária.

\d+ part_test
                                  Table "public.part_test"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 

\d+ part_test_1
                                 Table "public.part_test_1"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 

SELECT attach_range_partition('part_test'::regclass, 'part_test_1'::regclass, '2019-01-01 00:00:00'::timestamp, '2019-02-01 00:00:00'::timestamp);

Ao anexar uma partição, a relação de herança e as restrições são criadas automaticamente.

\d+ part_test_1
                                 Table "public.part_test_1"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_1_3_check" CHECK (crt_time >= '2019-01-01 00:00:00'::timestamp without time zone AND crt_time < '2019-02-01 00:00:00'::timestamp without time zone)
Inherits: part_test

Desanexar uma partição

Remova uma partição da hierarquia de herança da tabela primária. Essa operação não exclui os dados, mas remove a relação de herança e as restrições. A função é a seguinte:

detach_range_partition(partition REGCLASS)  -- Specify the partition name to convert it into a standard table

Veja um exemplo:

Consulte o volume atual de dados da tabela primária e da partição.

SELECT count(*) FROM part_test;
 count 
-------
  9256
(1 row)

SELECT count(*) FROM part_test_2;
 count 
-------
   733
(1 row)

Desanexe a partição.

SELECT detach_range_partition('part_test_2');

Consulte o volume atual de dados da tabela primária e da partição.

SELECT count(*) FROM part_test_2;
 count 
-------
   733
(1 row)

SELECT count(*) FROM part_test;
 count 
-------
  8523
(1 row)

Desativar o pg_pathman

Desative o pg_pathman para uma única tabela primária particionada. A função é a seguinte:

Importante

A operação disable_pathman_for não pode ser revertida. Use-a com cautela.

\sf disable_pathman_for
CREATE OR REPLACE FUNCTION public.disable_pathman_for(parent_relid regclass)
 RETURNS void
 LANGUAGE plpgsql
 STRICT
AS $function$
BEGIN
        PERFORM public.validate_relname(parent_relid);

        DELETE FROM public.pathman_config WHERE partrel = parent_relid;
        PERFORM public.drop_triggers(parent_relid);

        /* Notify backend about changes */
        PERFORM public.on_remove_partitions(parent_relid);
END
$function$

Veja um exemplo:

SELECT disable_pathman_for('part_test');

\d+ part_test
                                  Table "public.part_test"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Child tables: part_test_10,
              part_test_11,
              part_test_12,
              part_test_13,
              part_test_14,
              part_test_15,
              part_test_16,
              part_test_17,
              part_test_18,
              part_test_19,
              part_test_20,
              part_test_21,
              part_test_22,
              part_test_23,
              part_test_24,
              part_test_25,
              part_test_26,
              part_test_27,
              part_test_28,
              part_test_29,
              part_test_3,
              part_test_30,
              part_test_31,
              part_test_32,
              part_test_33,
              part_test_34,
              part_test_35,
              part_test_4,
              part_test_5,
              part_test_6,
              part_test_7,
              part_test_8,
              part_test_9

\d+ part_test_10
                                Table "public.part_test_10"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_10_3_check" CHECK (crt_time >= '2017-06-25 00:00:00'::timestamp without time zone AND crt_time < '2017-07-25 00:00:00'::timestamp without time zone)
Inherits: part_test

Após desativar a extensão pg_pathman, as relações de herança e as restrições permanecem inalteradas. A única diferença é que a extensão pg_pathman deixa de fornecer uma varredura personalizada no plano de execução. O plano de execução após a desativação da extensão é o seguinte:

EXPLAIN SELECT * FROM part_test WHERE crt_time='2017-06-25 00:00:00'::timestamp;
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Append  (cost=0.00..16.00 rows=2 width=45)
   ->  Seq Scan on part_test  (cost=0.00..0.00 rows=1 width=45)
         Filter: (crt_time = '2017-06-25 00:00:00'::timestamp without time zone)
   ->  Seq Scan on part_test_10  (cost=0.00..16.00 rows=1 width=45)
         Filter: (crt_time = '2017-06-25 00:00:00'::timestamp without time zone)
(5 rows)

Gerenciamento avançado de partições

Desativar a tabela primária

Depois que todos os dados da tabela primária forem migrados para as partições, desative a tabela primária. A função é a seguinte:

set_enable_parent(relation REGCLASS, value BOOLEAN)

Include/exclude parent table into/from query plan. 

In original PostgreSQL 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() or create_partitions_from_range() functions. 

If the partition_data parameter was true then all data have already been migrated to partitions and parent table disabled. 

Otherwise it is enabled.

Exemplo:

SELECT set_enable_parent('part_test', false);

Estender partições automaticamente

Para tabelas particionadas por intervalo, ative a criação automática de partições. Se os dados recém-inseridos não estiverem dentro do intervalo das partições existentes, uma nova partição será criada automaticamente.

set_auto(relation REGCLASS, value BOOLEAN)

Enable/disable auto partition propagation (only for RANGE partitioning). 

It is enabled by default.

Exemplo:

Consulte as partições atuais da tabela de teste.

\d+ part_test
                                  Table "public.part_test"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Child tables: part_test_10,
              part_test_11,
              part_test_12,
              part_test_13,
              part_test_14,
              part_test_15,
              part_test_16,
              part_test_17,
              part_test_18,
              part_test_19,
              part_test_20,
              part_test_21,
              part_test_22,
              part_test_23,
              part_test_24,
              part_test_25,
              part_test_26,
              part_test_3,
              part_test_4,
              part_test_5,
              part_test_6,
              part_test_7,
              part_test_8,
              part_test_9

\d+ part_test_26
                                Table "public.part_test_26"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_26_3_check" CHECK (crt_time >= '2018-09-25 00:00:00'::timestamp without time zone AND crt_time < '2018-10-25 00:00:00'::timestamp without time zone)
Inherits: part_test

\d+ part_test_25
                                Table "public.part_test_25"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_25_3_check" CHECK (crt_time >= '2018-08-25 00:00:00'::timestamp without time zone AND crt_time < '2018-09-25 00:00:00'::timestamp without time zone)
Inherits: part_test

Insira um valor fora do intervalo das partições existentes. Isso cria automaticamente a quantidade necessária de partições com base no intervalo especificado durante a configuração inicial. Observe que essa operação pode levar bastante tempo.

INSERT INTO part_test VALUES (1,'test','2222-01-01'::timestamp);

Consulte o status atual das partições:

\d+ part_test
                                  Table "public.part_test"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Child tables: part_test_10,
              part_test_100,
              part_test_1000,
              part_test_1001,
              ......

Nota

Recomendamos não ativar a criação automática de partições para tabelas particionadas por intervalo. Criar muitas partições automaticamente pode consumir muito tempo.

Funções de callback

Uma função de callback é acionada automaticamente sempre que uma partição é criada. Por exemplo, use uma função de callback para replicação lógica DDL a fim de registrar instruções DDL em uma tabela. A função para definir um callback é a seguinte:

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

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",
    "parttype":  "2",
    "partition": "abc_4",
    "range_max": "401",
    "range_min": "301"
}

/* HASH-partitioned table abc (child abc_0) */
{
    "parent":    "abc",
    "parttype":  "1",
    "partition": "abc_0"
}

Exemplo:

Crie uma função de callback.

CREATE OR REPLACE FUNCTION f_callback_test(jsonb) RETURNS void AS
$$
DECLARE
BEGIN
  CREATE TABLE if NOT EXISTS rec_part_ddl(id serial primary key, parent name, parttype int, partition name, range_max text, range_min text);
  if ($1->>'parttype')::int = 1 then
    raise notice 'parent: %, parttype: %, partition: %', $1->>'parent', $1->>'parttype', $1->>'partition';
    INSERT INTO rec_part_ddl(parent, parttype, partition) values (($1->>'parent')::name, ($1->>'parttype')::int, ($1->>'partition')::name);
  elsif ($1->>'parttype')::int = 2 then
    raise notice 'parent: %, parttype: %, partition: %, range_max: %, range_min: %', $1->>'parent', $1->>'parttype', $1->>'partition', $1->>'range_max', $1->>'range_min';
    INSERT INTO rec_part_ddl(parent, parttype, partition, range_max, range_min) values (($1->>'parent')::name, ($1->>'parttype')::int, ($1->>'partition')::name, $1->>'range_max', $1->>'range_min');
  END if;
END;
$$ LANGUAGE plpgsql strict;

Prepare uma tabela de teste.

CREATE TABLE tt(id int, info text, crt_time timestamp not null);

--- Set the callback function for the test table
SELECT set_init_callback('tt'::regclass, 'f_callback_test'::regproc);

--- Create partitions
SELECT                                                           
create_range_partitions('tt'::regclass,                    -- OID of the primary table
                        'crt_time',                        -- Name of the partition key column
                        '2016-10-25 00:00:00'::timestamp,  -- Start value
                        interval '1 month',                -- Interval. The interval data type is used for time-based partitioned tables.
                        24,                                -- Number of partitions to create
                        false) ;

Verifique se a função de callback foi invocada.

SELECT * FROM rec_part_ddl;

O resultado retornado é semelhante ao seguinte:

 id | parent | parttype | partition |      range_max      |      range_min      
----+--------+----------+-----------+---------------------+---------------------
  1 | tt     |        2 | tt_1      | 2016-11-25 00:00:00 | 2016-10-25 00:00:00
  2 | tt     |        2 | tt_2      | 2016-12-25 00:00:00 | 2016-11-25 00:00:00
  3 | tt     |        2 | tt_3      | 2017-01-25 00:00:00 | 2016-12-25 00:00:00
  4 | tt     |        2 | tt_4      | 2017-02-25 00:00:00 | 2017-01-25 00:00:00
  5 | tt     |        2 | tt_5      | 2017-03-25 00:00:00 | 2017-02-25 00:00:00
  6 | tt     |        2 | tt_6      | 2017-04-25 00:00:00 | 2017-03-25 00:00:00
  7 | tt     |        2 | tt_7      | 2017-05-25 00:00:00 | 2017-04-25 00:00:00
  8 | tt     |        2 | tt_8      | 2017-06-25 00:00:00 | 2017-05-25 00:00:00
  9 | tt     |        2 | tt_9      | 2017-07-25 00:00:00 | 2017-06-25 00:00:00
 10 | tt     |        2 | tt_10     | 2017-08-25 00:00:00 | 2017-07-25 00:00:00
 11 | tt     |        2 | tt_11     | 2017-09-25 00:00:00 | 2017-08-25 00:00:00
 12 | tt     |        2 | tt_12     | 2017-10-25 00:00:00 | 2017-09-25 00:00:00
 13 | tt     |        2 | tt_13     | 2017-11-25 00:00:00 | 2017-10-25 00:00:00
 14 | tt     |        2 | tt_14     | 2017-12-25 00:00:00 | 2017-11-25 00:00:00
 15 | tt     |        2 | tt_15     | 2018-01-25 00:00:00 | 2017-12-25 00:00:00
 16 | tt     |        2 | tt_16     | 2018-02-25 00:00:00 | 2018-01-25 00:00:00
 17 | tt     |        2 | tt_17     | 2018-03-25 00:00:00 | 2018-02-25 00:00:00
 18 | tt     |        2 | tt_18     | 2018-04-25 00:00:00 | 2018-03-25 00:00:00
 19 | tt     |        2 | tt_19     | 2018-05-25 00:00:00 | 2018-04-25 00:00:00
 20 | tt     |        2 | tt_20     | 2018-06-25 00:00:00 | 2018-05-25 00:00:00
 21 | tt     |        2 | tt_21     | 2018-07-25 00:00:00 | 2018-06-25 00:00:00
 22 | tt     |        2 | tt_22     | 2018-08-25 00:00:00 | 2018-07-25 00:00:00
 23 | tt     |        2 | tt_23     | 2018-09-25 00:00:00 | 2018-08-25 00:00:00
 24 | tt     |        2 | tt_24     | 2018-10-25 00:00:00 | 2018-09-25 00:00:00
(24 rows)