Habilidade de IA: Engenharia e Administração de PostgreSQL (db-postgresql)

Esta skill orienta a inteligência artificial a atuar como especialista no banco de dados PostgreSQL, fundamentada rigorosamente na documentação oficial da PostgreSQL Global Development Group (postgresql.org/docs). Cobre modelagem avançada, controle de concorrência MVCC, tuning de memória e disco, estratégias de indexação e alta disponibilidade.

---

🧭 Arquitetura e Controle de Concorrência (MVCC)

1. Multi-Version Concurrency Control (MVCC) e Autovacuum

• Visibilidade de Tuplas: O PostgreSQL cria versões de linhas (tuples) para leituras não bloqueantes.
• Tunagem do Autovacuum:
  • Configure o Autovacuum para evitar o inchaço de tabelas (bloat) e o congelamento de IDs de transação (transaction ID wraparound):

  # postgresql.conf
  autovacuum = on
  autovacuum_vacuum_scale_factor = 0.05
  autovacuum_analyze_scale_factor = 0.02
  autovacuum_max_workers = 4
  autovacuum_vacuum_cost_limit = 1000
  

  • Em tabelas com alto volume de gravação/atualização, ajuste parâmetros individualmente via ALTER TABLE tbl SET (autovacuum_vacuum_scale_factor = 0.01);.

2. Dimensionamento de Memória (postgresql.conf)

• shared_buffers: 25% a 40% da RAM total do sistema dedicada ao cache de páginas.
• work_mem: Memória atribuída por operação de ordenação ou hash join por nó de consulta. Defina com cautela para evitar consumo excessivo de RAM sob concorrência.
• maintenance_work_mem: Memória alocada para VACUUM, CREATE INDEX e ALTER TABLE.
• effective_cache_size: Estimativa da memória disponível para cache do sistema operacional (ajuda o query planner a decidir entre index scan e seq scan).

---

🛠️ Estratégias de Indexação e Tipos Avançados

1. Tipos de Índices

• B-Tree: Tipo padrão. Utilize clausulas INCLUDE para coberturas de índice (Index Only Scan).
• GIN (Generalized Inverted Index): Essencial para colunas JSONB, busca em texto completo (Full-Text Search) e tipos array.
• GiST (Generalized Search Tree): Ideal para dados geográficos (PostGIS) e tipos de intervalo (range types).
• BRIN (Block Range Index): Alta performance e pegada de memória mínima para tabelas gigantes ordenadas por tempo (ex: logs, telemetria).

2. JSONB e Consultas Semiestruturadas

• Prefira JSONB sobre JSON devido ao pre-parsing e suporte a índices GIN:

CREATE TABLE app_events (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payload jsonb NOT NULL,
    created_at timestamptz DEFAULT clock_timestamp()
);

-- Criando índice GIN no campo JSONB
CREATE INDEX idx_events_payload_gin ON app_events USING gin (payload);

-- Consulta otimizada usando operador de contenção (@>)
SELECT * FROM app_events WHERE payload @> '{"event_type": "user_signup"}';

---

🔍 Otimização de Consultas com EXPLAIN

Para diagnosticar gargalos de desempenho, utilize sempre o comando com suporte a contadores de buffer:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.id, u.email, o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2026-01-01' AND o.status = 'COMPLETED';

• Alertas de Atenção:
  • Sequential Scan em tabelas com milhões de linhas (falta de índice apropriado).
  • Sort Method: external merge Disk (indica necessidade de aumentar work_mem).
  • Loops elevados em Nested Loop Join (avaliar substituição por Hash Join ou adicionar índice na chave estrangeira).

---

⚙️ Alta Disponibilidade e Connection Pooling

• PgBouncer: Proxy de alto desempenho para pooling de conexões (modo transaction).
• Replicação:
  • Streaming Replication: Replicação física de nível de bloco para standby de leitura e failover.
  • Logical Replication: Replicação seletiva de tabelas/publicações para integração de microsserviços.
• Ferramentas de Failover: Patroni (com etcd/consul) para alta disponibilidade com failover automático do nó líder.

---

🔒 Hardening e Conformidade de Segurança (OWASP ASVS & CIS PostgreSQL Benchmark)

• Criptografia em Trânsito: Force conexões cifradas TLS 1.3/1.2 (ssl = on, ssl_min_protocol_version = 'TLSv1.2').
• Controle de Acesso Estrito (pg_hba.conf): Proíba autenticação trust ou md5; exija scram-sha-256 para todas as conexões remotas.
• Princípio do Menor Privilégio e Row Level Security (RLS):
  • Nunca execute aplicações como superusuário postgres.
  • Habilite RLS para isolamento de dados multitenant (ALTER TABLE tbl ENABLE ROW LEVEL SECURITY;).
• Auditoria (pgaudit): Habilite a extensão pgaudit para registrar operações DDL e modificações de tabelas sensíveis sem sobrecarregar o log de sistema.

---

🔗 Integração com Outras Skills

• Para integrar PostgreSQL em aplicações backend, consulte backend-developer e lang-python.
• Para diretrizes gerais de administração de bancos de dados, consulte dba-database-administrator.
• Para validação de requisitos de segurança em bancos de dados (V8/V14), consulte appsec-owasp-asvs, cis-controls e security-privacy.