Descubra como otimizar seu banco de dados com particionamento no PostgreSQL

O particionamento é um dos recursos mais desejados do PostgreSQL, amplamente adotado pelos desenvolvedores. Isso não é verdade apenas para o PostgreSQL 15, mas também para versões mais antigas que não ofereciam tantos recursos quanto a versão mais recente do banco de dados. É por isso que você deve saber não apenas como particionar tabelas corretamente, mas também como descobrir a qual partição um dado pertence.

O particionamento no PostgreSQL é uma técnica que permite dividir uma tabela grande em várias partes menores, conhecidas como partições, de forma transparente para o usuário. Cada partição é armazenada em um arquivo separado, o que pode melhorar o desempenho em consultas e operações de manutenção de dados.

Existem dois tipos de particionamento no PostgreSQL:

1. Particionamento por intervalo: as partições são criadas com base em um intervalo de valores de uma coluna específica da tabela. Por exemplo, se a tabela for particionada por data, cada partição pode conter os dados de um determinado ano.
2. Particionamento por lista: as partições são criadas com base em uma lista de valores de uma coluna específica da tabela. Por exemplo, se a tabela for particionada por região, cada partição pode conter os dados de uma região específica.

Para criar partições em uma tabela no PostgreSQL, é necessário seguir os seguintes passos:

1. Criar a tabela pai: essa tabela deve conter a definição das colunas que serão particionadas, mas não deve ter dados.
2. Criar as tabelas filhas: cada tabela filha representa uma partição da tabela pai e deve conter a mesma estrutura da tabela pai.
3. Definir as regras de particionamento: isso é feito usando a cláusula “CREATE TABLE … PARTITION BY” e especificando o tipo de particionamento e a coluna que será usada como referência.
4. Inserir dados nas partições: isso pode ser feito usando a cláusula “INSERT INTO … VALUES” ou “INSERT INTO … SELECT” para inserir dados em cada partição.

Portanto, há uma pequena questão que sempre surge: como descubro em qual partição acabei de inserir meus dados? Alguns pequenos “truques de velho consultor” podem ajudar a descobrir.

Para começar, primeiro você precisa criar uma atabela particionada:

CREATE TABLE t_turnover (

    id          serial,

    country     text,

    t           timestamptz,

    task        text,

    turnover    numeric

) PARTITION BY LIST (country);

CREATE TABLE t_austria

    PARTITION OF t_turnover FOR VALUES IN ('Austria');

CREATE TABLE t_usa

    PARTITION OF t_turnover FOR VALUES IN ('USA');

CREATE TABLE t_ger_swiss

    PARTITION OF t_turnover FOR VALUES IN ('Germany', 'Switzerland');

Nesse caso, você criou uma tabela simples para armazenar o faturamento. Atualmente, 4 idiomas são suportados. No entanto, muitas vezes você desejará dizer ao PostgreSQL que todos os outros dados devem ir para uma partição padrão. Veja como criar essa partição padrão:

CREATE TABLE t_rest PARTITION OF t_turnover DEFAULT ; CREATE TABLE

Esta tabela conterá todos os dados que não devem ir para nenhuma outra partição.

Insert em uma tabela particionada

Agora, vamos inserir nesta tabela e ver o que acontece:

sample=# \x

Expanded display is on.

sample=# INSERT INTO t_turnover (country, t, task, turnover)

VALUES ('Uganda', now(), 'Some task', 200)

RETURNING tableoid::regclass, *;

-[ RECORD 1 ]---------------------------

tableoid | t_rest

id       | 1

country  | Uganda

t        | 2022-11-01 09:35:38.991547+01

task     | Some task

turnover | 200

Aplicamos um pequeno truque aqui: No caso de INSERT, você pode usar RETURNING *, que é uma maneira simples e comumente usada para ver o que você acabou de inserir. Porém, há mais: Existe uma coluna oculta chamada “tableoid”: Ela representa o ID do objeto da tabela que você acabou de inserir. Observe que este é o ID do objeto da partição – não da tabela pai.

Select de uma tabela particionada

Até agora, você viu que pode usar o tableoid para determinar a partição em INSERT. No entanto, você também pode fazer a mesma coisa ao consultar a tabela:

sample=# SELECT tableoid, tableoid::regclass, * FROM t_turnover ORDER BY id;

 tableoid | tableoid  | id | country |               t               |        task        | turnover

----------+-----------+----+---------+-------------------------------+--------------------+----------

    27616 | t_rest    |  1 | Uganda  | 2022-11-01 09:35:30.193676+01 | Some task          |      200

    27597 | t_austria |  3 | Austria | 2022-11-01 11:11:40.583728+01 | PostgreSQL support |      900

(3 rows)

sample=#    SELECT  tableoid::regclass, count(*)

FROM        t_turnover

GROUP BY 1

ORDER BY 1;

 tableoid  | count

-----------+-------

 t_austria |     1

 t_rest    |     1

(2 rows)