Descrição

Atualmente estou enfrentando desafios de desempenho em um cenário de banco de dados PostgreSQL envolvendo uma tabela pai e 30 tabelas filho. Notavelmente, nenhuma dessas tabelas é particionada e algumas das tabelas secundárias têm tamanhos individuais substanciais, com um tamanho cumulativo de todas as tabelas atingindo 5 TB. Apesar de ter índices nas tabelas pai e filho, a execução de consultas, como a mostrada abaixo, leva um longo período, geralmente várias horas.

Pergunta

Estou buscando orientação sobre como otimizar o desempenho neste contexto. Existem configurações específicas, além dos índices, que poderiam melhorar significativamente a velocidade de consulta para uma estrutura de banco de dados tão grande e complexa?

Além disso, estou curioso para saber se o PostgreSQL pode ter limitações inerentes ao manuseio eficiente de bancos de dados desse tamanho e peso. Em caso afirmativo, existem estratégias alternativas que poderiam ser consideradas para um melhor desempenho?

Tem havido especulações sobre o PostgreSQL enfrentar desafios com E/S de disco, especialmente quando comparado a outros bancos de dados como Oracle ou NoSQL. Esta especulação é precisa?

Informações

Versão

Executando no Google Cloud SQL

PostgreSQL 13.12 em x86_64-pc-linux-gnu, compilado pelo Debian clang versão 12.0.1, 64 bits

Mesa

CREATE TABLE mytable (
    id_pos int8 NOT NULL PRIMARY KEY,
    date_insert DATE DEFAULT NOW()
);

CREATE TABLE mytable_child1 (
    id_pos int8 NOT NULL PRIMARY KEY,
    date_insert DATE DEFAULT NOW(),
    other_field varchar(10) NOT NULL
) INHERITS (mytable);

Índices

Em cada tabela (pai e filho) tenho esses índices

CREATE INDEX IF NOT EXISTS mytable_date_insert_idx ON mytable USING btree (date_insert);

CREATE INDEX IF NOT EXISTS mytable_child1_date_insert_idx ON mytable_child1 USING btree (date_insert);

Consulta

SELECT * FROM mytable WHERE date_insert >= CURRENT_DATE - INTERVAL 1 MONTH;