PostgreSQL ganha roteiro de tuning para rodar analytics sem sacrificar o OLTP

Karen Jex, da equipe de extensões PostgreSQL↳PostgreSQL11 conteúdosPostgreSQL via SSL com GolangData · abr 20195 itens legais sobre data types do PostgreSQLData · mar 20195 serviços gratuitos na cloud para bancos de dados PostgresData · fev 2025Ver tudo em Data → da Snowflake, detalhou no PGConf.EU técnicas de configuração para isolar consultas analíticas pesadas do tráfego transacional do dia a dia.

Karen Jex, engenheira sênior na equipe de extensões PostgreSQL da Snowflake (ela trabalha principalmente no conjunto de extensões pg_lake), apresentou em 20 de outubro de 2026 a palestra "Tuning Postgres for Analytics" no PGConf.EU, em Valência. Os slides e a transcrição saíram no Planet PostgreSQL em 22/10. O recorte da palestra é prático: o que fazer quando você precisa rodar consultas analíticas pesadas direto no banco que também atende sua aplicação, sem um data warehouse separado.

É um problema que qualquer time que opera Postgres em produção já enfrentou: o gerente de produto pede um relatório, alguém escreve uma consulta com GROUP BY, JOIN em várias tabelas e janela analítica, e de repente a aplicação começa a apresentar lentidão sem ninguém saber exatamente por quê.

OLTP e OLAP convivendo no mesmo banco

Jex recupera a distinção clássica. OLTP (Online Transaction Processing) é a carga do dia a dia: muitos usuários concorrentes, consultas curtas, várias transações com SELECT, UPDATE e INSERT por segundo. OLAP (Online Analytical Processing) é o oposto: consultas complexas, JOIN entre várias tabelas, grandes volumes de dados, alto consumo de CPU, memória e I/O.

Quando as duas cargas rodam na mesma instância, o nome técnico é HTAP (Hybrid Transactional/Analytical Processing), termo cunhado pelo Gartner por volta de 2010:

Processamento híbrido de transações e análise (HTAP) é uma arquitetura de aplicação emergente que "rompe o muro" entre processamento de transações e análise. Ela permite uma tomada de decisão mais informada e "em tempo real de negócio".

Hybrid transaction/analytical processing (HTAP) is an emerging application architecture that "breaks the wall" between transaction processing and analytics. It enables more informed and "in business real time" decision making.Gartner, via Wikipedia

A ressalva de Jex é que o desafio de desempenho de cargas mistas já é bem documentado na literatura acadêmica desde 2010-2011, mas a palestra não é uma discussão teórica: é um roteiro de configuração aplicável hoje.

Por que a consulta analítica derruba o banco transacional

Jex lista quatro mecanismos concretos pelos quais uma consulta analítica mal isolada prejudica a aplicação que divide o mesmo banco:

• Um Seq Scan em tabela grande expulsa páginas quentes de OLTP do buffer cache, forçando o tráfego transacional a ler do disco em vez da memória.
• Workers paralelos de agregação competem por CPU com os backends que atendem a aplicação.
• Há disputa direta por banda de I/O entre a varredura analítica e as gravações do OLTP.
• Transações analíticas longas seguram locks e bloqueiam o VACUUM, o que degrada a saúde da tabela ao longo do tempo.

O ambiente de teste: uma rede de cafeterias

Para ilustrar, Jex monta um cenário didático: uma rede fictícia de cafeterias em Valência com uma tabela cafe_sales contendo alguns milhões de linhas por loja (transaction_id, transaction_date, store_id, product_category, unit_price, sales_tax_pct, entre outras colunas). A carga transacional é simulada com pgbench, a ferramenta de benchmark que acompanha o Postgres por padrão.

Com pgbench -i e scale factor 50, ela gera cerca de 5 milhões de linhas (~750MB) na tabela pgbench_accounts, simulando transações baseadas no benchmark TPC-B (três UPDATE, um SELECT e um INSERT por transação). Essa é a carga OLTP de fundo que roda em paralelo às consultas analíticas durante os testes.

A consulta analítica usada como referência responde a uma pergunta de negócio real: qual loja teve a maior receita em cada mês do ano passado, considerando só vendas acima de 20 euros. A query combina uma CTE, agregação com SUM, e RANK() OVER (PARTITION BY ...) para eleger a loja vencedora de cada mês:

WITH ranked_sales AS (
 SELECT
 DATE_TRUNC('month', transaction_date) AS month,
 SUM(unit_price*(1+sales_tax_pct/100)*transaction_qty) AS total_monthly_sales,
 RANK() OVER (PARTITION BY DATE_TRUNC('month', transaction_date)
 ORDER BY SUM(unit_price*(1+sales_tax_pct/100)*transaction_qty) DESC) AS sales_rank
 FROM cafe_sales
 WHERE date_trunc('year',transaction_date) = date_trunc('year',now()) - interval '1 year'
 AND unit_price*(1+sales_tax_pct/100)*transaction_qty > 20
 GROUP BY 1, 2)
SELECT to_char(month, 'YYYY-MM') AS month, store, round(total_monthly_sales,2) AS total_monthly_sales
FROM ranked_sales
WHERE sales_rank = 1
ORDER BY month;

O que o EXPLAIN ANALYZE revela sem tuning

Sem nenhuma otimização, em banco ocioso, essa consulta leva cerca de 5 segundos. Rodando EXPLAIN ANALYZE sobre ela, Jex mostra três sintomas que qualquer leitor que já leu um plano de execução vai reconhecer na hora: um Seq Scan paralelo na tabela cafe_sales (leitura completa, sem usar índice), um filtro que descarta 2,8 milhões de linhas depois de lidas, e uma ordenação que não cabe em memória e estoura para disco (Sort Method: external merge Disk: 9288kB).

Com o pgbench rodando em paralelo simulando a carga da aplicação (8 conexões, 4 threads, 120 segundos), o impacto some à vista: em uma janela de 30 segundos, o benchmark processa 127 transações, com latência média de 1834 ms e taxa de apenas 4,36 transações por segundo. Jex é explícita sobre o que importa nesse número: não é o valor absoluto que conta, é como ele muda conforme você aplica cada ajuste de configuração.

As alavancas de configuração que ela recomenda ajustar primeiro

A parte prática da palestra passa por parâmetros que já existem no Postgres e que, segundo Jex, raramente recebem atenção depois do tuning inicial do banco. Primeiro, conexões: max_connections tem overhead alto por sessão, e o valor-padrão de 100 não deve subir muito além disso na maioria dos sistemas. Para separar o tráfego analítico do operacional, a recomendação é usar um pooler como pgbouncer, criando um pool menor e dedicado só para as conexões de analytics, limitando o raio de impacto delas.

O segundo ajuste central é work_mem, o limite de memória que uma operação de consulta pode usar antes de gravar em disco (o padrão é 4MB, exatamente o cenário que gerou o Sort Method: external merge visto no plano acima). Jex recomenda elevar esse valor apenas para sessões analíticas específicas, não para a instância inteira, já que uma consulta complexa pode rodar vários sort e hash em paralelo e multiplicar o consumo de memória. Um dos slides mostra como fazer isso dentro de uma transação específica, sem alterar a configuração global do servidor:

BEGIN;
SET LOCAL work_mem = '<valor acima do padrão de 4MB>';
-- consulta analítica aqui
COMMIT;

Para monitorar se o work_mem está baixo demais, ela sugere ativar log_temp_files = 0, que registra no log toda vez que uma operação precisa criar arquivo temporário em disco. Os slides seguem com statement_timeout, usado para cortar consultas analíticas que fogem do padrão esperado e ameaçam rodar indefinidamente, mas o material publicado até o momento é cortado antes de detalhar os valores recomendados para esse parâmetro.

O que muda para quem escreve a consulta, não só para quem administra o servidor

Para quem desenvolve sobre um Postgres compartilhado entre aplicação e relatórios, o recado prático é duplo. Primeiro, antes de pedir mais hardware ou migrar para um data warehouse, vale confirmar se o work_mem da sessão analítica está adequado e se a consulta está rodando num pool de conexões isolado via pgbouncer. Segundo, o plano de execução sempre conta a história real: um Seq Scan junto com um Sort Method: external merge Disk é sinal de que a query (ou a configuração da sessão) tem folga para melhorar antes de sequer cogitar reescrever o esquema.

Vale a ressalva: essas técnicas aliviam sintomas, não resolvem o problema de fundo se o volume de dados analíticos crescer continuamente. Jex vem de um contexto em que isso é central ao trabalho dela: a Snowflake, onde atua no time de extensões como o pg_lake, nasceu justamente para separar workloads analíticos de bancos operacionais. Para cargas analíticas que crescem além do que uma sessão isolada e um work_mem maior aguentam, a conversa muda para réplicas de leitura dedicadas ou sistemas analíticos especializados, algo que a própria apresentação reconhece como alternativa válida quando ela existe, só que nem toda equipe tem esse orçamento ou essa complexidade operacional disponível agora.

Fonte: Planet PostgreSQL