Guia de sobrevivência do Postgres para startups

• Com base nos problemas que a Hatchet enfrentou em produção ao longo de 2 anos, o guia organiza princípios operacionais por etapas, desde o design inicial de esquema e consultas até escritas em grande volume e migrações de tabelas
• Para leituras rápidas, alinhe índices e ORDER BY, mas como o query planner pode escolher uma varredura sequencial com base em estatísticas e custos, é preciso comparar estimativas e execução real com EXPLAIN ANALYZE
• O desempenho e a estabilidade de escrita dependem de transações curtas, bloqueio apenas das linhas necessárias, CREATE INDEX CONCURRENTLY e pooling de conexões; em medições da Hatchet, o processamento em lote aumentou a vazão em cerca de 10x
• Em ambientes com escritas de alta frequência, as configurações padrão do autovacuum podem não recuperar dead tuples e transaction IDs a tempo; ao chegar ao transaction ID wraparound, ocorre um downtime significativo
• À medida que a escala cresce, use filas de trabalho baseadas em FOR UPDATE SKIP LOCKED, particionamento, triggers e backfill em lotes, mas é preciso conseguir controlar SQL diretamente fora da abstração do ORM

---

Público-alvo e limites do ORM

• Guia estruturado para que desenvolvedores que conhecem os conceitos básicos de SQL, linhas, tabelas e índices consigam lidar com problemas de Postgres em produção
• O manual do Postgres é abrangente, mas difícil de consultar rapidamente em situações de incidente; por isso, o conteúdo foi condensado em torno da experiência operacional da Hatchet ao longo de 2 anos
• Mesmo usando um ORM, os princípios se aplicam, mas, conforme a escala aumenta, muitas otimizações só são possíveis ao sair da camada de abstração e escrever SQL diretamente
  • Recursos como Prisma TypedSQL permitem usar ORM e SQL direto em conjunto
  • A Hatchet, baseada em Go, usa sqlc, que oferece comportamento semelhante
  • Para ambientes em que o Claude escreve consultas, recomenda-se supabase/agent-skills

Design de esquema difícil de mudar

• Depois do deploy, mudanças de esquema são as mais difíceis; por isso, crie um rascunho das tabelas e chaves primárias e itere o design escrevendo as consultas necessárias para a aplicação
• Durante o design, verifique como as tabelas serão usadas com as seguintes perguntas
  • O que é mais frequente: leituras ou escritas?
  • Quais são os filtros mais usados nas leituras?
  • Quais colunas são atualizadas com mais frequência?
• É possível aplicar 1NF, 2NF e 3NF de normalização de banco de dados, mas formas normais às vezes entram em conflito com a conveniência necessária para eficiência de consultas ou desenvolvimento rápido
  • Em algumas situações, colocar os dados em uma coluna jsonb é mais simples
• As regras práticas aplicadas ao design de esquema foram as seguintes
  • Para chaves primárias, use inteiros auto-incrementais como colunas identity ou UUIDs integrados do Postgres
  • Colunas identity são um pouco mais rápidas que bigserial
  • Para tempo, use sempre timestamptz
  • Toda tabela deve ter uma chave primária
  • Use chaves estrangeiras com cascade delete em tabelas de baixo volume em que consistência e precisão são importantes, mas tenha cuidado em ambientes de alto volume

Consultas de leitura e índices

• Um modelo simples para entender SELECT rápido é que o Postgres encontra rapidamente uma linha usando um índice ou lê todas as linhas da tabela por meio de uma varredura sequencial (seq scan)
• Para busca rápida de uma única linha, use as seguintes estruturas
  • Índice explícito
  • Unique constraint, que é uma forma especial de índice
  • Chave primária, que o Postgres indexa automaticamente
• O índice padrão usa btree e pode ser entendido como uma tabela separada que armazena os dados em uma forma otimizada para busca
  • O tempo de busca de uma linha é aproximadamente log(n), em que n é o número de linhas da tabela
• Se um índice não puder ser usado, uma varredura sequencial será executada, mas bancos de dados modernos carregam linhas na memória rapidamente, então tabelas com menos de 20 mil linhas podem terminar quase instantaneamente

Joins e índices compostos

• Em geral, o alvo de joins internos deve usar a chave primária; caso contrário, pode haver um problema no design do esquema ou na normalização
• Trate a cláusula ON como a cláusula WHERE e use índices adequados nas condições de join
• Consultas de listagem em tabelas grandes tendem a ser as primeiras consultas que ficam lentas na aplicação
  • Se você filtra e ordena por organização e horário de criação em conjunto, pode usar um índice composto

CREATE INDEX CONCURRENTLY idx_documents_org_created
    ON documents (organization_id, created_at DESC);

• Em consultas complexas, uma regra prática é colocar a coluna do ORDER BY no fim do índice e alinhar também a direção da ordenação
  • O Postgres escaneia btree nos dois sentidos, então DESC pode não ter significado em uma única coluna, mas em índices compostos é melhor deixar alinhado
  • O comportamento detalhado de índices descendentes pode ser conferido em material relacionado

Escritas, bloqueios e migrações

• A primeira condição para escritas bem-sucedidas é manter transações curtas
  • A menos que haja um motivo especial, não consulte serviços externos durante uma transação
• A segunda condição é bloquear apenas as linhas necessárias
  • Ao atualizar uma linha, ela fica bloqueada até o commit da transação
  • Quanto maior a carga do sistema, mais perceptível fica o impacto dos bloqueios
• Executar um CREATE INDEX comum em uma tabela grande existente bloqueia a tabela e impede inserts e updates; portanto, use sempre CREATE INDEX CONCURRENTLY
• Uma boa capacidade de migração de esquema aumenta a velocidade de iteração no desenvolvimento e melhora o uptime
  • Sempre que possível, evite apagar ou remover colunas e faça mudanças adicionando coisas
  • Se possível, execute dentro de uma transação para lidar com rollback e aplicação parcial
  • Como abordagem mais avançada, é possível usar migrações do tipo expand and contract
• Em migrações, primeiro é preciso decidir se elas bloqueiam todas as escritas
  • Criar índice sem CONCURRENTLY pode bloquear todas as escritas e causar downtime
  • Operações ALTER TABLE devem ser revisadas novamente, e adicionar check constraint em tabelas grandes também pode bloquear escritas
  • Adicionar uma check constraint como NOT VALID evita esse bloqueio

Gerenciamento de conexões

• Toda consulta e transação usa uma conexão com o banco de dados, e conexões têm alto custo de CPU e memória, portanto devem ser mantidas por bastante tempo
• Criar e remover conexões com frequência desperdiça recursos
  • Uma connection storm, com muitas novas conexões ocorrendo ao mesmo tempo, pode causar problemas difíceis de depurar relacionados a bloqueios internos do Postgres
• Considere primeiro um pooler externo de conexões como pgbouncer; se não puder usar, adote um pool de conexões em memória como alternativa
  • A Hatchet usa pgxpool para Go porque não pode presumir que o banco de dados do usuário use um pooler externo

Query planner e estatísticas

• Consultas complexas com muitos joins ou uma mistura de vários métodos de join não são resolvidas simplesmente adicionando índices
  • Índices também têm overhead próprio, então não devem ser adicionados sem limite
• O query planner transforma SQL em operações internas do banco de dados e decide, entre outras coisas, se deve usar índices, mas pode não escolher o plano ideal por ter informações limitadas
• As informações usadas pelo planner são estatísticas de tabelas, que podem ser consultadas em pg_stats

SELECT *
FROM pg_stats
WHERE tablename = 'mytable';

• Estatísticas são coletadas durante ANALYZE e também atualizadas quando o autovacuum é executado
  • Aumentar a frequência do autovacuum também mantém as estatísticas de consultas atualizadas
  • Uma causa comum de consultas se comportarem mal é a frequência insuficiente de análise
• Avaliar consultas de forma simples, olhando se há varredura sequencial, reduz a chance de micro-otimizações aumentarem a imprevisibilidade do planner
  • Consultar principalmente por chaves primárias e índices facilita a escolha de plano pelo planner

Análise de planos de execução e varredura sequencial

• Alguns provedores, como o Google CloudSQL, fazem amostragem de consultas e armazenam consultas lentas, mas nem todos os serviços dão esse suporte
• EXPLAIN ANALYZE executa a consulta de fato e compara o número estimado de linhas, baseado nas estatísticas da tabela, com o número real de linhas escaneadas
  • Em produção, é preciso cuidado porque a consulta real será executada
  • Para verificar apenas o plano sem executar, use EXPLAIN sem ANALYZE
• É possível salvar o plano detalhado em JSON e visualizá-lo em explain.dalibo.com

psql -XqAt -f explain.sql -d $DATABASE_URL > analyze.json

• Se houver varredura sequencial mesmo com estatísticas e índices normais, o planner pode ter calculado que o custo da varredura sequencial é menor
  • Índices são armazenados separadamente do heap, onde ficam os dados reais da tabela, então há o custo de reler no heap várias linhas encontradas pelo índice
  • Se não for possível reestruturar muito a consulta, aceite a varredura sequencial ou considere particionamento

Escritas em grande volume e processamento em lote

• Cada consulta tem overhead de ida e volta ao banco de dados, tempo para obter uma conexão no pool de conexões da aplicação e tempo de processamento no Postgres
  • Bloqueios internos do Postgres também podem virar gargalo em ambientes de alta vazão
• Agrupar várias linhas em uma consulta reduz esses custos
  • O método mais simples é enviar várias consultas de uma vez ao servidor como uma transação implícita
  • Em Go, é possível usar SendBatch do pgx
• Na Hatchet, o processamento em lote aumentou a vazão em cerca de 10x, e otimizações adicionais de inserção estão organizadas no guia de inserts rápidos no Postgres

autovacuum e transaction ID wraparound

• O autovacuum é responsável pela limpeza de dead tuples e pelo gerenciamento de transaction IDs; em ambientes com escritas de alta frequência, pode ser necessário ajustar configurações
• Uma tuple é uma versão de uma linha armazenada no sistema de arquivos
  • Mesmo que uma linha seja atualizada ou excluída, a versão anterior permanece até que todas as transações iniciadas antes disso façam commit ou rollback
  • Uma versão que não pode mais ser lida por nenhuma transação é uma dead tuple
• Se a velocidade de escrita for alta demais, o autovacuum pode não acompanhar a taxa de criação de dead tuples, deteriorando rapidamente o estado do banco de dados
• Se, ao verificar processos ativos em pg_stat_activity, uma consulta de autovacuum estiver em execução por cerca de 1 hora ou mais, considere mudar a configuração
  • Métodos concretos de configuração podem ser vistos no material sobre tuning de autovacuum
• Se todos os transaction IDs se esgotarem antes que o autovacuum consiga recuperá-los, ocorre transaction ID wraparound, levando a um downtime significativo

Bloat de tabelas e índices

• O Postgres armazena linhas em páginas de 8 KB no disco e cria uma nova página quando não consegue inserir uma nova linha em uma página existente
• Se, depois que dead tuples são recuperadas, as páginas ficam parcialmente vazias, ocorre bloat de tabela, o que pode aumentar bastante o uso de disco
  • A melhor prevenção é ajustar o autovacuum antes que o bloat aconteça
  • Para tabelas que já estão com bloat, é possível usar extensões como pg_repack
  • O VACUUM FULL embutido quase nunca é uma boa escolha
  • O Postgres 19 deve adicionar REPACK...CONCURRENTLY para repacking concorrente de tabelas, mas a Hatchet ainda não testou
• Bloat de índice também é uma forma especial de bloat de tabela e pode ser reduzido com configurações adequadas de autovacuum
  • Para índices que já estão com bloat, é possível usar o comando embutido REINDEX INDEX CONCURRENTLY

Processamento concorrente baseado em FOR UPDATE SKIP LOCKED

• FOR UPDATE SKIP LOCKED reserva as linhas selecionadas para a transação atual sem atrapalhar outras consultas
• A Hatchet usa isso em filas de trabalho, permitindo bloquear tarefas pendentes e mudar seu status para RUNNING em uma única consulta

WITH eligible_tasks AS (
    SELECT *
    FROM tasks
    WHERE status = 'QUEUED'
    ORDER BY id ASC
    FOR UPDATE SKIP LOCKED
    LIMIT 100
)
UPDATE tasks
SET status = 'RUNNING'
FROM eligible_tasks
WHERE tasks.id = eligible_tasks.id
RETURNING tasks.*;

• Também é útil ao atualizar linhas independentes simultaneamente ou quando várias instâncias da aplicação gerenciam o lease de um objeto
  • A Hatchet usa isso para distribuir tenant leases entre vários engines

Particionamento

• O particionamento integrado do Postgres divide tabelas com base em valores de linha, como timestamp ou hash
• Em dados de séries temporais e nos dados históricos de trabalho da Hatchet, ele oferece os seguintes benefícios
  • Executar autovacuum de forma independente por partição, aumentando a escala do processamento de autovacuum da tabela
  • Remover dados antigos quase instantaneamente ao derrubar a tabela de partição, em vez de excluir linha por linha
• Se, na etapa de planejamento, o Postgres não conseguir remover partições desnecessárias, consultas de leitura podem sofrer overhead
  • Versões recentes do Postgres melhoraram a poda de partições
  • A experiência operacional da Hatchet está organizada no post sobre particionamento no Postgres

Movimentação de dados entre tabelas grandes

• A migração de tabelas grandes mencionada aqui não é mudança de esquema, mas a tarefa de mover grandes volumes de dados de uma tabela para outra
• Copiar uma tabela muito grande em uma única transação pode levar horas
  • Transações longas impedem o funcionamento normal do autovacuum e causam bloat de dead tuples
  • Se escritas continuarem ocorrendo na tabela antiga, esses dados não serão refletidos na nova tabela
• A Hatchet executa um grande backfill em lotes fora de uma transação, e novas escritas após o início da migração são copiadas para a nova tabela por triggers do Postgres
  • Usa a unique constraint da chave primária para evitar escritas duplicadas

[GN⁺]

Opiniões do Hacker News

• Se for um banco de dados de produção, acho que a primeira coisa deveria ser montar um plano de backup e recuperação. Alta disponibilidade pode até ser opcional no começo, mas é estranho que um guia de sobrevivência não inclua backup e recuperação
  Fico curioso se hoje em dia ainda se usa bastante o Barman (https://pgbarman.org/) para backups do PostgreSQL

  • Se você não é especialista em PostgreSQL, é melhor não operar por conta própria e usar um banco de dados gerenciado, como o RDS. A economia de auto-hospedar é mínima comparada ao custo de obter alta disponibilidade comprovada, backup e recuperação, recuperação point-in-time e réplicas de leitura
  • Estou usando pgBackRest. Ele oferece uma recuperação point-in-time melhor do que a solução própria de backups noturnos que eu usava antes, foi relativamente fácil configurá-lo para fazer backup no Backblaze B2 e não tive grandes problemas
  • Para a maioria dos casos, basta rodar pg_dump_all no cron, comprimir com zstd e copiar para S3, FTP etc. Quando os dados crescem, o tempo e o custo de backups completos passam a pesar, mas dá para aguentar bastante tempo com esse método simples
  • Se o banco de dados garante durabilidade mesmo em caso de falha de energia, é possível fazer backup com snapshots atômicos de volume. Para reduzir o tempo de recuperação, crie primeiro um checkpoint; para evitar corrupção de dados, a atomicidade do snapshot precisa ser obrigatoriamente garantida
    Na AWS, fizemos backup de um MongoDB de vários TB com snapshots do EBS, implementando backups incrementais rápidos e recuperação. Não oferece recuperação point-in-time, mas pode ser feito com frequência, em intervalos de horas, então é uma boa estratégia auxiliar para usar em paralelo com ferramentas específicas do PostgreSQL
  • Se você já opera Kubernetes, pode usar CloudNativePG

• Há alguns pontos a complementar. Use UUIDv7 em vez do UUIDv4 comum, e padronize de forma determinística a ordem de bloqueio em todas as consultas, como id ASC, não apenas a quantidade de linhas bloqueadas, para evitar deadlocks
  Com EXPLAIN (GENERIC_PLAN), é possível copiar a consulta mantendo os placeholders de parâmetros e também ver o plano de otimização quando o PostgreSQL não conhece os valores reais. Em tabelas vazias ou pequenas, dá para verificar a possibilidade de uso de índice com SET enable_seqscan = off
  Índices B-tree, que todo mundo usa por padrão, são pesados e propensos a bloat; se for apenas uma busca simples, sem ordenação nem pesquisa por intervalo, vale considerar índices hash. Não é possível criar um índice hash único, mas dá para obter um efeito parecido com uma restrição de exclusão por hash, e índices únicos multicoluna não são suportados
  Também vale aprender sobre índices GIN e GiST. Pode surpreender usuários de MySQL, mas é possível acelerar consultas comuns LIKE '%foo%' sem trocar para busca full-text

  • Deadlocks acontecem não só quando não há um ORDER BY consistente no conjunto de linhas a bloquear, mas também quando a ordem de bloqueio das tabelas é diferente. Se uma transação bloqueia na ordem table_a, table_b e outra transação bloqueia na ordem inversa, ocorre deadlock mesmo usando ORDER BY e FOR UPDATE dentro de cada tabela
    Em teoria isso é óbvio, mas na prática é muito mais difícil de depurar, porque é preciso entender globalmente quais tabelas todas as escritas tocam; já passei por isso em uma extensão específica. Estou testando GIN para consultas de chave-valor em JSONB, e o ganho de desempenho foi muito grande; a diferença de desempenho entre AND e OR também foi considerável
  • Usar qualquer UUID como chave primária gera custo alto, porque joins pela chave primária ficam frequentes, e em geral traz pouco benefício. Por padrão, é mais seguro usar uma chave primária sequencial incremental e, se for necessário expor externamente, adicionar uma coluna UUIDv4 com índice secundário. Fico curioso se o UUIDv7 realmente tem desempenho melhor em B-tree do que o UUIDv4
  • Ao desativar o sequential scan, acho que o PostgreSQL força o uso de algum índice se houver ao menos um. Portanto, isso provavelmente não indica se é o índice correto
  • Ferramentas de conversão entre UUIDv7 e UUIDv4, como https://github.com/ali-master/uuidv47 e https://github.com/stateless-me/uuidv47, já foram apresentadas várias vezes

• Esse conselho também é bom, mas as startups com que trabalhei esbarraram primeiro em problemas organizacionais bem mais básicos do que escalabilidade. É melhor não usar ORM, usar chaves primárias autoincrementais em vez de campos com significado e usar JSONB de forma limitada, apenas quando for realmente necessário.
  Os dados de origem devem ser append-only, permitindo apenas inserções, sem atualizações nem exclusões. Tabelas auxiliares desnormalizadas para desempenho e conveniência podem ser alteradas, mas não devem ser tratadas como a fonte da verdade.
  Use pool de conexões, mas fique atento ao número de conexões; se não houver problema, talvez nem seja necessário chegar ao PgBouncer. Evite transações explícitas sem um motivo claro, não faça operações demoradas como RPC enquanto uma transação estiver aberta, e quase nunca use SERIALIZABLE.
  Se você precisa de bloqueios explícitos como SELECT FOR UPDATE, há uma boa chance de o design estar errado. Não reinvente um sistema de tipos fazendo com que linhas de uma tabela tenham vários significados conforme um valor type int, nem tente imitar um banco de dados de grafos com tabelas node e edge que referenciam a si mesmas. A maioria dos casos pode ser resolvida com tabelas normalizadas comuns.

  • No backend PHP em que estou trabalhando, é preciso instanciar objetos para checagens de permissão etc., então ORM é muito útil. Implementar isso sem ORM parece exigir muito mais trabalho; fico curioso por que seria uma má escolha.
  • Se salários de desenvolvedores são o maior custo, o princípio de não usar ORM é controverso. Dadas as necessidades de negócio das tabelas, a pressão dos clientes e um orçamento apertado, o custo continua correndo enquanto se discute por muito tempo com um DBA sobre o design correto; por isso, princípios como evitar colunas de tipo ou estruturas em forma de grafo não são tão fáceis quanto parecem.
  • Para uma startup que precisa colocar um produto no ar rapidamente, ORM é uma escolha suficientemente boa. Se você entende armadilhas como consultas N+1 e carregamento preguiçoso, é um compromisso melhor do que também ter de criar manualmente o gerenciamento de queries e a parametrização.
    No início de um projeto, eu preferiria gastar tempo desenvolvendo o produto a pensar demais no esquema do banco de dados e otimizar prematuramente.
  • Usei SELECT FOR UPDATE de forma útil em vários lugares e fico curioso sobre qual é o problema. Também gostaria de saber se uma fonte da verdade append-only elimina a necessidade desse tipo de bloqueio.
  • Dados de origem append-only são atraentes, mas em vários sistemas em que trabalhei isso teria aumentado drasticamente o armazenamento de muitas tabelas em troca de benefícios duvidosos. É uma técnica útil, mas questiono se deve ser um princípio imposto em todos os lugares.
    Por outro lado, fico curioso sobre a abordagem de manter tabelas relacionais tradicionais mutáveis como fonte da verdade e registrar logs de alteração com triggers.

• Não gosto de exclusões em cascata. A maioria dos desenvolvedores vive mais na camada de aplicação, em Python, Node ou Go, do que no banco de dados; por isso, exclusões em cascata podem facilmente parecer mágica, quando apagar uma linha da tabela A também faz dados da tabela B desaparecerem. Se forem configuradas errado, são ainda mais perigosas; portanto, para manutenção de longo prazo, instruções de exclusão explícitas são melhores, e usar chaves estrangeiras corretamente já é suficiente para manter a consistência.
  As armadilhas e alternativas para migrações de tabelas grandes são reais, mas ferramentas como pg-osc já existem. O ideal é que seja tão simples quanto executar um comando e observar, tenso, pelas 24 horas em que os dados são copiados.
  Deploys de aplicação e de banco de dados devem ser separados desde cedo. Como não é possível implantar mudanças de esquema e de aplicação de forma totalmente simultânea em uma transação, depois que se entra em produção é preciso criar o hábito de fazer apenas mudanças de esquema compatíveis com versões anteriores, como criar novas colunas como nullable ou com valor padrão e não renomear tabelas ou colunas.
  A estratégia de gerenciamento de esquema também deve ser definida cedo. Deve-se evitar um processo de deploy em que um desenvolvedor sênior executa DDL manualmente no banco de produção a partir da própria máquina; é possível usar ferramentas conhecidas como Liquibase ou Flyway.

  • Criei pgschema, uma ferramenta declarativa de gerenciamento de esquema.

• O planejador de queries otimiza para o caso médio, mas às vezes é mais útil para uma aplicação otimizar para o pior caso. Para o usuário médio, havia poucas linhas e um índice específico retornava resultados em menos de 10 ms, mas para usuários com uso intenso a mesma query levava mais de 1 segundo dependendo dos parâmetros.
  Com uma query mais complexa, forçamos outro caminho de índice; o desempenho médio ficou um pouco mais lento, mas o pior caso também caiu para menos de 100 ms. Para a empresa, evitar timeouts era muito mais importante do que economizar 10 ms em média.

• SKIP LOCKED é útil em filas de trabalho baseadas em transações interativas, em que a aplicação mantém uma transação aberta e bloqueia linhas enquanto trabalha. Em aplicações de alto desempenho, é possível evitar esse tipo de transação e atualizar imediatamente a linha para pending, então SKIP LOCKED não é necessário.
  À medida que a escala aumenta, é preciso reduzir o estado mantido na memória do banco de dados, e transações interativas também contam como esse estado. Em ambientes escaláveis, idempotência é mais vantajosa do que atomicidade.

• Transações longas podem prejudicar o estado do banco de dados, portanto devem ser usadas apenas quando houver uma justificativa forte. Use idle_in_transaction_session_timeout para impedir que transações ociosas mantenham locks ou tuplas por muito tempo, e defina lock_timeout em migrações para que uma única DDL não pare o sistema inteiro.
  Também é preciso configurar statement_timeout para que uma única query cara não paralise o sistema.

• Operando PostgreSQL no início de uma startup, percebi que este texto não enfatiza suficientemente monitoramento e alertas. O PostgreSQL tem alguns tipos críticos de falha que precisam ser evitados a todo custo, e alertas podem detectar o risco cedo.
  Mesmo que a AWS envie um e-mail dizendo que você está se aproximando do wraparound de IDs de transação, em uma startup isso pode passar facilmente despercebido, especialmente em um dia como Boxing Day. Os sinais que a AWS monitora devem estar conectados a um pager, não a e-mail.

• Há diferenças importantes e pouco conhecidas nas implementações de pools de conexão. A maioria dos pools de conexão de aplicações usa FIFO (first in, first out) para otimizar baixa latência e disponibilidade de conexões, mas isso mantém as conexões sempre aquecidas, dificultando reduzir conexões desnecessárias.
  O PgBouncer e alguns poolers externos usam LIFO (last in, first out) para otimizar o número de conexões que chegam ao PostgreSQL e o throughput. Ao reutilizar primeiro a conexão mais recente, as conexões excedentes esfriam naturalmente e são encerradas.
  Para uma aplicação nova, FIFO é suficiente, mas, conforme a escala cresce, é melhor usar ferramentas como PgBouncer para reduzir centenas de conexões em cerca de 90%. A arquitetura do PostgreSQL, que cria um processo por conexão, funciona melhor quanto menor for o número de conexões.

• Em situações muito específicas, fazer joins na memória da aplicação trouxe bons resultados. Às vezes, ao tentar reduzir idas e voltas ao banco de dados, acaba-se criando uma única consulta com JOIN, UNION e CASE complexos todos misturados
  Em vez disso, executar várias consultas simples de forma independente e depois percorrer os resultados, conectando as linhas relacionadas por meio de um mapa, pode tornar o plano de consulta mais previsível e acabar sendo vantajoso, mesmo com o custo adicional de round-trips e iterações. Deve ser usado apenas de forma limitada, e o fato de alguns ORMs funcionarem assim internamente não significa que isso seja recomendado incondicionalmente

  • A eficácia dessa abordagem depende muito da situação. Se um join gerar um produto completo muito maior que os dados originais, buscar apenas os conjuntos originais e combiná-los localmente pode reduzir a carga no DB e o tráfego de rede
    Porém, joins internos seletivos produzem resultados muito menores que os dados originais, então trazer todos os registros e realizar a interseção e a filtragem localmente é muito mais caro. Em joins por índice, o planejador de consultas também pode usar índices para evitar varreduras completas de tabela, ordenações e filtragens indiscriminadas
  • Pelo que sei, também se usa a abordagem de criar duas views e depois fazer o join em vez de uma única consulta complexa