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

Banco de Dados

Pane em sistema apaga 16.500 processos do TCE-AM: 4Linux é contratada para recuperação

O TCE-AM (Tribunal de Contas do Estado do Amazonas) teve 16.500 processos apagados indevidamente devido a uma pane nos sistemas e-Contas e Spede (Sistema de Processos e Documentos Eletrônicos) depois

Banco de Dados

Como Recuperar a Senha do Root no MySQL: Guia Passo a Passo

Em muitas situações é necessário resgatar a senha para o usuário root no banco de dados MySQL. Entre elas, a mais comum é a necessidade de prestar manutenção a um

Banco de Dados

Maximize o desempenho do seu banco de dados com a ferramenta pg_activity

O monitoramento eficaz de um banco de dados é crucial para manter um desempenho otimizado e garantir a disponibilidade contínua de suas aplicações. Neste post, vamos explorar como a ferramenta