Skip to content

PostgreSQL Folha de referência

Sistema de banco de dados relacional open-source poderoso, com recursos avançados, extensibilidade e conformidade SQL.

01

Primeiros Passos

Conectando com psql

psql é o cliente de linha de comando do PostgreSQL. Meta-comandos começam com barra invertida (\). Use \l para listar bancos, \d para descrever tabelas e \? para ajuda. Strings de conexão (postgresql://) são práticas e suportadas pela maioria dos drivers. Use \i arquivo.sql para rodar um arquivo SQL. Defina a variável de ambiente PGPASSWORD ou o arquivo ~/.pgpass para evitar digitar senhas.

postgresql
# connect to a database
psql -h localhost -p 5432 -U postgres -d mydb
# -h host, -p port, -U user, -d database

# connect via connection string
psql "postgresql://user:pass@localhost:5432/mydb"

# common psql meta-commands
\l              # list databases
\c mydb         # connect to mydb
\dt             # list tables
\d users        # describe table
\dn             # list schemas
\df             # list functions
\du             # list roles/users
\q              # quit
\?              # help on meta-commands
\timing         # toggle query timing

Criando e Descartando Bancos

CREATE DATABASE cria um banco novo; você pode especificar owner, encoding, locale e template. template0 é um template limpo, sem dados específicos de locale; template1 (padrão) pode ser personalizado. DROP DATABASE exige nenhuma conexão ativa — WITH (FORCE) (13+) desconecta-as primeiro. Você não pode descartar o banco ao qual está conectado. ALTER DATABASE RENAME atualiza o nome, mas não o diretório no disco.

postgresql
# create a database
CREATE DATABASE mydb
  WITH OWNER alice
  ENCODING 'UTF8'
  LC_COLLATE 'en_US.UTF-8'
  LC_CTYPE 'en_US.UTF-8'
  TEMPLATE template0
  CONNECTION LIMIT 100;

# create from a template (clone)
CREATE DATABASE mydb_copy TEMPLATE mydb;

# drop a database (must disconnect first)
DROP DATABASE IF EXISTS mydb;

# drop with force (PostgreSQL 13+)
DROP DATABASE IF EXISTS mydb WITH (FORCE);

# rename a database
ALTER DATABASE mydb RENAME TO newdb;

Schemas e Search Path

Schemas são namespaces para objetos do banco — como pastas para tabelas. O schema padrão é 'public'. search_path determina quais schemas são procurados ao referenciar nomes não qualificados. Use schemas para organizar apps multi-inquilino, separar preocupações ou gerenciar versões. CASCADE descarta objetos dependentes; RESTRICT (padrão) recusa se existirem objetos. Schemas são mais flexíveis que bancos para multi-inquilinato numa única conexão.

postgresql
# create a schema
CREATE SCHEMA IF NOT EXISTS app_schema AUTHORIZATION alice;

# set the search path (schema lookup order)
SET search_path TO app_schema, public;
SHOW search_path;

# create a table in a specific schema
CREATE TABLE app_schema.users (id serial PRIMARY KEY);

# move a table between schemas
ALTER TABLE public.users SET SCHEMA app_schema;

# list objects in a schema
SELECT * FROM information_schema.tables
WHERE table_schema = 'app_schema';

# drop a schema (and its objects)
DROP SCHEMA IF EXISTS app_schema CASCADE;

Configuração e Configurações

As configurações do PostgreSQL vivem em postgresql.conf. SHOW lê o valor atual; SET o muda para a sessão; SET LOCAL só para a transação atual; ALTER DATABASE/ROLE define padrões que persistem. Algumas configurações (shared_buffers, max_connections) exigem reinício do servidor; outras (work_mem, statement_timeout) se aplicam imediatamente. Use pg_reload_conf() para aplicar mudanças do arquivo de config sem reinício. Monitore log_min_duration_statement para consultas lentas.

postgresql
# view a setting
SHOW shared_buffers;
SHOW max_connections;

# set a parameter for the session
SET work_mem = '64MB';
SET statement_timeout = '30s';

# set a parameter for a transaction
BEGIN;
SET LOCAL work_mem = '256MB';
-- heavy sort here
COMMIT;  -- reverts to previous value

# set at database or role level (persists)
ALTER DATABASE mydb SET log_min_duration_statement = 100;
ALTER ROLE alice SET search_path TO app_schema, public;

# reload config without restart
SELECT pg_reload_conf();

# view config file location
SHOW config_file;

Scripting e Variáveis do psql

psql suporta scripting com variáveis (\set), inclusão de arquivos (\i), redirecionamento de saída (\o) e execução de shell (\!). Variáveis são substituídas com :nomevar. Use \set para loops e condicionais de script. A flag -f roda um arquivo e sai, útil para trabalhos em lote e migrações. Para scripting complexo, considere funções PL/pgSQL ou ferramentas externas como sqitch ou Flyway para gestão de migrações.

postgresql
# run a SQL file
psql -d mydb -f script.sql
psql -d mydb < script.sql

# inside psql
\i /path/to/script.sql

# use psql variables
\set table_name 'users'
SELECT * FROM :table_name;

# prompt for input
\echo -n 'Enter user ID: ' \set user_id `read x && echo $x`
SELECT * FROM users WHERE id = :user_id;

# output to file
\o results.txt
SELECT * FROM users;
\o

# execute shell command
\! date
02

Tabelas e Restrições

Criando Tabelas

serial cria automaticamente uma sequência para inteiros auto-incrementáveis; use GENERATED ALWAYS AS IDENTITY (padrão SQL, preferido desde PG10) em vez disso, para tabelas novas. Restrições: PRIMARY KEY (única + não nula), UNIQUE, NOT NULL, CHECK, REFERENCES (chave estrangeira). ON DELETE CASCADE deleta linhas dependentes quando o pai é deletado; SET NULL define a FK como NULL. Tabelas TEMP desaparecem após a sessão — ótimas para resultados intermediários.

postgresql
CREATE TABLE users (
  id          serial PRIMARY KEY,
  username    varchar(50) UNIQUE NOT NULL,
  email       varchar(255) UNIQUE NOT NULL,
  password    text NOT NULL,
  age         integer CHECK (age >= 0 AND age <= 150),
  role        varchar(20) NOT NULL DEFAULT 'user',
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now()
);

# create with a foreign key
CREATE TABLE posts (
  id          serial PRIMARY KEY,
  user_id     integer NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title       text NOT NULL,
  body        text,
  published   boolean DEFAULT false
);

# create a temporary table (session-scoped)
CREATE TEMP TABLE temp_stats AS
  SELECT user_id, count(*) FROM posts GROUP BY user_id;

Colunas Identity (PG10+)

GENERATED ALWAYS AS IDENTITY é a substituição padrão SQL para serial — vincula a sequência à coluna, então descartar a coluna descarta a sequência (serial deixa sequências órfãs). ALWAYS impede inserções manuais do ID (use OVERRIDING SYSTEM VALUE para forçar). BY DEFAULT comporta-se como serial (permite inserção manual). Use colunas identity para tabelas novas; serial serve para as existentes. Ambas usam sequências por baixo.

postgresql
# preferred over serial (SQL standard)
CREATE TABLE products (
  id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name  text NOT NULL
);

# allow manual override (like serial behavior)
CREATE TABLE products (
  id    integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name  text NOT NULL
);

# restart a sequence
ALTER TABLE products ALTER COLUMN id RESTART WITH 1000;

# view identity info
SELECT * FROM pg_sequences WHERE sequencename = 'products_id_seq';

Alterando Tabelas

ALTER TABLE é como você evolui o schema sem recriar tabelas. Adicionar uma coluna com um padrão (não volátil) é rápido desde PG11 (sem reescrita completa da tabela). Mudar o tipo de coluna pode exigir reescrita completa e cláusula USING para conversão. CASCADE descarta objetos dependentes (views, chaves estrangeiras) — use com cautela. Adicione restrições com nomes, para gestão futura mais fácil. Use IF EXISTS/IF NOT EXISTS para tornar scripts idempotentes.

postgresql
# add a column
ALTER TABLE users ADD COLUMN bio text;
ALTER TABLE users ADD COLUMN score numeric DEFAULT 0;

# drop a column
ALTER TABLE users DROP COLUMN IF EXISTS bio;
ALTER TABLE users DROP COLUMN bio CASCADE;  -- drop dependent objects

# rename a column
ALTER TABLE users RENAME COLUMN username TO handle;

# change column type
ALTER TABLE users ALTER COLUMN age TYPE smallint USING age::smallint;

# set/drop default
ALTER TABLE users ALTER COLUMN role SET DEFAULT 'member';
ALTER TABLE users ALTER COLUMN role DROP DEFAULT;

# add constraint
ALTER TABLE users ADD CONSTRAINT email_check
  CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$');

Restrições

Restrições impõem integridade de dados no nível do banco — sempre prefira-as a checagens da aplicação. Chaves compostas servem para tabelas de junção. ON UPDATE CASCADE propaga mudanças de PK às FKs; ON DELETE SET NULL anula a FK (a coluna FK deve ser anulável). Restrições DEFERRABLE verificam na hora do commit (não por instrução), úteis para referências circulares. Nomeie suas restrições para gestão fácil — nomes gerados automaticamente são feios e variam entre bancos.

postgresql
# named constraint for easy management
ALTER TABLE users
  ADD CONSTRAINT positive_age CHECK (age >= 0);

# composite primary key
CREATE TABLE enrollments (
  student_id integer REFERENCES students(id),
  course_id  integer REFERENCES courses(id),
  PRIMARY KEY (student_id, course_id)
);

# unique constraint on multiple columns
ALTER TABLE users ADD CONSTRAINT unique_email_domain
  UNIQUE (email, domain);

# foreign key with actions
ALTER TABLE posts
  ADD FOREIGN KEY (user_id) REFERENCES users(id)
  ON UPDATE CASCADE ON DELETE SET NULL;

# drop a constraint
ALTER TABLE users DROP CONSTRAINT positive_age;

# defer a constraint until commit
SET CONSTRAINTS unique_email_domain DEFERRED;

Herança de Tabelas

A herança de tabelas do PostgreSQL é única — tabelas filhas herdam colunas dos pais. Consultar o pai retorna linhas de cada um dos filhos, a menos que você use ONLY. Útil para particionamento de tabelas (pré-particionamento declarativo) e modelagem de taxonomias. Porém, a herança tem peculiaridades: chaves estrangeiras não se propagam, e restrições UNIQUE se aplicam só por tabela. Para necessidades novas de particionamento, use particionamento declarativo (PARTITION BY) — mais robusto e melhor suportado.

postgresql
# PostgreSQL supports table inheritance
CREATE TABLE people (
  id   serial PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE employees (
  salary numeric,
  dept   text
) INHERITS (people);

CREATE TABLE customers (
  credit_limit numeric
) INHERITS (people);

# query parent sees all children
SELECT * FROM people;          -- includes employees and customers
SELECT * FROM ONLY people;     -- only people, not children

# insert into a child
INSERT INTO employees (name, salary, dept)
  VALUES ('Alice', 50000, 'Engineering');
03

Tipos de Dados

Tipos Numéricos

Use tipos inteiros dimensionados aos dados — smallint para faixas pequenas, bigint para grandes. numeric/decimal são exatos (essencial para dinheiro), mas mais lentos que real/double. Nunca use float para dinheiro — erros de arredondamento se acumulam. serial é legado; prefira GENERATED AS IDENTITY. O tipo money é consciente de locale e inflexível — use numeric(10,2). Para auto-incremento com lacuna, sequências podem pular números em rollback/crash (por design, pelo desempenho).

postgresql
# integer types (use the smallest that fits)
smallint    -- 2 bytes, -32768 to 32767
integer     -- 4 bytes, -2B to 2B
bigint      -- 8 bytes, very large

# auto-incrementing (use identity, not serial)
id integer GENERATED ALWAYS AS IDENTITY

# decimal/numeric (exact)
numeric(precision, scale)   -- e.g. numeric(10,2) for currency
decimal                     -- alias for numeric

# floating point (inexact, faster)
real        -- 4 bytes, 6 decimal digits
double precision  -- 8 bytes, 15 decimal digits

# special
serial / bigserial   -- legacy auto-increment (prefer identity)
money                -- currency (prefer numeric)

# examples
CREATE TABLE products (
  price numeric(10,2),        -- $99999999.99
  weight real,                -- 1.5 (kg)
  quantity integer DEFAULT 0
);

Tipos de Texto e Caractere

No PostgreSQL, varchar, char e text têm desempenho idêntico — text é preferido para ilimitado, varchar(n) quando precisa de restrição de comprimento. char(n) preenche com espaços e raramente é útil. Mudar o comprimento de varchar é operação só de metadados (rápida). LIKE usa curingas % e _; ILIKE não diferencia caixa; ~ é regex POSIX. Para busca de texto completo, use tsvector/tsquery (coberto depois). Sempre guarde texto como text/varchar, não como bytea.

postgresql
# three text types (all variable-length)
varchar(n)   -- varchar with max length
char(n)      -- fixed-length, padded with spaces
text         -- unlimited length (preferred)

# in practice, all three perform the same
# varchar(n) adds a length check; char(n) pads (rarely useful)

CREATE TABLE articles (
  title    varchar(200) NOT NULL,
  slug     varchar(100) UNIQUE,
  body     text,
  summary  text DEFAULT ''
);

# string functions
SELECT length(body), char_length(title), octet_length(title)
FROM articles;

# change length limit (fast, no rewrite)
ALTER TABLE articles ALTER COLUMN title TYPE varchar(500);

# pattern matching
SELECT * FROM articles WHERE title LIKE '%postgres%';
SELECT * FROM articles WHERE title ILIKE '%postgres%'; -- case-insensitive
SELECT * FROM articles WHERE slug ~ '^[a-z0-9-]+$';   -- regex

Tipos de Data e Hora

Sempre use timestamptz (não timestamp) para timestamps — guarda em UTC e converte para o fuso da sessão na exibição. timestamp (sem tz) é ingênuo e causa bugs em apps multi-fuso. interval é poderoso para aritmética ('1 day', '2 hours', '3 months'). date_trunc arredonda para baixo até uma unidade. Strings de formato to_char/to_timestamp são flexíveis. Defina o fuso por sessão ou papel — os dados são guardados em UTC de qualquer forma.

postgresql
# date/time types
date              -- date only (4 bytes)
time              -- time only (8 bytes)
timetz            -- time with time zone (12 bytes)
timestamp         -- timestamp without tz (8 bytes)
timestamptz       -- timestamp WITH time zone (8 bytes, preferred)
interval          -- time span (16 bytes)

# always use timestamptz for timestamps
CREATE TABLE events (
  id         serial PRIMARY KEY,
  created_at timestamptz DEFAULT now(),
  scheduled  timestamptz,
  duration   interval
);

# common operations
SELECT now();                               -- current timestamp
SELECT current_date;                         -- today
SELECT created_at + interval '1 hour';       -- add time
SELECT age('2025-01-01', '2024-01-01');       -- interval between
SELECT date_trunc('month', created_at);       -- truncate to month
SELECT to_char(created_at, 'YYYY-MM-DD HH:MI:SS');
SELECT to_timestamp('2025/01/15', 'YYYY/MM/DD');
SELECT EXTRACT(YEAR FROM created_at);

# timezone handling
SET timezone = 'UTC';
SET timezone = 'Asia/Shanghai';

JSON e JSONB

jsonb é JSON binário — mais rápido de consultar e indexável, mas um pouco mais lento de inserir e não preserva a ordem das chaves. Use jsonb para cada trabalho novo. -> retorna jsonb, ->> retorna texto. @> (contenção) é o operador JSON mais poderoso e funciona muito bem com índices GIN. jsonb_set modifica um caminho, || mescla objetos, - remove chaves. Para consultas frequentes num campo JSON, adicione um índice GIN ou um índice de expressão no valor extraído. JSONB é ideal para schemas flexíveis dentro de um banco relacional.

postgresql
# json stores text; jsonb stores binary (faster, preferred)
CREATE TABLE products (
  id    serial PRIMARY KEY,
  data  jsonb
);

# insert JSON
INSERT INTO products (data) VALUES
  ('{"name": "Widget", "price": 19.99, "tags": ["sale", "new"]}');

# query JSON fields
SELECT data->'name'        FROM products;  -- returns jsonb
SELECT data->>'name'       FROM products;  -- returns text
SELECT data->'tags'->0     FROM products;  -- first tag (jsonb)

# filter by JSON
SELECT * FROM products WHERE data->>'name' = 'Widget';
SELECT * FROM products WHERE data @> '{"tags": ["sale"]}';  -- containment

# modify JSON
UPDATE products SET data = jsonb_set(data, '{price}', '29.99');
UPDATE products SET data = data || '{"sale": true}';        -- merge
UPDATE products SET data = data - 'sale';                    -- remove key

# index JSON (GIN index)
CREATE INDEX idx_products_data ON products USING GIN (data);
CREATE INDEX idx_products_name ON products ((data->>'name'));

Arrays e Tipos Personalizados

Arrays do PostgreSQL são indexados a partir de 1 (diferente da maioria das linguagens). ANY e @> são os operadores principais de consulta; unnest() expande arrays em linhas (ótimo para joins). Arrays são poderosos, mas use-os com juízo — se você consulta/filtra elementos de array com frequência, uma tabela normalizada pode ser melhor. Enums são ordenados e validados na inserção. Tipos compostos permitem colunas estruturadas. Para dados aninhados complexos, considere JSONB em vez de arrays de compostos.

postgresql
# array columns
CREATE TABLE teams (
  id      serial PRIMARY KEY,
  name    text,
  members text[]           -- array of text
);

INSERT INTO teams (name, members) VALUES
  ('Engineering', ARRAY['Alice', 'Bob', 'Carol']),
  ('Sales', '{"Dave", "Eve"}');

# query arrays
SELECT * FROM teams WHERE 'Alice' = ANY(members);
SELECT * FROM teams WHERE members @> ARRAY['Alice'];  -- contains
SELECT members[1] FROM teams;            -- 1-indexed!
SELECT array_length(members, 1) FROM teams;
SELECT unnest(members) FROM teams WHERE id = 1;  -- expand to rows

# custom enum type
CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral');
CREATE TABLE persons (id serial PRIMARY KEY, current_mood mood);
INSERT INTO persons (current_mood) VALUES ('happy');

# composite type
CREATE TYPE address AS (street text, city text, zip text);
CREATE TABLE contacts (id serial, addr address);
04

Operações CRUD

INSERT

RETURNING é um recurso do PostgreSQL que retorna linhas inseridas/atualizadas/deletadas — elimina a necessidade de um SELECT separado após inserir. ON CONFLICT (UPSERT) é poderoso: DO UPDATE usa EXCLUDED para referenciar a linha proposta, DO NOTHING pula silenciosamente. Inserção de múltiplas linhas é muito mais rápida que inserções individuais. INSERT ... SELECT copia dados entre tabelas. Para cargas em massa, use COPY (ainda mais rápido) — contorna o custo de parsing do SQL.

postgresql
# basic insert
INSERT INTO users (username, email, age)
VALUES ('alice', '[email protected]', 30);

# multi-row insert
INSERT INTO users (username, email) VALUES
  ('bob', '[email protected]'),
  ('carol', '[email protected]'),
  ('dave', '[email protected]');

# insert with RETURNING (get auto-generated values)
INSERT INTO users (username, email)
VALUES ('eve', '[email protected]')
RETURNING id, username, created_at;

# insert from a query (INSERT ... SELECT)
INSERT INTO archive_users (username, email)
SELECT username, email FROM users WHERE active = false;

# upsert (insert or update on conflict)
INSERT INTO users (username, email) VALUES ('alice', '[email protected]')
ON CONFLICT (username)
DO UPDATE SET email = EXCLUDED.email
RETURNING id;

# do nothing on conflict
INSERT INTO users (username, email) VALUES ('alice', '[email protected]')
ON CONFLICT (username) DO NOTHING;

UPDATE

Sempre inclua WHERE, a menos que pretenda atualizar todas as linhas. UPDATE ... FROM permite joins em atualizações (não padrão, mas poderoso). RETURNING mostra o que mudou — essencial para auditoria. Expressões CASE habilitam atualizações condicionais numa única instrução. Atualizações que não mudam valores são no-ops (sem WAL, sem disparo de trigger no nível de instrução). Atualizações grandes podem inflar a tabela — agrupe-as e faça VACUUM depois.

postgresql
# basic update
UPDATE users SET email = '[email protected]' WHERE id = 1;

# update multiple columns
UPDATE users
SET email = '[email protected]', age = 31, updated_at = now()
WHERE id = 1;

# update with RETURNING
UPDATE users SET role = 'admin' WHERE id = 1
RETURNING id, username, role;

# update from another table
UPDATE posts p
SET title = p.title || ' (archived)'
FROM users u
WHERE p.user_id = u.id AND u.role = 'admin';

# conditional update with CASE
UPDATE products
SET price = CASE
  WHEN price < 10 THEN price * 1.2
  WHEN price < 50 THEN price * 1.1
  ELSE price
END;

# update all rows (be careful!)
UPDATE users SET status = 'active';

DELETE e TRUNCATE

DELETE remove linhas uma a uma (dispara triggers, visível em transações). TRUNCATE remove todas as linhas instantaneamente (DDL, não transacional da mesma forma, reseta sequências com RESTART IDENTITY). Use TRUNCATE para limpar tabelas inteiras — é ordens de magnitude mais rápido. CASCADE trunca tabelas com chaves estrangeiras que referenciam esta. DELETE com RETURNING é útil para logs de auditoria. Sempre use WHERE com DELETE — sem ele, você deleta tudo (seguro dentro de uma transação, catastrófico se confirmado).

postgresql
# basic delete
DELETE FROM users WHERE id = 1;

# delete with RETURNING
DELETE FROM users WHERE active = false
RETURNING id, username;

# delete based on a join
DELETE FROM posts
WHERE user_id IN (
  SELECT id FROM users WHERE role = 'deleted'
);

# delete using USING (join syntax)
DELETE FROM posts p
USING users u
WHERE p.user_id = u.id AND u.status = 'deleted';

# truncate (fast, resets sequences, DDL-like)
TRUNCATE TABLE posts;
TRUNCATE TABLE posts, comments;          -- multiple tables
TRUNCATE TABLE posts RESTART IDENTITY;   -- reset sequences
TRUNCATE TABLE posts CASCADE;            -- also truncates FK refs

# delete all rows (slower than TRUNCATE, but transactional)
DELETE FROM posts;

Básico de SELECT

DISTINCT ON é uma extensão do PostgreSQL que retorna a primeira linha por grupo — poderoso para consultas 'o mais recente por categoria' (precisa ORDER BY a coluna do DISTINCT ON primeiro). LIMIT/OFFSET é paginação simples, mas lenta para offsets grandes — use paginação por keyset (WHERE id > ultimo_id) para melhor desempenho. Sempre especifique colunas explicitamente em produção (não SELECT *) para evitar surpresas quando o schema muda. Use FETCH FIRST n ROWS ONLY para sintaxe padrão SQL.

postgresql
# basic select
SELECT id, username, email FROM users;

# filtering
SELECT * FROM users WHERE age >= 18 AND role = 'user';
SELECT * FROM users WHERE role IN ('admin', 'moderator');
SELECT * FROM users WHERE age BETWEEN 18 AND 65;
SELECT * FROM users WHERE email IS NOT NULL;
SELECT * FROM users WHERE username LIKE 'al%';

# sorting and limiting
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
SELECT * FROM users ORDER BY age DESC, username ASC
OFFSET 20 LIMIT 10;  -- pagination (or use FETCH)

# distinct
SELECT DISTINCT role FROM users;
SELECT DISTINCT ON (user_id) * FROM posts
ORDER BY user_id, created_at DESC;  -- latest post per user

# column aliases and expressions
SELECT
  username AS name,
  EXTRACT(YEAR FROM created_at) AS join_year,
  age * 365 AS age_in_days
FROM users;

MERGE (PG15+)

MERGE (padrão SQL, PG15+) é mais poderoso que ON CONFLICT: pode inserir, atualizar E deletar numa única instrução com base em condições de correspondência. Use-o para operações complexas de sincronização (ETL, mesclagens de data warehouse). Para upserts simples, ON CONFLICT é mais conciso e amplamente suportado. MERGE exige uma fonte (tabela ou consulta) e uma condição de correspondência. As cláusulas WHEN MATCHED/NOT MATCHED definem ações. Cada ramo pode ter condições AND opcionais para controle fino.

postgresql
# MERGE: insert, update, or delete in one statement (PG15+)
MERGE INTO products p
USING new_prices n
ON p.sku = n.sku
WHEN MATCHED AND p.price != n.price THEN
  UPDATE SET price = n.price, updated_at = now()
WHEN MATCHED AND n.discontinued = true THEN
  DELETE
WHEN NOT MATCHED THEN
  INSERT (sku, name, price) VALUES (n.sku, n.name, n.price);

# simpler upsert (still works, often preferred)
INSERT INTO products (sku, name, price) VALUES ('A1', 'Widget', 9.99)
ON CONFLICT (sku)
DO UPDATE SET price = EXCLUDED.price;

# MERGE with a source query
MERGE INTO inventory i
USING (SELECT product_id, sum(qty) AS total FROM orders GROUP BY product_id) o
ON i.product_id = o.product_id
WHEN MATCHED THEN UPDATE SET quantity = i.quantity - o.total;
05

Consultas e Filtragem

WHERE e Condições

ILIKE é o LIKE sem diferenciar caixa (extensão do PostgreSQL). ~ é regex POSIX (sensível a caixa), ~* sem diferenciar caixa, !~ negação. IS DISTINCT FROM trata NULL como valor comparável (NULL = NULL é NULL, não verdadeiro). ANY/ALL funcionam com arrays e subconsultas. Para casamento de texto complexo, considere busca de texto completo (tsvector) ou índices de trigramas (pg_trgm) pelo desempenho. Sempre indexe colunas usadas em cláusulas WHERE em tabelas grandes.

postgresql
# comparison operators
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price <> 0;        -- not equal
SELECT * FROM products WHERE name IS DISTINCT FROM 'Test';

# logical operators
SELECT * FROM products
WHERE (price > 50 AND category = 'electronics')
   OR (price < 10 AND category = 'books');

# IN, BETWEEN, LIKE/ILIKE
SELECT * FROM products WHERE category IN ('books', 'toys');
SELECT * FROM products WHERE price BETWEEN 10 AND 50;
SELECT * FROM products WHERE name LIKE '_o%';     -- second char 'o'
SELECT * FROM products WHERE name ILIKE '%widget%';

# IS NULL / IS NOT NULL
SELECT * FROM products WHERE description IS NULL;
SELECT * FROM products WHERE description IS NOT DISTINCT FROM NULL;

# ANY/ALL with arrays
SELECT * FROM products WHERE id = ANY(ARRAY[1,2,3]);
SELECT * FROM products WHERE price > ALL(ARRAY[10, 20, 30]);

# regex matching
SELECT * FROM products WHERE name ~ '^[A-C]';       -- starts with A-C
SELECT * FROM products WHERE name ~* 'widget';       -- case-insensitive
SELECT * FROM products WHERE name !~ 'test';         -- does not match

ORDER BY e Paginação

Paginação por keyset (WHERE id > ultimo_id) é dramaticamente mais rápida que OFFSET para datasets grandes — OFFSET varre e descarta linhas. ORDER BY random() é lento em tabelas grandes; use TABLESAMPLE para amostragem estatística. NULLS FIRST/LAST controla onde os NULLs se ordenam (o padrão varia por ASC/DESC). Sempre inclua um ORDER BY ao paginar — sem ele, a ordem das linhas é indefinida. Para paginação estável, ordene por uma coluna única.

postgresql
# basic sorting
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY last_name ASC, first_name ASC;

# sort by expression
SELECT * FROM products ORDER BY (price * 1.1) DESC;
SELECT * FROM events ORDER BY created_at::date;

# NULLS handling
SELECT * FROM users ORDER BY last_login DESC NULLS LAST;
SELECT * FROM users ORDER BY last_login DESC NULLS FIRST;

# LIMIT/OFFSET pagination (simple but slow for large offsets)
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 0;   -- page 1
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10;  -- page 2

# keyset pagination (faster, use for large datasets)
SELECT * FROM users WHERE id > 100 ORDER BY id LIMIT 10;

# FETCH (SQL standard)
SELECT * FROM users ORDER BY id FETCH FIRST 10 ROWS ONLY;

# random rows
SELECT * FROM products ORDER BY random() LIMIT 5;  -- slow on large tables
SELECT * FROM products TABLESAMPLE BERNOULLI(1);    -- 1% sample

GROUP BY e Agregados

GROUP BY colapsa linhas pelas colunas de agrupamento; agregados (count, sum, avg, min, max) computam por grupo. HAVING filtra grupos (WHERE filtra linhas antes do agrupamento). ROLLUP adiciona subtotais em cada nível; CUBE adiciona todas as combinações; GROUPING SETS permite especificar exatamente quais agrupamentos quer — cada um é poderoso para relatórios. count(*) conta linhas; count(coluna) conta valores não nulos. Use count(DISTINCT col) para contagens únicas.

postgresql
# basic grouping
SELECT category, count(*) FROM products GROUP BY category;
SELECT user_id, sum(amount) FROM orders GROUP BY user_id;

# multiple aggregates
SELECT
  category,
  count(*)           AS total,
  avg(price)         AS avg_price,
  min(price)         AS min_price,
  max(price)         AS max_price,
  sum(price)         AS total_value
FROM products
GROUP BY category;

# HAVING (filter on aggregates, like WHERE for groups)
SELECT user_id, count(*) AS order_count
FROM orders
GROUP BY user_id
HAVING count(*) > 5;

# GROUP BY with multiple columns
SELECT category, status, count(*)
FROM products
GROUP BY category, status
ORDER BY category, status;

# GROUP BY ROLLUP/CUBE (subtotals)
SELECT category, status, count(*)
FROM products
GROUP BY ROLLUP (category, status);  -- adds subtotals and grand total

# GROUP BY GROUPING SETS
SELECT category, status, count(*)
FROM products
GROUP BY GROUPING SETS ((category, status), (category), ());

DISTINCT e Operações de Conjunto

UNION combina resultados e remove duplicatas (lento); UNION ALL mantém duplicatas (rápido, preferido quando você sabe que não há duplicatas ou não se importa). INTERSECT retorna linhas comuns; EXCEPT retorna a diferença. Operações de conjunto exigem tipos e contagens de colunas compatíveis. DISTINCT ON é uma joia do PostgreSQL: retorna a primeira linha por grupo com base no ORDER BY — perfeito para consultas 'o mais recente/mais caro por categoria'. Todas as operações de conjunto podem se encadear, mas use parênteses para controlar a precedência.

postgresql
# distinct rows
SELECT DISTINCT category FROM products;
SELECT DISTINCT category, status FROM products;

# DISTINCT ON (first row per group)
SELECT DISTINCT ON (category) *
FROM products
ORDER BY category, price DESC;  -- most expensive per category

# UNION (combine, remove duplicates)
SELECT 'user' AS type, username FROM users
UNION
SELECT 'admin' AS type, username FROM admins;

# UNION ALL (faster, keeps duplicates)
SELECT username FROM active_users
UNION ALL
SELECT username FROM inactive_users;

# INTERSECT (common rows)
SELECT product_id FROM orders_2024
INTERSECT
SELECT product_id FROM orders_2025;

# EXCEPT (rows in first but not second)
SELECT product_id FROM all_products
EXCEPT
SELECT product_id FROM discontinued_products;

# set operations with ORDER BY (applies to the whole result)
SELECT username FROM users
UNION
SELECT username FROM admins
ORDER BY username;

CASE e Lógica Condicional

Expressões CASE adicionam lógica if/then/else ao SQL. CASE simples casa um valor; CASE buscado avalia condições. A cláusula FILTER (PostgreSQL) é mais limpa que CASE dentro de agregados para contagem condicional. CASE pode aparecer em SELECT, WHERE, ORDER BY e até GROUP BY. Use-o para pivôs, formatação condicional e lógica de negócio que pertence ao banco. Para lógica complexa de múltiplos ramos, considere uma função ou view.

postgresql
# simple CASE
SELECT
  name,
  CASE category
    WHEN 'book' THEN 'Media'
    WHEN 'laptop' THEN 'Electronics'
    ELSE 'Other'
  END AS category_group
FROM products;

# searched CASE (more flexible)
SELECT
  name,
  CASE
    WHEN price < 10 THEN 'Cheap'
    WHEN price < 50 THEN 'Moderate'
    WHEN price < 100 THEN 'Expensive'
    ELSE 'Luxury'
  END AS price_tier
FROM products;

# CASE in aggregate (pivot-like)
SELECT
  count(*) AS total,
  count(*) FILTER (WHERE status = 'active') AS active_count,
  count(*) FILTER (WHERE status = 'inactive') AS inactive_count
FROM users;

# CASE for conditional sorting
SELECT * FROM products
ORDER BY
  CASE WHEN featured THEN 0 ELSE 1 END,
  name;
06

Joins

Joins INNER e OUTER

INNER JOIN retorna só as linhas com correspondência. LEFT JOIN mantém todas as linhas da esquerda (colunas da direita NULL se sem correspondência) — o join mais comum para 'incluir dados relacionados se existirem'. RIGHT JOIN é raramente usado (reescreva como LEFT). FULL OUTER JOIN mantém tudo. O WHERE p.id IS NULL depois de um LEFT JOIN é o padrão 'anti-join' para achar linhas sem correspondência. Sempre qualifique nomes de colunas com apelidos de tabela (u.id, p.user_id) para evitar ambiguidade.

postgresql
# INNER JOIN (only matching rows)
SELECT u.username, p.title
FROM users u
INNER JOIN posts p ON u.id = p.user_id;

# LEFT JOIN (all left rows, matching right or NULL)
SELECT u.username, p.title
FROM users u
LEFT JOIN posts p ON u.id = p.user_id;

# RIGHT JOIN (all right rows, matching left or NULL)
SELECT u.username, p.title
FROM users u
RIGHT JOIN posts p ON u.id = p.user_id;

# FULL OUTER JOIN (all rows from both, NULLs where no match)
SELECT u.username, p.title
FROM users u
FULL OUTER JOIN posts p ON u.id = p.user_id;

# find rows with no match (anti-join pattern)
SELECT u.username
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE p.id IS NULL;  -- users with no posts

Joins Multi-tabela

PostgreSQL pode unir muitas tabelas, mas cada join adiciona custo — garanta que os joins usem índices. Self-joins são úteis para dados hierárquicos (mas considere CTEs recursivos para hierarquias profundas). Ao unir com agregação, LEFT JOIN + count(p.id) (não count(*)) dá 0 para usuários sem postagens. O otimizador escolhe a ordem dos joins, mas escrever consultas claras ajuda. Para consultas complexas multi-tabela, considere criar uma view.

postgresql
# join three tables
SELECT u.username, p.title, c.text AS comment
FROM users u
JOIN posts p ON u.id = p.user_id
JOIN comments c ON p.id = c.post_id;

# join with different conditions
SELECT
  u.username,
  p.title,
  l.name AS liked_by
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN likes l ON p.id = l.post_id AND l.user_id != u.id;

# self-join (e.g., employees and their managers)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

# join with aggregation
SELECT u.username, count(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username
ORDER BY post_count DESC;

CROSS JOIN e LATERAL

CROSS JOIN produz um produto cartesiano (cada linha de A com cada linha de B) — raramente o que você quer, mas útil com generate_series para gerar dados. LATERAL é poderoso: permite que uma subconsulta referencie colunas da consulta externa, como uma função por linha. Ideal para consultas 'top N por grupo' (muito mais limpo que funções de janela para casos simples). LEFT JOIN LATERAL ... ON true inclui linhas sem correspondência.

postgresql
# CROSS JOIN (Cartesian product — every combination)
SELECT u.username, c.name
FROM users u
CROSS JOIN categories c;
-- same as: SELECT ... FROM users, categories;

# useful for generating combinations
SELECT generate_series(1, 5) AS n, c.name
FROM categories c
CROSS JOIN generate_series(1, 5);

# LATERAL (subquery can reference outer query)
SELECT u.username, recent.title
FROM users u
CROSS JOIN LATERAL (
  SELECT title FROM posts
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 3
) recent;

# LATERAL with LEFT JOIN (include users with no posts)
SELECT u.username, recent.title
FROM users u
LEFT JOIN LATERAL (
  SELECT title FROM posts WHERE user_id = u.id
  ORDER BY created_at DESC LIMIT 3
) recent ON true;

Estratégias de Join (EXPLAIN)

PostgreSQL escolhe estratégias de join com base em tamanho de tabela, índices e estatísticas. Nested Loop é O(N*M) — serve para lookups pequenos ou indexados. Hash Join constrói uma tabela hash na entrada menor e depois sonda — excelente para joins grandes não ordenados. Merge Join exige ambas as entradas ordenadas — ótimo quando índices fornecem a ordem. Não force estratégias em produção; deixe o otimizador decidir. Use EXPLAIN (ANALYZE) para ver tempos reais e ajustar índices/estatísticas se preciso.

postgresql
# see how PostgreSQL joins tables
EXPLAIN SELECT * FROM users u JOIN posts p ON u.id = p.user_id;

# join strategies you'll see:
#   Nested Loop:   for each left row, scan right (good for small/indexed)
#   Hash Join:     hash one table, probe with other (good for large unsorted)
#   Merge Join:    both sorted, merge (good for sorted/indexed large tables)

# force a join strategy (for testing, not production)
SET enable_nestloop = off;
SET enable_hashjoin = off;
SET enable_mergejoin = off;

# check actual execution stats
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users u JOIN posts p ON u.id = p.user_id;

# join removal: PostgreSQL can remove unnecessary joins
# (e.g., LEFT JOIN to a table only used for FK validation)
EXPLAIN SELECT u.* FROM users u LEFT JOIN posts p ON u.id = p.user_id;

Dicas de Desempenho de Join

A otimização de join mais impactante é indexar colunas de chave estrangeira — PostgreSQL NÃO cria índices automaticamente para FKs. Mantenha as estatísticas frescas com ANALYZE (auto-vacuum faz isso, mas ANALYZE manual após cargas em massa ajuda). Evite envolver colunas de join em funções (impede o uso de índice). Selecione só as colunas necessárias. Para tabelas enormes, considere particionamento para que o otimizador possa podar partições. Monitore joins lentos com pg_stat_statements.

postgresql
# always index foreign key columns
CREATE INDEX idx_posts_user_id ON posts(user_id);

# index join columns on both sides
CREATE INDEX idx_orders_product_id ON orders(product_id);
CREATE INDEX idx_products_id ON products(id);  -- PK already indexed

# composite index for multi-column joins
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at);

# analyze tables for accurate statistics
ANALYZE users;
ANALYZE posts;

# avoid joining on expressions (prevents index use)
-- BAD:  JOIN ON lower(a.email) = lower(b.email)
-- GOOD: JOIN ON a.email = b.email (normalize case on insert)

# limit columns selected (reduces I/O)
SELECT u.username, p.title  -- not SELECT *
FROM users u JOIN posts p ON u.id = p.user_id;

# partition large tables for join pruning
SELECT * FROM orders o
JOIN products p ON o.product_id = p.id
WHERE o.order_date >= '2025-01-01';  -- prunes partitions
07

Agregações e Funções de Janela

Funções de Agregação

PostgreSQL tem agregados ricos além de count/sum/avg. string_agg e array_agg concatenam valores numa string/array — ótimo para evitar consultas N+1. bool_or/bool_and agregam booleanos. percentile_cont computa percentis exatos (mediana = 0.5) — muito útil para análises. mode() retorna o valor mais frequente. A cláusula FILTER (count(*) FILTER (WHERE...)) é mais limpa que CASE para agregação condicional. ORDER BY dentro de agregados controla a ordem da saída.

postgresql
# standard aggregates
SELECT
  count(*)              AS total_rows,
  count(distinct category) AS unique_categories,
  sum(price)            AS total_value,
  avg(price)            AS avg_price,
  min(price)            AS min_price,
  max(price)            AS max_price,
  stddev(price)         AS std_dev,
  variance(price)       AS variance
FROM products;

# string aggregation
SELECT
  user_id,
  string_agg(tag, ', ' ORDER BY tag) AS tags
FROM post_tags
GROUP BY user_id;

# array aggregation
SELECT
  user_id,
  array_agg(title ORDER BY created_at DESC) AS recent_titles
FROM posts
GROUP BY user_id;

# boolean aggregation
SELECT
  bool_or(published)    AS any_published,
  bool_and(published)   AS all_published
FROM posts;

# statistical aggregates
SELECT
  percentile_cont(0.5) WITHIN GROUP (ORDER BY price) AS median,
  percentile_cont(0.95) WITHIN GROUP (ORDER BY price) AS p95,
  mode() WITHIN GROUP (ORDER BY price) AS most_common
FROM products;

Funções de Janela

Funções de janela computam sobre um conjunto de linhas relacionadas à linha atual — não colapsam linhas como o GROUP BY. PARTITION BY define o grupo da janela; ORDER BY define a ordem dentro. ROW_NUMBER é sempre único; RANK pula números após empates; DENSE_RANK não pula. LAG/LEAD acessam outras linhas — perfeito para análise de séries temporais. Cláusulas de frame (ROWS BETWEEN ...) controlam quais linhas são incluídas na janela. Funções de janela são avaliadas depois de WHERE/GROUP BY/HAVING.

postgresql
# ROW_NUMBER: unique sequential number
SELECT
  title,
  price,
  ROW_NUMBER() OVER (ORDER BY price DESC) AS rank
FROM products;

# RANK and DENSE_RANK (handle ties)
SELECT
  title,
  category,
  price,
  RANK() OVER (PARTITION BY category ORDER BY price DESC) AS cat_rank,
  DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS dense_rank
FROM products;

# LAG and LEAD (compare to previous/next row)
SELECT
  date,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY date) AS prev_day,
  revenue - LAG(revenue, 1) OVER (ORDER BY date) AS daily_change,
  LEAD(revenue, 1) OVER (ORDER BY date) AS next_day
FROM daily_sales;

# running total
SELECT
  date,
  revenue,
  SUM(revenue) OVER (ORDER BY date) AS running_total,
  AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales;

Frames de Janela e NTILE

Frames de janela controlam quais linhas uma função de janela vê. O frame padrão para agregados é RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (soma acumulada). Para LAST_VALUE, você precisa estender o frame até UNBOUNDED FOLLOWING, ou ele retorna a linha atual (o frame padrão termina na atual). NTILE divide linhas em N buckets — útil para quartis/decis. FIRST_VALUE/LAST_VALUE retornam valores das linhas de fronteira do frame. Entender frames é a chave para dominar funções de janela.

postgresql
# frame types
#   ROWS:   exact row count
#   RANGE:  logical range (e.g., same value)
#   GROUPS: groups of peer rows

SELECT
  date,
  revenue,
  # running sum from start to current
  SUM(revenue) OVER (ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative,

  # 7-day moving average
  AVG(revenue) OVER (ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d,

  # sum of all rows (no frame = entire partition)
  SUM(revenue) OVER () AS grand_total
FROM daily_sales;

# NTILE: divide rows into N equal buckets
SELECT
  name,
  price,
  NTILE(4) OVER (ORDER BY price DESC) AS price_quartile
FROM products;

# FIRST_VALUE / LAST_VALUE
SELECT
  name,
  category,
  price,
  FIRST_VALUE(name) OVER (PARTITION BY category ORDER BY price DESC) AS most_expensive,
  LAST_VALUE(name) OVER (
    PARTITION BY category ORDER BY price DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS least_expensive
FROM products;

Cláusula FILTER

A cláusula FILTER é uma extensão do PostgreSQL mais limpa e frequentemente mais rápida que CASE dentro de agregados. Especialmente útil para relatórios tipo pivot, onde você precisa de múltiplos agregados condicionais. A consulta varre a tabela uma vez e computa cada um dos agregados numa única passada — muito mais eficiente que múltiplas subconsultas. FILTER faz parte do padrão SQL (diferente de muitas extensões PG) e é suportada por outros bancos também.

postgresql
# FILTER (PostgreSQL extension, cleaner than CASE)
SELECT
  count(*) AS total,
  count(*) FILTER (WHERE status = 'active') AS active,
  count(*) FILTER (WHERE status = 'inactive') AS inactive,
  count(*) FILTER (WHERE status = 'banned') AS banned,
  sum(amount) FILTER (WHERE status = 'active') AS active_revenue
FROM orders
GROUP BY user_id;

# equivalent with CASE (more verbose)
SELECT
  count(*) AS total,
  count(CASE WHEN status = 'active' THEN 1 END) AS active,
  sum(CASE WHEN status = 'active' THEN amount ELSE 0 END) AS active_revenue
FROM orders
GROUP BY user_id;

# FILTER with multiple aggregates in one pass (efficient)
SELECT
  category,
  count(*) FILTER (WHERE price > 100) AS expensive_count,
  avg(price) FILTER (WHERE price > 100) AS expensive_avg,
  count(*) FILTER (WHERE price <= 100) AS cheap_count,
  avg(price) FILTER (WHERE price <= 100) AS cheap_avg
FROM products
GROUP BY category;

Expressões de Tabela Comuns (CTE)

CTEs (cláusula WITH) melhoram a legibilidade da consulta e permitem reuso de subconsultas. PostgreSQL 12+ embute CTEs não recursivos por padrão (então o desempenho iguala subconsultas); antes da 12, CTEs eram cercas de otimização. CTEs RECURSIVE são poderosos para dados hierárquicos/de árvore — a consulta âncora semeia, a parte recursiva cresce. CTEs com RETURNING habilitam movimentação complexa de dados (deletar de uma tabela, arquivar em outra) numa única instrução. Use CTEs para quebrar consultas complexas em passos legíveis.

postgresql
# basic CTE (WITH clause)
WITH active_users AS (
  SELECT id, username FROM users WHERE active = true
)
SELECT au.username, count(p.id) AS post_count
FROM active_users au
LEFT JOIN posts p ON au.id = p.user_id
GROUP BY au.username;

# multiple CTEs (can reference each other)
WITH user_stats AS (
  SELECT user_id, count(*) AS post_count FROM posts GROUP BY user_id
),
top_users AS (
  SELECT user_id FROM user_stats WHERE post_count > 10
)
SELECT u.username, us.post_count
FROM users u
JOIN top_users tu ON u.id = tu.user_id
JOIN user_stats us ON u.id = us.user_id;

# recursive CTE (tree traversal)
WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT id, name, depth FROM org_tree ORDER BY depth;

# CTE for DML (data modification)
WITH deleted AS (
  DELETE FROM users WHERE active = false RETURNING id
)
INSERT INTO archived_users (user_id)
SELECT id FROM deleted;
08

Índices

Índices B-Tree

B-tree é o tipo de índice padrão e mais comum — suporta igualdade, intervalo e ordenação. Índices compostos são procurados da esquerda para a direita, então ponha colunas de igualdade primeiro, depois colunas de intervalo/ordenação. Índices parciais (cláusula WHERE) economizam espaço e são mais rápidos — use-os para consultas filtradas comuns (ex.: WHERE active = true). Índices de expressão habilitam indexação de resultados de funções. CONCURRENTLY constrói sem bloquear escritas (mais lento, mas seguro para produção). Sempre indexe chaves estrangeiras!

postgresql
# create a B-tree index (default)
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_created ON posts(created_at DESC);

# unique index (enforces uniqueness)
CREATE UNIQUE INDEX idx_users_username ON users(username);

# composite index (order matters!)
CREATE INDEX idx_posts_user_created
  ON posts(user_id, created_at DESC);

# partial index (only rows matching the condition)
CREATE INDEX idx_active_users ON users(last_login)
  WHERE active = true;

# expression index (index on a function)
CREATE INDEX idx_users_lower_email ON users(lower(email));
# query must use the same expression:
#   SELECT * FROM users WHERE lower(email) = '[email protected]'

# check index usage
SELECT * FROM pg_stat_user_indexes WHERE relname = 'users';

# drop an index
DROP INDEX IF EXISTS idx_users_email;
DROP INDEX CONCURRENTLY idx_users_email;  -- no lock, slower

Índices GIN e GiST

Índices GIN são ideais quando cada linha tem múltiplos valores indexados (chaves JSONB, elementos de array, tokens de texto). Suportam os operadores @> (contém), ? (chave existe) e o de texto completo @@. Índices GIN são maiores, mas tratam essas consultas com eficiência. GiST é para dados geométricos (pontos, polígonos) e tipos de intervalo — suporta sobreposição (&&), contenção (@>), contido (<@). Tanto GIN quanto GiST são mais lentos de construir/atualizar que B-tree, mas habilitam consultas que o B-tree não consegue. Use GIN para JSONB e texto completo; GiST para consultas espaciais e de intervalo.

postgresql
# GIN (Generalized Inverted Index) — for multi-value columns
# great for JSONB, arrays, full-text search

CREATE INDEX idx_products_data ON products USING GIN (data);        -- JSONB
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);              -- array
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector); -- full-text

# query patterns that use GIN
SELECT * FROM products WHERE data @> '{"tags": ["sale"]}';
SELECT * FROM posts WHERE tags && ARRAY['python'];
SELECT * FROM articles WHERE search_vector @@ to_tsquery('postgres & index');

# GiST (Generalized Search Tree) — for geometric/range data
CREATE INDEX idx_locations ON places USING GIST (location);  -- point/polygon
CREATE INDEX idx_events ON events USING GIST (during);       -- tsrange

# geometric queries
SELECT * FROM places
WHERE location <@ box '(0,0),(10,10)';

# range queries
SELECT * FROM events
WHERE during && tsrange('2025-01-01', '2025-02-01');

BRIN e Outros Tipos de Índice

BRIN (Block Range INdex) é extremamente compacto (KB, não GB) — guarda min/max por intervalo de blocos. É eficaz quando a ordem física das linhas casa com a ordem da consulta (ex.: logs de série temporal em que dados novos se anexam). BRIN dá filtragem 'boa o bastante' que elimina a maioria dos blocos; PostgreSQL então confere as linhas restantes. Para tabelas enormes append-only, BRIN muda o jogo. Índices hash só suportam igualdade (não intervalos), mas são rápidos para lookups pontuais. SP-GiST serve a dados desequilibrados como tabelas de roteamento IP.

postgresql
# BRIN (Block Range Index) — tiny, for naturally ordered data
# best for huge tables where data is physically ordered (e.g., time-series)

CREATE INDEX idx_logs_timestamp ON logs USING BRIN (timestamp);
# index is kilobytes, not gigabytes!

# BRIN works by storing min/max for each block range
# effective when physical order matches logical order (e.g., append-only logs)

# compare sizes:
#   B-tree on 1B rows: ~20GB
#   BRIN on 1B rows:   ~2MB (10000x smaller)

# SP-GiST (space-partitioned GiST) — for non-balanced data
CREATE INDEX idx_prefix ON routes USING SPGiST (prefix);  -- e.g., IP routing

# Hash index (equality only, crash-safe since PG10)
CREATE INDEX idx_users_session ON users USING HASH (session_token);
# only supports = operator, not ranges or sorting

# check what index types are available
SELECT amname FROM pg_am WHERE amtype = 'i';

Manutenção e Análise de Índices

Monitore o uso de índices com pg_stat_user_indexes — índices não usados desperdiçam espaço e retardam escritas. Remova duplicatas (mesmas colunas, mesma tabela). REINDEX reconstrói índices inchados (acontece com updates/deletes pesados); CONCURRENTLY (12+) evita travamento. Inchaço de índice é normal — autovacuum mantém os índices, mas às vezes é preciso REINDEX manual. Para índices muito grandes, considere REINDEX CONCURRENTLY ou pg_repack (ferramenta de terceiros que reconstrói sem travas). Sempre teste mudanças de índice em staging.

postgresql
# find unused indexes (candidates for removal)
SELECT
  schemaname, relname, indexrelname,
  idx_scan AS scans,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

# find duplicate indexes
SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) AS size,
       (array_agg(idx::text))[1] AS indexes
FROM (
  SELECT indexrelid::regclass AS idx, indrelid::regclass AS rel
  FROM pg_index
  GROUP BY indrelid, indkey
  HAVING count(*) > 1
) sub;

# REINDEX (rebuild a bloated index)
REINDEX INDEX idx_users_email;
REINDEX TABLE CONCURRENTLY users;  -- PG12+, no lock

# check index bloat
SELECT
  relname, pg_size_pretty(pg_relation_size(relid)) AS size,
  pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_relation_size(relid) DESC;

Estratégia de Indexação

Comece com PK (auto-indexada) e FK (manual). Adicione índices para os caminhos quentes de consulta. Índices compostos seguem a regra do prefixo mais à esquerda — ordene colunas por seletividade e padrões de consulta. INCLUDE (índice cobertor) permite que consultas sejam satisfeitas só pelo índice (varredura só de índice), sem buscar a linha — enorme para cargas de muita leitura. Não indexe tudo: cada índice adiciona custo de escrita. Revise e apare regularmente usando pg_stat_user_indexes. Sempre EXPLAIN para verificar o uso do índice.

postgresql
# 1. index primary keys (auto-created)
# 2. index foreign keys (NOT auto-created!)
CREATE INDEX idx_posts_user_id ON posts(user_id);

# 3. index columns in WHERE, JOIN, ORDER BY, GROUP BY
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_orders_created ON orders(created_at);

# 4. use composite indexes for multi-column queries
# leftmost prefix rule: (a, b, c) helps WHERE a=?, WHERE a=? AND b=?
# but NOT WHERE b=? alone
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

# 5. partial indexes for common filters
CREATE INDEX idx_active_orders ON orders(created_at)
  WHERE status = 'active';

# 6. covering indexes (INCLUDE for index-only scans)
CREATE INDEX idx_products_cat_price ON products(category, price)
  INCLUDE (name);  -- name available without heap fetch

# 7. don't over-index — every index slows writes
# drop indexes used < 10 times/month (check pg_stat_user_indexes)

# verify the index is used
EXPLAIN SELECT * FROM orders WHERE user_id = 1;
09

Transações e Concorrência

Básico de Transações

Transações agrupam instruções numa unidade atômica — todas têm sucesso ou todas falham. BEGIN inicia, COMMIT salva, ROLLBACK desfaz. Savepoints permitem rollback parcial dentro de uma transação — úteis para recuperação de erro em operações complexas. Por padrão, PostgreSQL auto-confirma cada instrução. Para operações de múltiplas instruções (transferências, inserções multi-linha), sempre use transações explícitas. Mantenha as transações curtas, para reduzir contenção de travas e deadlocks.

postgresql
# explicit transaction
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- or ROLLBACK to undo

# savepoints (partial rollback)
BEGIN;
  INSERT INTO orders (user_id, amount) VALUES (1, 50);
  SAVEPOINT order_inserted;
  INSERT INTO order_items (order_id, product_id) VALUES (1, 100);
  -- oops, wrong product
  ROLLBACK TO order_inserted;
  INSERT INTO order_items (order_id, product_id) VALUES (1, 200);
COMMIT;

# transaction control
BEGIN;
  -- statements
SAVEPOINT my_savepoint;
  -- more statements
ROLLBACK TO my_savepoint;  -- undo back to savepoint
RELEASE my_savepoint;      -- remove savepoint (commit its changes)
COMMIT;
-- or ROLLBACK to undo everything

# auto-commit (default — each statement is its own transaction)
SET autocommit = on;  -- default

Níveis de Isolamento

O padrão do PostgreSQL (READ COMMITTED) serve à maioria dos apps — sem leituras sujas, mas uma releitura na mesma transação pode ver dados novos. REPEATABLE READ no PostgreSQL é mais forte que o padrão SQL — também previne fantasmas (usando isolamento por snapshot). SERIALIZABLE é o mais forte, mas pode abortar transações que conflitam — seu app precisa repetir no SQLSTATE 40001. Para a maioria dos apps web, READ COMMITTED basta. Use SERIALIZABLE para correção crítica (livros-razão financeiros) com lógica de repetição.

postgresql
# set isolation level for a transaction
BEGIN ISOLATION LEVEL READ COMMITTED;   -- default
BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN ISOLATION LEVEL SERIALIZABLE;

# or set it for the session
SET default_transaction_isolation = 'serializable';

# READ COMMITTED (default):
#   - each query sees committed data at query start
#   - no dirty reads, but non-repeatable reads and phantoms possible

# REPEATABLE READ:
#   - each query sees snapshot from transaction start
#   - no non-repeatable reads, but serialization anomalies possible
#   - PostgreSQL's RR is stronger than SQL standard (no phantoms!)

# SERIALIZABLE:
#   - strongest isolation, behaves as if transactions ran one at a time
#   - may abort with serialization failure (retry needed)
#   - uses SSI (Serializable Snapshot Isolation)

# check current level
SHOW transaction_isolation;

# handle serialization failures (retry)
BEGIN ISOLATION LEVEL SERIALIZABLE;
  -- work
COMMIT;  -- may fail with SQLSTATE 40001 -> retry the transaction

Travas e Deadlocks

SELECT FOR UPDATE trava linhas para atualização (impede modificação concorrente) — útil para padrões 'ler, checar, atualizar'. Travas de aviso (advisory) são travas no nível da aplicação usando chaves inteiras — ótimas para coordenar processos distribuídos. Deadlocks ocorrem quando duas transações esperam uma pela outra; PostgreSQL os detecta e aborta uma (capture e repita). Para evitar deadlocks, adquira travas numa ordem consistente entre transações. Mantenha as transações curtas. Monitore pg_locks por sessões presas.

postgresql
# row-level locks (acquired by UPDATE, DELETE, SELECT FOR UPDATE)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;        -- lock row
SELECT * FROM accounts WHERE id = 1 FOR NO KEY UPDATE; -- weaker lock
SELECT * FROM accounts WHERE id = 1 FOR SHARE;         -- allow reads, block writes

# advisory locks (application-level locks)
SELECT pg_advisory_lock(12345);          -- session-level
SELECT pg_advisory_unlock(12345);
SELECT pg_try_advisory_lock(12345);      -- non-blocking
SELECT pg_advisory_xact_lock(12345);     -- transaction-level (auto-released)

# view active locks
SELECT pid, mode, granted, query
FROM pg_locks l JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted;

# terminate a blocked session
SELECT pg_cancel_backend(pid);    -- cancel query
SELECT pg_terminate_backend(pid); -- kill session

# deadlocks (PostgreSQL detects and resolves by killing one transaction)
-- Transaction A: UPDATE users WHERE id=1; UPDATE users WHERE id=2;
-- Transaction B: UPDATE users WHERE id=2; UPDATE users WHERE id=1;
-- -> ERROR: deadlock detected

MVCC e Isolamento por Snapshot

O MVCC do PostgreSQL significa que leitores nunca bloqueiam escritores e vice-versa — cada transação vê um snapshot. Atualizações criam versões novas de linhas; versões velhas permanecem até o VACUUM removê-las. Transações de longa duração impedem o VACUUM de limpar versões velhas, causando inchaço. Mantenha as transações curtas! Monitore sessões 'idle in transaction' — elas seguram snapshots e bloqueiam a limpeza. Wraparound de ID de transação é um problema sério (autovacuum o previne, mas monitore se o autovacuum estiver sofrendo).

postgresql
# PostgreSQL uses MVCC (Multi-Version Concurrency Control)
# each transaction sees a consistent snapshot of data

# visible transactions
SELECT txid_current();           -- current transaction ID
SELECT txid_snapshot_xmin(txid_current_snapshot());

# see long-running transactions (can prevent vacuum)
SELECT
  pid,
  age(clock_timestamp(), xact_start) AS duration,
  state,
  query
FROM pg_stat_activity
WHERE state IN ('active', 'idle in transaction')
ORDER BY xact_start;

# long transactions block vacuum (old row versions can't be removed)
# check for this:
SELECT
  pid,
  age(txid_current(), backend_xmin) AS xmin_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xmin_age DESC;

# vacuum and the transaction ID wraparound
# (autovacuum handles this, but monitor it!)
SELECT relname, last_autovacuum, last_autoanalyze, n_dead_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000;

Padrões de Travamento

FOR UPDATE trava linhas pessimisticamente — use para consistência crítica (transferências). Lock otimista (coluna de versão) é melhor para baixa contenção: sem travas, só repetir em conflito. SKIP LOCKED é perfeito para filas de trabalho — múltiplos workers pegam trabalhos diferentes sem bloquear. NOWAIT falha rápido se travado (use para 'tentar atualizar, sem esperar'). Travas de aviso coordenam operações no nível da aplicação (migrações, trabalhos cron). Escolha pessimista para alta contenção, otimista para baixa.

postgresql
# pessimistic locking (lock before work)
BEGIN;
  SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
  -- now safe to modify
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

# optimistic locking (check version, retry on conflict)
-- uses a version column
UPDATE products SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5;  -- 0 rows = conflict, retry

# SKIP LOCKED (process queue items without blocking)
SELECT * FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT 10;
-- grabs 10 jobs, skips locked ones (other workers don't wait)

# NOWAIT (fail immediately if locked)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock if row is locked

# named lock for coordination
SELECT pg_advisory_lock(hashtext('migrate_users'));
-- only one session can hold this lock
SELECT pg_advisory_unlock(hashtext('migrate_users'));
10

Views e Views Materializadas

Criando Views

Views são consultas salvas que se comportam como tabelas. Views simples (tabela única, sem agregados) são automaticamente atualizáveis — inserts/updates passam à tabela subjacente. WITH CHECK OPTION impede inserts/updates que tornariam a linha invisível através da view. Use views para simplificar consultas complexas, impor segurança (acesso no nível de coluna) e fornecer uma API estável. CASCADE descarta objetos dependentes. Views não guardam dados — rodam a consulta a cada vez.

postgresql
# simple view
CREATE VIEW active_users AS
SELECT id, username, email FROM users WHERE active = true;

# use it like a table
SELECT * FROM active_users WHERE username LIKE 'a%';

# view with computed columns
CREATE VIEW user_summary AS
SELECT
  u.id,
  u.username,
  count(p.id) AS post_count,
  max(p.created_at) AS last_post
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username;

# updatable view (simple views can be inserted/updated through)
CREATE VIEW user_emails AS
SELECT id, email FROM users;
INSERT INTO user_emails (id, email) VALUES (1, '[email protected]');  -- works!

# WITH CHECK OPTION (prevent inserts that wouldn't be visible)
CREATE VIEW active_users AS
SELECT * FROM users WHERE active = true
WITH CHECK OPTION;
-- INSERT with active=false fails

# modify or drop a view
ALTER VIEW active_users RENAME TO current_users;
DROP VIEW IF EXISTS active_users CASCADE;

Views Materializadas

Views materializadas guardam o resultado da consulta — muito mais rápidas de consultar, mas velhas até serem renovadas. Use-as para agregações caras (dashboards, relatórios). CONCURRENTLY renova sem travar a view (leitores continuam), mas exige um índice único. Agende renovações com pg_cron ou ferramentas externas. Para dados quase em tempo real, renove a cada poucos minutos; para análises históricas, diariamente. A troca é frescor vs velocidade de consulta — views materializadas são a chave para análises rápidas em tabelas grandes.

postgresql
# create a materialized view (stores the result)
CREATE MATERIALIZED VIEW sales_summary AS
SELECT
  date_trunc('day', created_at) AS day,
  product_id,
  count(*) AS order_count,
  sum(amount) AS total_revenue
FROM orders
GROUP BY 1, 2
WITH DATA;  -- or WITH NO DATA to populate later

# refresh (re-runs the query)
REFRESH MATERIALIZED VIEW sales_summary;
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;  -- needs unique index

# create a unique index for concurrent refresh
CREATE UNIQUE INDEX idx_sales_summary_day_product
ON sales_summary(day, product_id);

# schedule refresh (use pg_cron extension or external scheduler)
-- pg_cron: SELECT cron.schedule('0 * * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary');

# drop
DROP MATERIALIZED VIEW IF EXISTS sales_summary;

# use it (like a regular table)
SELECT * FROM sales_summary WHERE day = '2025-01-01';

Views de Segurança

Views são uma ferramenta de segurança poderosa: exponha só as colunas/linhas que um usuário deve ver, sem conceder acesso à tabela. Conceda SELECT na view, não na tabela subjacente. Para segurança no nível de linha, considere as políticas RLS (Row Level Security) embutidas do PostgreSQL em vez de views — são mais robustas e funcionam com acesso direto à tabela. Views com variáveis de sessão (current_setting) habilitam filtragem por usuário. Use views para criar uma camada de API estável que esconde a complexidade do schema das aplicações.

postgresql
# column-level security (hide sensitive columns)
CREATE VIEW public_users AS
SELECT id, username, created_at FROM users;
-- password, email columns are hidden

# row-level security via view
CREATE VIEW user_posts AS
SELECT * FROM posts WHERE user_id = current_setting('app.current_user_id')::int;

-- set the user context per session
SET app.current_user_id = '42';
SELECT * FROM user_posts;  -- only sees user 42's posts

# view with joins for simplified access
CREATE VIEW order_details AS
SELECT
  o.id AS order_id,
  o.created_at,
  u.username,
  p.name AS product_name,
  oi.quantity,
  oi.price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

# grant access to view but not underlying tables
GRANT SELECT ON public_users TO app_readonly;
REVOKE SELECT ON users FROM app_readonly;

Gestão de Views

CREATE OR REPLACE VIEW tem limitações — você pode adicionar colunas no fim, mas não pode removê-las nem reordená-las. Para mudanças significativas, descarte e recrie (mas confira as dependências primeiro). Views podem depender de outras views; descartar uma view base exige CASCADE. Use pg_views para inspecionar definições. O bloco DO para renovar todas as views materializadas é prático para scripts de manutenção — mas use CONCURRENTLY quando possível, para evitar travamento.

postgresql
# list views
SELECT viewname, definition FROM pg_views WHERE schemaname = 'public';

# get view definition
\d+ view_name  -- in psql

# create or replace (limited — can't change columns)
CREATE OR REPLACE VIEW user_summary AS
SELECT id, username, email FROM users WHERE active = true;
-- can add columns at the end, but can't remove/reorder

# to change columns significantly, drop and recreate
DROP VIEW IF EXISTS user_summary;
CREATE VIEW user_summary AS
SELECT id, username FROM users WHERE active = true;

# view dependencies (what depends on this view)
SELECT dependee.relname AS view, depender.relname AS depends_on
FROM pg_depend d
JOIN pg_class dependee ON d.objid = dependee.oid
JOIN pg_class depender ON d.refobjid = depender.oid
WHERE dependee.relname = 'user_summary';

# refresh all materialized views
DO $$
DECLARE r RECORD;
BEGIN
  FOR r IN SELECT matviewname FROM pg_matviews WHERE schemaname = 'public' LOOP
    EXECUTE 'REFRESH MATERIALIZED VIEW ' || r.matviewname;
  END LOOP;
END $$;

Views Recursivas

Views recursivas (CREATE RECURSIVE VIEW) embrulham um CTE recursivo — ótimo para travessia de árvore/grafo. Sempre inclua um limite de profundidade/distância na parte recursiva, para prevenir loops infinitos (ciclos nos dados). A coluna de caminho (concatenação de texto) é uma forma simples de acompanhar e exibir o caminho da travessia. Para hierarquias muito profundas, considere guardar uma tabela de fechamento ou a extensão ltree, por melhor desempenho. Views recursivas são convenientes, mas podem ser lentas em datasets grandes — adicione índices nas colunas de join.

postgresql
# recursive view for hierarchical data
CREATE RECURSIVE VIEW employee_tree(id, name, manager_id, level, path) AS
SELECT
  id, name, manager_id, 0, name::text
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT
  e.id, e.name, e.manager_id, et.level + 1,
  et.path || ' > ' || e.name
FROM employees e
JOIN employee_tree et ON e.manager_id = et.id;

# query the tree
SELECT * FROM employee_tree ORDER BY path;

# find all subordinates (2 levels deep)
SELECT * FROM employee_tree
WHERE path LIKE 'CEO > Engineering > %' AND level <= 3;

# view for graph traversal (e.g., friend connections)
CREATE RECURSIVE VIEW friend_chain(person1, person2, distance, path) AS
SELECT person1, person2, 1, person1 || '->' || person2
FROM friendships
UNION
SELECT
  fc.person1, f.person2, fc.distance + 1,
  fc.path || '->' || f.person2
FROM friend_chain fc
JOIN friendships f ON fc.person2 = f.person1
WHERE fc.distance < 6;  -- limit depth to prevent infinite loops
11

Funções e Operadores

Funções de String

PostgreSQL tem funções de string abrangentes. position() e strpos() acham substrings. substring() com FROM/FOR é padrão SQL; substr() é mais compacta. split_part() é prática para dados delimitados. initcap() capitaliza cada palavra. lpad/rpad preenchem strings a uma largura fixa (útil para formatação). string_to_array/array_to_string convertem entre strings e arrays. Para casamento de padrões, LIKE/ILIKE (curingas), ~ (regex) e SIMILAR TO (padrão SQL) cobrem a maioria das necessidades.

postgresql
# length and position
SELECT length('hello'), char_length('hello'), octet_length('hello');
SELECT position('lo' in 'hello');          -- 4
SELECT strpos('hello', 'lo');               -- 4

# substring and slicing
SELECT substring('hello' from 2 for 3);     -- 'ell'
SELECT substring('hello' from 2);           -- 'ello'
SELECT substr('hello', 2, 3);               -- 'ell'
SELECT left('hello', 3);                    -- 'hel'
SELECT right('hello', 3);                   -- 'llo'

# case and trim
SELECT upper('hello'), lower('HELLO'), initcap('hello world');
SELECT trim('  hello  '), ltrim('xxhello', 'x'), rtrim('helloxx', 'x');
SELECT btrim('xxhelloxx', 'x');             -- trim both sides

# split and join
SELECT split_part('a,b,c', ',', 2);         -- 'b' (1-indexed)
SELECT string_to_array('a,b,c', ',');       -- {a,b,c}
SELECT array_to_string(ARRAY['a','b','c'], ', ');  -- 'a, b, c'

# replace and pad
SELECT replace('hello', 'l', 'L');          -- 'heLLo'
SELECT lpad('5', 3, '0');                   -- '005'
SELECT rpad('5', 3, '0');                   -- '500'
SELECT repeat('ab', 3);                     -- 'ababab'
SELECT reverse('hello');                    -- 'olleh'

Funções de Data/Hora

now() e current_timestamp são equivalentes (hora de início da transação). EXTRACT/date_part obtêm componentes. date_trunc arredonda para baixo até uma unidade — essencial para agrupar dados por tempo. age() retorna intervalos (legíveis por humanos). to_char/to_date/to_timestamp cuidam de formatação/análise. generate_series com datas é incrivelmente útil para preencher lacunas em dados de séries temporais (faça LEFT JOIN contra ela para mostrar dias de contagem zero). Sempre use timestamptz; a aritmética de datas respeita o fuso da sessão.

postgresql
# current date/time
SELECT now(), current_timestamp, transaction_timestamp();
SELECT current_date, current_time;

# extract components
SELECT EXTRACT(YEAR FROM now()), EXTRACT(MONTH FROM now());
SELECT date_part('dow', now());     -- day of week (0=Sunday)
SELECT date_part('epoch', now());   -- Unix timestamp

# date arithmetic
SELECT now() + interval '1 day';
SELECT now() - interval '2 hours';
SELECT age('2025-01-01');                    -- interval since date
SELECT age('2025-01-01', '2024-01-01');      -- interval between dates
SELECT date '2025-01-15' - date '2025-01-01'; -- 14 (integer days)

# truncation and rounding
SELECT date_trunc('month', now());           -- first day of month, 00:00
SELECT date_trunc('hour', now());            -- current hour, 00 minutes

# formatting
SELECT to_char(now(), 'YYYY-MM-DD HH:MI:SS');
SELECT to_char(now(), 'Day, DD Mon YYYY');
SELECT to_char(12345.678, 'FM999,999.00');
SELECT to_date('2025/01/15', 'YYYY/MM/DD');
SELECT to_timestamp(1700000000);             -- Unix epoch

# generate series (date ranges)
SELECT generate_series(
  '2025-01-01'::date,
  '2025-01-31'::date,
  '1 day'::interval
);

Funções Numéricas e Matemáticas

round() com dois argumentos funciona em numeric (não double — faça cast primeiro). Funções trigonométricas usam radianos (use radians()/degrees() para converter). random() retorna 0-1; combine com matemática para intervalos. generate_series é um cavalo de batalha do PostgreSQL — gera linhas na hora para sequências, intervalos de datas e preencher lacunas. gcd/lcm (13+) são práticos. Para cálculos financeiros, sempre use numeric (exato) — funções de ponto flutuante como round em double podem ter problemas de precisão.

postgresql
# rounding
SELECT round(3.14159, 2);    -- 3.14
SELECT ceil(3.1), ceiling(3.1);  -- 4
SELECT floor(3.9);           -- 3
SELECT trunc(3.14159, 2);    -- 3.14 (truncate, no rounding)

# power and roots
SELECT power(2, 10);         -- 1024
SELECT sqrt(16);             -- 4
SELECT cbrt(27);             -- 3
SELECT exp(1);               -- 2.718... (e^x)
SELECT ln(10), log(100);     -- natural log, base-10 log

# trigonometry (radians!)
SELECT sin(radians(90));     -- 1 (convert degrees to radians)
SELECT degrees(1.5708);      -- ~90
SELECT pi();                 -- 3.14159...

# random
SELECT random();             -- 0 to 1
SELECT floor(random() * 100)::int;  -- 0 to 99
SELECT setseed(0.5);         -- seed for reproducibility

# sequences and ranges
SELECT generate_series(1, 10);
SELECT generate_series(1, 10, 2);   -- 1, 3, 5, 7, 9
SELECT generate_series(0, 1, 0.1);  -- 0.0, 0.1, ..., 1.0

# absolute, sign, gcd
SELECT abs(-5), sign(-5);    -- 5, -1
SELECT gcd(12, 8);           -- 4 (PG 13+)
SELECT lcm(12, 8);           -- 24 (PG 13+)

Funções Condicionais e de NULL

COALESCE retorna o primeiro valor não NULL — essencial para valores padrão. NULLIF retorna NULL se os valores casam — mais limpo que CASE para evitar divisão por zero. GREATEST/LEAST comparam valores (NULLs se propagam no GREATEST, mas nem sempre no LEAST). CASE é o condicional geral. ISNULL/NOTNULL são atalhos do PG. Comparação de linhas (ROW(a,b) < ROW(c,d)) compara lexicograficamente — útil para paginação por chave composta. Entender o comportamento de NULL (NULL = NULL é NULL, não verdadeiro) é crucial no PostgreSQL.

postgresql
# COALESCE (return first non-NULL)
SELECT COALESCE(nickname, username, email, 'anonymous') FROM users;

# NULLIF (return NULL if two values are equal)
SELECT NULLIF(status, '') FROM users;  -- '' becomes NULL
-- useful to avoid division by zero:
SELECT total / NULLIF(count, 0) FROM stats;  -- NULL instead of error

# GREATEST / LEAST
SELECT GREATEST(1, 2, 3);        -- 3
SELECT LEAST(NULL, 1, 2);        -- NULL (NULLs propagate)
SELECT GREATEST(a, b, c) FROM numbers;

# CASE expressions
SELECT
  CASE
    WHEN age < 18 THEN 'minor'
    WHEN age >= 65 THEN 'senior'
    ELSE 'adult'
  END AS age_group
FROM users;

# ISNULL / NOTNULL (PostgreSQL shortcuts)
SELECT * FROM users WHERE email ISNULL;    -- IS NULL
SELECT * FROM users WHERE email NOTNULL;   -- IS NOT NULL

# row comparison
SELECT ROW(1, 2) < ROW(1, 3);  -- true (compares column by column)

Funções de Array e JSON

Funções de array habilitam manipulação de dados poderosa dentro do banco: unnest expande arrays em linhas (ótimo para joins), array_agg é o inverso. Operadores JSONB: -> e #> retornam jsonb, ->> e #>> retornam texto. @> (contém) é o operador JSONB mais importante e usa índices GIN. ? verifica existência de chave. jsonb_build_object constrói JSON a partir de colunas. jsonb_set/||/- modificam JSON. Essas funções fazem do JSONB um tipo de dados de primeira classe — flexível, porém consultável.

postgresql
# array functions
SELECT array_length(ARRAY[1,2,3], 1);    -- 3
SELECT array_append(ARRAY[1,2], 3);       -- {1,2,3}
SELECT array_prepend(1, ARRAY[2,3]);      -- {1,2,3}
SELECT array_concat(ARRAY[1,2], ARRAY[3,4]); -- {1,2,3,4}
SELECT array_remove(ARRAY[1,2,3], 2);     -- {1,3}
SELECT array_replace(ARRAY[1,2,1], 1, 9); -- {9,2,9}
SELECT unnest(ARRAY[1,2,3]);              -- 3 rows
SELECT array_agg(name) FROM users;        -- aggregate to array

# JSONB functions
SELECT data->'name'           -- jsonb (key access)
SELECT data->>'name'          -- text (key access)
SELECT data#>'{address,city}' -- jsonb (path access)
SELECT data#>>'{address,city}'-- text (path access)
SELECT jsonb_build_object('name', username, 'age', age) FROM users;
SELECT jsonb_object_keys(data) FROM products;  -- keys as rows
SELECT jsonb_array_elements(data->'tags') FROM products;  -- expand array

# JSONB predicates
SELECT data @> '{"active": true}'     -- contains
SELECT data ? 'name'                   -- key exists
SELECT data ?| ARRAY['name','email']   -- any key exists
SELECT data ?& ARRAY['name','email']   -- all keys exist

# modify JSONB
SELECT jsonb_set(data, '{price}', '29.99')
SELECT data || '{"new": true}'         -- merge
SELECT data - 'old_key'                -- remove key
SELECT data #- '{nested,old_key}'      -- remove nested key
12

Triggers

Funções de Trigger

Funções de trigger precisam retornar trigger e são escritas em PL/pgSQL (ou outras linguagens). TG_OP, TG_TABLE_NAME, OLD, NEW são variáveis especiais disponíveis dentro de triggers. Triggers BEFORE podem modificar NEW (e pular operações retornando NULL). Triggers AFTER servem a efeitos colaterais (logs de auditoria, notificações). FOR EACH ROW dispara por linha; FOR EACH STATEMENT dispara uma vez. Triggers são poderosos, mas podem causar efeitos colaterais ocultos — use-os para preocupações transversais (auditoria, timestamps), não para lógica de negócio.

postgresql
# create a trigger function (must return trigger)
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

# attach the trigger
CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

# now any UPDATE on users sets updated_at automatically
UPDATE users SET email = '[email protected]' WHERE id = 1;
-- updated_at is set automatically

# audit log trigger
CREATE OR REPLACE FUNCTION audit_log()
RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_table (table_name, operation, user_name, changed_at, row_data)
  VALUES (
    TG_TABLE_NAME, TG_OP, current_user, now(),
    to_jsonb(OLD)  -- or NEW for insert
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_audit
  AFTER INSERT OR UPDATE OR DELETE ON users
  FOR EACH ROW EXECUTE FUNCTION audit_log();

Triggers de Instrução vs Linha

Triggers FOR EACH ROW disparam por linha (podem ser lentos para operações em massa). FOR EACH STATEMENT dispara uma vez (não acessa OLD/NEW individuais, mas pode usar tabelas de transição para lote). Triggers BEFORE podem modificar/pular linhas; AFTER servem a efeitos colaterais. A cláusula WHEN filtra quando o trigger dispara (avaliada por linha). Tabelas de transição (REFERENCING NEW TABLE/OLD TABLE) dão aos triggers de instrução acesso a todas as linhas alteradas — ótimo para auditar operações em massa sem custo por linha.

postgresql
# ROW trigger (fires once per affected row)
CREATE TRIGGER log_each_change
  AFTER UPDATE ON products
  FOR EACH ROW
  WHEN (OLD.price IS DISTINCT FROM NEW.price)
  EXECUTE FUNCTION log_price_change();

# STATEMENT trigger (fires once per statement, regardless of rows)
CREATE TRIGGER refresh_summary
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH STATEMENT
  EXECUTE FUNCTION refresh_sales_summary();

# BEFORE vs AFTER
# BEFORE: can modify NEW, skip the operation (return NULL), or change values
# AFTER: cannot modify the row, used for side effects (logs, notifications)

# conditional trigger (WHEN clause — evaluated per row)
CREATE TRIGGER check_high_value
  BEFORE INSERT ON orders
  FOR EACH ROW
  WHEN (NEW.amount > 10000)
  EXECUTE FUNCTION require_approval();

# transition tables (NEW/OLD as sets, for statement triggers)
CREATE TRIGGER audit_orders
  AFTER INSERT ON orders
  REFERENCING NEW TABLE AS new_rows
  FOR EACH STATEMENT
  EXECUTE FUNCTION log_bulk_insert();

Event Triggers (DDL)

Event triggers disparam em comandos DDL (CREATE/ALTER/DROP) — úteis para auditar mudanças de schema ou prevenir operações perigosas em produção. Usam tg_event e tg_tag para identificar o comando. Casos de uso comuns: impedir DROP TABLE, registrar cada DDL por conformidade, gerar automaticamente scripts de migração. Event triggers disparam DEPOIS do comando (ddl_command_end) ou podem interceptar (ddl_command_start). São no nível do banco, não da tabela. Use com parcimônia — adicionam custo a cada operação DDL.

postgresql
# event triggers fire on DDL events (CREATE, ALTER, DROP)
CREATE OR REPLACE FUNCTION no_drop_table()
RETURNS event_trigger AS $$
BEGIN
  IF tg_event = 'ddl_command_end' AND tg_tag = 'DROP TABLE' THEN
    RAISE EXCEPTION 'Dropping tables is not allowed in production';
  END IF;
END;
$$ LANGUAGE plpgsql;

# register the event trigger
CREATE EVENT TRIGGER protect_tables
  ON ddl_command_end
  WHEN tag IN ('DROP TABLE')
  EXECUTE FUNCTION no_drop_table();

# log all DDL changes
CREATE OR REPLACE FUNCTION log_ddl()
RETURNS event_trigger AS $$
DECLARE
  obj record;
BEGIN
  INSERT INTO ddl_log (event, tag, user_name, object_identity)
  VALUES (tg_event, tg_tag, current_user, NULL);
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER ddl_logger
  ON ddl_command_end
  EXECUTE FUNCTION log_ddl();

# drop an event trigger
DROP EVENT TRIGGER IF EXISTS protect_tables;

Gestão de Triggers

Desative triggers temporariamente para cargas em massa (muito mais rápido) — mas lembre de reativar! session_replication_role = 'replica' desativa cada um dos triggers e rules — use para importações em massa ou replicação. ALTER TABLE ... DISABLE TRIGGER é mais direcionado. Sempre reative os triggers após cargas em massa. Monitore o uso de triggers — podem causar problemas de desempenho ocultos (cada trigger adiciona custo por linha). Information_schema.triggers lista cada um dos triggers; \d+ no psql é mais rápido para inspecionar.

postgresql
# list triggers
SELECT
  event_object_table AS table_name,
  trigger_name,
  action_timing,       -- BEFORE/AFTER/INSTEAD OF
  event_manipulation,  -- INSERT/UPDATE/DELETE
  action_statement     -- function call
FROM information_schema.triggers;

# or use psql
\d+ users  -- shows triggers on the table

# enable/disable triggers
ALTER TABLE users DISABLE TRIGGER set_updated_at;
ALTER TABLE users ENABLE TRIGGER set_updated_at;
ALTER TABLE users DISABLE TRIGGER ALL;    -- all triggers
ALTER TABLE users ENABLE TRIGGER ALL;

# session_replication_role (disable for replication)
SET session_replication_role = 'replica';
-- triggers don't fire (useful for bulk data loads)
SET session_replication_role = 'origin';  -- re-enable

# drop a trigger
DROP TRIGGER IF EXISTS set_updated_at ON users;

# rename a trigger
ALTER TRIGGER set_updated_at ON users RENAME TO update_timestamp;

Padrões Comuns de Triggers

Padrões comuns de trigger: auto-timestamps (updated_at), logs de auditoria (acompanhar todas as mudanças por conformidade), imposição de regras de negócio (validação entre tabelas) e sincronização de dados desnormalizados (manter contadores). Cada padrão resolve um problema real, mas adiciona complexidade e custo. Considere alternativas: defaults para timestamps, logging de auditoria no nível da aplicação, restrições CHECK para regras simples, views materializadas para desnormalização. Use triggers quando a lógica precisa viver no banco (múltiplos apps acessam os dados, ou a regra é inegociável).

postgresql
# 1. Auto-update updated_at
CREATE FUNCTION set_updated_at() RETURNS trigger AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_updated_at BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

# 2. Audit log (track all changes)
CREATE FUNCTION audit() RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_log (table_name, op, old_data, new_data, changed_by, changed_at)
  VALUES (TG_TABLE_NAME, TG_OP,
    CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN to_jsonb(OLD) END,
    CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN to_jsonb(NEW) END,
    current_user, now());
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

# 3. Enforce business rules
CREATE FUNCTION check_budget() RETURNS trigger AS $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM departments d
    WHERE d.id = NEW.dept_id AND d.budget < NEW.amount
  ) THEN
    RAISE EXCEPTION 'Amount exceeds department budget';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

# 4. Sync denormalized data
CREATE FUNCTION update_post_count() RETURNS trigger AS $$
BEGIN
  UPDATE users SET post_count = (
    SELECT count(*) FROM posts WHERE user_id = NEW.user_id
  ) WHERE id = NEW.user_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
13

Stored Procedures e PL/pgSQL

Funções PL/pgSQL

PL/pgSQL é a linguagem procedural do PostgreSQL — adiciona variáveis, condicionais, loops e tratamento de erros ao SQL. Funções retornam um valor ou tabela. SELECT INTO atribui resultados de consulta a variáveis. RETURN QUERY retorna linhas de uma consulta. Parâmetros podem ter padrões. Use funções para encapsular lógica reutilizável, consultas complexas e regras de negócio. Mantenha as funções simples e bem nomeadas. Pelo desempenho, considere funções SQL (LANGUAGE sql) para consultas simples — podem ser embutidas pelo otimizador.

postgresql
# basic function
CREATE OR REPLACE FUNCTION add(a integer, b integer)
RETURNS integer AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

# function with variables and logic
CREATE OR REPLACE FUNCTION get_user_post_count(user_id integer)
RETURNS integer AS $$
DECLARE
  post_count integer;
BEGIN
  SELECT count(*) INTO post_count
  FROM posts WHERE posts.user_id = get_user_post_count.user_id;

  IF post_count IS NULL THEN
    RETURN 0;
  END IF;

  RETURN post_count;
END;
$$ LANGUAGE plpgsql;

# function returning a table (set-returning)
CREATE OR REPLACE FUNCTION get_recent_posts(limit_count integer DEFAULT 10)
RETURNS TABLE(id integer, title text, created_at timestamptz) AS $$
BEGIN
  RETURN QUERY
  SELECT posts.id, posts.title, posts.created_at
  FROM posts
  ORDER BY posts.created_at DESC
  LIMIT limit_count;
END;
$$ LANGUAGE plpgsql;

# call functions
SELECT get_user_post_count(42);
SELECT * FROM get_recent_posts(5);

Estruturas de Controle

PL/pgSQL tem estruturas de controle completas: IF/ELSIF/ELSE, CASE, LOOP/EXIT/CONTINUE, WHILE, FOR (intervalos inteiros) e FOREACH (iteração de array). Use EXIT WHEN para quebrar loops. FOR i IN 1..n itera intervalos (inclusivos). FOREACH itera arrays. EXECUTE roda SQL dinâmico (com format() para injeção segura de identificadores). Mantenha os loops no banco pequenos — loops grandes deveriam ser SQL baseado em conjunto sempre que possível. Para operações em massa, use INSERT/UPDATE com SELECT em vez de loops linha a linha.

postgresql
# IF/ELSIF/ELSE
CREATE OR REPLACE FUNCTION categorize_price(price numeric)
RETURNS text AS $$
BEGIN
  IF price IS NULL THEN RETURN 'unknown';
  ELSIF price < 10 THEN RETURN 'cheap';
  ELSIF price < 50 THEN RETURN 'moderate';
  ELSE RETURN 'expensive';
  END IF;
END;
$$ LANGUAGE plpgsql;

# CASE expression (inside BEGIN...END)
CREATE OR REPLACE FUNCTION get_discount(tier text)
RETURNS numeric AS $$
BEGIN
  RETURN CASE tier
    WHEN 'gold'   THEN 0.20
    WHEN 'silver' THEN 0.10
    WHEN 'bronze' THEN 0.05
    ELSE 0.00
  END;
END;
$$ LANGUAGE plpgsql;

# LOOP (with EXIT)
CREATE OR REPLACE FUNCTION find_first_null(table_name text)
RETURNS integer AS $$
DECLARE i integer := 1; val text;
BEGIN
  LOOP
    EXECUTE format('SELECT col FROM %I WHERE id = %s', table_name, i) INTO val;
    EXIT WHEN val IS NULL;
    i := i + 1;
    EXIT WHEN i > 1000;  -- safety limit
  END LOOP;
  RETURN i;
END;
$$ LANGUAGE plpgsql;

# WHILE and FOR loops
CREATE OR REPLACE FUNCTION sum_n(n integer)
RETURNS integer AS $$
DECLARE total integer := 0;
BEGIN
  FOR i IN 1..n LOOP
    total := total + i;
  END LOOP;
  RETURN total;
END;
$$ LANGUAGE plpgsql;

Stored Procedures (PG11+)

Procedures (PG11+) diferem de funções: podem gerenciar transações (COMMIT/ROLLBACK dentro), não retornam valores como funções (use parâmetros OUT em vez disso) e são chamadas com CALL. Use procedures para operações de múltiplas instruções que precisam de controle de transação (ETL, migrações de dados). Use funções para computações e consultas que retornam valores. Procedures são a forma padrão SQL de encapsular lógica transacional. Antes do PG11, funções não podiam commit/rollback — procedures preenchem essa lacuna.

postgresql
# PROCEDURE (unlike FUNCTION, can manage transactions)
CREATE OR REPLACE PROCEDURE transfer_money(
  from_account integer,
  to_account integer,
  amount numeric
) AS $$
BEGIN
  -- inside a procedure, we can use COMMIT/ROLLBACK
  UPDATE accounts SET balance = balance - amount WHERE id = from_account;
  UPDATE accounts SET balance = balance + amount WHERE id = to_account;

  -- check for negative balance
  IF EXISTS (SELECT 1 FROM accounts WHERE id = from_account AND balance < 0) THEN
    ROLLBACK;
    RAISE EXCEPTION 'Insufficient funds';
  END IF;

  COMMIT;
END;
$$ LANGUAGE plpgsql;

# call a procedure (CALL, not SELECT)
CALL transfer_money(1, 2, 100.00);

# procedure with output parameters
CREATE OR REPLACE PROCEDURE get_stats(
  OUT user_count integer,
  OUT post_count integer
) AS $$
BEGIN
  SELECT count(*) INTO user_count FROM users;
  SELECT count(*) INTO post_count FROM posts;
END;
$$ LANGUAGE plpgsql;

CALL get_stats(?, ?);  -- output params

Tratamento de Erros

RAISE EXCEPTION aborta a transação com um erro personalizado. Blocos EXCEPTION capturam erros (como try/catch) — comuns para degradação graciosa. SQLSTATE é o código de erro de 5 caracteres; SQLERRM é a mensagem. USING HINT/DETAIL adiciona contexto útil. RAISE NOTICE é ótimo para depuração (visível no psql). Capture erros específicos (division_by_zero, unique_violation) em vez de OTHERS, para não mascarar bugs reais. Dentro de EXCEPTION, a transação está em estado abortado — só se pode commit/rollback, não rodar mais consultas (até procedures do PG11+).

postgresql
# RAISE for messages and exceptions
CREATE OR REPLACE FUNCTION validate_age(age integer)
RETURNS void AS $$
BEGIN
  IF age < 0 THEN
    RAISE EXCEPTION 'Age cannot be negative: %', age
      USING HINT = 'Please provide a valid age (0-150)';
  ELSIF age > 150 THEN
    RAISE EXCEPTION 'Age seems unrealistic: %', age;
  ELSE
    RAISE NOTICE 'Age validated: %', age;
  END IF;
END;
$$ LANGUAGE plpgsql;

# TRY/CATCH with EXCEPTION block
CREATE OR REPLACE FUNCTION safe_divide(a numeric, b numeric)
RETURNS numeric AS $$
DECLARE result numeric;
BEGIN
  result := a / b;
  RETURN result;
EXCEPTION
  WHEN division_by_zero THEN
    RAISE NOTICE 'Division by zero, returning NULL';
    RETURN NULL;
  WHEN OTHERS THEN
    RAISE NOTICE 'Unexpected error: %', SQLERRM;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

# RAISE levels: DEBUG, LOG, INFO, NOTICE, WARNING, EXCEPTION
# EXCEPTION aborts the transaction; others just log

# get error details
SELECT sqlstate, message, detail, hint, context
FROM pg_stat_activity;  -- or in exception handler: SQLSTATE, SQLERRM

SQL Dinâmico

SQL dinâmico (EXECUTE) roda strings como SQL — essencial quando nomes de tabela/coluna são variáveis. Sempre use format() com %I (identificadores) e %L (literais) para prevenir SQL injection — nunca concatene entrada de usuário diretamente! USING passa parâmetros com segurança (como instruções preparadas). SQL dinâmico é poderoso, mas opaco ao planejador de consultas e às ferramentas de análise estática. Use quando necessário (nomes de tabela dinâmicos, geração de DDL), mas prefira SQL estático onde possível. Teste bem — erros de SQL dinâmico são de runtime, não de compilação.

postgresql
# EXECUTE for dynamic queries
CREATE OR REPLACE FUNCTION count_rows(table_name text)
RETURNS integer AS $$
DECLARE result integer;
BEGIN
  EXECUTE format('SELECT count(*) FROM %I', table_name) INTO result;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

# format() for safe SQL construction
#   %I = identifier (table/column name, quoted)
#   %L = literal (value, quoted)
#   %s = simple string substitution (UNSAFE — avoid for user input)
SELECT format('SELECT * FROM %I WHERE id = %L', 'users', 42);
-- 'SELECT * FROM users WHERE id = 42'

# dynamic SQL with USING for parameters
CREATE OR REPLACE FUNCTION get_user_by(field text, value text)
RETURNS setof users AS $$
BEGIN
  RETURN QUERY EXECUTE format(
    'SELECT * FROM users WHERE %I = $1', field
  ) USING value;
END;
$$ LANGUAGE plpgsql;

# bulk operations with dynamic SQL
CREATE OR REPLACE FUNCTION archive_old_data(days_old integer)
RETURNS integer AS $$
DECLARE deleted_count integer;
BEGIN
  EXECUTE format(
    'WITH deleted AS (
       DELETE FROM logs WHERE created_at < now() - interval '%s days'
       RETURNING *
     ) SELECT count(*) FROM deleted'
  ) INTO deleted_count USING days_old;

  RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;
14

Segurança e Permissões

Usuários e Papéis (Roles)

No PostgreSQL, usuários e papéis são a mesma entidade — um 'usuário' é só um papel com privilégio LOGIN. Crie papéis de grupo (sem LOGIN) para gerenciar permissões centralmente e depois GRANT-os aos papéis de login. VALID UNTIL define expiração de senha. CONNECTION LIMIT limita conexões concorrentes por papel. Definir search_path por papel é ótimo para isolamento multi-inquilino. Sempre use papéis para agrupamento de permissões, em vez de conceder permissões a usuários individuais. DROP ROLE exige remover cada um dos seus privilégios e membros primeiro.

postgresql
# create a role (users are roles that can log in)
CREATE ROLE app_user WITH LOGIN PASSWORD 'secret';
CREATE ROLE analyst WITH LOGIN PASSWORD 'secret' VALID UNTIL '2025-12-31';

# create a role that cannot log in (for grouping)
CREATE ROLE readonly;

# alter a role
ALTER ROLE app_user WITH PASSWORD 'new_secret';
ALTER ROLE app_user WITH VALID UNTIL 'infinity';
ALTER ROLE app_user CONNECTION LIMIT 10;
ALTER ROLE app_user SET search_path TO app_schema, public;

# grant role to another (inherit permissions)
GRANT readonly TO app_user;
GRANT readonly TO analyst;

# view roles
\du  -- in psql
SELECT rolname, rolsuper, rolcanlogin FROM pg_roles;

# drop a role (must revoke privileges first)
REVOKE readonly FROM app_user;
DROP ROLE app_user;

Concedendo Privilégios

Privilégios: SELECT/INSERT/UPDATE/DELETE/TRUNCATE/REFERENCES/TRIGGER para tabelas; USAGE/CREATE para schemas; USAGE/SELECT para sequências; EXECUTE para funções; CONNECT/CREATE/TEMPORARY para bancos. Concessões no nível de coluna são poderosas para segurança. ALTER DEFAULT PRIVILEGES concede permissões sobre objetos futuros — essencial quando tabelas novas são criadas com frequência. Sempre conceda USAGE de sequência a papéis que inserem em tabelas com colunas serial/identity. Revogue privilégios em excesso, para acesso de menor privilégio.

postgresql
# grant table privileges
GRANT SELECT, INSERT, UPDATE ON products TO app_user;
GRANT ALL ON products TO admin;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

# grant column-level privileges
GRANT SELECT (id, name, email) ON users TO app_user;
-- app_user can only see those columns

# grant schema privileges
GRANT USAGE ON SCHEMA app_schema TO app_user;
GRANT CREATE ON SCHEMA app_schema TO developer;

# grant sequence privileges (for serial/identity)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

# grant database privileges
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT CREATE ON DATABASE mydb TO developer;
GRANT TEMPORARY ON DATABASE mydb TO app_user;

# grant function privileges
GRANT EXECUTE ON FUNCTION get_user_post_count(integer) TO app_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO app_user;

# revoke privileges
REVOKE INSERT, UPDATE ON products FROM app_user;

# alter default privileges (for future objects)
ALTER DEFAULT PRIVILEGES FOR ROLE postgres
  IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

Row Level Security (RLS)

Row Level Security (RLS) impõe controle de acesso por linha — usuários veem só linhas que casem com a política. USING filtra leituras; WITH CHECK valida escritas. Defina variáveis de sessão específicas do app (SET app.user_id) para identificar o usuário atual. FORCE RLS faz até o dono da tabela sujeitar-se às políticas. O atributo de papel BYPASSRLS pula cada RLS (para admins). RLS é poderoso para apps multi-inquilino — sem necessidade de views por usuário. Cuidado: políticas RLS precisam estar corretas, pois filtram dados silenciosamente. Teste bem com usuários diferentes.

postgresql
# enable RLS on a table
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

# create a policy (users see only their own posts)
CREATE POLICY user_posts ON posts
  FOR SELECT
  USING (user_id = current_setting('app.user_id')::int);

# policy for all operations
CREATE POLICY user_posts_all ON posts
  USING (user_id = current_setting('app.user_id')::int)
  WITH CHECK (user_id = current_setting('app.user_id')::int);

# USING:    filter existing rows (SELECT, UPDATE, DELETE)
# WITH CHECK: validate new rows (INSERT, UPDATE)

# set the user context per session
SET app.user_id = '42';
SELECT * FROM posts;  -- only sees user 42's posts

# bypass RLS (for admins)
ALTER TABLE posts FORCE ROW LEVEL SECURITY;  -- even owner respects RLS
GRANT BYPASSRLS TO admin_role;  -- role-level bypass

# policy for admins (see all)
CREATE POLICY admin_all ON posts
  FOR ALL
  TO admin_role
  USING (true)
  WITH CHECK (true);

# disable RLS
ALTER TABLE posts DISABLE ROW LEVEL SECURITY;

Autenticação e SSL

pg_hba.conf é a primeira linha de defesa — controla que hosts/usuários podem conectar e como. Sempre use scram-sha-256 (não md5 nem trust). hostssl exige SSL para a conexão. sslmode=require garante criptografia; verify-full também verifica o certificado (o mais seguro). Para produção: use SSL para todas as conexões remotas, restrinja o pg_hba.conf a IPs conhecidos e use scram-sha-256. Recarregue com pg_reload_conf() após editar. Monitore pg_stat_activity por conexões não autorizadas.

postgresql
# pg_hba.conf controls who can connect and how
# host  database  user  address  method
#   host all all 127.0.0.1/32 scram-sha-256
#   host all all 0.0.0.0/0   scram-sha-256
#   hostssl all all 0.0.0.0/0 scram-sha-256  # require SSL

# reload pg_hba.conf
SELECT pg_reload_conf();

# authentication methods:
#   trust:       no password (NEVER use in production)
#   md5:         legacy password hash (deprecated)
#   scram-sha-256:  modern password auth (default, recommended)
#   cert:        client certificate
#   peer:        OS username matches DB username (local only)

# set password encryption
SET password_encryption = 'scram-sha-256';
ALTER ROLE app_user PASSWORD 'secret';  -- re-hash with scram

# SSL connections
SHOW ssl;                  -- is SSL enabled?
SHOW ssl_cert_file;
SHOW ssl_key_file;

# connect with SSL
psql "postgresql://user@host/db?sslmode=require"
# sslmode: disable, allow, prefer, require, verify-ca, verify-full

# view active connections
SELECT pid, usename, client_addr, ssl, ssl_cipher
FROM pg_stat_activity;

Criptografia e Auditoria

pgcrypto fornece criptografia no nível de coluna: pgp_sym_encrypt/decrypt para criptografia reversível, crypt/gen_salt para hash de senhas. Use bcrypt (bf) para senhas — é lento por design (resiste a força bruta). Por conformidade, pgAudit registra todas as operações DDL e DML no log do servidor. Para dados sensíveis, prefira criptografia na camada da aplicação (chaves não devem viver no banco). Sempre faça hash de senhas (nunca criptografe reversivelmente). Monitore pg_stat_activity para auditar consultas ativas. Combine essas ferramentas para defesa em profundidade.

postgresql
# encrypt columns with pgcrypto
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE users (
  id serial PRIMARY KEY,
  email text,
  ssn bytea  -- encrypted
);

INSERT INTO users (email, ssn) VALUES
  ('[email protected]', pgp_sym_encrypt('123-45-6789', 'secret_key'));

SELECT pgp_sym_decrypt(ssn, 'secret_key') AS ssn FROM users;

# one-way hashing (passwords)
SELECT crypt('password', gen_salt('bf'));      -- bcrypt
SELECT crypt('password', gen_salt('md5'));     -- MD5 (weak)
-- store the hash, verify with:
SELECT crypt('password', stored_hash) = stored_hash;

# audit with pgAudit extension
CREATE EXTENSION pgaudit;
ALTER SYSTEM SET pgaudit.log = 'write, ddl';
SELECT pg_reload_conf();
-- logs all writes and DDL to the PostgreSQL log

# session auditing
SELECT
  pid, usename, application_name, client_addr,
  query_start, state, query
FROM pg_stat_activity
WHERE state = 'active';
15

Backup e Restauração

Básico de pg_dump

pg_dump cria um backup lógico (instruções SQL para recriar o banco). O formato personalizado (-F c) é comprimido e suporta restauração paralela e seletiva — sempre prefira-o. O formato diretório (-F d) habilita dump e restauração paralelos. pg_dumpall faz backup de cada um dos bancos mais papéis e tablespaces (use para backups completos de cluster). --clean --if-exists torna os dumps idempotentes (podem restaurar várias vezes). Para bancos grandes, considere --jobs para dump paralelo (só formato diretório).

postgresql
# backup a single database
pg_dump -h localhost -U postgres -d mydb -F c -f mydb.dump
# -F c: custom format (compressed, supports parallel restore)
# -F p: plain SQL (human-readable, default)
# -F d: directory format (parallel, multiple files)

# backup with options
pg_dump -d mydb -F c -f mydb.dump \
  --no-owner           -- don't include ownership commands
  --no-privileges      -- don't include GRANT/REVOKE
  --schema=app_schema  -- only this schema
  --table=users        -- only this table
  --data-only          -- only data, no schema
  --schema-only        -- only schema, no data
  --clean              -- include DROP statements
  --if-exists          -- use IF EXISTS in DROPs

# backup all databases
pg_dumpall -h localhost -U postgres -f all_databases.sql
# includes roles, tablespaces, and all databases

# compress a plain SQL dump
pg_dump -d mydb | gzip > mydb.sql.gz

# backup a remote database
pg_dump "postgresql://user:pass@remote-host:5432/mydb" -F c -f mydb.dump

pg_restore

pg_restore restaura dos formatos personalizado/diretório (não SQL puro — use psql para isso). --jobs=N habilita restauração paralela (muito mais rápida em multicore). --clean --if-exists descarta objetos existentes primeiro. --no-owner é essencial ao restaurar para um usuário diferente. --list mostra o conteúdo do dump; use --table/--schema para restauração seletiva. Para restaurações grandes, desative triggers e restrições temporariamente e depois reative. Sempre teste as restaurações — um backup que você não consegue restaurar não vale nada.

postgresql
# restore from custom format
pg_restore -h localhost -U postgres -d mydb -F c mydb.dump

# restore with options
pg_restore -d mydb mydb.dump \
  --clean              -- drop objects before recreating
  --if-exists          -- use IF EXISTS in DROPs
  --no-owner           -- don't restore ownership
  --schema=app_schema  -- only this schema
  --table=users        -- only this table
  --data-only          -- only data
  --jobs=4             -- parallel restore (4 threads)
  --verbose

# restore to a new database
createdb newdb
pg_restore -d newdb mydb.dump

# restore from plain SQL
psql -d mydb -f mydb.sql
gunzip -c mydb.sql.gz | psql -d mydb

# list contents of a dump
pg_restore -l mydb.dump

# restore specific items
pg_restore -d mydb mydb.dump --table=users --table=posts

# generate SQL to inspect (without restoring)
pg_restore mydb.dump > mydb.sql
pg_restore --list mydb.dump  -- list items with indices

Backup Físico e PITR

pg_basebackup cria uma cópia binária de o diretório inteiro de dados — mais rápido que pg_dump para bancos grandes e suporta PITR. O arquivamento de WAL (Write-Ahead Log) é essencial para PITR: arquive cada um dos arquivos WAL, depois restaure o backup base + reproduza o WAL até qualquer ponto no tempo. recovery_target_time permite recuperar a um timestamp específico (desfazer erros como DROP TABLE acidental). pg_create_restore_point cria um alvo de recuperação nomeado. PITR é o padrão-ouro de backup — combine com pg_dump para backups lógicos.

postgresql
# physical backup with pg_basebackup (online)
pg_basebackup -h localhost -U repluser -D /backup/base \
  -Ft -z -P          -- tar format, compressed, progress
  --wal-method=stream  -- stream WAL files separately

# set up WAL archiving (postgresql.conf)
#   archive_mode = on
#   archive_command = 'cp %p /archive/%f'
SELECT pg_reload_conf();

# Point-in-Time Recovery (PITR):
# 1. Restore base backup
# 2. Create recovery.signal file
# 3. Configure recovery (postgresql.conf or recovery.conf):
#      restore_command = 'cp /archive/%f %p'
#      recovery_target_time = '2025-01-15 14:30:00'
#      recovery_target_action = 'promote'
# 4. Start PostgreSQL — it replays WAL to the target time

# create a recovery point
SELECT pg_create_restore_point('before_migration');

# check WAL archiving status
SELECT * FROM pg_stat_archiver;

Setup de Replicação

Replicação de streaming: o primário envia WAL às réplicas em tempo real. pg_basebackup -R configura uma réplica num único comando. Réplicas são somente leitura (hot standby) e podem servir consultas de leitura. Monitore o atraso de replicação (pg_stat_replication no primário, pg_last_xact_replay_timestamp na réplica). Promoção (pg_ctl promote) torna uma réplica primária — use para failover. Para failover automático, use ferramentas como Patroni, repmgr ou pg_auto_failover. Replicação em cascata reduz a carga no primário encadeando réplicas.

postgresql
# on primary (postgresql.conf):
#   wal_level = replica
#   max_wal_senders = 10
#   hot_standby = on  (for replica)

# create replication role
CREATE ROLE repluser WITH REPLICATION LOGIN PASSWORD 'secret';

# in pg_hba.conf, allow replication connections:
#   host replication repluser 0.0.0.0/0 scram-sha-256

# on replica, create standby using pg_basebackup:
pg_basebackup -h primary-host -U repluser -D /var/lib/postgresql/data \
  -Fp -Xs -P -R
# -R creates standby.signal and configures primary_conninfo

# monitor replication
# on primary:
SELECT * FROM pg_stat_replication;
SELECT * FROM pg_current_wal_lsn();

# on replica:
SELECT * FROM pg_stat_wal_receiver;
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

# promote a replica to primary (failover)
pg_ctl promote -D /var/lib/postgresql/data

# cascading replication
#   replica can also be a primary to other replicas
#   set cascade_level or use primary_conninfo pointing to another replica

Replicação Lógica

Replicação lógica (PG10+) replica mudanças no nível de tabela — mais flexível que a física: versões PostgreSQL diferentes, tabelas seletivas, schemas diferentes em cada lado. O publicador cria uma PUBLICATION; o assinante cria uma SUBSCRIPTION. Os dados iniciais são copiados, depois as mudanças transmitem em tempo real. Conflitos (ex.: INSERT com PK existente) param a replicação — trate com SKIP ou correções manuais. Ótimo para atualizações sem indisponibilidade (replicar da versão velha à nova e depois trocar). Use para compartilhamento de dados entre bancos ou replicação parcial.

postgresql
# logical replication (PG10+) — replicate specific tables
# on publisher:
CREATE PUBLICATION my_pub FOR TABLE users, posts;

# add more tables to the publication
ALTER PUBLICATION my_pub ADD TABLE comments;

# on subscriber:
CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=publisher-host user=repluser dbname=mydb'
  PUBLICATION my_pub;

# initial data copy happens automatically
# subsequent changes are replicated in real-time

# monitor logical replication
SELECT * FROM pg_stat_subscription;
SELECT * FROM pg_replication_slots;

# replicate only specific operations
CREATE PUBLICATION insert_only FOR TABLE logs WITH (publish = 'insert');

# control conflict resolution
# (subscriber applies changes; conflicts cause replication to stop)
# handle with: ALTER SUBSCRIPTION ... SKIP or manual resolution

# cross-version replication (e.g., PG14 -> PG16)
# logical replication works across major versions!

# remove
DROP SUBSCRIPTION my_sub;
DROP PUBLICATION my_pub;
16

Desempenho e Ajuste

EXPLAIN

EXPLAIN mostra o plano da consulta; ANALYZE roda a consulta e mostra tempos reais. Procure Seq Scan em tabelas grandes (índice ausente), alto Rows Removed by Filter (consulta ineficaz) e ordenações externas (aumente work_mem). Index Only Scan é o mais rápido (precisa de índice cobertor). Bitmap Scan fica entre Seq Scan e Index Scan. Os números de 'custo' são estimativas do planejador; 'tempo real' é real. Sempre use EXPLAIN (ANALYZE, BUFFERS) para ajuste — revela o gargalo verdadeiro. Compare linhas estimadas vs reais para notar estatísticas velhas.

postgresql
# basic EXPLAIN (query plan without running)
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

# EXPLAIN ANALYZE (actually runs the query, shows real stats)
EXPLAIN ANALYZE SELECT * FROM users JOIN posts ON users.id = posts.user_id;

# with buffer stats
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM large_table WHERE id = 42;

# with format (JSON, TEXT, XML, YAML)
EXPLAIN (FORMAT JSON) SELECT * FROM users;

# common plan nodes:
#   Seq Scan:        full table scan (slow for large tables)
#   Index Scan:      index lookup + heap fetch
#   Index Only Scan: index-only (fastest, needs covering index)
#   Bitmap Scan:     bitmap of matching rows, then fetch
#   Hash Join:       hash one table, probe with other
#   Nested Loop:     for each left row, scan right (small datasets)
#   Sort:            explicit sort (look for "Sort Method: external merge" = bad)

# warning signs:
#   "Seq Scan on large_table" -> missing index
#   "Sort Method: external disk" -> increase work_mem
#   "Rows Removed by Filter: 999999" -> bad filter, missing index

VACUUM e ANALYZE

O MVCC do PostgreSQL cria linhas mortas em UPDATE/DELETE — VACUUM as recupera. autovacuum roda automaticamente (mantenha LIGADO!), mas pode precisar de ajuste para tabelas de muita escrita. VACUUM FULL reescreve a tabela (recupera espaço para o SO), mas toma uma trava exclusiva — use pg_repack em vez disso, para reorganização online. ANALYZE atualiza as estatísticas que o planejador usa — crítico após cargas em massa. Estatísticas velhas causam planos de consulta ruins. Ajuste autovacuum_scale_factor por tabela (menor para tabelas grandes e de muita escrita). Monitore n_dead_tup para detectar atraso de vacuum.

postgresql
# VACUUM (reclaim space from dead rows, don't lock)
VACUUM users;            -- regular vacuum
VACUUM FULL users;       -- rewrites table (locks, reclaims to OS)
VACUUM ANALYZE users;    -- vacuum + update statistics
VACUUM (VERBOSE, ANALYZE) users;  -- with output

# autovacuum (should be ON in production)
SHOW autovacuum;  -- should be on
SHOW autovacuum_vacuum_threshold;     -- default 50
SHOW autovacuum_vacuum_scale_factor;  -- default 0.2 (20% dead rows)

# tune autovacuum per table
ALTER TABLE users SET (
  autovacuum_vacuum_scale_factor = 0.1,  -- vacuum at 10% dead rows
  autovacuum_analyze_scale_factor = 0.05
);

# ANALYZE (update planner statistics — critical for query plans)
ANALYZE users;         -- sample the table
ANALYZE;               -- all tables

# check dead tuples (need vacuuming)
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

# check autovacuum activity
SELECT pid, datname, query
FROM pg_stat_activity
WHERE query LIKE '%autovacuum%';

Memória e Configuração

shared_buffers é o cache compartilhado do PostgreSQL (25% da RAM é a recomendação clássica). effective_cache_size informa ao planejador sobre o cache do SO (defina 50-75% da RAM, é só uma dica). work_mem é por operação de ordenação/hash — defini-lo alto demais com muitas conexões pode exaurir a memória. maintenance_work_mem acelera VACUUM e CREATE INDEX. Para SSDs, defina random_page_cost=1.1 (o padrão 4.0 assume HDDs lentos). Essas configurações afetam drasticamente o desempenho — sempre faça benchmark das mudanças.

postgresql
# key memory settings (in postgresql.conf)
SHOW shared_buffers;        -- default 128MB (set to 25% of RAM)
SHOW effective_cache_size;  -- default 4GB (set to 50-75% of RAM)
SHOW work_mem;              -- default 4MB (per-sort/hash memory)
SHOW maintenance_work_mem;  -- default 64MB (for VACUUM, CREATE INDEX)

# recommended production settings (example for 16GB RAM):
#   shared_buffers = 4GB
#   effective_cache_size = 12GB
#   work_mem = 64MB          (careful: per-operation, not global)
#   maintenance_work_mem = 512MB
#   max_connections = 100
#   random_page_cost = 1.1   (for SSDs; default 4.0 is for HDDs)

# check current connections vs max
SELECT count(*), state FROM pg_stat_activity GROUP BY state;
SHOW max_connections;

# WAL settings for write performance
SHOW wal_buffers;        -- default -1 (auto-tuned to 1/32 shared_buffers)
SHOW checkpoint_timeout; -- default 5min
SHOW max_wal_size;       -- default 1GB

# parallel query
SHOW max_parallel_workers;        -- default 8
SHOW max_parallel_workers_per_gather; -- default 2
SET max_parallel_workers_per_gather = 4;  -- more parallelism

# reload config
SELECT pg_reload_conf();

Otimização de Consultas

As maiores vitórias: indexe colunas de FK, use COPY para cargas em massa, evite OFFSET para paginação (use keyset) e não envolva colunas indexadas em funções. EXISTS costuma ser mais rápido que IN para subconsultas. SELECT * impede varreduras só de índice e desperdiça I/O. Inserções em lote (multi-linha ou COPY) são 10-100x mais rápidas que inserções individuais. Após cargas em massa, rode ANALYZE, para que o planejador tenha estatísticas precisas. Sempre EXPLAIN (ANALYZE) antes e depois das mudanças para verificar melhorias.

postgresql
# 1. use indexes (check with EXPLAIN)
# bad:  SELECT * FROM users WHERE lower(email) = '[email protected]'
# good: SELECT * FROM users WHERE email = '[email protected]'
#   (or use an expression index on lower(email))

# 2. avoid SELECT * (reduces I/O, enables index-only scans)
SELECT id, username FROM users WHERE active = true;

# 3. use EXISTS instead of IN for subqueries
-- BAD:  SELECT * FROM users WHERE id IN (SELECT user_id FROM posts)
-- GOOD: SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM posts WHERE user_id = u.id)

# 4. batch inserts (much faster than row-by-row)
INSERT INTO logs (msg) VALUES ('a'), ('b'), ('c');  -- one statement
-- or use COPY for bulk loads:
COPY logs FROM '/path/to/file.csv' WITH (FORMAT csv);

# 5. use CTEs or subqueries for readability, but check performance
# (PG12+ inlines CTEs; before that, CTEs were optimization fences)

# 6. avoid OFFSET for pagination (slow for large offsets)
-- BAD:  SELECT * FROM posts ORDER BY id OFFSET 100000 LIMIT 10
-- GOOD: SELECT * FROM posts WHERE id > 100000 ORDER BY id LIMIT 10

# 7. use partial indexes for common filtered queries
CREATE INDEX idx_active_users ON users(last_login) WHERE active = true;

# 8. analyze after bulk loads
ANALYZE products;  -- update statistics for the planner

Monitoramento de Consultas

pg_stat_statements é a ferramenta de monitoramento mais valiosa — acompanha contagens de consulta, tempo total/médio e linhas. Habilite-a em shared_preload_libraries. pg_stat_activity mostra consultas em tempo real (procure durações longas e 'idle in transaction'). Monitore a porcentagem de tuplas mortas por inchaço. Ache índices não usados (idx_scan=0) para descartar — desperdiçam espaço e retardam escritas. Defina log_min_duration_statement para registrar consultas lentas automaticamente. Combine essas ferramentas com EXPLAIN para identificar e corrigir gargalos de forma sistemática.

postgresql
# find slow queries (needs pg_stat_statements extension)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT
  query,
  calls,
  mean_exec_time AS avg_ms,
  total_exec_time AS total_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

# currently running queries
SELECT
  pid,
  now() - query_start AS duration,
  state,
  query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

# long-running transactions (block vacuum)
SELECT
  pid,
  now() - xact_start AS transaction_duration,
  state,
  query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

# table sizes (find bloat candidates)
SELECT
  relname,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  n_live_tup, n_dead_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

# index usage (find unused indexes)
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
17

Particionamento

Particionamento por Intervalo

O particionamento por intervalo é ideal para dados de série temporal. PostgreSQL roteia automaticamente as inserções para a partição correta e poda partições nas consultas (só varre as relevantes). Crie uma partição DEFAULT para capturar dados fora do intervalo (ou configure um trabalho de particionamento para criar partições futuras). Cada partição é uma tabela separada — pode gerenciá-las independentemente (descartar dados velhos com DROP TABLE em vez de DELETE lento). Sub-particionamento (intervalo depois hash) funciona para datasets muito grandes. Automatize a criação de partições com a extensão pg_partman.

postgresql
# partition by date range (most common for time-series)
CREATE TABLE orders (
  id          bigserial,
  created_at  timestamptz NOT NULL,
  user_id     integer NOT NULL,
  amount      numeric
) PARTITION BY RANGE (created_at);

# create monthly partitions
CREATE TABLE orders_2025_01 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE orders_2025_02 PARTITION OF orders
  FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

# create a default partition (catches anything outside range)
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

# insert (automatically routed to correct partition)
INSERT INTO orders (created_at, user_id, amount)
VALUES ('2025-01-15', 1, 100.00);  -- goes to orders_2025_01

# query (partition pruning — only scans relevant partitions)
SELECT * FROM orders WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01';

# sub-partition (e.g., range by date, then hash by user)
CREATE TABLE orders_2025_01 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01')
  PARTITION BY HASH (user_id);

Particionamento por Lista

O particionamento por lista é para valores discretos (regiões, categorias, IDs de inquilino). Cada partição guarda linhas para valores específicos. Como o particionamento por intervalo, PostgreSQL poda partições nas consultas. Ótimo para apps multi-inquilino — os dados de cada inquilino ficam isolados, podem ser backup/restaurados independentemente e movidos para armazenamento diferente. Use DEFAULT para valores inesperados. Para adicionar uma partição nova a um valor novo, CREATE TABLE ... FOR VALUES IN (...). Desanexe uma partição com ALTER TABLE ... DETACH PARTITION (útil para arquivar).

postgresql
# partition by discrete values (e.g., region, category)
CREATE TABLE users (
  id       serial,
  name     text,
  region   text NOT NULL
) PARTITION BY LIST (region);

CREATE TABLE users_us PARTITION OF users
  FOR VALUES IN ('US', 'CA');

CREATE TABLE users_eu PARTITION OF users
  FOR VALUES IN ('UK', 'DE', 'FR', 'ES');

CREATE TABLE users_apac PARTITION OF users
  FOR VALUES IN ('CN', 'JP', 'AU', 'SG');

CREATE TABLE users_other PARTITION OF users DEFAULT;

# insert routes automatically
INSERT INTO users (name, region) VALUES ('Alice', 'US');  -- users_us

# query prunes to relevant partition
SELECT * FROM users WHERE region = 'JP';  -- only scans users_apac

# multi-region apps benefit: partition by tenant
CREATE TABLE tenant_data (
  id          serial,
  tenant_id   integer NOT NULL,
  data        jsonb
) PARTITION BY LIST (tenant_id);

CREATE TABLE tenant_1 PARTITION OF tenant_data FOR VALUES IN (1);
CREATE TABLE tenant_2 PARTITION OF tenant_data FOR VALUES IN (2);

Particionamento por Hash

O particionamento por hash distribui linhas uniformemente entre partições hasheando a chave de partição — ótimo para distribuir carga. Consultas que filtram pela chave de partição (WHERE user_id = 42) podam para uma partição; consultas de intervalo (WHERE user_id > 100) varrem todas as partições. O número de partições é fixo na criação (modulus deve ser potência de 2) — adicionar mais depois exige recriar a tabela. Escolha a contagem de partições com base no tamanho esperado dos dados. Use hash quando precisar de distribuição uniforme e sempre consultar pela chave de partição.

postgresql
# partition by hash (distribute evenly)
CREATE TABLE events (
  id          bigserial,
  user_id     integer NOT NULL,
  event_type  text,
  data        jsonb
) PARTITION BY HASH (user_id);

# create 4 partitions (modulus 4, remainder 0-3)
CREATE TABLE events_0 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_1 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE events_2 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE events_3 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 3);

# insert distributes by hash of user_id
INSERT INTO events (user_id, event_type) VALUES (42, 'login');  -- routes to a partition

# query by user_id prunes to one partition
SELECT * FROM events WHERE user_id = 42;

# note: range queries on user_id scan ALL partitions
SELECT * FROM events WHERE user_id > 100;  -- scans all 4

# adding partitions later requires recreating (modulus must be power of 2)
# plan partition count upfront!

Manutenção de Partições

DETACH PARTITION converte uma partição numa tabela independente — perfeito para arquivar dados velhos sem travar o pai. ATTACH PARTITION adiciona uma tabela existente como partição nova (exige schema e restrições correspondentes). Use tablespaces para armazenamento em camadas (mover partições velhas a discos mais baratos). pg_partman automatiza a criação e manutenção de partições — essencial para séries temporais. A exclusão por restrição (definida como 'partition' por padrão) é o mecanismo de poda de partições. Sempre planeje a criação de partições com antecedência — ficar sem partições futuras causa falhas de INSERT.

postgresql
# detach a partition (keep data, remove from parent)
ALTER TABLE orders DETACH PARTITION orders_2024_01;
-- now orders_2024_01 is a standalone table

# detach and archive
ALTER TABLE orders DETACH PARTITION orders_2024_01;
ALTER TABLE orders_2024_01 RENAME TO orders_archive_2024_01;
-- move to cheaper storage, export, etc.

# attach an existing table as a partition
CREATE TABLE orders_2025_03 (LIKE orders);
ALTER TABLE orders ATTACH PARTITION orders_2025_03
  FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

# move partition to different tablespace (tiered storage)
CREATE TABLESPACE cold_storage LOCATION '/mnt/cold';
ALTER TABLE orders_2024_01 SET TABLESPACE cold_storage;

# create future partitions automatically (pg_partman extension)
CREATE EXTENSION pg_partman;
SELECT partman.create_parent('public.orders', 'created_at', 'native', 'monthly');
-- pg_partman auto-creates future partitions

# constraint exclusion (older partitioning, avoid for new work)
SET constraint_exclusion = on;  -- default 'partition' is usually fine

# check partition info
SELECT
  inhrelid::regclass AS partition,
  pg_get_expr(c.relpartbound, c.oid) AS partition_bounds
FROM pg_inherits
JOIN pg_class c ON inhrelid = c.oid
WHERE inhparent = 'orders'::regclass;

Estratégia de Particionamento

Particione quando as tabelas ficarem grandes (>100GB) ou quando precisar de ciclo de vida rápido de dados (descartar partições velhas em vez de DELETE). Escolha a chave de partição com base no seu padrão de consulta mais comum — a poda de partições só funciona quando você filtra pela chave de partição. Restrições únicas em tabelas particionadas PRECISAM incluir a chave de partição (PG impõe isto). Índices em tabelas particionadas se aplicam automaticamente a todas as partições. Evite partições demais (custo de planejamento). Para séries temporais, partições mensais são um ponto ideal comum. Use pg_partman para automação.

postgresql
# when to partition:
#   - table > 100GB (hard to vacuum, slow queries)
#   - time-series data (drop old data fast)
#   - multi-tenant (isolate tenants)
#   - parallel maintenance (vacuum per partition)

# choosing partition key:
#   - most common filter in queries (enables pruning)
#   - time for time-series (created_at, ordered_at)
#   - tenant_id for multi-tenant

# choosing partition type:
#   - RANGE: time-series, ordered data
#   - LIST: discrete categories (region, tenant)
#   - HASH: even distribution, always query by key

# partition count:
#   - too few:  doesn't help (large partitions)
#   - too many: planning overhead (1000+ partitions slow planning)
#   - sweet spot: 50-500 partitions

# indexes on partitioned tables
CREATE INDEX idx_orders_user ON orders(user_id);  -- creates index on all partitions
CREATE INDEX idx_orders_created ON orders(created_at);

# unique constraint must include partition key
CREATE TABLE orders (...) PARTITION BY RANGE (created_at);
ALTER TABLE orders ADD CONSTRAINT unique_order
  UNIQUE (id, created_at);  -- must include created_at!

# foreign keys TO partitioned tables (PG12+)
ALTER TABLE order_items ADD FOREIGN KEY (order_id, order_date)
  REFERENCES orders(id, created_at);
18

Recursos Avançados

Busca de Texto Completo

PostgreSQL tem busca de texto completo embutida: tsvector (texto pré-processado), tsquery (termos de busca) e o operador @@. Índices GIN tornam as buscas rápidas. to_tsvector tokeniza e lematiza palavras (inglês, chinês etc. — configure a linguagem). ts_rank pontua os resultados; ts_headline destaca as correspondências. O tsvector_update_trigger mantém a coluna de busca automaticamente. Para busca simples, rivaliza com Elasticsearch — sem serviço externo. Para busca complexa em escala, considere Elasticsearch/OpenSearch, mas comece com o FTS do PostgreSQL.

postgresql
# create a search column (tsvector)
ALTER TABLE articles ADD COLUMN search_vector tsvector;

UPDATE articles SET search_vector =
  to_tsvector('english', title || ' ' || body);

# index the search vector (GIN)
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

# search
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgres & index');

# match any word (OR)
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('postgres | mysql');

# phrase search
SELECT * FROM articles
WHERE search_vector @@ phraseto_tsquery('full text search');

# rank results
SELECT
  title,
  ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('postgres') query
WHERE search_vector @@ query
ORDER BY rank DESC;

# highlight matches
SELECT
  ts_headline('english', body, query) AS highlighted
FROM articles, to_tsquery('postgres') query
WHERE search_vector @@ query;

# auto-update search_vector with a trigger
CREATE TRIGGER articles_search_update
  BEFORE INSERT OR UPDATE ON articles
  FOR EACH ROW EXECUTE FUNCTION
  tsvector_update_trigger(search_vector, 'pg_catalog.english', title, body);

Consultas Avançadas JSONB

Expressões de caminho JSONB (PG12+) usam a linguagem de caminho SQL/JSON ($) para consultas poderosas — filtros, predicados e extração numa única expressão. jsonb_agg e jsonb_build_object constroem JSON a partir de dados relacionais — ótimo para respostas de API. O índice GIN jsonb_path_ops é menor e mais rápido que o GIN padrão, mas só suporta @> e operadores de caminho (@?, @@). Use jsonb_path_query para extrair elementos de array que casem e jsonb_agg para construir arrays JSON a partir de linhas — perfeito para gerar respostas de API direto do SQL sem ORM.

postgresql
# JSONB path queries
SELECT data#>>'{address,city}' FROM users;  -- text at path

# JSON Path (PG12+) — powerful JSON querying
SELECT * FROM products
WHERE data @? '$.tags[*] ? (@ == "sale")';

# find products with price > 100 in JSON
SELECT * FROM products
WHERE data @? '$.price ? (@ > 100)';

# extract values with JSON Path
SELECT jsonb_path_query(data, '$.tags[*]') FROM products;

# JSONB aggregation (build JSON from rows)
SELECT
  jsonb_agg(jsonb_build_object('id', id, 'name', name)) AS products
FROM products
WHERE category = 'electronics';

# group by JSON field
SELECT
  data->>'category' AS category,
  count(*) AS count
FROM products
GROUP BY data->>'category'
ORDER BY count DESC;

# JSONB containment and existence
SELECT * FROM products WHERE data @> '{"color": "red"}';
SELECT * FROM products WHERE data ? 'discount';
SELECT * FROM products WHERE data ?| ARRAY['discount', 'sale'];
SELECT * FROM products WHERE data ?& ARRAY['discount', 'sale'];

# JSONB with GIN index for fast queries
CREATE INDEX idx_products_data ON products USING GIN (data jsonb_path_ops);
# jsonb_path_ops: smaller index, supports @> and path operators

Geoespacial com PostGIS

PostGIS transforma PostgreSQL num banco espacial completo — adicione com CREATE EXTENSION. Use geography para coordenadas GPS (lat/lng) e geometry para coordenadas projetadas. ST_DWithin acha pontos dentro de uma distância; ST_DDistance mede com precisão. O operador <-> habilita consultas KNN (vizinho mais próximo) que usam o índice GiST com eficiência. Sempre crie um índice GiST em colunas geometry/geography. Note que ST_MakePoint recebe (lng, lat) — longitude primeiro! Para cargas geoespaciais sérias, PostGIS rivaliza com sistemas GIS dedicados.

postgresql
-- enable the PostGIS extension
CREATE EXTENSION IF NOT EXISTS postgis;

-- create a table with geography (lat/lng)
CREATE TABLE places (
  id serial PRIMARY KEY,
  name text,
  location geography(POINT, 4326)
);

-- insert a point (lng, lat — note the order!)
INSERT INTO places (name, location)
VALUES ('Eiffel Tower', ST_MakePoint(2.2945, 48.8584)::geography);

-- find places within 5 km
SELECT name, ST_Distance(location, ST_MakePoint(2.3522, 48.8566)::geography) AS meters
FROM places
WHERE ST_DWithin(location, ST_MakePoint(2.3522, 48.8566)::geography, 5000);

-- bounding box query (uses GiST index)
SELECT name FROM places
WHERE location && ST_MakeEnvelope(2.2, 48.8, 2.4, 48.9, 4326);

-- find the nearest 10 places (KNN query)
SELECT name, location <-> ST_MakePoint(2.35, 48.85)::geography AS dist
FROM places
ORDER BY dist
LIMIT 10;

-- compute the area of a polygon (in square meters)
SELECT ST_Area(ST_GeomFromText(
  'POLYGON((2.3 48.8, 2.4 48.8, 2.4 48.9, 2.3 48.9, 2.3 48.8))', 4326)::geography
) AS sq_meters;

Foreign Data Wrappers (FDW)

Foreign Data Wrappers (FDW) permitem consultar fontes de dados externas como se fossem tabelas locais. postgres_fdw conecta a outros servidores PostgreSQL; file_fdw lê arquivos CSV. Outros FDWs existem para MySQL, Oracle, SQLite, APIs REST etc. As consultas são empurradas ao servidor remoto quando possível (WHERE, JOIN), mas joins entre servidores buscam dados localmente. FDW é ótimo para integração de dados, migrações e consultas federadas sem ETL. O desempenho é mais lento que tabelas locais, pelo custo de rede.

postgresql
-- enable the postgres_fdw extension (foreign PostgreSQL)
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- create a foreign server
CREATE SERVER foreign_db
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'remote.host', dbname 'remotedb', port '5432');

-- map a local user to the foreign server
CREATE USER MAPPING FOR local_user
  SERVER foreign_db
  OPTIONS (user 'remote_user', password 'secret');

-- import a schema from the foreign server
IMPORT FOREIGN SCHEMA public
  FROM SERVER foreign_db
  INTO remote_schema;

-- query a foreign table like a local one
SELECT * FROM remote_schema.users WHERE active = true;

-- file_fdw: read CSV files as tables
CREATE EXTENSION file_fdw;
CREATE SERVER csv_server FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE logs (
  id int, level text, message text, ts timestamp
) SERVER csv_server
  OPTIONS (filename '/var/log/app.csv', format 'csv', header 'true');

-- join local and remote data
SELECT l.id, u.name FROM local_orders l
  JOIN remote_schema.users u ON l.user_id = u.id;

Travas de Aviso e Concorrência

Travas de aviso são travas definidas pela aplicação, não ligadas a nenhuma linha de tabela — perfeitas para coordenação. Travas de sessão persistem até serem liberadas explicitamente ou a sessão acabar; travas de transação se liberam automaticamente em COMMIT/ROLLBACK. pg_try_advisory_lock é não bloqueante — ótimo para padrões 'só um worker'. Combine com FOR UPDATE SKIP LOCKED para processamento seguro de fila de trabalho. Travas são identificadas por bigint ou dois ints. Sempre pareie lock com unlock nos caminhos de erro. Travas de aviso são a espinha dorsal de sistemas seguros de trabalho em segundo plano concorrente no PostgreSQL.

postgresql
-- acquire a session-level advisory lock (held until session ends)
SELECT pg_advisory_lock(12345);

-- release it
SELECT pg_advisory_unlock(12345);

-- transaction-level lock (auto-released on COMMIT/ROLLBACK)
BEGIN;
SELECT pg_advisory_xact_lock(67890);
-- critical section here
COMMIT;  -- lock auto-released

-- try-lock (non-blocking, returns true/false)
SELECT pg_try_advisory_lock(12345);  -- true if acquired, false if held

-- two-key variant (namespace + id)
SELECT pg_advisory_lock(100, 42);

-- use case: ensure only one worker runs a job
SELECT CASE
  WHEN pg_try_advisory_lock(999) THEN run_job()
  ELSE 'skipped: another worker is running'
END;

-- use case: per-row processing lock
SELECT id, pg_advisory_lock(hashtext(id::text))
FROM tasks WHERE status = 'pending'
LIMIT 10 FOR UPDATE SKIP LOCKED;
19

Extensões

Gestão de Extensões

Extensões adicionam funcionalidade além do PostgreSQL central — instale com CREATE EXTENSION. pg_available_extensions mostra o que está instalado no disco; pg_extension mostra o que está carregado. Extensões precisam ser instaladas por banco. Algumas (como pg_stat_statements) também precisam de uma entrada de config em shared_preload_libraries. CASCADE descarta objetos dependentes. Sempre fixe versões de extensão em produção, por reprodutibilidade. Extensões são uma força-chave do PostgreSQL — transformam-no numa plataforma, não só um banco.

postgresql
-- list all available extensions
SELECT * FROM pg_available_extensions ORDER BY name;

-- list installed extensions
SELECT * FROM pg_extension;

-- install an extension (requires superuser or privileges)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- install in a specific schema
CREATE EXTENSION postgis SCHEMA geo;

-- update an extension to a new version
ALTER EXTENSION pg_stat_statements UPDATE TO '1.10';

-- remove an extension
DROP EXTENSION IF EXISTS postgis CASCADE;

-- check extension versions
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL;

pg_stat_statements (Profiling de Consultas)

pg_stat_statements é a extensão PostgreSQL mais valiosa — registra estatísticas de execução para cada consulta. Ache consultas lentas por total_exec_time (impacto) ou mean_exec_time (custo por consulta). Linhas/chamada alto sugere índices ausentes. A extensão normaliza consultas (trocando literais por $1), então consultas similares se agregam. Resete as estatísticas após deploys para acompanhar desempenho fresco. Esta é a primeira ferramenta a recorrer ao otimizar — mostra exatamente para onde vai o tempo.

postgresql
-- enable in postgresql.conf first:
-- shared_preload_libraries = 'pg_stat_statements'
-- then restart PostgreSQL

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- find the slowest queries by total time
SELECT
  query,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round(max_exec_time::numeric, 2) AS max_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- find queries with highest average time
SELECT query, calls, round(mean_exec_time, 2) AS avg_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- find queries that scan the most rows
SELECT query, rows, calls, rows / calls AS avg_rows
FROM pg_stat_statements
ORDER BY rows DESC
LIMIT 10;

-- reset stats (e.g., after a deploy)
SELECT pg_stat_statements_reset();

pgcrypto (Criptografia e Hash)

pgcrypto fornece hashing (digest, HMAC), geração de UUID (gen_random_uuid) e criptografia (PGP simétrica/assimétrica). gen_random_uuid() é a forma padrão de gerar UUID v4 (embutida desde PG13, mas pgcrypto a fornece para versões antigas). Para armazenamento de senhas, prefira bcrypt ou argon2 no código da aplicação — o hashing do pgcrypto é para integridade de dados, não armazenamento de senhas. Criptografia PGP serve para criptografar colunas em repouso. Sempre gerencie chaves de criptografia fora do banco em produção.

postgresql
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- hash a password with SHA-256
SELECT encode(digest('mypassword', 'sha256'), 'hex');

-- generate a UUID v4
SELECT gen_random_uuid();

-- HMAC (keyed hashing)
SELECT encode(hmac('message', 'secretkey', 'sha256'), 'hex');

-- encrypt data with PGP symmetric encryption
SELECT armor(pgp_sym_encrypt('sensitive data', 'my_passphrase'))
  AS encrypted_blob;

-- decrypt data
SELECT pgp_sym_decrypt(
  dearmor('-----BEGIN PGP MESSAGE-----...-----END PGP MESSAGE-----'),
  'my_passphrase'
) AS decrypted;

-- store encrypted columns
CREATE TABLE secrets (
  id serial PRIMARY KEY,
  data bytea  -- store pgp_sym_encrypt() output here
);

INSERT INTO secrets (data)
VALUES (pgp_sym_encrypt('credit card number', 'passphrase'));

UUID e Geração de IDs

Use gen_random_uuid() (PG13+) para UUIDs aleatórios — embutido e sem exigir extensão. uuid-ossp fornece UUIDs v1 (baseado em tempo) e v5 (namespace), se necessário. Para inteiros auto-incrementáveis, prefira GENERATED ALWAYS AS IDENTITY (PG10+) a serial — é padrão SQL, impede inserções manuais acidentais e trata permissões limpo. RETURNING id obtém o ID inserido numa única ida e volta. UUIDs são ótimos para sistemas distribuídos; colunas identity são mais simples para apps de nó único.

postgresql
-- built-in UUID v4 (PostgreSQL 13+)
SELECT gen_random_uuid();

-- using uuid-ossp extension (for other UUID versions)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v1();  -- time-based
SELECT uuid_generate_v4();  -- random
SELECT uuid_generate_v5('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'name'); -- namespace+name

-- UUID primary key
CREATE TABLE events (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  data jsonb,
  created_at timestamptz DEFAULT now()
);

-- identity columns (PostgreSQL 10+, preferred over serial)
CREATE TABLE users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);

-- compare: serial vs identity
-- serial: old, separate sequence, can be overridden
-- identity: SQL standard, tied to table, cleaner

-- get the last inserted ID
INSERT INTO users (name) VALUES ('Alice') RETURNING id;

Catálogo de Extensões Úteis

O ecossistema de extensões do PostgreSQL é vasto. pg_trgm habilita casamento difuso de texto (operador %) e consultas LIKE rápidas. PostGIS adiciona capacidades geoespaciais. pg_partman automatiza a criação de partições para séries temporais. TimescaleDB e Citus são extensões de terceiros para séries temporais e escala horizontal, respectivamente. hstore é legado — use jsonb. unaccent remove diacríticos para busca internacionalizada. Navegue pelas extensões disponíveis com pg_available_extensions e confira o PGXN (PostgreSQL Extension Network) para extensões da comunidade.

postgresql
-- PostGIS: geospatial data
CREATE EXTENSION postgis;

-- pg_trgm: trigram fuzzy text search
CREATE EXTENSION pg_trgm;
SELECT * FROM users WHERE name % 'Jon';  -- fuzzy match
CREATE INDEX idx_users_name_trgm ON users USING GIN (name gin_trgm_ops);

-- btree_gin / btree_gist: B-tree support in GIN/GiST
CREATE EXTENSION btree_gin;

-- hstore: key-value store (legacy, use jsonb instead)
CREATE EXTENSION hstore;

-- pg_partman: partition management
CREATE EXTENSION pg_partman;

-- timescaledb: time-series optimization (third-party)
-- citus: distributed PostgreSQL (third-party)

-- intarray: integer array operations
CREATE EXTENSION intarray;
SELECT * FROM posts WHERE tags && ARRAY[1,2,3];

-- unaccent: remove accents for search
CREATE EXTENSION unaccent;
SELECT unaccent('café');  -- 'cafe'
20

Monitoramento e Solução de Problemas

Consultas Ativas (pg_stat_activity)

pg_stat_activity é a sua visão em tempo real do que o PostgreSQL está fazendo. Confira o estado (active, idle, idle in transaction). Consultas de longa duração e sessões idle-in-transaction são problemas comuns — as últimas seguram travas e impedem o vacuum. pg_cancel_backend envia um SIGINT (a consulta cancela, a sessão sobrevive); pg_terminate_backend mata a sessão por completo. Sempre investigue antes de matar. Defina idle_in_transaction_session_timeout para matar automaticamente transações presas. Monitore as contagens de conexão para não atingir max_connections.

postgresql
-- see all active queries
SELECT
  pid,
  usename,
  application_name,
  client_addr,
  state,
  wait_event_type,
  wait_event,
  query_start,
  now() - query_start AS duration,
  query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- find long-running queries (> 5 minutes)
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '5 minutes';

-- count connections by state
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;

-- terminate a runaway query (use with caution!)
SELECT pg_cancel_backend(12345);     -- cancel the query
SELECT pg_terminate_backend(12345);  -- kill the session

-- find idle-in-transaction sessions (hold locks)
SELECT pid, usename, xact_start,
  now() - xact_start AS xact_duration,
  query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

Travas e Bloqueios

Contenção de travas é um problema comum de produção. pg_locks mostra todas as travas ativas; travas não concedidas significam que uma sessão está esperando. A consulta de cadeia de bloqueio identifica exatamente quem bloqueia quem — essencial para depurar deadlocks e consultas lentas. AccessExclusive (de ALTER TABLE, DROP, VACUUM FULL) bloqueia tudo — rode em janelas de manutenção. Use lock_timeout para impedir que sessões esperem para sempre. Transações longas são o culpado usual — mantenha as transações curtas e confirme prontamente.

postgresql
-- see all locks
SELECT
  pid,
  relation::regclass AS table_name,
  mode,
  granted,
  query
FROM pg_locks
WHERE granted = false;

-- find blocking chains (who blocks whom)
SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks ul ON ul.locktype = bl.locktype
  AND ul.relation IS NOT DISTINCT FROM bl.relation
  AND ul.granted
JOIN pg_stat_activity blocking ON ul.pid = blocking.pid;

-- lock modes: AccessShare, RowShare, RowExclusive,
--   ShareUpdateExclusive, Share, ShareRowExclusive,
--   Exclusive, AccessExclusive

-- check for table-level locks
SELECT relation::regclass, mode, pid, granted
FROM pg_locks
WHERE locktype = 'relation' AND granted = false;

-- set a lock timeout for a session
SET lock_timeout = '5s';
SET statement_timeout = '30s';

Vacuum e Inchaço

PostgreSQL usa MVCC — UPDATE/DELETE criam tuplas mortas que o VACUUM recupera. Autovacuum roda automaticamente, mas pode precisar de ajuste para tabelas grandes ou de muita escrita. n_dead_tup mostra a limpeza pendente; um dead_pct alto significa que o autovacuum não está acompanhando. VACUUM FULL recupera espaço em disco, mas toma uma trava AccessExclusive — use pg_repack para remover inchaço online. Transações longas bloqueiam o vacuum (podem ver linhas velhas), causando inchaço e risco de wraparound de ID de transação. Monitore xid_age para prevenir wraparound (falha forçada perto de 2 bilhões).

postgresql
-- check table bloat (dead tuples)
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct,
  last_autovacuum,
  last_manual_vacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- manual vacuum (analyze updates planner stats)
VACUUM ANALYZE users;
VACUUM FULL users;  -- reclaims space, locks table!

-- check autovacuum settings
SHOW autovacuum;
SHOW autovacuum_vacuum_threshold;
SHOW autovacuum_vacuum_scale_factor;

-- tune autovacuum per table
ALTER TABLE big_table SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02
);

-- check if any transactions prevent vacuum
SELECT pid, age(clock_timestamp(), xact_start) AS xact_age, query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')
ORDER BY xact_age DESC;

-- check wraparound risk (transaction ID age)
SELECT
  relname,
  age(relfrozenxid) AS xid_age
FROM pg_class
WHERE relkind IN ('r', 't', 'm')
ORDER BY xid_age DESC LIMIT 10;

Análise de Logs

O logging é configurado em postgresql.conf. log_min_duration_statement registra consultas lentas (0 para todas, 100 para >100ms). log_lock_waits captura contenção de travas. Logging CSV permite consultar logs como tabela estrangeira (file_fdw). Prefixos comuns: %t (timestamp), %p (PID), %u (usuário), %d (banco). Para produção, use um coletor de logs como pgBadger para analisar e visualizar logs. Não registre toda consulta em produção (custo de I/O) — use pg_stat_statements para análise agregada em vez disso.

postgresql
-- in postgresql.conf, enable slow query logging:
-- log_min_duration_statement = 100  -- log queries > 100ms
-- log_line_prefix = '%t [%p] %u@%d '
-- log_lock_waits = on
-- log_temp_files = 0
-- log_autovacuum_min_duration = 0

-- check current log settings
SHOW log_min_duration_statement;
SHOW log_destination;

-- view server logs (if using CSV logging)
CREATE FOREIGN TABLE pg_log (
  log_time timestamp,
  user_name text,
  database_name text,
  process_id int,
  connection_from text,
  session_id text,
  session_line_num bigint,
  command_tag text,
  session_start_time timestamp,
  virtual_transaction_id text,
  transaction_id bigint,
  error_severity text,
  sql_state_code text,
  message text,
  detail text,
  hint text,
  internal_query text,
  internal_query_pos int,
  context text,
  query text,
  query_pos int,
  location text,
  file_name text,
  file_line_num int,
  application_name text
) SERVER pglog
  OPTIONS (filename '/var/log/postgresql/postgres.csv', format 'csv');

-- query logs with SQL
SELECT log_time, error_severity, message
FROM pg_log
WHERE error_severity = 'ERROR'
ORDER BY log_time DESC LIMIT 20;

Checagens de Saúde e Métricas

Métricas-chave de saúde: taxa de acerto do cache (mire >99% — se baixa, aumente shared_buffers), razão de uso de índices (baixa significa índices ausentes ou consultas ruins), tamanhos de tabelas (capture inchaço cedo) e contagens de conexões. pg_stat_user_tables e pg_statio_user_tables são minas de ouro de dados operacionais. Configure monitoramento com Prometheus + postgres_exporter para dashboards. Fique atento a: tuplas mortas crescendo, taxa de acerto do cache declinando, conexões aumentando e tabelas crescendo mais rápido que o esperado. Checagens regulares de saúde previnem emergências de madrugada.

postgresql
-- database size
SELECT pg_size_pretty(pg_database_size('mydb'));

-- table sizes (with indexes)
SELECT
  relname,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  pg_size_pretty(pg_relation_size(relid)) AS table_size,
  pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

-- cache hit ratio (should be > 99%)
SELECT
  sum(heap_blks_hit) AS hits,
  sum(heap_blks_read) AS reads,
  round(sum(heap_blks_hit)::numeric /
    NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100, 2) AS hit_ratio_pct
FROM pg_statio_user_tables;

-- index usage ratio
SELECT
  sum(idx_scan) AS index_scans,
  sum(seq_scan) AS seq_scans,
  round(sum(idx_scan)::numeric / NULLIF(sum(idx_scan) + sum(seq_scan), 0) * 100, 2) AS index_usage_pct
FROM pg_stat_user_tables;

-- connection stats
SELECT count(*) AS total,
  count(*) FILTER (WHERE state = 'active') AS active,
  count(*) FILTER (WHERE state = 'idle') AS idle,
  count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_txn
FROM pg_stat_activity;

-- uptime and version
SELECT version();
SELECT pg_postmaster_start_time();

Was this helpful?