Postgres, the 80/20 for developers.

O que é um banco relacional e um schemaWhat a relational database and a schema are

Um banco relacional guarda dados em tabelas: linhas (registros) e colunas (campos), com relações entre tabelas por chaves. É "relacional" no sentido matemático de relação (um conjunto de tuplas), não porque as tabelas se relacionam entre si — embora também façam isso. O gerenciador que guarda, organiza e responde consultas sobre esses dados é um SGBD (sistema de gerenciamento de banco de dados; em inglês, RDBMS).A relational database stores data in tables: rows (records) and columns (fields), with relations between tables through keys. It is "relational" in the mathematical sense of a relation (a set of tuples), not because tables relate to one another — though they also do that. The manager that stores, organizes and answers queries over that data is a DBMS (database management system).

O que é um schema, de fatoWhat a schema actually is

Um schema é um namespace dentro de um database: uma forma de organizar tabelas (por exemplo, vendas.pedidos e rh.funcionarios no mesmo database) sem precisar de um database separado para cada área. É diferente do uso da palavra "schema" em outros bancos (no MySQL, "schema" costuma ser sinônimo de "database" inteiro).A schema is a namespace inside a database: a way to organize tables (for example, vendas.pedidos and rh.funcionarios in the same database) without needing a separate database for each area. This differs from how the word "schema" is used in other databases (in MySQL, "schema" is usually a synonym for a whole "database").

CREATE SCHEMA vendas;
CREATE TABLE vendas.pedidos (id bigint, total numeric(10,2));

SET search_path TO vendas, public;   -- define a ordem de busca; agora "pedidos" sozinho já encontra vendas.pedidos
SELECT * FROM pedidos;               -- funciona sem o prefixo "vendas."

Gerenciadores de banco: as opções mais comunsDatabase managers: the common options

Esta nota usa Postgres porque é hoje a escolha padrão para projetos novos: licença permissiva, extensões (PostGIS, pg_trgm, pgvector), tipos de dado ricos e um dos motores de consulta mais corretos que existem.This note uses Postgres because it is today's default choice for new projects: a permissive license, extensions (PostGIS, pg_trgm, pgvector), rich data types, and one of the most correct query engines around.

Ferramentas para abrir um bancoTools to open a database

Como conectarHow to connect

Conectar ao Postgres sempre passa pelos mesmos seis dados: quem você é, com qual senha, em qual máquina, em qual porta, e em qual database. Toda ferramenta — psql, uma lib de app, um painel gráfico — pede exatamente isso, às vezes como uma connection string só.Connecting to Postgres always goes through the same six pieces of data: who you are, with which password, on which machine, on which port, and which database. Every tool — psql, an app library, a graphical panel — asks for exactly that, sometimes as a single connection string.

Pelo psqlThrough psql

psql -h db.exemplo.com -p 5432 -U ana -d loja        # pede a senha interativamente
psql "postgresql://ana:s3nha@db.exemplo.com:5432/loja"   # tudo numa string só
psql -U postgres                                     # local, usuário "postgres", pede/usa auth local
PGPASSWORD=s3nha psql -h localhost -U ana loja        # senha por variável (cuidado: aparece em `ps`!)

Dentro do psql, estes comandos resolvem 80% do dia a dia:Inside psql, these commands cover 80% of daily use:

Usuários e senhas: as três camadasUsers and passwords: the three layers

Esta é a maior fonte de confusão para quem está começando, porque a palavra "usuário" significa três coisas diferentes que não têm relação automática entre si. Desembaraçar isso resolve a maioria dos "funciona na minha máquina, não funciona no servidor".This is the biggest source of confusion for beginners, because the word "user" means three different things with no automatic relation between them. Untangling this solves most "works on my machine, not on the server" problems.

Como as camadas se conectam na práticaHow the layers connect in practice

Exercício: criar uma role, um database e testarExercise: create a role, a database, and test it

sudo -u postgres psql    # entra como a role "postgres" (super-usuária do banco)

CREATE ROLE ana LOGIN PASSWORD 's3nha-forte-de-verdade';
CREATE DATABASE loja OWNER ana;
GRANT ALL PRIVILEGES ON DATABASE loja TO ana;
\q

psql -h localhost -U ana -d loja     # agora conecte como ANA, não como postgres
# Password for user ana: ****

Resultado esperado: o segundo psql pede senha (porque agora você está entrando como a role ana, via rede/senha, não por confiança do sistema operacional) e o prompt muda para loja=>. Se ana tentar CREATE DATABASE sem CREATEDB, recebe um erro de permissão — ela só é dona do banco loja, não super-usuária.Expected result: the second psql asks for a password (because you are now logging in as the role ana, over the network/password, not by operating-system trust) and the prompt changes to loja=>. If ana tries CREATE DATABASE without CREATEDB, she gets a permission error — she only owns the loja database, she is not a superuser.

Atributos de role mais usadosMost-used role attributes

Segurança de acesso: pg_hba, TLS e privilégio mínimoAccess security: pg_hba, TLS and least privilege

Quem pode se conectar, de onde, e como ela precisa provar quem é — tudo isso é decidido por um arquivo: pg_hba.conf (host-based authentication). É o "firewall de identidade" do Postgres, separado do firewall de rede (nftables/ufw) e separado das permissões SQL (GRANT).Who may connect, from where, and how they must prove who they are — all of that is decided by one file: pg_hba.conf (host-based authentication). It is Postgres's "identity firewall", separate from the network firewall (nftables/ufw) and separate from SQL permissions (GRANT).

Lendo o pg_hba.confReading pg_hba.conf

# TYPE  DATABASE  USER   ADDRESS          METHOD
local   all       postgres                         peer
local   all       all                               scram-sha-256
host    loja      ana     127.0.0.1/32              scram-sha-256
host    loja      app     10.0.0.0/8                scram-sha-256
hostssl all       all     0.0.0.0/0                 scram-sha-256
host    all       all     0.0.0.0/0                 reject

TLS e o que "sslmode" realmente garanteTLS and what "sslmode" actually guarantees

Privilégio mínimoLeast privilege

A aplicação nunca deveria conectar como super-usuária. O padrão sensato: uma role por aplicação, com só as permissões que ela usa.The application should never connect as a superuser. The sensible default: one role per application, with only the permissions it uses.

CREATE ROLE app_readonly NOLOGIN;
GRANT CONNECT ON DATABASE loja TO app_readonly;
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_readonly;  -- vale para tabelas FUTURAS também

CREATE ROLE app_web LOGIN PASSWORD 'outra-senha-forte' IN ROLE app_readonly;
GRANT INSERT, UPDATE, DELETE ON pedidos, itens_pedido TO app_web;   -- só nessas tabelas

Pooling de conexõesConnection pooling

Cada conexão Postgres custa memória (um processo por conexão). Para apps com muitas conexões curtas (serverless, muitas réplicas), um pooler como PgBouncer ou Supavisor fica entre a aplicação e o Postgres, reaproveitando um número menor de conexões reais — o lado de segurança é que o pooler também costuma ser o lugar certo para autenticar e limitar por aplicação.Every Postgres connection costs memory (one process per connection). For apps with many short-lived connections (serverless, many replicas), a pooler like PgBouncer or Supavisor sits between the application and Postgres, reusing a smaller number of real connections — the security angle is that the pooler is also usually the right place to authenticate and limit per application.

Comandos do dia a diaEveryday commands

Um resumo do SQL que cobre a maior parte do trabalho de um desenvolvedor: criar estrutura (DDL) e ler/escrever dados (DML).A summary of the SQL that covers most of a developer's work: creating structure (DDL) and reading/writing data (DML).

DDL: estruturaDDL: structure

CREATE TABLE pedidos (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  cliente_id  bigint NOT NULL REFERENCES clientes(id),
  total       numeric(10,2) NOT NULL DEFAULT 0,
  status      text NOT NULL DEFAULT 'pendente',
  criado_em   timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE pedidos ADD COLUMN observacao text;
ALTER TABLE pedidos ALTER COLUMN total SET NOT NULL;
DROP TABLE IF EXISTS tabela_antiga;
CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);

DML: dadosDML: data

SELECT id, total FROM pedidos WHERE status = 'pendente' ORDER BY criado_em DESC LIMIT 20;

INSERT INTO pedidos (cliente_id, total) VALUES (42, 199.90) RETURNING id;

UPDATE pedidos SET status = 'pago' WHERE id = 7;

DELETE FROM pedidos WHERE status = 'cancelado' AND criado_em < now() - interval '1 year';

SELECT c.nome, count(p.id) AS total_pedidos
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.id
GROUP BY c.nome
HAVING count(p.id) > 0
ORDER BY total_pedidos DESC;

CTEs e window functions (o "pivô")CTEs and window functions (the "pivot")

CTEs (Common Table Expressions, o bloco WITH) dão nome a uma subconsulta para usá-la como se fosse uma tabela, tornando consultas grandes legíveis. Window functions (funções de janela) fazem cálculos "através" de um grupo de linhas sem colapsá-las numa só — é a ferramenta certa para o que costuma ser pedido como "pivô" (comparar cada linha com um agregado do seu grupo).CTEs (Common Table Expressions, the WITH block) name a subquery so it can be used as if it were a table, making large queries readable. Window functions compute "across" a group of rows without collapsing them into one — the right tool for what is usually asked for as a "pivot" (comparing each row against an aggregate of its group).

CTE simplesA simple CTE

WITH pedidos_pagos AS (
  SELECT cliente_id, total FROM pedidos WHERE status = 'pago'
)
SELECT c.nome, sum(p.total) AS total_gasto
FROM clientes c
JOIN pedidos_pagos p ON p.cliente_id = c.id
GROUP BY c.nome
ORDER BY total_gasto DESC;

CTE recursiva: percorrer uma árvoreRecursive CTE: walking a tree

WITH RECURSIVE subordinados AS (
  SELECT id, nome, gerente_id FROM funcionarios WHERE id = 1        -- âncora: o chefe
  UNION ALL
  SELECT f.id, f.nome, f.gerente_id
  FROM funcionarios f
  JOIN subordinados s ON f.gerente_id = s.id                         -- passo recursivo
)
SELECT * FROM subordinados;

Resultado esperado: todos os subordinados diretos e indiretos do funcionário 1, por quantos níveis existirem — sem precisar saber a profundidade do organograma de antemão.Expected result: every direct and indirect subordinate of employee 1, through however many levels exist — with no need to know the org chart's depth beforehand.

Window functions: o "pivô" do PostgresWindow functions: Postgres's "pivot"

Bancos como o SQL Server têm um comando PIVOT; o Postgres resolve o mesmo problema com FILTER (agregação condicional) ou funções de janela com OVER.Databases like SQL Server have a PIVOT command; Postgres solves the same problem with FILTER (conditional aggregation) or window functions with OVER.

-- "pivô" clássico: uma coluna por status, por mês
SELECT
  date_trunc('month', criado_em) AS mes,
  count(*) FILTER (WHERE status = 'pago')      AS pagos,
  count(*) FILTER (WHERE status = 'cancelado') AS cancelados
FROM pedidos
GROUP BY mes
ORDER BY mes;

-- window function: o total do cliente ao lado de CADA pedido, sem agrupar
SELECT
  id, cliente_id, total,
  sum(total) OVER (PARTITION BY cliente_id) AS total_do_cliente,
  rank()     OVER (PARTITION BY cliente_id ORDER BY total DESC) AS posicao
FROM pedidos;

Resultado esperado: a segunda consulta mantém uma linha por pedido (diferente de um GROUP BY, que colapsaria), com total_do_cliente repetindo a soma do grupo em cada linha e posicao ranqueando os pedidos de cada cliente do maior para o menor.Expected result: the second query keeps one row per order (unlike a GROUP BY, which would collapse them), with total_do_cliente repeating the group's sum on every row and posicao ranking each customer's orders from largest to smallest.

Index: ler sem escanear tudoIndexes: reading without scanning everything

Um índice é como o sumário de um livro: uma estrutura à parte que aponta direto para onde cada valor está, em vez de o Postgres ler a tabela inteira (sequential scan) para achar o que você pediu. Custa espaço em disco e um pouco de velocidade a mais em cada escrita; em compensação, consultas que filtram ou ordenam por aquela coluna ficam ordens de magnitude mais rápidas.An index is like a book's index: a separate structure that points straight to where each value is, instead of Postgres reading the whole table (a sequential scan) to find what you asked for. It costs disk space and a little extra time on every write; in exchange, queries that filter or sort by that column get orders of magnitude faster.

Exercício: ver o índice funcionandoExercise: see the index at work

EXPLAIN ANALYZE SELECT * FROM pedidos WHERE cliente_id = 42;
-- antes de criar o índice: "Seq Scan on pedidos ... rows=1 ... Execution Time: 8.421 ms"

CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);

EXPLAIN ANALYZE SELECT * FROM pedidos WHERE cliente_id = 42;
-- depois: "Index Scan using idx_pedidos_cliente ... Execution Time: 0.045 ms"

Resultado esperado: o plano muda de Seq Scan (lê a tabela inteira) para Index Scan ou Bitmap Index Scan, e o Execution Time cai — em tabelas pequenas a diferença é pequena, mas cresce junto com a tabela. EXPLAIN ANALYZE executa a consulta de verdade; para só ver o plano sem rodar, use EXPLAIN sozinho.Expected result: the plan changes from Seq Scan (reads the whole table) to Index Scan or Bitmap Index Scan, and Execution Time drops — on small tables the difference is small, but it grows with the table. EXPLAIN ANALYZE actually runs the query; to see just the plan without running it, use EXPLAIN alone.

UUID ou serial?UUID or serial?

A chave primária de uma tabela costuma ser um número sequencial (serial/GENERATED ALWAYS AS IDENTITY) ou um UUID (um número aleatório gigante, praticamente único no universo). Cada um resolve um problema diferente.A table's primary key is usually either a sequential number (serial/GENERATED ALWAYS AS IDENTITY) or a UUID (a huge random number, practically unique in the universe). Each solves a different problem.

-- serial moderno (desde o Postgres 10, prefira a IDENTITY ao velho "serial")
CREATE TABLE pedidos (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, ...);

-- UUID
CREATE EXTENSION IF NOT EXISTS pgcrypto;           -- só em Postgres < 13
CREATE TABLE sessoes (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),    -- nativo desde o Postgres 13
  usuario_id bigint NOT NULL,
  criada_em timestamptz NOT NULL DEFAULT now()
);

Full-text search no lugar de LIKEFull-text search instead of LIKE

LIKE '%palavra%' funciona, mas não usa índice de forma eficiente (o % no início impede o B-tree de "pular" direto ao lugar certo) e não entende idioma nenhum — busca "corre" não acha "correu". Para busca de texto de verdade, o Postgres tem busca textual embutida.LIKE '%word%' works, but does not use an index efficiently (the leading % stops a B-tree from "jumping" straight to the right place) and understands no language at all — searching "run" will not find "ran". For real text search, Postgres has built-in full-text search.

tsvector e tsquerytsvector and tsquery

ALTER TABLE produtos ADD COLUMN busca tsvector
  GENERATED ALWAYS AS (to_tsvector('portuguese', nome || ' ' || descricao)) STORED;

CREATE INDEX idx_produtos_busca ON produtos USING gin (busca);

SELECT nome FROM produtos
WHERE busca @@ to_tsquery('portuguese', 'camisa & azul')     -- E
   OR busca @@ to_tsquery('portuguese', 'camisa | blusa');   -- OU

SELECT nome, ts_rank(busca, to_tsquery('portuguese', 'camisa')) AS relevancia
FROM produtos
WHERE busca @@ to_tsquery('portuguese', 'camisa')
ORDER BY relevancia DESC;

Resultado esperado: buscar "camisa" encontra também "camisas" (o to_tsvector reduz palavras à raiz — stemming) e o ts_rank ordena pela relevância, algo que LIKE nunca faz.Expected result: searching "shirt" also finds "shirts" (to_tsvector reduces words to their root — stemming) and ts_rank sorts by relevance, something LIKE never does.

Quando o full-text search é demais: pg_trgmWhen full-text search is overkill: pg_trgm

Para "busca parecida" (tolerar erros de digitação, autocompletar) sem a complexidade de idioma do full-text, a extensão pg_trgm indexa trigramas (grupos de 3 letras) e acelera ILIKE/similarity().For "fuzzy search" (tolerating typos, autocomplete) without full-text's language complexity, the pg_trgm extension indexes trigrams (groups of 3 letters) and speeds up ILIKE/similarity().

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_produtos_nome_trgm ON produtos USING gin (nome gin_trgm_ops);

SELECT nome FROM produtos WHERE nome ILIKE '%camisa%';         -- agora usa o índice
SELECT nome, similarity(nome, 'camiza azul') AS parecido
FROM produtos ORDER BY parecido DESC LIMIT 5;                  -- tolera o erro de digitação

Quem tem acesso ao quêWho has access to what

"Quem tem acesso a quê" no Postgres se decide em camadas: a role precisa existir, precisa de permissão no database, no schema, e por fim na tabela (ou até na linha). GRANT concede, REVOKE retira.Who has access to what, in Postgres, is decided in layers: the role must exist, and needs permission on the database, on the schema, and finally on the table (or even on the row). GRANT gives, REVOKE takes away.

GRANT CONNECT ON DATABASE loja TO ana;
GRANT USAGE ON SCHEMA vendas TO ana;
GRANT SELECT, INSERT ON vendas.pedidos TO ana;
GRANT SELECT (nome, email) ON clientes TO suporte;        -- só essas colunas
REVOKE INSERT ON vendas.pedidos FROM ana;

-- o que uma role pode fazer:
SELECT grantee, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'ana';

Row-Level Security (RLS): permissão por linhaRow-Level Security (RLS): permission per row

Quando até colunas não bastam — por exemplo, cada cliente só pode ver seus próprios pedidos — o RLS aplica um filtro automático em toda consulta daquela role, sem precisar confiar que todo WHERE da aplicação está correto.When even columns are not enough — for example, each customer may only see their own orders — RLS applies an automatic filter to every query from that role, without having to trust that every WHERE in the application is correct.

ALTER TABLE pedidos ENABLE ROW LEVEL SECURITY;

CREATE POLICY pedidos_do_proprio_cliente ON pedidos
  USING (cliente_id = current_setting('app.cliente_id')::bigint);

-- a aplicação define quem está logado antes de consultar:
SET app.cliente_id = '42';
SELECT * FROM pedidos;   -- só retorna pedidos do cliente 42, mesmo sem WHERE

Backups e a regra 3-2-1Backups and the 3-2-1 rule

Um backup só vale alguma coisa se você já tiver restaurado ele com sucesso pelo menos uma vez. O Postgres tem três ferramentas principais, e por cima delas vale uma regra que não é específica de banco de dados: a 3-2-1.A backup is only worth something once you have restored it successfully at least once. Postgres has three main tools, and on top of them sits a rule that is not database-specific at all: the 3-2-1 rule.

As três ferramentasThe three tools

pg_dump -U postgres -h localhost -Fc loja > loja_$(date +%F).dump   # formato comprimido, -Fc
pg_dump -U postgres loja > loja.sql                                 # texto puro, legível, dá pra ler/editar

pg_restore -U postgres -d loja_nova loja_$(date +%F).dump           # restaura o formato -Fc
psql -U postgres -d loja_nova < loja.sql                            # restaura o SQL puro

Resultado esperado: depois do pg_restore num database novo e vazio, \dt mostra as mesmas tabelas do original, e um SELECT count(*) bate com a contagem de antes do dump.Expected result: after pg_restore into a new, empty database, \dt shows the same tables as the original, and a SELECT count(*) matches the count from before the dump.

Point-in-time recovery (PITR), em uma frasePoint-in-time recovery (PITR), in one sentence

Com pg_basebackup (uma foto dos arquivos) mais o WAL (write-ahead log: todo write é gravado ali antes de ir para a tabela) arquivado continuamente, dá para reconstruir o banco exatamente como estava um segundo antes de alguém rodar um DELETE sem WHERE — não só "como estava no backup de ontem à noite".With pg_basebackup (a snapshot of the files) plus the continuously archived WAL (write-ahead log: every write is recorded there before it reaches the table), you can rebuild the database exactly as it was one second before someone ran a DELETE with no WHERE — not just "as it was in last night's backup".

A regra 3-2-1The 3-2-1 rule

Cache: o 80/20 para quem programaCaching: the 80/20 for developers

Cache é "guardar uma resposta pronta para não refazer o trabalho". É provavelmente a técnica isolada que mais acelera sistemas reais — e também a que mais causa bugs sutis, porque introduz uma segunda fonte de verdade que pode ficar desatualizada.A cache is "keeping a ready-made answer so you do not redo the work". It is probably the single technique that speeds up real systems the most — and also the one that causes the most subtle bugs, because it introduces a second source of truth that can go stale.

Onde o Postgres já faz cache por conta própriaWhere Postgres already caches on its own

O 80/20 de cache de aplicaçãoThe 80/20 of application caching

Onde colocar o cacheWhere to put the cache

O problema N+1: o "cache que você nem precisava"The N+1 problem: the "cache you did not even need"

-- N+1: 1 consulta para os pedidos + N consultas, uma por pedido, para o cliente
SELECT * FROM pedidos LIMIT 20;                     -- 1
SELECT * FROM clientes WHERE id = 1;                -- repetida 20x por um ORM "ingênuo"

-- resolvido com UM join, sem precisar de cache nenhum
SELECT p.*, c.nome FROM pedidos p JOIN clientes c ON c.id = p.cliente_id LIMIT 20;

Boa parte do "preciso de cache" na prática é, na verdade, uma consulta ruim. Antes de adicionar uma camada de cache, rode EXPLAIN ANALYZE e confira se um índice ou um JOIN já resolvem.A good part of "I need a cache" in practice is actually a bad query. Before adding a cache layer, run EXPLAIN ANALYZE and check whether an index or a JOIN already fixes it.

Laboratório com DockerLab with Docker

Um Postgres local em segundos, para treinar tudo desta nota sem instalar nada no sistema.A local Postgres in seconds, to practise everything in this note without installing anything on your system.

mkdir lab-postgres && cd lab-postgres
cat > docker-compose.yml <<'EOF'
services:
  db:
    image: postgres:16
    environment:
      POSTGRES_USER: ana
      POSTGRES_PASSWORD: s3nha-forte-de-verdade
      POSTGRES_DB: loja
    ports:
      - "127.0.0.1:5432:5432"
    volumes:
      - dados:/var/lib/postgresql/data
volumes:
  dados:
EOF
docker compose up -d
docker compose exec db psql -U ana -d loja -c "SELECT version();"

Resultado esperado: a última linha imprime a versão do Postgres — se aparecer, o container subiu, aceitou a senha definida e criou o database loja.Expected result: the last line prints the Postgres version — if it shows up, the container came up, accepted the configured password, and created the loja database.

psql "postgresql://ana:s3nha-forte-de-verdade@localhost:5432/loja"   # do seu HOST, direto
docker compose exec db pg_dump -U ana loja > backup.sql              # pratique o backup da seção anterior
docker compose down                                                   # para os containers, mantém o volume
docker compose down -v                                                # remove TAMBÉM o volume (apaga os dados)

Para algo mais perto de produção, veja a nota de AWS (RDS) ou suba um Postgres numa VM de verdade e aplique o pg_hba.conf e o TLS desta nota de propósito.For something closer to production, see the AWS note (RDS) or spin up Postgres on a real VM and apply this note's pg_hba.conf and TLS on purpose.

Cola e revisãoCheat sheet and review

ColaCheat sheet

Perguntas de revisãoReview questions