O PostgreSQL é um sistema de banco de dados muito versátil, capaz de funcionar eficientemente em ambientes de recursos muito baixos e ambientes compartilhados com uma variedade de outros aplicativos. Para garantir que ele seja executado adequadamente em muitos ambientes diferentes, a configuração padrão é muito conservadora e não é muito apropriada para um banco de dados de produção de alto desempenho. Adicione o fato de que os bancos de dados geoespaciais têm padrões de uso diferentes, e os dados tendem a consistir em menos registros, muito maiores do que os bancos de dados não espaciais, e você pode ver que a configuração padrão não será totalmente apropriada para nossos propósitos.

Com esse objetivo, irei apresentar abaixo alguns parâmetros que você pode alterar para que o seu PostGIS “voe”. Para cada parâmetro irei dar uma breve descrição falando para que ele serve, o seu valor padrão e qual o valor que sugiro a você alterá-lo.

Lembrando que essa é uma sugestão e você analisando sua infraestrutura e o volume da sua base de dados pode colocar valores mais apropriados para sua realidade.

1. Configurações no postgresql.conf:

1.1 Shared buffer: Define a quantidade de memória que o servidor de banco de dados usa para buffers de memória compartilhada. Estes são compartilhados entre os processos de back-end, como o nome sugere. Os valores padrão são normalmente insuficientes para bancos de dados de produção.

Valor padrão: 32 mb
Valor recomendado: Até 75% da memória do banco de dados

1.2 Work men: Define a quantidade de memória que as operações internas de classificação e as tabelas de hash podem consumir antes de o banco de dados alternar para arquivos em disco. Este valor define a memória disponível para cada operação; consultas complexas podem ter várias operações de classificação ou hash sendo executadas em paralelo e cada sessão conectada pode estar executando uma consulta.

Dessa forma, você deve considerar quantas conexões e a complexidade das consultas esperadas antes de aumentar esse valor. O benefício de aumentar é que o processamento de mais dessas operações, incluindo cláusulas ORDER BY e DISTINCT, junções de mesclagem e hash, agregação baseada em hash e processamento baseado em hash de subconsultas, pode ser realizado sem incorrer em gravações em disco.

Valor padrão: 4 mb
Valor recomendado: 16 MB

1.3 Maintenance_work_mem: Define a quantidade de memória usada para operações de manutenção, incluindo criação de aspiração, índice e chave estrangeira. Como essas operações não são muito comuns, o valor padrão pode ser aceitável.

Valor padrão: 64 mb
Valor recomendado: 64 MB

1.4 Wal_buffers: Define a quantidade de memória usada para dados de registro de adiantamento (WAL). Os registros de write-ahead fornecem um mecanismo de alto desempenho para garantir a integridade dos dados. Durante cada comando de alteração, os efeitos das alterações são gravados primeiro nos arquivos WAL e liberados no disco. Apenas quando os arquivos WAL forem liberados, as alterações serão gravadas nos próprios arquivos de dados. Isso permite que os arquivos de dados sejam gravados no disco de maneira ideal e assíncrona, garantindo que, em caso de falha, todas as alterações de dados possam ser recuperadas do WAL.

O tamanho desse buffer precisa ser grande o suficiente para conter os dados do WAL para uma única transação típica. Embora o valor padrão geralmente seja suficiente para a maioria dos dados, os dados geoespaciais tendem a ser muito maiores. Portanto, recomenda-se aumentar o tamanho desse parâmetro.

Valor padrão: 64k
Valor recomendado: 1 MB

1.5 Random_page_cost: Esse é um valor sem unidade que representa o custo de um acesso aleatório a uma página do disco. Esse valor é relativo a vários outros parâmetros de custo, incluindo acesso sequencial à página e custos de operação da CPU. Embora não haja um marcador mágico para esse valor, o padrão é geralmente conservador.

Valor padrão: 4.0
Valor recomendado: 2.0

1.6 Seq_page_cost: Esse é o parâmetro que controla o custo de um acesso de página seqüencial. Esse valor geralmente não requer ajuste, mas a diferença entre esse valor e random_page_cost afeta muito as escolhas feitas pelo planejador de consulta. Esse valor também pode ser definido em uma base por sessão
Valor padrão: 1.0
Valor recomendado: 1.0

Após alterar a configuração do banco é necessário reiniciar o serviço para que elas possam ser carregadas. Com a alteração desses parâmetros o banco de dados já deve ter um aumento significativo na performance.

Se preferir pode assistir ao vídeo do YouTube com esse contéudo:



Fonte: Boundless