Ajuste Fino de OLTP vs OLAP

OLTP e OLAP descrevem duas formas diferentes de trabalho de banco de dados, e o PostgreSQL foi projetado primeiro e melhor para a forma OLTP. O tráfego OLTP consiste em muitas transações curtas que tocam poucas linhas cada, onde cada milissegundo de latência é sentido diretamente por um usuário.

O tráfego OLAP é o oposto: um punhado de consultas pesadas que escaneiam milhões de linhas para produzir um agregado, onde a taxa de transferência total é mais importante do que o tempo de resposta de qualquer consulta individual.

Executar ambas as formas contra a mesma instância PostgreSQL é comum e muitas vezes necessário, mas não é gratuito, porque as configurações que ajudam uma forma prejudicam ativamente a outra.

Esta página constrói o modelo mental para raciocinar sobre esse conflito antes de recorrer a qualquer botão de ajuste fino.

Resumo

• Cargas de trabalho OLTP e OLAP otimizam para coisas opostas, baixa latência por consulta versus alta taxa de transferência agregada, então elas puxam os mesmos botões de configuração do PostgreSQL em direções opostas.
• Por que Importa: Ajustar um cluster para uma forma de carga de trabalho sem considerar a outra causa picos de latência intermitentes e difíceis de diagnosticar sempre que as duas colidem.
• Conceitos-Chave: OLTP, OLAP, conjunto de trabalho, work_mem, consulta paralela, poluição de cache.
• Quando Usar: Qualquer equipe que execute tráfego de aplicação interativo e relatórios internos ou análises contra o mesmo cluster PostgreSQL.
• Limitações / Compromissos: Reconciliar ambas as cargas de trabalho em uma única instância requer salvaguardas, como configurações baseadas em função ou roteamento de réplicas, em vez de uma única configuração global que satisfaça ambas.
• Tópicos Relacionados: pooling de conexões e isolamento de carga de trabalho, topologia de réplica de leitura, planejamento de consulta e paralelismo.

Fundamentos

Uma consulta OLTP é tipicamente uma busca pontual ou uma varredura de intervalo estreito, o tipo de coisa para a qual um índice foi construído, e ela precisa retornar em milissegundos de um dígito para manter uma aplicação responsiva.

Uma consulta OLAP é tipicamente um agregado amplo sobre uma grande fatia de uma tabela, o tipo de coisa que uma varredura sequencial e um agregado hash tratam bem, e ela pode razoavelmente levar segundos ou minutos.

Esses dois padrões de acesso favorecem estratégias físicas opostas, varreduras de índice para OLTP e varreduras sequenciais com ordenações ou hashes para OLAP, mesmo contra tabelas subjacentes idênticas.

O planejador do PostgreSQL escolhe entre essas estratégias por consulta usando estimativas de custo, mas a configuração do servidor circundante, como limites de memória e limites de paralelismo, é compartilhada por todas as consultas em execução na instância.

Essa configuração compartilhada é a fonte real do conflito, não o SQL em si.

Uma analogia útil é uma única cozinha servindo tanto um balcão de almoço rápido quanto um departamento de catering: o balcão de almoço precisa de panelas pequenas e rotatividade rápida constante, enquanto o catering precisa de panelas enormes reservadas por horas, e dar aos dois departamentos equipamentos idênticos subatende um deles por design.

O PostgreSQL é essa única cozinha, a menos que a equipe dê deliberadamente a cada carga de trabalho seu próprio equipamento através da configuração.

Mecânicas e Interações

Shared buffers, o cache de página em memória do PostgreSQL, é o primeiro lugar onde o conflito se torna concreto. O desempenho OLTP depende de seu conjunto de trabalho, o punhado de tabelas e índices quentes que ele toca constantemente, permanecendo residente nesse cache.

Uma grande varredura sequencial OLAP lê muito mais páginas distintas do que os buffers compartilhados podem conter e, dependendo do padrão de varredura, pode remover as páginas quentes do OLTP para abrir espaço, um modo de falha geralmente chamado de poluição de cache.

work_mem agrava o problema do lado da memória, pois não é um limite global, mas uma permissão por operação, o que significa que uma única consulta com várias ordenações ou junções hash pode multiplicar seu uso de memória pelo número de operadores que executam concorrentemente.

Um work_mem definido generosamente para uma consulta de relatório torna-se perigoso quando cada conexão OLTP herda a mesma configuração e executa suas próprias ordenações sob carga concorrente.

-- Distinguindo a forma da carga de trabalho ao vivo: consultas curtas vs. de longa duração
SELECT
  CASE WHEN now() - query_start < interval '100 ms' THEN 'forma-oltp'
       ELSE 'forma-olap' END AS shape,
  count(*)
FROM pg_stat_activity
WHERE state = 'active'
GROUP BY 1;

Consulta paralela adiciona um terceiro eixo ao mesmo conflito, pois iniciar trabalhadores em segundo plano para dividir uma varredura ajuda um grande agregado OLAP a terminar mais rápido, mas adiciona sobrecarga pura a uma busca pontual OLTP que já era rápida. O paralelismo também compete pelos mesmos núcleos de CPU que as conexões OLTP precisam para agendamento de baixa latência, de modo que uma consulta analítica que inicia vários trabalhadores pode desacelerar mensuravelmente o tráfego interativo não relacionado em execução no mesmo momento.

Nenhum desses efeitos é visível no EXPLAIN de uma consulta individual, pois eles tratam da contenção entre consultas, e não de uma consulta individual ser mal escrita.

É precisamente por isso que este é um problema de configuração e topologia, não um problema de ajuste fino de consulta.

Considerações Avançadas e Aplicações

A alavanca mais direta para reconciliar ambas as cargas de trabalho em um primário é a configuração baseada em função, usando ALTER ROLE ... SET para dar a uma função de aplicação OLTP e a uma função de relatório padrões diferentes para work_mem, statement_timeout e limites de trabalhadores paralelos.

Essa abordagem mantém ambas as cargas de trabalho nos mesmos dados sem latência de replicação, ao custo de ainda compartilhar o mesmo cache de buffers compartilhados e os mesmos núcleos de CPU por baixo. Réplicas de leitura removem a contenção de cache compartilhado e CPU compartilhada completamente, roteando o tráfego OLAP para uma instância PostgreSQL separada, ao custo de introduzir latência de replicação que as consultas de relatório devem tolerar. Além de uma única réplica, algumas equipes roteiam CDC ou replicação lógica para um data warehouse colunar construído para fins específicos, trocando complexidade operacional por desempenho analítico genuinamente especializado que o armazenamento de linha do PostgreSQL não consegue igualar em escala muito grande. Views materializadas oferecem um caminho intermediário para agregados repetidos e previsíveis, pré-calculando a parte cara em uma programação para que a consulta ao vivo se torne uma leitura barata, ao custo de o resultado ser apenas tão atual quanto a última atualização. A infraestrutura de planejador e paralela do PostgreSQL 18.4 continua melhorando, mas nenhuma versão altera a forma fundamental desse trade-off, pois é arquitetural, não uma limitação do planejador esperando para ser corrigida.

Decidir entre essas opções é realmente sobre quanta obsolescência, área de superfície operacional e custo de infraestrutura uma equipe está disposta a aceitar em troca de isolar as duas cargas de trabalho.

Conceitos Erros Comuns

• "PostgreSQL não consegue realmente fazer análises." Ele lida bem com cargas de trabalho analíticas moderadas com os índices corretos, consulta paralela e ajuste de memória, mas não é um data warehouse colunar, e esperar desempenho em escala de data warehouse apenas do armazenamento de linha leva à decepção.
• "HTAP significa que obtenho ambas as cargas de trabalho de graça." O marketing transacional/analítico híbrido ainda enfrenta a mesma contenção de cache compartilhado e CPU compartilhada descrita aqui, e reduz a dor em vez de eliminar o trade-off subjacente.
• "Uma réplica de leitura não tem desvantagens." A latência de replicação é um custo real, às vezes variável, e as consultas de relatório construídas em uma réplica precisam tolerar resultados que estão segundos ou minutos atrás do primário.
• "A consulta paralela é desempenho gratuito, então devo aumentar os limites de trabalhadores em todos os lugares." Mais trabalhadores paralelos no primário competem diretamente com conexões OLTP por CPU, então configurações de paralelismo uniformemente altas podem desacelerar o tráfego que mais importa.
• "Um work_mem maior é sempre mais seguro." work_mem é concedido por operação, não por conexão, então uma configuração global generosa sob carga OLTP concorrente pode se multiplicar em pressão de memória que o benefício de uma única consulta de relatório nunca justificou.

FAQs

Relacionados

• Fundamentos de Carga de Trabalho - padrões de função práticos e comparações de forma de plano
• Réplicas de Leitura para Relatórios - roteando tráfego analítico para fora do primário
• Consulta Paralela - quando o paralelismo ajuda e quando custa mais do que economiza
• work_mem & ajuste fino de ordenação/hash - limites de memória por operação em profundidade
• Função e Tempo Limite por Pool - aplicando separação de carga de trabalho no pooler

Versões da Stack: Esta página foi escrita para PostgreSQL 18.4 (estável 18, manutenção 17).