Descubra como otimizar seu banco de dados com particionamento no PostgreSQL

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)
O tipo de dados regclass é uma maneira conveniente de transformar um ID de objeto em uma string legível por humanos. Se você usar este truque para determinar a partição em que os dados estão, poderá aplicar ainda mais truques: ninguém impede que você use funções agregadas nesta string para gerar algumas estatísticas sobre o conteúdo de suas partições:
sample=#    SELECT  tableoid::regclass, count(*)
FROM        t_turnover
GROUP BY 1
ORDER BY 1;
 tableoid  | count
-----------+-------
 t_austria |     1
 t_rest    |     1
(2 rows)
A vantagem sobre uma consulta que se concentra no catálogo do sistema é que você pode obter uma contagem real e não apenas estimativas. Também é muito mais fácil do que unir ao resultado dessas partições individuais.
O particionamento pode trazer vários benefícios para o desempenho do banco de dados, especialmente em tabelas muito grandes. No entanto, é importante considerar cuidadosamente a estratégia de particionamento a ser usada, pois isso pode afetar o desempenho de consultas e operações de manutenção de dados. Além disso, o particionamento pode aumentar a complexidade do banco de dados e exigir mais recursos de hardware e manutenção.

Líder em Treinamento e serviços de Consultoria, Suporte e Implantação para o mundo open source. Conheça nossas soluções:

CURSOSCONSULTORIA

Anterior A lenda do arquivo perdido. Domine a busca de arquivos no Linux com o comando find!
Próxima Gerenciamento eficiente de índices no Elasticsearch com o Index Lifecycle Management

About author

Você pode gostar também

DevOps

Crie sua própria Wiki: Guia passo a passo para instalação e uso

Uma wiki é uma coleção de páginas da web interconectadas e editáveis colaborativamente. O termo “wiki” é derivado da palavra havaiana “wiki wiki”, que significa “rápido” ou “veloz”. A característica

Desenvolvimento

Descubra as novidades do PostgreSQL 13: suporte a colações não determinísticas

Com o lançamento recente do PostgreSQL 13 e com a grande maturidade das versões anteriores, algumas das novidades dessas versões mais recentes se tornam cada vez mais disponíveis para uso

Infraestrutura TI

Gerando Dados Aleatórios com Paralelização no Shell: Guia Prático

Gerar dados aleatórios com paralelização no shell. Falando assim parece até alguma coisa muito importante ou difícil, mas vamos entender sua utilidade na prática. Vez ou outra preciso de uma