PostgreSQL

Tipos de Dados

Numéricos

-- Inteiros
smallint        -- 2 bytes, -32768 a 32767
integer / int   -- 4 bytes, -2.1B a 2.1B
bigint          -- 8 bytes, -9.2 quintilhões a 9.2 quintilhões

-- Ponto flutuante (inexato)
real            -- 4 bytes, ~6 dígitos de precisão
double precision -- 8 bytes, ~15 dígitos

-- Numérico exato (ideal para dinheiro)
numeric(12, 2)  -- até 12 dígitos, 2 decimais
decimal(10, 4)  -- alias de numeric
money           -- evitar: formatação dependente de locale

-- Sequências / auto-incremento
-- Recomendado (SQL padrão):
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
-- Legado (ainda funcional):
id bigserial PRIMARY KEY  -- expande para bigint + sequence + default

Texto

char(n)         -- comprimento fixo, padding com espaços
varchar(n)      -- comprimento variável com limite
text            -- comprimento variável ilimitado (preferido no PG)

-- Collation por coluna
name varchar(100) COLLATE "pt_BR.utf8"

-- Full-text
tsvector        -- documento pré-processado
tsquery         -- expressão de busca

Temporais

date            -- YYYY-MM-DD
time            -- HH:MM:SS[.ssssss]
timetz          -- time with time zone (evitar)
timestamp       -- sem time zone (local)
timestamptz     -- com time zone (recomendado)
interval        -- duração: '3 hours 30 minutes', '2 years 1 month'

-- Exemplos
SELECT now()::date;
SELECT now() AT TIME ZONE 'America/Sao_Paulo';
SELECT '2024-01-01'::timestamptz + interval '90 days';
SELECT EXTRACT(year FROM now());
SELECT DATE_TRUNC('month', created_at);

UUID

-- Requer extensão uuid-ossp ou pgcrypto
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();

-- PG 13+ nativo
SELECT gen_random_uuid();

id uuid DEFAULT gen_random_uuid() PRIMARY KEY

Arrays

CREATE TABLE products (
  id    int PRIMARY KEY,
  tags  text[]
);

INSERT INTO products VALUES (1, ARRAY['electronics', 'sale']);
INSERT INTO products VALUES (2, '{"books","education"}');

SELECT tags[1] FROM products;           -- acesso por índice (base 1)
SELECT * FROM products WHERE 'sale' = ANY(tags);
SELECT * FROM products WHERE tags @> ARRAY['books'];
SELECT unnest(tags) FROM products;      -- expande array em linhas

JSON / JSONB

-- json: armazenamento exato do texto; jsonb: binário, indexável (preferido)
CREATE TABLE orders (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  metadata jsonb
);

INSERT INTO orders (metadata) VALUES ('{"status": "paid", "items": [{"sku": "ABC", "qty": 2}]}');

Enum

CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');

CREATE TABLE orders (
  status order_status DEFAULT 'pending'
);

-- Adicionar valor (não remove!)
ALTER TYPE order_status ADD VALUE 'refunded' AFTER 'delivered';

GENERATED ALWAYS AS IDENTITY vs SERIAL

DDL

CREATE SCHEMA app;

CREATE TABLE app.users (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      varchar(255) NOT NULL UNIQUE,
  name       varchar(100) NOT NULL,
  role       varchar(50)  NOT NULL DEFAULT 'customer' CHECK (role IN ('admin','customer','vendor')),
  created_at timestamptz  NOT NULL DEFAULT now(),
  deleted_at timestamptz
);

CREATE TABLE app.products (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku         varchar(50)    NOT NULL UNIQUE,
  name        text           NOT NULL,
  price       numeric(12, 2) NOT NULL CHECK (price >= 0),
  stock       int            NOT NULL DEFAULT 0 CHECK (stock >= 0)
);

CREATE TABLE app.orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id     bigint         NOT NULL REFERENCES app.users(id) ON DELETE RESTRICT,
  total       numeric(12, 2) NOT NULL CHECK (total >= 0),
  status      varchar(50)    NOT NULL DEFAULT 'pending',
  metadata    jsonb,
  created_at  timestamptz    NOT NULL DEFAULT now()
);

CREATE TABLE app.order_items (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id   bigint         NOT NULL REFERENCES app.orders(id) ON DELETE CASCADE,
  product_id bigint         NOT NULL REFERENCES app.products(id) ON DELETE RESTRICT,
  quantity   int            NOT NULL CHECK (quantity > 0),
  unit_price numeric(12, 2) NOT NULL
);

-- ALTER TABLE
ALTER TABLE app.users ADD COLUMN phone varchar(20);
ALTER TABLE app.users ALTER COLUMN phone SET NOT NULL;  -- requer UPDATE antes se já há linhas
ALTER TABLE app.users ALTER COLUMN phone DROP NOT NULL;
ALTER TABLE app.users RENAME COLUMN phone TO phone_number;
ALTER TABLE app.users DROP COLUMN phone_number;

ALTER TABLE app.orders ADD CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES app.users(id) ON DELETE RESTRICT;

ALTER TABLE app.products ADD CONSTRAINT uq_products_sku UNIQUE (sku);

-- Renomear
ALTER TABLE app.users RENAME TO members;
ALTER TABLE app.members RENAME TO users;

-- DROP
DROP TABLE IF EXISTS app.order_items CASCADE;
DROP SCHEMA IF EXISTS app CASCADE;

DML

-- INSERT simples
INSERT INTO app.users (email, name, role)
VALUES ('alice@example.com', 'Alice', 'admin');

-- INSERT multi-row
INSERT INTO app.products (sku, name, price, stock) VALUES
  ('SKU-001', 'Teclado Mecânico', 349.90, 50),
  ('SKU-002', 'Mouse Gamer',      199.50, 120),
  ('SKU-003', 'Monitor 27"',     1899.00, 20);

-- UPSERT (ON CONFLICT)
INSERT INTO app.products (sku, name, price, stock)
VALUES ('SKU-001', 'Teclado Mecânico Pro', 399.90, 60)
ON CONFLICT (sku) DO UPDATE SET
  name  = EXCLUDED.name,
  price = EXCLUDED.price,
  stock = app.products.stock + EXCLUDED.stock;

-- ON CONFLICT DO NOTHING
INSERT INTO app.users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO NOTHING;

-- UPDATE
UPDATE app.products
SET price = price * 1.10,
    stock = stock - 5
WHERE sku = 'SKU-002';

-- UPDATE com JOIN (usando subquery ou FROM)
UPDATE app.order_items oi
SET unit_price = p.price
FROM app.products p
WHERE oi.product_id = p.id
  AND oi.order_id = 42;

-- DELETE
DELETE FROM app.orders
WHERE status = 'cancelled'
  AND created_at < now() - interval '1 year';

-- RETURNING — retorna linhas afetadas
INSERT INTO app.orders (user_id, total)
VALUES (1, 549.40)
RETURNING id, created_at;

UPDATE app.products
SET stock = stock - 1
WHERE id = 3
RETURNING id, stock;

DELETE FROM app.users
WHERE deleted_at IS NOT NULL
RETURNING id, email;

-- TRUNCATE (muito mais rápido que DELETE sem WHERE)
TRUNCATE app.order_items, app.orders RESTART IDENTITY CASCADE;

SELECT e Filtering

-- WHERE básico
SELECT id, name, price
FROM app.products
WHERE price BETWEEN 100 AND 500
  AND stock > 0;

-- IN / NOT IN
SELECT * FROM app.orders
WHERE status IN ('pending', 'processing');

SELECT * FROM app.users
WHERE id NOT IN (SELECT user_id FROM app.orders WHERE status = 'cancelled');
-- Atenção: NOT IN com NULL retorna vazio — use NOT EXISTS

-- LIKE / ILIKE (case-insensitive)
SELECT * FROM app.products WHERE name ILIKE '%teclado%';
SELECT * FROM app.products WHERE sku LIKE 'SKU-%';

-- IS NULL / IS NOT NULL
SELECT * FROM app.users WHERE deleted_at IS NULL;

-- ORDER BY
SELECT * FROM app.orders
ORDER BY total DESC, created_at ASC NULLS LAST;

-- LIMIT / OFFSET (paginação — prefira keyset em tabelas grandes)
SELECT * FROM app.products
ORDER BY id
LIMIT 20 OFFSET 40;  -- página 3, 20 por página

-- Keyset pagination (mais eficiente)
SELECT * FROM app.products
WHERE id > 40       -- último id da página anterior
ORDER BY id
LIMIT 20;

-- DISTINCT
SELECT DISTINCT status FROM app.orders;

-- DISTINCT ON (PostgreSQL-specific)
SELECT DISTINCT ON (user_id)
  user_id, id AS order_id, total, created_at
FROM app.orders
ORDER BY user_id, created_at DESC;  -- mais recente por usuário

JOINs

-- INNER JOIN
SELECT o.id, u.name, u.email, o.total
FROM app.orders o
INNER JOIN app.users u ON u.id = o.user_id
WHERE o.status = 'delivered';

-- LEFT JOIN (todos os usuários, mesmo sem pedidos)
SELECT u.id, u.name, COUNT(o.id) AS order_count
FROM app.users u
LEFT JOIN app.orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

-- RIGHT JOIN (equivalente a LEFT JOIN com tabelas trocadas)
SELECT o.id, u.name
FROM app.users u
RIGHT JOIN app.orders o ON o.user_id = u.id;

-- FULL OUTER JOIN
SELECT u.id AS user_id, o.id AS order_id
FROM app.users u
FULL OUTER JOIN app.orders o ON o.user_id = u.id;

-- CROSS JOIN (produto cartesiano)
SELECT p1.name AS a, p2.name AS b
FROM app.products p1
CROSS JOIN app.products p2
WHERE p1.id < p2.id;

-- LATERAL (permite referenciar colunas da esquerda)
SELECT u.id, u.name, recent.*
FROM app.users u
CROSS JOIN LATERAL (
  SELECT id, total, created_at
  FROM app.orders
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 3
) recent;

-- Anti-join: usuários SEM pedidos
-- Opção 1: LEFT JOIN IS NULL
SELECT u.*
FROM app.users u
LEFT JOIN app.orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- Opção 2: NOT EXISTS (geralmente mais eficiente)
SELECT u.*
FROM app.users u
WHERE NOT EXISTS (
  SELECT 1 FROM app.orders o WHERE o.user_id = u.id
);

-- Self join: encontrar usuários com o mesmo nome
SELECT a.id, a.name, b.id AS duplicate_id
FROM app.users a
JOIN app.users b ON a.name = b.name AND a.id < b.id;

Aggregações

-- Funções básicas
SELECT
  COUNT(*)                          AS total_orders,
  COUNT(DISTINCT user_id)           AS unique_customers,
  SUM(total)                        AS revenue,
  AVG(total)                        AS avg_order,
  MIN(total)                        AS min_order,
  MAX(total)                        AS max_order
FROM app.orders
WHERE status = 'delivered';

-- STRING_AGG e ARRAY_AGG
SELECT
  user_id,
  STRING_AGG(CAST(id AS text), ', ' ORDER BY created_at) AS order_ids,
  ARRAY_AGG(total ORDER BY created_at DESC)              AS totals
FROM app.orders
GROUP BY user_id;

-- GROUP BY e HAVING
SELECT
  status,
  COUNT(*)       AS qty,
  SUM(total)     AS revenue
FROM app.orders
GROUP BY status
HAVING COUNT(*) > 5
ORDER BY revenue DESC;

-- FILTER (aggregate condicional)
SELECT
  COUNT(*) FILTER (WHERE status = 'delivered')   AS delivered,
  COUNT(*) FILTER (WHERE status = 'cancelled')   AS cancelled,
  COUNT(*) FILTER (WHERE status = 'pending')     AS pending,
  SUM(total) FILTER (WHERE status = 'delivered') AS delivered_revenue
FROM app.orders;

-- GROUPING SETS — múltiplos agrupamentos em uma query
SELECT
  status,
  DATE_TRUNC('month', created_at) AS month,
  SUM(total) AS revenue
FROM app.orders
GROUP BY GROUPING SETS (
  (status),
  (DATE_TRUNC('month', created_at)),
  ()  -- grand total
);

-- ROLLUP — subtotais hierárquicos
SELECT
  EXTRACT(year  FROM created_at) AS year,
  EXTRACT(month FROM created_at) AS month,
  SUM(total) AS revenue
FROM app.orders
GROUP BY ROLLUP (
  EXTRACT(year FROM created_at),
  EXTRACT(month FROM created_at)
)
ORDER BY year, month;

Window Functions

-- ROW_NUMBER, RANK, DENSE_RANK
SELECT
  id,
  user_id,
  total,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) AS rn,
  RANK()       OVER (PARTITION BY user_id ORDER BY total DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY user_id ORDER BY total DESC) AS dense_rnk
FROM app.orders;

-- Top N por grupo (top 2 pedidos de cada usuário)
SELECT *
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) AS rn
  FROM app.orders
) sub
WHERE rn <= 2;

-- LAG e LEAD (acessar linha anterior / próxima)
SELECT
  id,
  created_at,
  total,
  LAG(total)  OVER (PARTITION BY user_id ORDER BY created_at) AS prev_order_total,
  LEAD(total) OVER (PARTITION BY user_id ORDER BY created_at) AS next_order_total,
  total - LAG(total) OVER (PARTITION BY user_id ORDER BY created_at) AS delta
FROM app.orders;

-- FIRST_VALUE e LAST_VALUE
SELECT
  id,
  user_id,
  total,
  FIRST_VALUE(total) OVER w AS first_order_total,
  LAST_VALUE(total)  OVER w AS last_order_total
FROM app.orders
WINDOW w AS (
  PARTITION BY user_id
  ORDER BY created_at
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

-- Média móvel (rolling average — últimas 3 linhas)
SELECT
  id,
  created_at,
  total,
  AVG(total) OVER (
    PARTITION BY user_id
    ORDER BY created_at
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS rolling_avg_3
FROM app.orders;

-- ROWS vs RANGE
-- ROWS: conta linhas físicas
-- RANGE: considera linhas com mesmo valor de ORDER BY (padrão quando não especificado)

-- Percentil / Ntile
SELECT
  id, total,
  NTILE(4) OVER (ORDER BY total) AS quartile,
  PERCENT_RANK() OVER (ORDER BY total) AS pct_rank,
  CUME_DIST()    OVER (ORDER BY total) AS cume_dist
FROM app.orders;

CTEs

-- CTE simples
WITH high_value_orders AS (
  SELECT *
  FROM app.orders
  WHERE total > 1000
)
SELECT u.name, hvo.total
FROM high_value_orders hvo
JOIN app.users u ON u.id = hvo.user_id;

-- CTEs encadeadas
WITH
  delivered AS (
    SELECT user_id, SUM(total) AS revenue
    FROM app.orders
    WHERE status = 'delivered'
    GROUP BY user_id
  ),
  top_customers AS (
    SELECT user_id, revenue
    FROM delivered
    WHERE revenue > 5000
  )
SELECT u.name, tc.revenue
FROM top_customers tc
JOIN app.users u ON u.id = tc.user_id
ORDER BY tc.revenue DESC;

-- CTE Recursiva: hierarquia de categorias
CREATE TABLE categories (
  id        int PRIMARY KEY,
  name      text,
  parent_id int REFERENCES categories(id)
);

WITH RECURSIVE category_tree AS (
  -- Âncora: nós raiz
  SELECT id, name, parent_id, 0 AS depth, name::text AS path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- Parte recursiva
  SELECT c.id, c.name, c.parent_id,
         ct.depth + 1,
         ct.path || ' > ' || c.name
  FROM categories c
  JOIN category_tree ct ON ct.id = c.parent_id
)
SELECT * FROM category_tree ORDER BY path;

-- MATERIALIZED vs não-materializado (PG 12+)
WITH cte AS MATERIALIZED (        -- força materialização (calcula uma vez)
  SELECT * FROM app.orders WHERE total > 100
)
SELECT * FROM cte WHERE status = 'pending';

WITH cte AS NOT MATERIALIZED (    -- permite ao planner inline-ar (padrão PG 12+)
  SELECT * FROM app.orders WHERE total > 100
)
SELECT * FROM cte WHERE status = 'pending';

-- EXISTS vs IN vs NOT IN — pegadinha com NULL
-- NOT IN com NULL sempre retorna vazio!
SELECT * FROM app.users
WHERE id NOT IN (SELECT user_id FROM app.orders WHERE user_id IS NULL);
-- ^ Retorna 0 linhas! Se qualquer user_id for NULL, NOT IN falha.

-- Correto: use NOT EXISTS
SELECT u.*
FROM app.users u
WHERE NOT EXISTS (
  SELECT 1 FROM app.orders o WHERE o.user_id = u.id
);

-- EXISTS vs IN: EXISTS para de varrer ao primeiro match (mais eficiente em grandes datasets)
SELECT * FROM app.users u
WHERE EXISTS (
  SELECT 1 FROM app.orders o WHERE o.user_id = u.id AND o.total > 1000
);

JSON e JSONB

-- Operadores básicos
-- ->  retorna JSON (objeto ou array)
-- ->> retorna texto
-- #>  caminho como array
-- #>> caminho como array, retorna texto

SELECT
  metadata -> 'status'             AS status_json,    -- "paid"
  metadata ->> 'status'            AS status_text,    -- paid
  metadata -> 'items' -> 0         AS first_item,
  metadata -> 'items' -> 0 ->> 'sku' AS first_sku,
  metadata #> '{items,0,qty}'      AS qty_json,
  metadata #>> '{items,0,qty}'     AS qty_text
FROM app.orders;

-- Verificar existência de chave
SELECT * FROM app.orders WHERE metadata ? 'discount';
SELECT * FROM app.orders WHERE metadata ?| ARRAY['discount', 'coupon'];
SELECT * FROM app.orders WHERE metadata ?& ARRAY['status', 'items'];

-- Containment @>
SELECT * FROM app.orders
WHERE metadata @> '{"status": "paid"}';

-- jsonb_set: atualizar campo específico
UPDATE app.orders
SET metadata = jsonb_set(metadata, '{status}', '"refunded"')
WHERE id = 1;

-- Remover chave
UPDATE app.orders
SET metadata = metadata - 'temp_field';

-- Concatenar / merge
UPDATE app.orders
SET metadata = metadata || '{"reviewed": true, "score": 5}'
WHERE id = 1;

-- Expandir array JSONB em linhas
SELECT o.id, item
FROM app.orders o,
     jsonb_array_elements(o.metadata -> 'items') AS item;

-- Extrair campos do array
SELECT
  o.id,
  item ->> 'sku'    AS sku,
  (item ->> 'qty')::int AS qty
FROM app.orders o,
     jsonb_array_elements(o.metadata -> 'items') AS item;

-- jsonb_agg e jsonb_build_object
SELECT
  u.id,
  jsonb_build_object(
    'user', u.name,
    'orders', jsonb_agg(
      jsonb_build_object('id', o.id, 'total', o.total)
      ORDER BY o.created_at
    )
  ) AS summary
FROM app.users u
JOIN app.orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

-- Índice GIN para JSONB
CREATE INDEX idx_orders_metadata_gin
  ON app.orders USING GIN (metadata);

-- jsonb_path_ops: menor, otimizado para @> e @?
CREATE INDEX idx_orders_metadata_path
  ON app.orders USING GIN (metadata jsonb_path_ops);

-- jsonpath (PG 12+)
SELECT * FROM app.orders
WHERE metadata @? '$.items[*] ? (@.qty > 5)';

-- Quando usar JSONB vs tabela normalizada
-- Use JSONB para: dados semi-estruturados, esquema variável, payloads de eventos, metadados opcionais
-- Use tabela normalizada para: dados consultados frequentemente, JOINs, aggregações, integridade referencial

Índices

-- B-tree (padrão): ideal para igualdade, range, ORDER BY
CREATE INDEX idx_orders_user_id   ON app.orders (user_id);
CREATE INDEX idx_orders_status    ON app.orders (status);
CREATE INDEX idx_orders_created   ON app.orders (created_at DESC);

-- Índice composto — ORDEM IMPORTA!
-- Útil para: WHERE status = ? AND created_at > ?
-- Não útil para: WHERE created_at > ? (sem status)
CREATE INDEX idx_orders_status_created ON app.orders (status, created_at DESC);

-- Hash: igualdade exata (não suporta range/sort, evitar geralmente)
CREATE INDEX idx_users_email_hash ON app.users USING HASH (email);

-- Parcial: índica só subset de linhas (menor, mais rápido)
CREATE INDEX idx_orders_pending ON app.orders (created_at)
  WHERE status = 'pending';

CREATE INDEX idx_users_active ON app.users (email)
  WHERE deleted_at IS NULL;

-- Expressão: indexa resultado de função
CREATE INDEX idx_users_lower_email ON app.users (lower(email));
-- Uso: WHERE lower(email) = 'alice@example.com'

-- INCLUDE: colunas não-chave no índice (index-only scan)
CREATE INDEX idx_orders_user_covering
  ON app.orders (user_id, created_at)
  INCLUDE (total, status);

-- GIN: arrays, JSONB, full-text search
CREATE INDEX idx_products_tags   ON app.products USING GIN (tags);
CREATE INDEX idx_orders_metadata ON app.orders   USING GIN (metadata);

-- GiST: tipos geométricos, range types, full-text (alternativa ao GIN)
CREATE INDEX idx_events_period ON events USING GiST (tstzrange);

-- BRIN: dados fisicamente ordenados (ex: séries temporais em tabelas imutáveis)
-- Muito menor que B-tree, mas menos preciso
CREATE INDEX idx_orders_created_brin ON app.orders USING BRIN (created_at);

-- CONCURRENTLY: não trava a tabela (pode ser mais lento)
CREATE INDEX CONCURRENTLY idx_orders_total ON app.orders (total);

-- Recriar sem lock
DROP INDEX CONCURRENTLY idx_orders_total;
CREATE INDEX CONCURRENTLY idx_orders_total ON app.orders (total);

-- Inspecionar índices
SELECT indexname, indexdef FROM pg_indexes
WHERE tablename = 'orders' AND schemaname = 'app';

-- Uso dos índices
SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'orders';

EXPLAIN e Performance

-- EXPLAIN básico: mostra plano sem executar
EXPLAIN SELECT * FROM app.orders WHERE user_id = 1;

-- EXPLAIN ANALYZE: executa e mostra tempo real
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, u.name, o.total
FROM app.orders o
JOIN app.users u ON u.id = o.user_id
WHERE o.status = 'delivered'
ORDER BY o.total DESC
LIMIT 10;

-- Nós importantes no plano:
-- Seq Scan: varre tabela inteira (ruim em tabelas grandes com filtro seletivo)
-- Index Scan: usa índice, acessa heap por tid (bom)
-- Index Only Scan: usa índice sem acessar heap (ótimo)
-- Bitmap Index Scan: combina vários índices, depois acessa heap (bom para OR / baixa seletividade)
-- Hash Join: build hash de lado menor, probe com lado maior (bom para equi-joins sem índice)
-- Merge Join: requer ambos ordenados (bom com índices em chave de join)
-- Nested Loop: bom quando lado externo é pequeno

-- work_mem: aumentar para sorts e hash joins em memória
SET work_mem = '64MB';  -- por operação por conexão

-- Estatísticas
ANALYZE app.orders;  -- atualiza pg_statistic

-- pg_stat_user_tables: hit ratio, dead tuples
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
  last_vacuum,
  last_autovacuum,
  last_analyze
FROM pg_stat_user_tables
WHERE schemaname = 'app'
ORDER BY n_dead_tup DESC;

-- Cache hit ratio (ideal > 99%)
SELECT
  relname,
  heap_blks_read,
  heap_blks_hit,
  ROUND(100.0 * heap_blks_hit / NULLIF(heap_blks_hit + heap_blks_read, 0), 2) AS hit_ratio
FROM pg_statio_user_tables
WHERE schemaname = 'app'
ORDER BY heap_blks_read DESC;

-- VACUUM e AUTOVACUUM
VACUUM app.orders;             -- reclaim dead tuples
VACUUM ANALYZE app.orders;     -- + atualiza estatísticas
VACUUM FULL app.orders;        -- rewrite físico (trava tabela, libera espaço ao SO)

-- Forçar autovacuum mais agressivo para tabela específica
ALTER TABLE app.orders SET (
  autovacuum_vacuum_scale_factor = 0.01,  -- vacuuma com 1% de dead tuples
  autovacuum_analyze_scale_factor = 0.005
);

-- Queries longas em andamento
SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE state != 'idle'
  AND query_start < now() - interval '30 seconds'
ORDER BY duration DESC;

Transações

-- Básico
BEGIN;
  UPDATE app.products SET stock = stock - 1 WHERE id = 1;
  INSERT INTO app.orders (user_id, total) VALUES (1, 199.50);
COMMIT;

-- Rollback
BEGIN;
  DELETE FROM app.users WHERE id = 99;
ROLLBACK;  -- nada foi alterado

-- SAVEPOINT: rollback parcial
BEGIN;
  INSERT INTO app.orders (user_id, total) VALUES (1, 100);
  SAVEPOINT sp1;
  INSERT INTO app.order_items (order_id, product_id, quantity, unit_price)
    VALUES (currval('orders_id_seq'), 999, 1, 100);  -- produto inválido
  ROLLBACK TO SAVEPOINT sp1;
  -- order ainda existe, order_item foi revertido
COMMIT;

-- Níveis de isolamento
-- READ COMMITTED (padrão): vê commits de outras transações durante a tx
-- REPEATABLE READ: snapshot no início, evita non-repeatable reads
-- SERIALIZABLE: mais restritivo, detecta anomalias de serialização

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  SELECT * FROM app.products WHERE id = 1;
  -- mesmo se outra tx atualizar, vemos o snapshot original
COMMIT;

-- SELECT FOR UPDATE: bloqueia linhas para update (evita race condition)
BEGIN;
  SELECT * FROM app.products WHERE id = 1 FOR UPDATE;
  UPDATE app.products SET stock = stock - 1 WHERE id = 1;
COMMIT;

-- SKIP LOCKED: útil para job queues (não bloqueia, pula linhas travadas)
BEGIN;
  SELECT * FROM app.orders
  WHERE status = 'pending'
  ORDER BY created_at
  LIMIT 5
  FOR UPDATE SKIP LOCKED;
COMMIT;

-- Advisory locks: locks de aplicação (não ligados a linhas/tabelas)
SELECT pg_try_advisory_lock(12345);        -- tenta, retorna false se já travado
SELECT pg_advisory_lock(12345);            -- bloqueia até adquirir
SELECT pg_advisory_unlock(12345);          -- libera
SELECT pg_advisory_xact_lock(12345);       -- lock de transação (auto-release no commit)

Particionamento

-- Tabela pai (não armazena dados diretamente)
CREATE TABLE orders_partitioned (
  id         bigint      NOT NULL,
  user_id    bigint      NOT NULL,
  total      numeric(12,2),
  status     varchar(50),
  created_at timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

-- Partições RANGE
CREATE TABLE orders_2024_q1 PARTITION OF orders_partitioned
  FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE orders_2024_q2 PARTITION OF orders_partitioned
  FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- Default partition (captura valores fora das partições definidas)
CREATE TABLE orders_default PARTITION OF orders_partitioned DEFAULT;

-- Particionamento LIST
CREATE TABLE orders_by_status (
  id     bigint,
  status varchar(50) NOT NULL,
  total  numeric(12,2)
) PARTITION BY LIST (status);

CREATE TABLE orders_active   PARTITION OF orders_by_status FOR VALUES IN ('pending', 'processing');
CREATE TABLE orders_closed   PARTITION OF orders_by_status FOR VALUES IN ('delivered', 'cancelled');

-- Particionamento HASH (distribui uniformemente)
CREATE TABLE orders_hash (
  id      bigint,
  user_id bigint NOT NULL,
  total   numeric(12,2)
) PARTITION BY HASH (user_id);

CREATE TABLE orders_hash_0 PARTITION OF orders_hash FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_hash_1 PARTITION OF orders_hash FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE orders_hash_2 PARTITION OF orders_hash FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE orders_hash_3 PARTITION OF orders_hash FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- Sub-particionamento
CREATE TABLE orders_2024_q1_delivered PARTITION OF orders_2024_q1
  FOR VALUES IN ('delivered');  -- se a partição pai for LIST

-- Índices: criados na tabela pai propagam para todas partições (PG 11+)
CREATE INDEX ON orders_partitioned (user_id);
CREATE INDEX ON orders_partitioned (created_at);

-- ATTACH / DETACH (migração de dados)
-- Preparar tabela existente e attachar
CREATE TABLE orders_2025_q1 (LIKE orders_partitioned INCLUDING ALL);
ALTER TABLE orders_2025_q1 ADD CONSTRAINT chk_2025q1
  CHECK (created_at >= '2025-01-01' AND created_at < '2025-04-01');
ALTER TABLE orders_partitioned ATTACH PARTITION orders_2025_q1
  FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

-- DETACH: isola partição (dados ficam na tabela standalone)
ALTER TABLE orders_partitioned DETACH PARTITION orders_2024_q1;

-- Partition pruning: o planner elimina partições desnecessárias
-- Verificar com EXPLAIN:
EXPLAIN SELECT * FROM orders_partitioned WHERE created_at >= '2024-04-01';
-- Deve mostrar apenas a partição relevante

Full-Text Search

-- tsvector: representação indexável do documento
SELECT to_tsvector('portuguese', 'O PostgreSQL é um banco de dados relacional poderoso');
-- 'banc':8 'dado':10 'postgresq':2 'poderoso':13 'relacional':12

-- tsquery: expressão de busca
SELECT to_tsquery('portuguese', 'banco & dados');
SELECT plainto_tsquery('portuguese', 'banco de dados');
SELECT websearch_to_tsquery('portuguese', 'banco dados -NoSQL');

-- Operador @@: verificar correspondência
SELECT 'banco de dados relacional'::tsvector @@ 'banco'::tsquery;

-- Busca em tabela
ALTER TABLE app.products ADD COLUMN search_vector tsvector;

UPDATE app.products
SET search_vector = to_tsvector('portuguese', name || ' ' || COALESCE(description, ''));

-- Manter atualizado com trigger
CREATE FUNCTION products_search_vector_update() RETURNS trigger AS $$
BEGIN
  NEW.search_vector := to_tsvector('portuguese',
    coalesce(NEW.name, '') || ' ' || coalesce(NEW.description, '')
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trig_products_search
BEFORE INSERT OR UPDATE ON app.products
FOR EACH ROW EXECUTE FUNCTION products_search_vector_update();

-- Índice GIN para FTS
CREATE INDEX idx_products_fts ON app.products USING GIN (search_vector);

-- Query com ranking
SELECT
  id,
  name,
  ts_rank(search_vector, query)      AS rank,
  ts_headline('portuguese', name, query,
    'StartSel=<b>, StopSel=</b>, MaxWords=10') AS headline
FROM app.products,
     websearch_to_tsquery('portuguese', 'teclado mecânico') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

-- setweight: pesos A, B, C, D (A é o mais relevante)
UPDATE app.products
SET search_vector =
  setweight(to_tsvector('portuguese', name), 'A') ||
  setweight(to_tsvector('portuguese', COALESCE(description, '')), 'B');

-- Configurações de idioma disponíveis
SELECT cfgname FROM pg_ts_config;

-- pg_trgm: busca por similaridade (typos, substrings)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_trgm ON app.products USING GIN (name gin_trgm_ops);

SELECT name, similarity(name, 'teclado') AS sim
FROM app.products
WHERE name % 'teclado'
ORDER BY sim DESC;

Administração

-- Roles e permissões
CREATE ROLE app_readonly;
CREATE ROLE app_readwrite;
CREATE ROLE app_admin;

-- Conceder permissões
GRANT CONNECT ON DATABASE myapp TO app_readonly;
GRANT USAGE ON SCHEMA app TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT ON TABLES TO app_readonly;

GRANT app_readonly TO app_readwrite;  -- herança
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA app TO app_readwrite;

GRANT ALL PRIVILEGES ON SCHEMA app TO app_admin;
GRANT ALL ON ALL TABLES IN SCHEMA app TO app_admin;

-- Criar usuário e associar role
CREATE USER api_user WITH PASSWORD 'senha_segura';
GRANT app_readwrite TO api_user;

-- Revogar
REVOKE INSERT ON app.users FROM app_readwrite;

-- Ver permissões
\dp app.orders  -- no psql
SELECT * FROM information_schema.role_table_grants WHERE table_name = 'orders';

-- pg_dump / pg_restore
-- Dump completo (custom format — recomendado)
pg_dump -U postgres -d myapp -Fc -f myapp.dump

-- Dump apenas schema
pg_dump -U postgres -d myapp --schema-only -f schema.sql

-- Dump apenas dados
pg_dump -U postgres -d myapp --data-only -Fc -f data.dump

-- Dump tabela específica
pg_dump -U postgres -d myapp -t app.orders -Fc -f orders.dump

-- Restore
pg_restore -U postgres -d myapp_restore -Fc myapp.dump

-- Restore com paralelismo
pg_restore -U postgres -d myapp_restore -j 4 -Fc myapp.dump

-- Extensões úteis
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";  -- uuid_generate_v4()
CREATE EXTENSION IF NOT EXISTS pgcrypto;     -- gen_random_uuid(), crypt()
CREATE EXTENSION IF NOT EXISTS pg_trgm;      -- busca por similaridade
CREATE EXTENSION IF NOT EXISTS unaccent;     -- remove acentos em FTS
CREATE EXTENSION IF NOT EXISTS tablefunc;    -- crosstab (pivot)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements; -- histórico de queries

-- pg_stat_statements: queries mais lentas
SELECT
  LEFT(query, 80) AS query,
  calls,
  ROUND(total_exec_time::numeric, 2) AS total_ms,
  ROUND(mean_exec_time::numeric, 2)  AS mean_ms,
  rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

-- Terminar conexão específica
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'myapp'
  AND pid <> pg_backend_pid()
  AND state = 'idle'
  AND query_start < now() - interval '10 minutes';

-- Queries de diagnóstico

-- 1) Cache hit ratio global
SELECT
  sum(blks_hit)::float / sum(blks_hit + blks_read) AS cache_hit_ratio
FROM pg_stat_database;

-- 2) Table bloat estimado
SELECT
  relname,
  n_dead_tup,
  n_live_tup,
  ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS bloat_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY bloat_pct DESC;

-- 3) Locks em espera
SELECT
  blocked_locks.pid       AS blocked_pid,
  blocked_activity.query  AS blocked_query,
  blocking_locks.pid      AS blocking_pid,
  blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
   AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
   AND blocking_locks.pid != blocked_locks.pid
   AND blocking_locks.granted
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- 4) Índices não utilizados
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexname NOT LIKE '%pkey%'
ORDER BY schemaname, tablename;

-- 5) Tamanho de tabelas e índices
SELECT
  relname,
  pg_size_pretty(pg_total_relation_size(oid)) AS total_size,
  pg_size_pretty(pg_relation_size(oid))       AS table_size,
  pg_size_pretty(pg_indexes_size(oid))        AS indexes_size
FROM pg_class
WHERE relkind = 'r'
  AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'app')
ORDER BY pg_total_relation_size(oid) DESC;

-- psql meta-commands úteis
-- \d  app.orders        -- descreve tabela (colunas, índices, constraints)
-- \d+ app.orders        -- detalhado (tamanho, storage)
-- \dt app.*             -- lista tabelas do schema app
-- \di app.*             -- lista índices
-- \dn                   -- lista schemas
-- \df                   -- lista funções
-- \x                    -- expanded mode (uma coluna por linha)
-- \timing on            -- mostra tempo de execução
-- \copy app.orders TO '/tmp/orders.csv' CSV HEADER
-- \copy app.orders FROM '/tmp/orders.csv' CSV HEADER
-- \e                    -- abre editor externo
-- \l                    -- lista databases
-- \c myapp              -- conecta ao database myapp

---

## Logical Replication

A replicação lógica transmite **mudanças a nível de linha** (INSERT/UPDATE/DELETE) usando o protocolo de decodificação lógica do WAL, ao contrário da replicação física que copia blocos de disco byte a byte.

| Aspecto | Physical Replication | Logical Replication |
|---|---|---|
| Granularidade | Bloco de disco (cluster inteiro) | Tabela ou conjunto de tabelas |
| Versão do PG | Deve ser idêntica | Pode diferir (PG 10+) |
| Filtragem | Impossível | Por tabela, linha ou coluna |
| DDL replicado | Sim (implícito) | Não — DDL deve ser aplicado manualmente |
| Caso de uso | HA / standby | Migração, fan-out, multi-master parcial |

### Configurar no publisher (origem)

```sql
-- postgresql.conf (ou ALTER SYSTEM):
-- wal_level = logical   ← obrigatório
-- max_replication_slots = 10
-- max_wal_senders = 10

-- Criar publicação de todas as tabelas
CREATE PUBLICATION pub_all FOR ALL TABLES;

-- Publicação seletiva
CREATE PUBLICATION pub_financeiro
  FOR TABLE transacoes, contas, categorias;

-- Publicação com filtro de operação
CREATE PUBLICATION pub_inserts_only
  FOR TABLE eventos
  WITH (publish = 'insert');

-- Listar publicações
SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables;

Configurar no subscriber (destino)

-- As tabelas devem existir previamente no subscriber com schema compatível
CREATE TABLE transacoes (LIKE transacoes_origem INCLUDING ALL);

-- Criar assinatura
CREATE SUBSCRIPTION sub_financeiro
  CONNECTION 'host=db-origem port=5432 dbname=producao user=replicator password=senha'
  PUBLICATION pub_financeiro;

-- Desativar / reativar
ALTER SUBSCRIPTION sub_financeiro DISABLE;
ALTER SUBSCRIPTION sub_financeiro ENABLE;

-- Remover (não remove o replication slot no publisher — fazer manualmente)
DROP SUBSCRIPTION sub_financeiro;

-- Listar assinaturas
SELECT * FROM pg_subscription;

Monitoramento

-- No publisher: status dos replication slots
SELECT slot_name, plugin, slot_type, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;

-- Lag de replicação (bytes acumulados não enviados)
SELECT
  slot_name,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM pg_replication_slots
WHERE slot_type = 'logical';

-- No publisher: workers de replicação
SELECT pid, usename, application_name, state, sent_lsn, write_lsn, replay_lsn
FROM pg_stat_replication;

-- No subscriber: status de apply
SELECT subname, received_lsn, latest_end_lsn, last_msg_receipt_time
FROM pg_stat_subscription;

Use cases

-- 1) Migração zero-downtime entre versões do PostgreSQL
--    Publisher: PG 14 (produção)  →  Subscriber: PG 16 (novo cluster)
--    Migrar tabela por tabela, redirecionar conexões gradualmente

-- 2) Replicar subconjunto de tabelas para analytics
CREATE PUBLICATION pub_analytics
  FOR TABLE pedidos, produtos, clientes;
-- Subscriber é um banco de leitura pesada (reporting, BI)

-- 3) Fan-out para múltiplos subscribers
CREATE PUBLICATION pub_eventos FOR TABLE eventos;
-- Cada subscriber consome de forma independente (microserviços)

-- 4) Multi-master com resolução manual de conflitos
--    Ambos os lados publicam e assinam mutuamente
--    Requer lógica de deduplicação (ex.: coluna updated_at + trigger)

VACUUM e Autovacuum Tuning

Por que VACUUM importa

PostgreSQL usa MVCC (Multi-Version Concurrency Control): toda atualização cria uma nova versão de linha e marca a antiga como “morta” (dead tuple). O VACUUM é o processo responsável por reclamar esse espaço e manter o catálogo saudável.

Sem VACUUM adequado:

• Table bloat: tabela cresce indefinidamente com dead tuples
• Index bloat: índices também acumulam entradas obsoletas
• XID wraparound: após ~2 bilhões de transações, o PostgreSQL congela o banco para evitar corrupção (emergency autovacuum)

Verificar dead tuples e bloat

-- Tabelas com mais dead tuples (candidatas a VACUUM manual)
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Estimar bloat de tabelas (tamanho real vs ideal)
SELECT
  relname,
  pg_size_pretty(pg_total_relation_size(oid)) AS total,
  pg_size_pretty(pg_relation_size(oid))       AS heap,
  pg_size_pretty(pg_indexes_size(oid))        AS indexes
FROM pg_class
WHERE relkind = 'r'
ORDER BY pg_total_relation_size(oid) DESC
LIMIT 20;

VACUUM manual

-- VACUUM simples: libera dead tuples (não devolve espaço ao SO)
VACUUM transacoes;

-- VACUUM FULL: reescreve a tabela inteira (devolve espaço, mas TRAVA a tabela)
VACUUM FULL transacoes;

-- VACUUM VERBOSE: diagnóstico detalhado
VACUUM VERBOSE transacoes;
-- Saída inclui: páginas removidas, dead tuples coletadas, XID mais antigo, etc.

-- VACUUM ANALYZE: vacuum + atualiza estatísticas do planner
VACUUM ANALYZE transacoes;

-- VACUUM FREEZE: força congelamento de XIDs (previne wraparound)
-- Executar em tabelas que raramente sofrem VACUUM automático
VACUUM FREEZE transacoes;

-- ANALYZE isolado (só estatísticas, sem vacuum)
ANALYZE transacoes;

Parâmetros de Autovacuum

-- Ver configurações atuais
SHOW autovacuum_vacuum_scale_factor;
SHOW autovacuum_vacuum_threshold;

-- Fórmula de disparo do autovacuum:
-- dead_tuples > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * n_live_tup
-- Padrão: 50 + 0.20 * n_live_tup  → em tabelas grandes (10M linhas), dispara com 2M dead tuples

-- Ajuste por tabela (substitui globals)
ALTER TABLE transacoes SET (
  autovacuum_vacuum_scale_factor     = 0.01,   -- disparar com 1% de dead tuples
  autovacuum_vacuum_threshold        = 100,    -- mínimo de 100 dead tuples
  autovacuum_analyze_scale_factor    = 0.005,  -- analyze com 0.5% de mudanças
  autovacuum_vacuum_cost_delay       = 2,      -- ms de pausa entre rounds (reduz I/O impact)
  autovacuum_vacuum_cost_limit       = 400     -- quanto I/O por round (padrão 200)
);

-- Configurações globais relevantes (postgresql.conf / ALTER SYSTEM)
-- autovacuum_naptime = 1min         -- intervalo entre varreduras do autovacuum daemon
-- autovacuum_max_workers = 3        -- workers paralelos
-- autovacuum_vacuum_cost_delay = 2  -- throttling de I/O global
-- maintenance_work_mem = 256MB      -- memória por worker de vacuum

-- Verificar configurações por tabela
SELECT relname, reloptions
FROM pg_class
WHERE reloptions IS NOT NULL;

Monitorar progresso de VACUUM

-- VACUUM em andamento (PG 9.6+)
SELECT pid, relid::regclass AS table, phase, heap_blks_scanned, heap_blks_vacuumed,
       index_vacuum_count, num_dead_tuples
FROM pg_stat_progress_vacuum;

-- Idade de XID (risco de wraparound — alerta se > 500M)
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

-- Tabelas mais velhas em XID (candidatas a VACUUM FREEZE)
SELECT relname, age(relfrozenxid) AS xid_age
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 10;

Extensões Populares

pg_stat_statements — análise de queries lentas

-- Instalar (requer shared_preload_libraries no postgresql.conf)
-- shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 queries por tempo total
SELECT
  LEFT(query, 80)                                     AS query_trunc,
  calls,
  ROUND(total_exec_time::numeric, 2)                  AS total_ms,
  ROUND(mean_exec_time::numeric, 2)                   AS mean_ms,
  ROUND(stddev_exec_time::numeric, 2)                 AS stddev_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Queries com maior tempo médio (candidatas a otimização)
SELECT LEFT(query, 80), calls, ROUND(mean_exec_time::numeric, 2) AS mean_ms
FROM pg_stat_statements
WHERE calls > 50
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Resetar estatísticas
SELECT pg_stat_statements_reset();

uuid-ossp e gen_random_uuid()

-- PG 13+: função nativa, sem extensão
SELECT gen_random_uuid();  -- UUIDv4

-- Extensão uuid-ossp para variantes específicas
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v1();  -- baseado em MAC + timestamp
SELECT uuid_generate_v4();  -- aleatório (equivalente ao nativo)
SELECT uuid_generate_v5(uuid_ns_url(), 'https://example.com');  -- deterministico (namespace + nome)

-- Uso como PK
CREATE TABLE usuarios (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  email text NOT NULL
);

pg_trgm — busca por similaridade e LIKE rápido

-- Trigrams: divide a string em grupos de 3 caracteres para comparação fuzzy
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Índice GIN para acelerar LIKE/ILIKE e similarity
CREATE INDEX idx_produtos_nome_trgm ON produtos USING GIN (nome gin_trgm_ops);

-- LIKE com índice (antes precisaria de índice B-tree e prefixo fixo)
SELECT * FROM produtos WHERE nome ILIKE '%notebook%';

-- Similaridade (0.0 a 1.0)
SELECT nome, similarity(nome, 'notbok') AS sim
FROM produtos
WHERE nome % 'notbok'   -- operador %: similarity >= pg_trgm.similarity_threshold (padrão 0.3)
ORDER BY sim DESC;

-- Distância (menor = mais similar)
SELECT nome, nome <-> 'notbok' AS dist
FROM produtos
ORDER BY dist
LIMIT 10;

-- Ajustar limiar
SET pg_trgm.similarity_threshold = 0.4;

hstore — key-value em coluna

-- Armazenar atributos dinâmicos sem JSON quando a estrutura é plana
CREATE EXTENSION IF NOT EXISTS hstore;

CREATE TABLE produtos (
  id bigint PRIMARY KEY,
  nome text,
  atributos hstore
);

INSERT INTO produtos VALUES
  (1, 'Notebook Pro', 'cor=>preto, ram=>16GB, ssd=>512GB'),
  (2, 'Monitor 4K',   'cor=>branco, refresh=>144hz, panel=>IPS');

-- Acessar chave
SELECT atributos -> 'cor' FROM produtos WHERE id = 1;

-- Verificar existência de chave
SELECT * FROM produtos WHERE atributos ? 'ssd';

-- Filtrar por valor
SELECT * FROM produtos WHERE atributos @> 'cor=>preto';

-- Adicionar / atualizar chave
UPDATE produtos SET atributos = atributos || 'garantia=>2anos' WHERE id = 1;

-- Remover chave
UPDATE produtos SET atributos = DELETE(atributos, 'garantia') WHERE id = 1;

-- Índice GIN para buscas em hstore
CREATE INDEX idx_produtos_atributos ON produtos USING GIN (atributos);

pg_partman — gerenciamento de partições automático

-- pg_partman automatiza a criação de novas partições e remoção das antigas
-- Instalar: geralmente via pgxn ou pacote do SO
CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;

-- Criar tabela particionada por tempo
CREATE TABLE eventos (
  id           bigint GENERATED ALWAYS AS IDENTITY,
  created_at   timestamptz NOT NULL DEFAULT now(),
  tipo         text,
  payload      jsonb
) PARTITION BY RANGE (created_at);

-- Configurar pg_partman para criar partições mensais
SELECT partman.create_parent(
  p_parent_table => 'public.eventos',
  p_control      => 'created_at',
  p_type         => 'native',
  p_interval     => 'monthly',
  p_premake      => 3     -- pré-criar 3 partições futuras
);

-- Executar manutenção (normalmente via cron ou pg_cron)
SELECT partman.run_maintenance();

-- Configurar retenção (remover partições com mais de 12 meses)
UPDATE partman.part_config
SET retention = '12 months', retention_keep_table = false
WHERE parent_table = 'public.eventos';

-- Listar partições criadas
SELECT child_schema, child_table, partition_range
FROM partman.show_partitions('public.eventos');

TimescaleDB — time-series

-- TimescaleDB transforma o PostgreSQL em banco de séries temporais de alta performance
-- Instalação via Docker: timescale/timescaledb-ha:pg16

CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Criar hypertable (tabela particionada automaticamente por tempo)
CREATE TABLE metricas (
  time   timestamptz NOT NULL,
  sensor text        NOT NULL,
  valor  double precision
);

SELECT create_hypertable('metricas', 'time',
  chunk_time_interval => INTERVAL '1 day'
);

-- Inserção e consulta são SQL padrão
INSERT INTO metricas VALUES (now(), 'cpu', 72.5);

SELECT time_bucket('1 hour', time) AS hora, sensor, AVG(valor)
FROM metricas
WHERE time > now() - INTERVAL '24 hours'
GROUP BY hora, sensor
ORDER BY hora;

-- Compressão automática de chunks antigos
ALTER TABLE metricas SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'sensor'
);

SELECT add_compression_policy('metricas', INTERVAL '7 days');

-- Retenção automática (deletar dados > 90 dias)
SELECT add_retention_policy('metricas', INTERVAL '90 days');

-- Continuous aggregates (materialized views incrementais)
CREATE MATERIALIZED VIEW metricas_horarias
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS hora, sensor, AVG(valor) AS media
FROM metricas
GROUP BY hora, sensor;

SELECT add_continuous_aggregate_policy('metricas_horarias',
  start_offset => INTERVAL '3 hours',
  end_offset   => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour'
);