PostgreSQL

O que é PostgreSQL?

PostgreSQL é um banco de dados relacional de código aberto com a reputação de fazer as coisas básicas de forma correta. Ele armazena dados em tabelas, aplica regras com constraints, envolve alterações em transações reais e expõe tudo isso através de SQL padrão. Ele vem sendo desenvolvido continuamente desde a década de 1980 e é governado por uma comunidade ampla, em vez de por uma única empresa.

Essa longevidade reflete-se nos detalhes. O Postgres possui o sistema de tipos mais rico de qualquer banco de dados popular, um mecanismo de extensão que permite que terceiros adicionem capacidades inteiramente novas e um planejador de consultas que lida com tudo, desde uma busca simples por ponto até uma consulta analítica com window functions. Ele é o banco de dados relacional padrão para novas aplicações na maioria das empresas e está disponível como serviço gerenciado em quase qualquer lugar.

Se você está escolhendo um banco de dados para aprender a fundo, este é o que mais compensa o tempo investido.

Por que as equipes escolhem o Postgres

Três propriedades explicam a maior parte de sua popularidade.

Ele é correto por padrão. As constraints são aplicadas no engine, não no código da aplicação. Um CHECK não pode ser ignorado por um serviço com bugs, um FOREIGN KEY não pode ser negligenciado por um batch job, e um índice UNIQUE não pode sofrer race conditions entre duas requisições concorrentes. A integridade dos dados torna-se uma propriedade do schema.

Ele é extensível. Em vez de embutir cada funcionalidade no core, o Postgres expõe hooks para novos tipos, operadores, métodos de indexação e linguagens procedurais. É assim que o PostGIS, pgvector, TimescaleDB e dezenas de outros projetos existem como extensões em vez de forks.

Ele fala SQL padrão. Habilidades e queries são transferíveis entre bancos de dados, ORMs e ferramentas. Você não precisa aprender um dialeto proprietário para começar.

O modelo relacional, brevemente

Um banco de dados relacional armazena dados em tabelas compostas por linhas e colunas. Cada tabela possui uma primary key que identifica unicamente uma linha, e os relacionamentos são expressos através de foreign keys que apontam para outras tabelas. O objetivo é armazenar cada fato apenas uma vez e permitir que os joins o remontem.

Considere usuários e pedidos. Em vez de repetir o e-mail de um cliente em cada pedido, você armazena o e-mail uma única vez em users e referencia o usuário a partir de orders através de user_id. Isso é a normalização, e ela evita a anomalia clássica onde uma linha é atualizada e outras mil ainda mantêm o valor antigo.

A normalização não é uma religião. A terceira forma normal é o padrão sensato, e a desnormalização deliberada — um order_count em cache, uma materialized view — é uma decisão de performance que você toma posteriormente, com medições em mãos.

Tipos de dados que realmente valem a pena

O Postgres oferece mais tipos do que a maioria dos projetos precisa, mas alguns poucos são essenciais no dia a dia:

• text — string de comprimento variável sem limite arbitrário. Prefira-o ao varchar(n), a menos que o limite seja uma regra de negócio real.
• integer e bigint — números inteiros. Use bigint para qualquer coisa que possa crescer sem limites, como IDs de uma sequência.
• numeric(p, s) — aritmética decimal exata. Este é o tipo correto para dinheiro, não real ou double precision.
• timestamptz — um timestamp armazenado em UTC com suporte a fuso horário. Sempre prefira-o ao timestamp para dados de aplicação.
• uuid — um identificador de 128 bits, útil quando os clientes geram IDs ou quando você não quer expor a contagem de registros.
• jsonb — JSON binário que você pode indexar e consultar.
• Arrays e tipos compostos — text[], integer[] e tipos de linha personalizados, úteis para tags e pequenas listas ordenadas.

O exemplo do dinheiro vale a pena ser internalizado: 0.1 + 0.2 não é 0.3 em ponto flutuante binário. Armazene valores como unidades menores inteiras (total_cents) ou como numeric, nunca como float.

Criando tabelas e constraints

Um schema é um contrato. Cada constraint que você declara é uma classe de bug que você não consegue enviar para produção.

CREATE TABLE users (
  id         bigserial PRIMARY KEY,
  email      text NOT NULL UNIQUE,
  name       text NOT NULL CHECK (length(name) > 0),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id          bigserial PRIMARY KEY,
  user_id     bigint NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  status      text NOT NULL DEFAULT 'pending'
                CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
  total_cents integer NOT NULL CHECK (total_cents >= 0),
  created_at  timestamptz NOT NULL DEFAULT now(),
  metadata    jsonb NOT NULL DEFAULT '{}'::jsonb
);

As escolhas importantes aqui são deliberadas. NOT NULL elimina toda uma família de bugs de dados ausentes. UNIQUE em email impõe o invariante no banco de dados, que é o único lugar onde uma race condition não pode derrotá-lo. A CHECK em status documenta e impõe a máquina de estados. ON DELETE CASCADE decide o que acontece quando um usuário desaparece, em vez de deixar registros órfãos.

Prefira adicionar constraints na mesma migration que cria a tabela. Adicionar uma NOT NULL ou uma foreign key posteriormente significa que você terá que primeiro limpar quaisquer linhas inválidas que tenham se acumulado enquanto a regra estava ausente.

Consultando com joins

A maioria das consultas reais combina tabelas. Um join correlaciona linhas de duas relações com base em uma condição, e a escolha do tipo de join decide o que acontece com as linhas que não possuem correspondência.

SELECT u.email,
       o.id,
       o.total_cents
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 50;

JOIN (ou INNER JOIN) mantém apenas os pares correspondentes. LEFT JOIN mantém todas as linhas à esquerda e preenche o lado direito com NULL quando nada corresponde — a maneira padrão de perguntar “quais usuários não possuem pedidos”:

SELECT u.email, count(o.id) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email
HAVING count(o.id) = 0;

Agregadores como count, sum, avg, min e max colapsam grupos de linhas em apenas uma. Toda coluna no SELECT que não esteja dentro de um agregador deve aparecer no GROUP BY. A cláusula WHERE filtra as linhas antes do agrupamento; HAVING filtra os grupos depois. Confundir as duas é uma fonte comum de resultados confusos.

Índices e EXPLAIN ANALYZE

Um índice é uma estrutura ordenada que permite ao planejador encontrar linhas sem escanear a tabela inteira. O padrão é o B-tree, que atende a predicados de igualdade e intervalo e suporta ORDER BY diretamente.

CREATE INDEX orders_user_created_idx
  ON orders (user_id, created_at DESC);

Um índice composto como este cobre consultas que filtram por user_id e ordenam por created_at. A ordem das colunas importa: a coluna mais à esquerda deve aparecer na consulta para que o índice seja útil na filtragem. Esta é a regra do prefixo à esquerda (leftmost-prefix rule), e é por isso que o design de índices começa pelas consultas, não pelas colunas.

Outros tipos de índices cobrem diferentes formatos:

• GIN — índices invertidos para jsonb, arrays e busca textual (tsvector).
• GiST — geometria, intervalos e busca de vizinho mais próximo, amplamente utilizado pelo PostGIS.
• BRIN — índices minúsculos sobre dados naturalmente ordenados, como tabelas de timestamp apenas de anexo (append-only).
• Hash — buscas apenas de igualdade, raramente necessários já que o B-tree as resolve.

Nunca tente adivinhar se um índice está sendo usado. Pergunte ao planejador:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 10;

A saída mostra a árvore do plano, o custo estimado e — com ANALYZE — a quantidade real de linhas e o tempo.

Limit  (cost=0.43..12.94 rows=10 width=48)
       (actual time=0.031..0.079 rows=10 loops=1)
  ->  Index Scan Backward using orders_user_created_idx on orders
        (cost=0.43..521.10 rows=417 width=48)
        (actual time=0.029..0.070 rows=10 loops=1)
        Index Cond: (user_id = 42)
Planning Time: 0.184 ms
Execution Time: 0.108 ms

Leia de dentro para fora. Aqui, o Index Scan com Index Cond: (user_id = 42) confirma que o índice composto está cumprindo seu papel. Procure por Seq Scan em uma tabela grande onde você esperava um Index Scan, e por uma diferença grande entre as linhas estimadas e as reais, o que geralmente indica estatísticas obsoletas. Execute ANALYZE orders; para atualizá-las e EXPLAIN (ANALYZE, BUFFERS) para ver quanto dado foi lido do cache versus disco.

Transações e MVCC

Uma transação agrupa instruções para que todas entrem em vigor ou nenhuma delas o faça. O Postgres implementa transações com MVCC: em vez de bloquear linhas para leitura, ele mantém múltiplas versões e fornece um snapshot para cada transação.

BEGIN;
UPDATE accounts SET balance_cents = balance_cents - 5000 WHERE id = 1;
UPDATE accounts SET balance_cents = balance_cents + 5000 WHERE id = 2;
COMMIT;

Se algo falhar, ROLLBACK desfaz a transação inteira. O código da aplicação deve envolver escritas de múltiplas etapas em uma transação e estar preparado para tentar novamente (retry) em caso de falhas de serialização ao utilizar níveis de isolamento mais rigorosos.

Os níveis de isolamento decidem o que uma transação pode observar:

• Read Committed (padrão) — cada instrução vê um snapshot atualizado; ideal para a maioria das cargas de trabalho.
• Repeatable Read — a transação inteira vê um único snapshot; útil quando você lê as mesmas linhas repetidamente.
• Serializable — as transações se comportam como se fossem executadas uma após a outra; a garantia mais forte e a que tem maior probabilidade de exigir retentativas.

O MVCC tem um custo: linhas atualizadas e deletadas deixam “dead tuples” para trás. O Autovacuum as recupera em segundo plano. Transações de longa duração impedem a limpeza e causam o inchaço (bloat) das tabelas, portanto, mantenha as transações curtas e monitore o vacuum em tabelas com alta rotatividade de dados.

Connection pooling com PgBouncer

O Postgres gerencia cada conexão de cliente com um processo separado do sistema operacional. Isso é robusto, mas significa que as conexões são mais pesadas do que no MySQL, e algumas centenas de clientes inativos podem consumir uma quantidade considerável de memória. O limite prático é max_connections, e excedê-lo gera erros, em vez de uma degradação suave.

A solução é um connection pooler. O PgBouncer fica entre a aplicação e o Postgres, mantém um pequeno pool de conexões reais e multiplexa diversas conexões de clientes nelas. Ele oferece três modos:

• Session pooling — uma conexão com o servidor é mantida durante toda a sessão do cliente.
• Transaction pooling — uma conexão com o servidor é mantida apenas durante uma transação; a escolha mais comum para web apps.
• Statement pooling — uma conexão é mantida para apenas um statement; o modo mais agressivo e restritivo.

O transaction pooling quebra funcionalidades com escopo de sessão, como SET, advisory locks mantidos entre statements e LISTEN. Use SET LOCAL dentro de transações e verifique se o seu ORM se comporta bem no modo de transação.

Independentemente da sua escolha, utilize pool no lado da aplicação também. Um pool por processo com um máximo razoável, somado a um pooler na frente, é a arquitetura padrão. Nunca abra uma nova conexão por requisição.

JSONB: quando utilizá-lo

jsonb armazena JSON em um formato binário decomposto que suporta indexação e um conjunto rico de operadores. É genuinamente útil, mas também é fácil exagerar no seu uso.

SELECT id, metadata->>'coupon' AS coupon
FROM orders
WHERE metadata @> '{"channel": "mobile"}';

O operador de contenção @> pode utilizar um índice GIN:

CREATE INDEX orders_metadata_idx ON orders USING gin (metadata);

Use JSONB para dados cujo formato seja genuinamente variável: payloads de webhook, respostas de terceiros, atributos definidos pelo usuário, feature flags. Não o utilize apenas para evitar a escrita de uma migration. Campos que você filtra, faz join, restringe ou agrega devem ser colunas com tipos reais. O teste é simples: se você se pegar convertendo metadata->>'price' para um número na maioria das queries, ele deveria ter sido um integer.

Um caminho intermediário é o híbrido: campos estáveis como colunas, e tudo o que for opcional em um “saco” attributes de JSONB. Isso oferece constraints e índices onde eles são importantes e flexibilidade onde não são.

Extensões que expandem as possibilidades

CREATE EXTENSION instala um módulo empacotado em um banco de dados. Algumas delas valem a pena conhecer:

• pg_stat_statements — registra o texto normalizado das queries com tempo de execução e I/O; a primeira coisa a ser habilitada ao investigar performance.
• PostGIS — tipos e funções geográficas; o motivo pelo qual muitas equipes escolhem Postgres para dados de localização.
• pgvector — colunas de vetores e índices de vizinhos mais próximos aproximados para embeddings e busca semântica.
• pgcrypto — funções criptográficas, como gen_random_uuid() em versões mais antigas.
• pg_trgm — índices de trigramas para LIKE rápido e fuzzy matching.

Habilite apenas o que você utiliza e lembre-se de que provedores gerenciados geralmente exigem uma configuração ou uma solicitação de suporte antes que uma extensão possa ser instalada.

Papéis e permissões

O Postgres separa roles (que podem fazer login ou possuir objetos) de privileges (o que uma role pode fazer). Conceda o menor privilégio necessário para o funcionamento.

CREATE ROLE app_user LOGIN PASSWORD 'secret';

GRANT CONNECT ON DATABASE shop TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE
  ON ALL TABLES IN SCHEMA public TO app_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

A linha ALTER DEFAULT PRIVILEGES é importante porque ela se aplica a tabelas criadas posteriormente, e não apenas às existentes. Evite conectar-se como o proprietário do banco de dados ou como um superusuário a partir da aplicação; um app comprometido não deve ser capaz de DROP TABLE. Para migrações, utilize uma role separada com direitos de DDL.

Backups e recuperação

Existem dois tipos de backups. Backups lógicos utilizam pg_dump para produzir um script portátil ou arquivo de um banco de dados, e pg_dumpall para capturar roles e globais. Eles são simples e tolerantes a versões, mas mais lentos para restaurar em larga escala.

pg_dump --format=custom --file=shop.dump shop
pg_restore --dbname=shop_restore shop.dump

Backups físicos copiam o diretório de dados e o write-ahead log. pg_basebackup somado ao arquivamento contínuo de WAL permite a recuperação de ponto no tempo (point-in-time recovery), permitindo que você restaure o sistema para um momento específico antes de uma migration problemática. Esta é a abordagem utilizada por provedores gerenciados.

Independentemente da sua escolha, a regra é a mesma: automatize, armazene fora do host primário e restaure regularmente em um banco de dados temporário. Um backup que você nunca restaurou é apenas uma esperança, não um plano.

Busca textual (full-text search) sem serviços adicionais

O Postgres possui busca textual nativa, o que geralmente é suficiente para evitar a execução de um cluster de busca separado. Ele funciona convertendo o texto em um tsvector de lexemas e as consultas em um tsquery, para então realizar a correspondência entre eles.

ALTER TABLE posts
  ADD COLUMN search tsvector
    GENERATED ALWAYS AS (
      to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
    ) STORED;

CREATE INDEX posts_search_idx ON posts USING gin (search);

SELECT id, title
FROM posts
WHERE search @@ plainto_tsquery('english', 'connection pooling')
ORDER BY ts_rank(search, plainto_tsquery('english', 'connection pooling')) DESC
LIMIT 20;

Uma coluna gerada mantém o vetor sincronizado automaticamente, e o índice GIN torna a correspondência do @@ rápida. O plainto_tsquery transforma a entrada do usuário em uma consulta de forma segura, enquanto o ts_rank ordena por relevância. Você pode adicionar ts_headline para destacar as correspondências nos resultados.

Recorra a um mecanismo de busca dedicado apenas quando precisar de faceting, tolerância a erros de digitação (fuzzy search) em larga escala ou ajuste de relevância entre múltiplos documentos. Para a maioria das aplicações, a versão nativa elimina a necessidade de gerenciar mais uma peça na infraestrutura.

Views e materialized views

Uma view é uma query armazenada que se comporta como uma tabela. Ela não armazena dados; é apenas uma forma nomeada de encapsular um formato comum e manter as permissões consistentes.

CREATE VIEW paid_orders AS
SELECT id, user_id, total_cents, created_at
FROM orders
WHERE status = 'paid';

SELECT * FROM paid_orders WHERE created_at >= now() - interval '7 days';

Uma materialized view armazena o resultado, o que torna agregações custosas mais baratas de ler, ao custo de os dados poderem ficar defasados (staleness).

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT date_trunc('day', created_at) AS day,
       sum(total_cents)             AS revenue_cents
FROM orders
WHERE status = 'paid'
GROUP BY 1;

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

CONCURRENTLY atualiza sem bloquear os leitores, mas requer um índice único na view. Agende as atualizações de acordo com a sua tolerância a números defasados e lembre-se de que uma materialized view é um cache: ela sempre pode ser reconstruída a partir das tabelas base.

Alterando schemas com segurança

Em um banco de dados em produção, DDLs aplicam locks. O objetivo é evitar locks de ACCESS EXCLUSIVE prolongados que bloqueiem leituras e escritas enquanto uma tabela é reescrita.

ALTER TABLE orders ADD COLUMN channel text;
ALTER TABLE orders ALTER COLUMN channel SET DEFAULT 'web';

CREATE INDEX CONCURRENTLY orders_channel_idx ON orders (channel);

Adicionar uma coluna nullable sem valor default é instantâneo no Postgres moderno, e definir um default altera apenas os metadados. O CREATE INDEX CONCURRENTLY constrói o índice sem manter um write lock, embora não possa ser executado dentro de uma transação e possa falhar, deixando um índice inválido que deve ser removido para nova tentativa. Adicionar uma constraint de NOT NULL em uma tabela grande deve ser feito em etapas: adicione uma CHECK que seja validada separadamente e, então, converta-a.

Versione cada alteração como uma migration para que todos os ambientes cheguem ao mesmo schema na mesma ordem. Nunca edite uma tabela de produção manualmente; você acabará esquecendo o que fez.

Carga em massa com COPY

Inserir linhas em instruções individuais é a maneira mais lenta de carregar dados. O Postgres possui o COPY, que transmite os dados em um único comando e pode ser ordens de magnitude mais rápido do que um loop de INSERTs.

COPY orders (user_id, status, total_cents, created_at)
FROM '/data/orders.csv'
WITH (FORMAT csv, HEADER true);

COPY (SELECT id, email FROM users WHERE created_at > now() - interval '30 days')
TO '/tmp/recent-users.csv'
WITH (FORMAT csv, HEADER true);

O COPY é executado no servidor, portanto o arquivo deve ser legível pelo processo do banco de dados; use \copy no psql para ler a partir do cliente. Para código de aplicação, a API copy do driver transmite as linhas sem a necessidade de um arquivo temporário. Envolva a carga em uma transação quando precisar que a operação seja atômica (tudo ou nada), e remova ou recrie os índices após cargas muito grandes, pois a manutenção de índices durante um insert em massa é o principal custo.

Inserts em lote (batch inserts) são um meio-termo mais simples para quando o COPY é impraticável:

INSERT INTO orders (user_id, status, total_cents)
VALUES (1, 'paid', 1999),
       (2, 'paid', 4599),
       (3, 'pending', 999);

Melhores práticas

• Escolha text, bigint, numeric, timestamptz e jsonb deliberadamente em vez de usar varchar e floats por padrão.
• Declare NOT NULL, UNIQUE, CHECK e foreign keys na mesma migration que cria a tabela.
• Projete índices com base em queries reais e confirme-os com EXPLAIN ANALYZE.
• Mantenha as transações curtas e escolha o nível de isolamento mais baixo que ainda seja correto.
• Coloque um pooler como o PgBouncer na frente do banco de dados e utilize pooling na aplicação também.
• Use JSONB para dados genuinamente variáveis, e não como uma forma de pular o design do schema.
• Conceda o privilégio mínimo às roles da aplicação e mantenha o DDL em uma role de migration.
• Ative pg_stat_statements, monitore o autovacuum e restaure um backup periodicamente.
• Versione cada alteração de schema como uma migration para que os ambientes permaneçam sincronizados.

Erros comuns

• Armazenar dinheiro em double precision e perder centavos devido ao arredondamento.
• Usar timestamp em vez de timestamptz e acabar armazenando horários locais por acidente.
• Esquecer o WHERE em um UPDATE ou DELETE e acabar alterando todas as linhas.
• Criar um índice por coluna em vez de índices compostos que correspondam à query.
• Abrir uma nova conexão por requisição e esgotar o max_connections.
• Manter uma transação aberta durante uma chamada HTTP ou interação do usuário.
• Tratar NULL como igual a NULL; comparações exigem IS NULL e IS NOT NULL.
• Presumir que um índice está sendo usado sem verificar o plano de execução da query.
• Executar queries da aplicação como proprietário do banco de dados ou superusuário.

Próximos passos

O Postgres recompensa quem se aprofunda, e o próximo passo natural é a fluência na linguagem que ele fala: leia o guia de SQL para aprimorar joins, CTEs e window functions. Se você está avaliando alternativas, o guia de MySQL aborda o outro banco de dados relacional open-source dominante, o de MongoDB explica o modelo de documentos, e o de Redis mostra a camada de cache que geralmente fica à frente de um armazenamento relacional.