Otimizar PostgreSQL e pgvector para cargas de IA em produção

radio_button_uncheckedshort_text1. Diagnosticar a carga vetorial antes de ajustar

Considere um recomendador que passou de 50 mil para dois milhões de produtos. A consulta de cerca de 30 ms supera um segundo e campanhas atraem dezenas de milhares de usuários simultâneos. O objetivo é manter pesquisas representativas abaixo de 100 ms sem perder recall útil nem estabilidade de vazão.

• Meça latência, recall, QPS, taxa de acertos do cache, CPU, memória, E/S, conexões e atraso de réplica.
• Ajuste memória e planejador do PostgreSQL.
• Escolha ANN por volume, atualizações, precisão e janela de compilação.
• Mude layout, computação, leituras, cache e conexões somente conforme a evidência.

Resumo do tópico

Comece com metas explícitas de latência e recall e uma linha de base semelhante à produção em todo o caminho da solicitação.

radio_button_uncheckedshort_text2. Compreender o custo da distância vetorial

Uma comparação de 1.536 dimensões percorre todos os elementos; um exame exato de um milhão de linhas ultrapassa 1,5 bilhão de operações elementares. L2 (<->) ainda calcula raiz quadrada. Cosseno (<=>) ignora magnitude e é comum em semântica. Produto interno negativo (<#>) tende a ser mais leve, porém só representa corretamente a similaridade quando os vetores estão normalizados.

Duplicar dimensões duplica o armazenamento e aumenta muito o cálculo. Teste 768 ou 1.024 dimensões antes de pagar por 1.536 ou 3.072. Dois milhões de vetores com 1.536 dimensões ocupam perto de 12 GB sem índices; um grafo HNSW pode acrescentar aproximadamente metade disso ou mais.

Resumo do tópico

Métrica e dimensão influenciam relevância e também CPU, memória e armazenamento de cada busca.

radio_button_uncheckedshort_text3. Ajustar memória sem multiplicar o risco

shared_buffers mantém páginas no cache do PostgreSQL. Cerca de 25% da memória é apenas um ponto inicial; predefinições do Azure e medições prevalecem. Em carga vetorial, investigue taxa de acerto abaixo de aproximadamente 99%. work_mem vale para cada ordenação ou hash e pode ser usado várias vezes por conexão: 256 MB globalmente em centenas de sessões pode esgotar memória, portanto use SET LOCAL para consultas excepcionais.

effective_cache_size não aloca RAM: informa ao planejador quanto cache do PostgreSQL e do sistema operacional provavelmente existe. Aproximadamente 75% pode iniciar testes em servidor dedicado, sempre ajustado à camada real.

-- Inspect memory and planner settings
SHOW shared_buffers;
SHOW work_mem;
SHOW effective_cache_size;
SHOW random_page_cost;
SHOW effective_io_concurrency;

-- Change expensive settings only for the current transaction
BEGIN;
SET LOCAL work_mem = '256MB';
SET LOCAL hnsw.ef_search = 100;
-- run the vector query here
COMMIT;

SELECT
  sum(heap_blks_hit)::numeric /
  nullif(sum(heap_blks_hit + heap_blks_read), 0) AS cache_hit_ratio
FROM pg_statio_user_tables;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title
FROM products
WHERE category_id = $2
ORDER BY embedding <=> $1::vector
LIMIT 10;

Resumo do tópico

Mantenha o conjunto ativo em cache, descreva a memória disponível com realismo e aplique work_mem alto somente à transação que precisa.

radio_button_uncheckedshort_text4. Alinhar o planejador e a E/S SSD

O random_page_cost padrão de 4,0 representa discos giratórios. Em SSD gerenciado, teste algo em torno de 1,1–1,5 para não penalizar acesso aleatório por índice. effective_io_concurrency perto de 200 pode favorecer pré-busca e Bitmap Heap Scan com filtros de metadados.

Sem ANN, mais max_parallel_workers_per_gather e custos paralelos menores podem favorecer exame exato paralelo. Confirme com EXPLAIN (ANALYZE, BUFFERS), linhas estimadas versus reais, Index Scan ou Seq Scan, ANALYZE depois de grandes cargas e métricas do Azure Monitor.

Resumo do tópico

Os custos do planejador devem refletir SSD; planos reais e métricas de plataforma mostram se a alteração ajudou.

radio_button_uncheckedshort_text5. Saber quando o índice aproximado compensa

ANN pode acelerar por ordens de grandeza, mas troca parte do recall. Compare sempre com vizinhos de uma busca exata. O exame exato ainda serve para tabela pequena, pré-filtro muito seletivo, obrigação de precisão perfeita ou dados que mudam rápido demais.

Resumo do tópico

Adote ANN quando o exame domina o custo e preserve uma linha exata para medir a troca entre recall e latência.

radio_button_uncheckedshort_text6. Configurar IVFFlat para compilação rápida

IVFFlat agrupa vetores representativos em listas e consulta apenas parte delas. Compila mais rápido e usa menos memória que HNSW, adequado a recursos limitados, meta de recall de 90–95%, desenvolvimento e atualização completa em lotes. Precisa de dados antes do treinamento e pode exigir reconstrução após mudança semântica relevante.

Contagens de listas são heurísticas: perto de 100 até 100 mil linhas, 1.000 em torno de um milhão e milhares para vários milhões. Em dois milhões, teste 1.500–2.000 junto com alternativas maiores. Inicie probes perto de sqrt(lists), ou 5–10% das listas em um teste orientado a recall, e escolha pela curva medida.

-- IVFFlat: build after representative rows are loaded
CREATE INDEX CONCURRENTLY products_embedding_ivf_idx
ON products USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 2000);

SET LOCAL ivfflat.probes = 45;

-- HNSW: higher memory and build cost, usually stronger recall/latency
CREATE INDEX CONCURRENTLY products_embedding_hnsw_idx
ON products USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

SET LOCAL hnsw.ef_search = 100;

Resumo do tópico

IVFFlat privilegia construção e memória; lists forma os grupos e probes governa o trabalho e o recall da consulta.

radio_button_uncheckedshort_text7. Configurar HNSW e avaliar DiskANN

HNSW percorre um grafo multicamada. Comece m perto de 16; 32 pode melhorar conectividade com mais memória. ef_construction deve ser ao menos 2 × m e frequentemente parte de 4 × m. ef_search costuma iniciar em 40: teste 20 para menor latência e 100–200 para recall alto, nunca abaixo do LIMIT.

HNSW combina bem com leitura intensa, RAM suficiente, inserções contínuas pequenas e recall próximo de 99%. DiskANN é uma opção específica do Azure para grande escala, alto recall, quantização de produto e, em versões recentes, dimensões bem superiores às aceitas pelos índices HNSW/IVFFlat. Confira versão e disponibilidade.

Resumo do tópico

HNSW investe memória e compilação em recall/latência; DiskANN amplia a escolha para escala e dimensões elevadas no Azure.

radio_button_uncheckedshort_text8. Compilar, verificar e manter índices vetoriais

A classe deve corresponder ao operador: vector_cosine_ops com <=>, vector_l2_ops com <-> e vector_ip_ops com <#>. Incompatibilidade costuma causar Seq Scan. Carregue dados antes do IVFFlat, crie índices de produção com CONCURRENTLY, atualize estatísticas e confirme com EXPLAIN ANALYZE.

Tempos de minutos para IVFFlat em um milhão e horas para HNSW em dez milhões são meramente ilustrativos: hardware, armazenamento, paralelismo e parâmetros dominam. Observe progresso, tamanho e idx_scan e reconstrua concorrentemente quando houver desvio de distribuição ou bloat.

SELECT phase,
       round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS percent
FROM pg_stat_progress_create_index;

SELECT indexrelname, idx_scan, idx_tup_read,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'products';

REINDEX INDEX CONCURRENTLY products_embedding_hnsw_idx;

Resumo do tópico

Criação de índice é operação observável: operador, estatísticas, progresso, uso e política de reconstrução precisam estar corretos.

radio_button_uncheckedshort_text9. Projetar vetores e metadados para as consultas

Declare dimensões para rejeitar embeddings incompatíveis. Separe vetores de título, imagem e comportamento e indexe cada espaço consultado. Use colunas tipadas para filtros estáveis e frequentes; deixe JSONB para atributos dinâmicos, aninhados ou raros.

B-tree atende igualdade e intervalo; índices compostos seguem o prefixo à esquerda e parciais são úteis para in_stock. GIN acelera contenção e chaves JSONB; intervalo numérico extraído do JSONB pede índice de expressão com conversão numérica.

CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  category_id BIGINT NOT NULL,
  price NUMERIC(12,2) NOT NULL,
  in_stock BOOLEAN NOT NULL,
  attributes JSONB NOT NULL DEFAULT '{}',
  title_embedding vector(768),
  image_embedding vector(512)
);

CREATE INDEX products_category_price_idx
  ON products(category_id, price);
CREATE INDEX products_available_category_idx
  ON products(category_id) WHERE in_stock;
CREATE INDEX products_attributes_gin_idx
  ON products USING gin(attributes);
CREATE INDEX products_attribute_price_idx
  ON products (((attributes->>'price')::numeric));

Resumo do tópico

Use um espaço vetorial por finalidade, colunas tipadas para predicados comuns e JSONB apenas onde sua flexibilidade compensa.

radio_button_uncheckedshort_text10. Combinar filtros de metadados e pesquisa vetorial

Uma categoria que seleciona 5% de dois milhões reduz o conjunto potencial a 100 mil. O PostgreSQL pode começar pelo B-tree quando o filtro é seletivo ou pelo índice vetorial quando é amplo; EXPLAIN ANALYZE revela a escolha.

Quando o predicado é aplicado depois da ANN, recupere mais candidatos do que o resultado final. Dimensione essa sobrebusca pela seletividade: pouca quantidade omite resultados; excesso desperdiça o ganho do índice.

-- Let PostgreSQL combine a selective metadata filter with vector ordering
SELECT id, title
FROM products
WHERE category_id = $2 AND in_stock
ORDER BY embedding <=> $1::vector
LIMIT 10;

-- If filtering occurs after ANN retrieval, deliberately overfetch
WITH candidates AS (
  SELECT id, title, category_id, in_stock,
         embedding <=> $1::vector AS distance
  FROM products
  ORDER BY embedding <=> $1::vector
  LIMIT 100
)
SELECT * FROM candidates
WHERE category_id = $2 AND in_stock
ORDER BY distance LIMIT 10;

Resumo do tópico

Filtros seletivos reduzem o cálculo vetorial; pós-filtro requer sobrebusca deliberada e plano verificado.

radio_button_uncheckedshort_text11. Particionar somente quando o acesso combinar

Com dezenas de milhões de linhas, particionar por data, locatário ou categoria permite poda, descarte barato de dados antigos e manutenção de índices menores. RANGE serve ao tempo, LIST a poucas categorias e HASH à distribuição de chaves. Índice criado no pai gera correspondentes nas partições.

CREATE TABLE product_vectors (
  id BIGINT, created_at TIMESTAMPTZ NOT NULL, embedding vector(1536)
) PARTITION BY RANGE (created_at);

CREATE TABLE product_vectors_2026_08
PARTITION OF product_vectors
FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

CREATE INDEX ON product_vectors
USING hnsw (embedding vector_cosine_ops);

O custo inclui consultas entre partições, restrições únicas com a chave de particionamento e maior complexidade. Só adote quando consultas importantes carregarem a chave e o plano comprovar a poda.

Resumo do tópico

Particione por uma fronteira natural de acesso em grande escala, não para compensar esquema ou índices inadequados.

radio_button_uncheckedshort_text12. Escalar computação pelo conjunto ativo e concorrência

Como hipótese inicial, teste Uso Geral com 4–8 vCores abaixo de um milhão e Otimizado para Memória com 8–16 entre um e dez milhões. Centenas de consultas concorrentes podem pedir 32 ou mais. Escale verticalmente quando uma consulta precisa de CPU, memória ou E/S; distribua leituras quando o problema for QPS agregado. CPU sustentada acima de 70%, cache insuficiente e E/S saturada valem mais que uma fórmula por linhas.

Resumo do tópico

Escolha camada pelo conjunto ativo, custo de uma consulta, concorrência e saturação medida.

radio_button_uncheckedshort_text13. Distribuir leituras e armazenar resultados em cache

Réplicas de leitura usam streaming físico assíncrono e possuem endpoint, região e tamanho próprios. Servem a recomendações e pesquisas que toleram breve defasagem; leitura após gravação deve ir ao primário. Monitore atraso, agravado por lotes, rede ou réplica subdimensionada. A documentação atual admite réplicas em Uso Geral e Otimizado para Memória, não na camada intermitente.

Coloque em cache embeddings populares, listas pré-calculadas, vetores estáveis de usuário e agregados. Vetores arbitrários, dados voláteis e combinações infinitas de filtros são maus candidatos. Comece TTL de recomendações em 15–60 minutos e use invalidação por evento ou atualização em segundo plano quando necessário.

SELECT now() - pg_last_xact_replay_timestamp() AS replica_lag;

# Cache a bounded, repeatable recommendation result
SETEX recommendations:product:4281 1800 '{"ids":[18,77,304]}'

O módulo-fonte cita Cache do Azure para Redis. A Microsoft anunciou sua desativação e recomenda o Redis Gerenciado pelo Azure para novos projetos e migrações; o padrão de cache permanece, mas o serviço deve seguir a orientação atual.

Resumo do tópico

Réplicas ampliam leitura com possível atraso; Redis elimina trabalho repetido quando chaves e frescor são controláveis.

radio_button_uncheckedshort_text14. Monitorar capacidade, crescimento e custo

Acompanhe CPU, memória, E/S, conexões, P95/P99, QPS, recall, acerto do cache e atraso de réplica. Alertas ilustrativos podem avisar CPU acima de 80% por cinco minutos e criticar memória acima de 90%, mas a linha de base e o SLO definem valores reais.

1. Registre situação atual e pico.
2. Projete catálogo, dimensões, atualização, QPS e concorrência.
3. Teste com vetores e filtros representativos.
4. Documente gatilhos para camada, réplica, cache, reconstrução ou partição.
5. Reavalie após mudanças de modelo e tráfego.

Reduza custo quando CPU fica abaixo de cerca de 30% com sobra excessiva. Avalie reserva para a base estável, preserve elasticidade no pico, remova índices sem uso, arquive vetores antigos, use a menor precisão aprovada e mantenha camada intermitente em desenvolvimento.

Resumo do tópico

Planejamento une telemetria, previsão, gatilhos e custo em um ciclo repetível.

radio_button_uncheckedshort_text15. Agrupar conexões com PgBouncer

Abrir conexão pode gastar 50–200 ms em TCP, TLS, autenticação, processo de backend e inicialização. Limites mudam por camada e tamanho; valores como 859 em Uso Geral pequeno ou perto de 5 mil em tamanhos maiores são exemplos, não garantias. Consulte o servidor implantado.

PgBouncer interno atende Uso Geral e Otimizado para Memória na porta 6432. O modo transaction devolve a conexão no commit/rollback e costuma ser ideal para API vetorial. Session preserva recursos de sessão, mas reutiliza menos; statement maximiza reuso, porém não permite transação com várias instruções.

# Built-in PgBouncer endpoint uses port 6432
postgresql://app@server:password@server.postgres.database.azure.com:6432/appdb

# Suggested starting posture; benchmark for the real workload
pool_mode = transaction
default_pool_size = 40
max_client_conn = 5000
query_wait_timeout = 60

No modo transaction, SET comum não persiste: use SET LOCAL ou padrão do servidor. Prepared statements nomeados podem prender-se ao backend e LISTEN/NOTIFY não é compatível. Comece perto de 20–50 conexões por pool, deixe margem e teste.

Resumo do tópico

PgBouncer amortiza conexão e protege limites; transaction funciona bem quando o código não depende do estado de sessão.

radio_button_uncheckedshort_text16. Combinar pools de SDK, lotes, assíncrono e resiliência

O pool da aplicação reduz churn antes do PgBouncer. Se 1.000 backends seguros forem divididos por dez instâncias, 100 por instância ainda deve reservar margem operacional. Pool pequeno cria fila; grande transforma concorrência em sobrecarga. Recicle após 30–60 minutos e mantenha connection strings idênticas para não fragmentar pools Npgsql.

from psycopg_pool import AsyncConnectionPool

pool = AsyncConnectionPool(
    conninfo=DATABASE_URL,
    min_size=5,
    max_size=20,
    max_idle=300,
    max_lifetime=3600,
)

async with pool.connection() as conn:
    async with conn.transaction():
        await conn.execute("SET LOCAL hnsw.ef_search = 100")
        rows = await conn.execute(
            "SELECT id FROM products ORDER BY embedding <=> %s LIMIT 10",
            (query_vector,),
        )

Agrupe IDs com ANY e use COPY para ingestão grande. Assíncrono melhora vazão enquanto espera E/S, sem ultrapassar o pool. Defina connect_timeout e statement_timeout, trate PoolTimeout como sobrecarga controlada e repita OperationalError transitório poucas vezes com backoff exponencial e jitter.

Resumo do tópico

Pool limitado, PgBouncer, SET LOCAL, operações em lote, assíncrono, timeouts e repetição finita formam uma única estratégia.

radio_button_uncheckedshort_text17. Laboratório guiado: otimizar pesquisa vetorial

O exercício-fonte reserva cerca de 30 minutos para uma comparação controlada.

1. Prepare assinatura do Azure, Visual Studio Code, Azure CLI atual e psql.
2. Baixe os arquivos iniciais e revise a implantação.
3. Implante Banco de Dados do Azure para PostgreSQL com autenticação Microsoft Entra.
4. Gere dados de teste com embeddings.
5. Registre linha de base exata e plano.
6. Crie IVFFlat e HNSW e compare latência e recall com o resultado exato.
7. Mude lists/probes ou m/ef uma variável por vez, registre a decisão e exclua recursos descartáveis.

Resumo do tópico

O laboratório gera uma linha exata, duas experiências ANN e uma decisão documentada de velocidade versus recall.

radio_button_uncheckedshort_text18. Avaliação comentada e checklist de produção

1. Com dois milhões de vetores de 1.536 dimensões e apenas 85% de acerto do cache, priorize shared_buffers e confirme o conjunto ativo.
2. Para cinco milhões substituídos diariamente e compilação curta, IVFFlat com lists orientado ao volume se ajusta melhor que HNSW.
3. Se filtro category_id gera Seq Scan, confirme primeiro B-tree nessa coluna; depois classe vetorial e estatísticas.
4. A 500 consultas vetoriais/s, use PgBouncer transaction com pool dividido entre instâncias, não uma conexão por solicitação.
5. Com CPU em 75% no Uso Geral e meta P95 individual abaixo de 50 ms, teste primeiro Otimizado para Memória maior; réplica e cache não aceleram a consulta individual sem cache.

• Meça linha de base e recall.
• Mantenha dimensão, métrica, operador e classe consistentes.
• Verifique planos relacionais e ANN.
• Observe compilação, uso, drift, bloat e atraso.
• Siga a orientação atual de Redis e limite pools/repetições.
• Documente gatilhos e reavalie toda mudança relevante.

Referências oficiais

1. Otimizar o desempenho do pgvector no Banco de Dados do Azure para PostgreSQL
2. Habilitar e usar o DiskANN
3. Réplicas de leitura no Banco de Dados do Azure para PostgreSQL
4. PgBouncer interno no Banco de Dados do Azure para PostgreSQL
5. Migrar para o Redis Gerenciado pelo Azure

Resumo do tópico

Desempenho vetorial em produção resulta de ajuste baseado em evidência em computação, dados, índices, distribuição, cache e conexões.