Advanced SQL: 200 Senior/DBA-Level Commands

This is where SQL meets the infrastructure. These 200 items are what you use when the database is already in production, with volume, concurrency and someone on the phone asking why the report is slow.

Execution plans, advanced indexes, isolation, partitioning, replication, backup, security and diagnostics — with each database's syntax where it diverges.

A warning: several commands here change the server's behaviour. Test on staging first, and understand what each one does before running it in production.

Contents

• 1–18 · Advanced window functions
• 19–30 · Analytical queries and recursion
• 31–54 · Execution plan and optimisation
• 55–74 · Advanced indexes
• 75–90 · Concurrency, locks and MVCC
• 91–105 · Partitioning
• 106–119 · Replication and high availability
• 120–133 · Backup and recovery
• 134–146 · Maintenance, vacuum and statistics
• 147–159 · Advanced security
• 160–170 · Extensions and modern features
• 171–190 · Diagnostics in production
• 191–200 · Production good practices

Advanced window functions

Frames, distribution and the gaps-and-islands pattern — serious analysis without leaving SQL.

1. ROWS frame

Defines the window by a number of physical rows before/after the current one.

SELECT dia, valor,
  AVG(valor) OVER (ORDER BY dia ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS media_7d
FROM metricas;

2. RANGE frame

Defines the window by value, not by position: ties come in together.

SELECT valor, SUM(valor) OVER (ORDER BY valor RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM vendas;

3. GROUPS frame

Counts groups of equal values as a unit (PostgreSQL 11+).

SELECT dia, SUM(valor) OVER (ORDER BY dia GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW)
FROM metricas;

4. EXCLUDE in the frame

Removes the current row (or its group) from the calculation — the others' average, not your own.

SELECT id, AVG(nota) OVER (PARTITION BY turma ORDER BY id
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW) AS media_dos_outros
FROM provas;

5. PERCENT_RANK

The relative position from 0 to 1 within the partition.

SELECT nome, total, ROUND(PERCENT_RANK() OVER (ORDER BY total)::numeric, 3) AS percentil
FROM vendedores;

6. CUME_DIST

The cumulative distribution: the proportion of rows with a smaller or equal value.

SELECT nome, CUME_DIST() OVER (ORDER BY salario) AS acumulada FROM funcionarios;

7. PERCENTILE_CONT

An interpolated percentile — the true median, insensitive to outliers.

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total) AS mediana FROM pedidos;

8. PERCENTILE_DISC

A discrete percentile: it returns a value that actually exists in the set.

SELECT PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY duracao_ms) AS p90 FROM requisicoes;

9. MODE

The group's most frequent value.

SELECT MODE() WITHIN GROUP (ORDER BY categoria) AS mais_vendida FROM pedidos;

10. NTH_VALUE

Takes the nth value of the window.

SELECT cliente_id, NTH_VALUE(total, 2) OVER (PARTITION BY cliente_id ORDER BY criado_em) AS segundo_pedido
FROM pedidos;

11. Gaps and islands

Detects continuous runs: the difference between the date and the ROW_NUMBER is constant within an island.

SELECT usuario_id, MIN(dia) AS inicio, MAX(dia) AS fim, COUNT(*) AS dias_seguidos
FROM (
  SELECT usuario_id, dia,
         dia - (ROW_NUMBER() OVER (PARTITION BY usuario_id ORDER BY dia))::int AS grupo
  FROM acessos
) ace_num
GROUP BY usuario_id, grupo;

12. Sessionisation

Groups events into sessions when the gap between them exceeds a threshold.

SELECT *, SUM(nova_sessao) OVER (PARTITION BY usuario_id ORDER BY quando) AS sessao
FROM (
  SELECT *, CASE WHEN quando - LAG(quando) OVER (PARTITION BY usuario_id ORDER BY quando)
                 > INTERVAL '30 minutes' THEN 1 ELSE 0 END AS nova_sessao
  FROM eventos
) eve_marcado;

13. Month-on-month retention

Compares the set of users between periods with a window + join.

WITH usu_mes AS (
  SELECT DISTINCT usuario_id, DATE_TRUNC('month', quando) AS mes
  FROM eventos
)
SELECT atual.mes,
       COUNT(*) FILTER (WHERE seg.usuario_id IS NOT NULL) AS retidos
FROM usu_mes atual
LEFT JOIN usu_mes seg
ON seg.usuario_id = atual.usuario_id
AND seg.mes = atual.mes + INTERVAL '1 month'
GROUP BY atual.mes
ORDER BY atual.mes;

14. Top N per group with ties

RANK instead of ROW_NUMBER when ties should come in together.

SELECT *
FROM (
  SELECT prod.*,
         RANK() OVER (PARTITION BY prod.categoria ORDER BY prod.vendas DESC) AS posicao
  FROM produtos prod
) prod_rank
WHERE posicao <= 3;

15. Distance to the group's leader

Compares each row against the best in its partition.

SELECT nome, categoria, vendas,
  MAX(vendas) OVER (PARTITION BY categoria) - vendas AS distancia_do_topo
FROM produtos;

16. Rolling total by time

RANGE with INTERVAL creates windows by real time, not by number of rows.

SELECT quando, valor,
  SUM(valor) OVER (ORDER BY quando RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW) AS ultimos_7d
FROM transacoes;

17. Fill in a missing value

Repeats the last known value (last value carried forward).

SELECT dia, cotacao,
  COALESCE(cotacao, (array_agg(cotacao) FILTER (WHERE cotacao IS NOT NULL)
    OVER (ORDER BY dia ROWS UNBOUNDED PRECEDING))[COUNT(cotacao) OVER (ORDER BY dia ROWS UNBOUNDED PRECEDING)]) AS preenchida
FROM cotacoes;

18. The cost of a window

Each distinct window can generate a sort. Reuse the same OVER clause whenever you can.

-- A single sort: the same named window
SELECT SUM(v) OVER w, AVG(v) OVER w, COUNT(*) OVER w
FROM t WINDOW w AS (PARTITION BY g ORDER BY d);

Analytical queries and recursion

Multidimensional aggregations and graph traversal straight in the database.

19. GROUPING SETS

Several GROUP BYs in a single query, with no UNION ALL.

SELECT regiao, produto, SUM(valor)
FROM vendas
GROUP BY GROUPING SETS ((regiao, produto), (regiao), ());

20. ROLLUP

Hierarchical subtotals + a grand total.

SELECT ano, mes, SUM(valor) FROM vendas GROUP BY ROLLUP (ano, mes);

21. CUBE

Every possible combination of subtotals.

SELECT regiao, canal, SUM(valor) FROM vendas GROUP BY CUBE (regiao, canal);

22. GROUPING()

Tells you whether the row is a subtotal — it avoids confusing it with a NULL in the data.

SELECT regiao, GROUPING(regiao) AS eh_total, SUM(valor)
FROM vendas GROUP BY ROLLUP (regiao);

23. PIVOT

Rows become columns. Native on SQL Server and Oracle; on PostgreSQL use CASE or crosstab.

SELECT *
FROM vendas ven
PIVOT (SUM(ven.valor) FOR ven.mes IN ([1],[2],[3])) AS ven_pivot;   -- SQL Server

24. UNPIVOT

Columns become rows — it normalises an imported spreadsheet.

SELECT produto, mes, valor
FROM vendas_larga ven_larga
UNPIVOT (valor FOR mes IN (jan, fev, mar)) AS ven_normalizada;   -- SQL Server / Oracle

25. crosstab (PostgreSQL)

A dynamic pivot via the tablefunc extension.

CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT * FROM crosstab('SELECT produto, mes, valor FROM vendas ORDER BY 1,2')
  AS t(produto TEXT, jan NUMERIC, fev NUMERIC);

26. Graph traversal

A recursive CTE walks relationships to an arbitrary depth.

WITH RECURSIVE caminho AS (
  SELECT origem, destino, 1 AS saltos FROM rotas WHERE origem = 'GRU'
  UNION ALL
  SELECT cam.origem, rot.destino, cam.saltos + 1
  FROM caminho cam
  INNER JOIN rotas rot
  ON rot.origem = cam.destino
  WHERE cam.saltos < 4
)
SELECT DISTINCT destino,
                MIN(saltos)
FROM caminho
GROUP BY destino;

27. Detect a cycle in the recursion

Carry the path travelled and stop when a node repeats.

WITH RECURSIVE arvore AS (
  SELECT id, pai_id, ARRAY[id] AS caminho
  FROM nos
  WHERE pai_id IS NULL
  UNION ALL
  SELECT nos.id, nos.pai_id, arv.caminho || nos.id
  FROM nos
  INNER JOIN arvore arv
  ON nos.pai_id = arv.id
  WHERE NOT nos.id = ANY(arv.caminho)
)
SELECT *
FROM arvore;

28. CYCLE — native detection

PostgreSQL 14+ flags cycles without you assembling the array by hand.

WITH RECURSIVE arvore AS (
  SELECT id, pai_id
  FROM nos
  WHERE pai_id IS NULL
  UNION ALL
  SELECT nos.id, nos.pai_id
  FROM nos
  INNER JOIN arvore arv
  ON nos.pai_id = arv.id
) CYCLE id SET tem_ciclo USING caminho
SELECT *
FROM arvore;

29. SEARCH — the traversal order

Controls whether the recursion is breadth-first or depth-first.

WITH RECURSIVE t AS (...) SEARCH DEPTH FIRST BY id SET ordem
SELECT * FROM t ORDER BY ordem;

30. Bill of materials explosion

A bill of materials: components of components, with an accumulated quantity.

WITH RECURSIVE bom AS (
  SELECT peca_id, componente_id, qtd FROM estrutura WHERE peca_id = 100
  UNION ALL
  SELECT bom.peca_id, estr.componente_id, bom.qtd * estr.qtd
  FROM bom
  INNER JOIN estrutura estr
  ON estr.peca_id = bom.componente_id
)
SELECT componente_id,
       SUM(qtd)
FROM bom
GROUP BY componente_id;

Execution plan and optimisation

Stop guessing. The plan says exactly where the time is going.

31. EXPLAIN

Shows the plan the optimiser intends to use, without executing it.

EXPLAIN SELECT * FROM pedidos WHERE cliente_id = 42;

32. EXPLAIN ANALYZE

Actually executes it and compares the estimate against reality. It is the command that solves most cases.

EXPLAIN ANALYZE SELECT * FROM pedidos WHERE cliente_id = 42;

33. EXPLAIN with buffers

Shows cache and disk reads — it separates an I/O problem from a CPU problem.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM pedidos WHERE total > 1000;

34. The plan in JSON format

The ideal format for plan visualisation tools.

EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM pedidos;

35. Seq Scan

A full scan. Bad for a few rows; great when you are reading nearly everything anyway.

-- If a Seq Scan shows up on a selective filter, an index is missing (or the statistics are stale).

36. Index Scan vs Index Only Scan

An Index Only Scan never touches the table: every column came from the index.

CREATE INDEX idx_cob ON pedidos (cliente_id) INCLUDE (total);
EXPLAIN SELECT cliente_id, total FROM pedidos WHERE cliente_id = 1;

37. Bitmap Scan

Combines several indexes or reads many scattered rows in page order.

EXPLAIN SELECT * FROM pedidos WHERE status = 'pago' AND cliente_id < 500;

38. Nested Loop

Good when one side is small and the other has an index. Terrible when both are large.

-- A Nested Loop with millions of rows on both sides = a stuck query

39. Hash Join

Builds a hash table from the smaller side. Fast for large volumes, uses memory.

SET work_mem = '128MB';  -- a hash that fits in memory avoids going to disk

40. Merge Join

Joins two already-sorted sets. Great when the indexes already deliver the order.

EXPLAIN
SELECT *
FROM pedidos ped
INNER JOIN itens ite
ON ite.pedido_id = ped.id;   -- look for Merge Join in the plan

41. Estimate vs reality

An estimated rows=1000 against actual rows=2000000 is the classic symptom of stale statistics.

ANALYZE pedidos;   -- and run the EXPLAIN ANALYZE again

42. The plan's cost

Cost is a relative unit of the optimiser, not seconds. It serves to compare plans against each other.

EXPLAIN SELECT * FROM pedidos;  -- cost=0.00..1234.56

43. Sort and spilling to disk

external merge Disk in the plan means work_mem was not enough.

SET work_mem = '64MB';
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM pedidos ORDER BY total;

44. SARGable

A filter that preserves the index. A function applied to the column breaks that.

-- Does not use the index
WHERE UPPER(nome) = 'ANA'
-- Does (with an expression index)
CREATE INDEX ON clientes (UPPER(nome));

45. Avoid SELECT *

Extra columns prevent an Index Only Scan and shift useless data around.

SELECT id, total FROM pedidos WHERE cliente_id = 1;   -- and not SELECT *

46. The OR that kills the index

An OR across different columns usually turns into a Seq Scan. UNION ALL solves it.

SELECT * FROM t WHERE a = 1
UNION ALL
SELECT * FROM t WHERE b = 2 AND a <> 1;

47. Pagination with a large OFFSET

OFFSET 100000 reads and discards 100 thousand rows. Paginate by key (keyset pagination).

-- Slow
SELECT * FROM pedidos ORDER BY id LIMIT 20 OFFSET 100000;

-- Fast
SELECT * FROM pedidos WHERE id > 100000 ORDER BY id LIMIT 20;

48. EXISTS instead of COUNT

To know whether something exists, stop at the first row.

-- Bad
SELECT COUNT(*) FROM pedidos WHERE cliente_id = 1;
-- Good
SELECT EXISTS (SELECT 1 FROM pedidos WHERE cliente_id = 1);

49. The N+1 problem

A thousand one-row queries cost far more than one thousand-row query.

SELECT * FROM pedidos WHERE cliente_id = ANY($1);   -- a single round trip

50. Materialise an intermediate result

An expensive calculation reused several times deserves a temporary table or a materialised view.

CREATE TEMP TABLE base AS SELECT ... ;
ANALYZE base;

51. Force or disable an operator

For diagnosis: disable a method and see whether the alternative plan is better.

SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT ...;
RESET enable_seqscan;

52. Hints (Oracle and MySQL)

Some databases let you instruct the optimiser directly. A last resort.

SELECT /*+ INDEX(p idx_pedidos_cliente) */ * FROM pedidos ped WHERE cliente_id = 1;

53. Query timeout

Stops a bad query monopolising the server.

SET statement_timeout = '30s';

54. Production plan vs staging plan

Different volumes and statistics produce different plans. Always validate with realistic data.

-- Compare the EXPLAIN from both environments before concluding that 'it is fast here'.

Advanced indexes

Beyond the B-tree: the right structure for each kind of search.

55. B-tree

The default: equality, range and ordering. It solves 90% of cases.

CREATE INDEX idx_padrao ON pedidos (criado_em);

56. Hash

Equality only, no ordering. A narrow niche on modern PostgreSQL.

CREATE INDEX idx_hash ON sessoes USING HASH (token);

57. GIN

For composite values: arrays, JSONB and text search.

CREATE INDEX idx_gin ON eventos USING GIN (payload);

58. GiST

Geometric structures, ranges and nearest-neighbour.

CREATE INDEX idx_gist ON reservas USING GIST (periodo);

59. BRIN

Tiny, for giant tables with naturally ordered data (time series).

CREATE INDEX idx_brin ON logs USING BRIN (criado_em);

60. SP-GiST

Partitioned structures: unbalanced data, prefixes, quadtrees.

CREATE INDEX idx_spgist ON pontos USING SPGIST (coord);

61. Full-text index

Word search with ranking, not with LIKE.

CREATE INDEX idx_busca ON artigos USING GIN (to_tsvector('portuguese', titulo || ' ' || corpo));
SELECT * FROM artigos WHERE to_tsvector('portuguese', corpo) @@ plainto_tsquery('portuguese', 'banco de dados');

62. Trigrams

Speeds up LIKE '%middle%' and similarity search.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_trgm ON clientes USING GIN (nome gin_trgm_ops);
SELECT * FROM clientes WHERE nome ILIKE '%silva%';

63. Similarity index

Sorts by textual proximity — a search that tolerates typos.

SELECT cli.nome,
       similarity(cli.nome, 'jonatan') AS similaridade
FROM clientes cli
ORDER BY similaridade DESC
LIMIT 10;

64. Clustered index

On SQL Server and MySQL/InnoDB, the table is physically ordered by the primary key.

CREATE CLUSTERED INDEX ix_pedidos ON pedidos (criado_em);   -- SQL Server

65. CLUSTER (PostgreSQL)

Physically reorders the table according to an index. It improves range reads; it has to be redone periodically.

CLUSTER pedidos USING idx_pedidos_data;

66. Fill factor

Leaves free space in the page for updates, reducing fragmentation.

CREATE INDEX idx_x ON t (col) WITH (fillfactor = 80);

67. An index on a low-cardinality column

A column with 2 values rarely pays off — unless as a partial index over the rare value.

CREATE INDEX idx_erro ON logs (criado_em) WHERE nivel = 'ERROR';

68. Detect unused indexes

Zero scans in production = a candidate for removal.

SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY relname;

69. Detect duplicate indexes

The same leading column across several indexes usually indicates redundancy.

SELECT indrelid::regclass, array_agg(indexrelid::regclass)
FROM pg_index GROUP BY indrelid, indkey HAVING COUNT(*) > 1;

70. Index bloat

A heavily updated index bloats and loses efficiency. REINDEX CONCURRENTLY solves it with no downtime.

REINDEX INDEX CONCURRENTLY idx_pedidos_cliente;

71. An index for ORDER BY + LIMIT

If the index already delivers the order, the database reads only the first rows.

CREATE INDEX idx_top ON pedidos (criado_em DESC);
SELECT * FROM pedidos ORDER BY criado_em DESC LIMIT 10;

72. Composite index in the right order

Equality first, range afterwards. (status, criado_em) serves status = X AND criado_em > Y.

CREATE INDEX idx_ordem ON pedidos (status, criado_em);

73. An index on a nullable column

If you filter on IS NULL often, a partial index is far smaller.

CREATE INDEX idx_sem_processar ON pedidos (id) WHERE processado_em IS NULL;

74. Maintenance cost

Measure the impact on writes: an index that speeds up one report and slows down 10 thousand INSERTs a minute may not be worth it.

SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes ORDER BY pg_relation_size(indexrelid) DESC;

Concurrency, locks and MVCC

What happens when a thousand sessions want the same row.

75. MVCC

Each transaction sees a consistent snapshot of the database; reading does not block writing.

-- PostgreSQL and Oracle use MVCC by default:
-- readers do not block writers and vice versa.

76. Anomaly: non-repeatable read

The same query returns different values within the transaction. It disappears under REPEATABLE READ.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

77. Anomaly: phantom read

New rows appear in the middle of the transaction. It disappears under SERIALIZABLE.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

78. Write skew

Two transactions read, validate and write — each correct on its own, together they violate the rule. Only SERIALIZABLE prevents it.

-- Two doctors on call going off duty at the same time: each transaction sees the other on call.

79. Row lock

FOR UPDATE locks the rows until the end of the transaction.

SELECT * FROM contas WHERE id = 1 FOR UPDATE;

80. FOR NO KEY UPDATE

A weaker lock: it allows other operations that do not change the key.

SELECT * FROM pedidos WHERE id = 1 FOR NO KEY UPDATE;

81. FOR SHARE

Prevents changes, but allows other locked reads.

SELECT * FROM produtos WHERE id = 1 FOR SHARE;

82. LOCK TABLE

Locks the entire table. Use it very sparingly and with an extremely short transaction.

BEGIN;
LOCK TABLE inventario IN EXCLUSIVE MODE;
-- critical operation
COMMIT;

83. Advisory lock

A lock named by the application, with no table involved. It guarantees only one instance runs the routine.

SELECT pg_try_advisory_lock(12345);
-- exclusive routine
SELECT pg_advisory_unlock(12345);

84. See the active locks

Blocking diagnostics: who is holding what.

SELECT loc.pid,
       loc.mode,
       loc.granted,
       cla.relname
FROM pg_locks loc
LEFT JOIN pg_class cla
ON cla.oid = loc.relation
WHERE NOT loc.granted;

85. Find out who is blocking whom

Shows the direct blocking chain.

SELECT pid, pg_blocking_pids(pid) AS bloqueado_por, query
FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;

86. lock_timeout

Gives up waiting for the lock instead of building a queue.

SET lock_timeout = '5s';

87. idle_in_transaction_timeout

Kills an open, forgotten transaction — the biggest cause of locking and bloat.

SET idle_in_transaction_session_timeout = '60s';

88. Concurrent work queue

SKIP LOCKED lets several workers consume the same table without colliding.

UPDATE fila SET status = 'processando'
WHERE id = (SELECT id FROM fila WHERE status = 'pendente'
            ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1)
RETURNING *;

89. Retry on a serialization failure

Under SERIALIZABLE, the application has to retry the aborted transaction. It is not a bug, it is the contract.

-- Application code: catch SQLSTATE 40001 and try again (with backoff).

90. A consistent access order

A deadlock almost always comes from differing orders. Standardise the order of tables and keys.

-- Always: contas (lowest id first) -> lancamentos

Partitioning

When the table gets too big to be treated as a single thing.

91. Partition by range

The most common case: one partition per month of time-based data.

CREATE TABLE eventos (id BIGSERIAL, criado_em DATE NOT NULL, dados JSONB)
PARTITION BY RANGE (criado_em);

92. Create the partition

Each range becomes its own physical table.

CREATE TABLE eventos_2026_01 PARTITION OF eventos
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

93. Partition by list

One partition per discrete value: region, country, tenant.

CREATE TABLE clientes (id BIGINT, regiao TEXT) PARTITION BY LIST (regiao);
CREATE TABLE clientes_sul PARTITION OF clientes FOR VALUES IN ('PR','SC','RS');

94. Partition by hash

Distributes evenly when there is no natural criterion.

CREATE TABLE sessoes (id BIGINT) PARTITION BY HASH (id);
CREATE TABLE sessoes_0 PARTITION OF sessoes FOR VALUES WITH (MODULUS 4, REMAINDER 0);

95. Default partition

Takes whatever does not match any range — it prevents insertion errors.

CREATE TABLE eventos_outros PARTITION OF eventos DEFAULT;

96. Partition pruning

The real gain: the database reads only the partitions the filter reaches. Confirm it in the EXPLAIN.

EXPLAIN SELECT * FROM eventos WHERE criado_em >= DATE '2026-01-01';

97. An index on a partitioned table

Created on the parent, it is propagated to every partition.

CREATE INDEX idx_eventos_data ON eventos (criado_em);

98. DETACH — archive

Detaches the old partition without deleting it: it becomes a normal table to export.

ALTER TABLE eventos DETACH PARTITION eventos_2025_01;

99. ATTACH — attach

Brings an existing table into the partitioned set (it validates the range).

ALTER TABLE eventos ATTACH PARTITION eventos_2026_02
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

100. DROP a partition

Deleting old data becomes an instantaneous operation, with no DELETE of millions of rows.

DROP TABLE eventos_2024_01;

101. Create partitions automatically

Schedule the creation in advance: a missing partition brings the insert down.

-- A monthly routine that creates the next 3 months.
-- Or use pg_partman.

102. The partition key in the PK

The partition key has to be part of the primary key and of the unique constraints.

CREATE TABLE eventos (id BIGINT, criado_em DATE, PRIMARY KEY (id, criado_em))
PARTITION BY RANGE (criado_em);

103. When not to partition

Below tens of millions of rows, a good index usually solves it better and with no complexity.

-- Partition out of a maintenance need (purging, archiving), not out of fashion.

104. Sharding

Partitioning across different servers. It gains write scale, it loses JOINs and global transactions.

-- Choose the shard key with great care: changing it later means rewriting the system.

105. Foreign table

With postgres_fdw, a partition can live on another server (declarative sharding).

CREATE EXTENSION postgres_fdw;
CREATE SERVER shard2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'db2', dbname 'loja');

Replication and high availability

How the database survives a machine that dies — and how to distribute reads.

106. WAL — the transaction log

Every change becomes a WAL record before going to the table. It is the basis of durability, replication and PITR.

SHOW wal_level;   -- 'replica' or 'logical' to replicate

107. Physical replication

The replica applies the WAL byte by byte: an identical copy of the whole cluster.

-- On the replica
pg_basebackup -h primario -U replicador -D /var/lib/postgresql/data -R -P

108. Logical replication

Replicates per table, between different versions and with transformation. The basis of migration with no downtime.

-- Source
CREATE PUBLICATION pub_vendas FOR TABLE pedidos, itens;

-- Destination
CREATE SUBSCRIPTION sub_vendas
  CONNECTION 'host=origem dbname=loja user=repl'
  PUBLICATION pub_vendas;

109. Synchronous replication

The COMMIT only returns after the replica has confirmed. Zero loss, more latency.

ALTER SYSTEM SET synchronous_standby_names = 'replica1';
SELECT pg_reload_conf();

110. Asynchronous replication

A fast commit, with the risk of losing the last transactions in a failover.

ALTER SYSTEM SET synchronous_commit = 'off';   -- weigh the risk first

111. Measure the replica's lag

High lag means stale reads and a riskier failover.

SELECT now() - pg_last_xact_replay_timestamp() AS atraso;

112. Read replica

Point the reports at the replica and relieve the primary.

-- The application uses two connections: writes on the primary, reads on the replica.
SELECT pg_is_in_recovery();   -- true = it is a replica

113. Promote a replica

Failover: the replica becomes the primary.

SELECT pg_promote();

114. Replication slot

It guarantees the primary does not discard WAL the replica has not consumed yet — and it fills the disk if the replica vanishes.

SELECT * FROM pg_replication_slots;
SELECT pg_drop_replication_slot('slot_orfao');

115. Automatic failover

Tools such as Patroni, repmgr and pg_auto_failover handle the election and the redirection.

-- With no orchestrator, failover is manual: someone has to promote and repoint the application.

116. Split-brain

Two primaries accepting writes at the same time. Fencing and quorum exist to prevent that.

-- Never promote manually without guaranteeing the old primary is isolated.

117. AlwaysOn and Data Guard

The equivalents on SQL Server (Availability Groups) and Oracle (Data Guard).

-- SQL Server
ALTER AVAILABILITY GROUP ag1 FAILOVER;

118. Replication on MySQL

Based on the binlog, with GTID for reliable positioning.

CHANGE REPLICATION SOURCE TO SOURCE_HOST='primario', SOURCE_AUTO_POSITION=1;
START REPLICA;
SHOW REPLICA STATUS\G

119. Connection pooling

A database does not like thousands of connections. PgBouncer saves memory and stabilises the latency.

-- pgbouncer.ini
-- pool_mode = transaction
-- max_client_conn = 1000
-- default_pool_size = 20

Backup and recovery

A backup that has never been restored is not a backup — it is hope.

120. Logical dump

Exports the database as SQL statements. Portable between versions and architectures.

pg_dump -h localhost -U app -Fc loja > loja.dump

121. Restore a dump

Restores the generated file, with parallelism where the format allows.

pg_restore -h localhost -U app -d loja -j 4 loja.dump

122. Dump of the whole cluster

It includes users, roles and permissions — which a single database's pg_dump does not bring.

pg_dumpall -h localhost -U postgres > cluster.sql

123. Schema-only dump

Useful for comparing structures between environments.

pg_dump --schema-only -U app loja > schema.sql

124. Physical backup

A copy of the cluster's files; far faster for restoring large databases.

pg_basebackup -h localhost -U replicador -D /backup/base -Fp -Xs -P

125. WAL archiving

Keeping the WAL segments is what allows recovery to any point in time.

ALTER SYSTEM SET archive_mode = on;
ALTER SYSTEM SET archive_command = 'cp %p /backup/wal/%f';

126. PITR — recover to an instant

Restores the base backup and reapplies the WAL up to the second before the incident.

-- postgresql.conf for the restore
restore_command = 'cp /backup/wal/%f %p'
recovery_target_time = '2026-08-10 14:29:00'

127. Backup on MySQL

mysqldump for a logical one; Percona XtraBackup for a hot physical one.

mysqldump --single-transaction --routines --triggers loja > loja.sql

128. Backup on SQL Server

Full, differential and log backups form the recovery chain.

BACKUP DATABASE loja TO DISK = 'D:\bkp\loja.bak' WITH COMPRESSION;
BACKUP LOG loja TO DISK = 'D:\bkp\loja_log.trn';

129. Backup on Oracle

RMAN manages the backup, the catalogue and the recovery.

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

130. Backup on Firebird

gbak does a logical backup; nbackup does a physical incremental one.

gbak -b -user SYSDBA -password senha /dados/loja.fdb /backup/loja.fbk

131. Test the restore

Schedule a periodic restore on a separate machine. It is the only test that counts.

# monthly cron: restores the latest backup and runs a sanity query
pg_restore -d loja_teste ultimo.dump && psql -d loja_teste -c 'SELECT COUNT(*) FROM pedidos;'

132. 3-2-1 retention

Three copies, on two types of media, one off-site. It applies to databases too.

-- Daily locally (7 days) + weekly in object storage (8 weeks) + monthly offsite (12 months)

133. Backup before a migration

Every structural migration starts with a verified backup and a written rollback plan.

pg_dump -Fc loja > pre-migracao-$(date +%F).dump

Maintenance, vacuum and statistics

The database needs housekeeping. Ignoring it means accumulating debt until it stops.

134. VACUUM

Marks as reusable the space of old row versions (MVCC).

VACUUM pedidos;

135. VACUUM FULL

Rewrites the table and returns space to the system — with an exclusive lock. Maintenance window only.

VACUUM FULL pedidos;

136. Autovacuum

It runs on its own. The common mistake is leaving it too conservative on high-write tables.

ALTER TABLE eventos SET (autovacuum_vacuum_scale_factor = 0.02);

137. Table bloat

Accumulated dead space. The symptom: the table grows and the query slows down with no increase in useful data.

SELECT relname, n_dead_tup, n_live_tup
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

138. Transaction ID wraparound

A real PostgreSQL risk: without vacuum, the transaction counter overflows and the database enters protected mode.

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

139. ANALYZE

Recalculates the distribution statistics the optimiser uses.

ANALYZE VERBOSE pedidos;

140. Extended statistics

Teaches the optimiser that two columns are correlated (city and state, for instance).

CREATE STATISTICS st_cidade_estado (dependencies) ON cidade, estado FROM clientes;
ANALYZE clientes;

141. Increase the sampling

A column with an irregular distribution deserves a more detailed histogram.

ALTER TABLE pedidos ALTER COLUMN status SET STATISTICS 1000;
ANALYZE pedidos;

142. REINDEX CONCURRENTLY

Rebuilds bloated indexes without blocking writes.

REINDEX TABLE CONCURRENTLY pedidos;

143. Maintenance on MySQL

OPTIMIZE TABLE reorganises; ANALYZE updates the statistics.

OPTIMIZE TABLE pedidos;
ANALYZE TABLE pedidos;

144. Maintenance on SQL Server

Index rebuild/reorganize and statistics updates.

ALTER INDEX ALL ON pedidos REBUILD;
UPDATE STATISTICS pedidos;

145. Maintenance on Firebird

A sweep removes old versions; a backup/restore compacts the file.

gfix -sweep -user SYSDBA -password senha /dados/loja.fdb

146. Maintenance window

Schedule it for the usage trough, monitor the execution time and have an abort plan.

SET statement_timeout = '2h';   -- an explicit limit for the heavy routine

Advanced security

Sensitive data demands more than a username and a password.

147. Row Level Security

Filters rows by policy, inside the database itself.

ALTER TABLE pedidos ENABLE ROW LEVEL SECURITY;
CREATE POLICY p_tenant ON pedidos USING (tenant_id = current_setting('app.tenant')::int);

148. A policy per operation

Different rules for reading and writing.

CREATE POLICY p_insert ON pedidos FOR INSERT
WITH CHECK (tenant_id = current_setting('app.tenant')::int);

149. FORCE ROW LEVEL SECURITY

Applies the policy even to the table's owner.

ALTER TABLE pedidos FORCE ROW LEVEL SECURITY;

150. Encryption at rest

TDE on SQL Server/Oracle, LUKS/dm-crypt on the disk, or field-level encryption in the application.

-- SQL Server
ALTER DATABASE loja SET ENCRYPTION ON;

151. Field-level encryption

Only whoever has the key reads the data — it protects even against a leaked dump.

CREATE EXTENSION IF NOT EXISTS pgcrypto;
INSERT INTO clientes (cpf_cifrado) VALUES (pgp_sym_encrypt('123.456.789-00', 'chave'));
SELECT pgp_sym_decrypt(cpf_cifrado, 'chave') FROM clientes;

152. Password hashing

A password is never reversible encryption: it is a hash with a salt and a high cost (bcrypt, argon2).

SELECT crypt('senha-do-usuario', gen_salt('bf', 12));

153. Mandatory TLS connection

Without TLS, credentials and data travel readable across the network.

-- pg_hba.conf
hostssl  all  all  0.0.0.0/0  scram-sha-256

154. SCRAM instead of MD5

PostgreSQL's modern authentication method.

ALTER SYSTEM SET password_encryption = 'scram-sha-256';

155. Auditing

Recording who read and changed sensitive data — a legal requirement in many scenarios.

-- pgaudit
ALTER SYSTEM SET pgaudit.log = 'write, ddl';

156. Data masking

A test environment should not have real PII. Mask it in the copy.

UPDATE clientes SET email = 'user' || id || '@exemplo.com', cpf = NULL;

157. SQL injection

The defence is a parameterised query. Concatenating a string with user input is the origin of nearly every leak.

-- Vulnerable
-- 'SELECT * FROM users WHERE email = ''' + entrada + ''''

-- Safe
SELECT * FROM users WHERE email = $1;

158. SQL injection in database code

A function with dynamic SQL is vulnerable too. Use quote_ident and quote_literal.

EXECUTE format('SELECT * FROM %I WHERE nome = %L', tabela, valor);

159. Principle of least privilege

The application does not create tables, does not drop anything and does not read what it does not need.

REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO app_web;

Extensions and modern features

What the database has gained in recent years and many people still do not use.

160. pg_stat_statements

The most valuable extension for performance: it aggregates time per normalised query.

CREATE EXTENSION pg_stat_statements;
SELECT query, calls, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

161. PostGIS

Geographic data with a spatial index and real distance calculation.

CREATE EXTENSION postgis;
SELECT nome FROM lojas
ORDER BY geom <-> ST_MakePoint(-47.06, -22.90)::geography LIMIT 5;

162. TimescaleDB

Time series at scale: hypertables, compression and continuous aggregation.

SELECT create_hypertable('metricas', 'coletado_em');

163. pgvector

Vector similarity search — the basis of RAG and recommendation.

CREATE EXTENSION vector;
CREATE TABLE docs (id BIGSERIAL, embedding vector(1536));
SELECT * FROM docs ORDER BY embedding <-> $1 LIMIT 5;

164. Foreign Data Wrapper

Queries another database (or a CSV, or an API) as if it were a local table.

CREATE EXTENSION postgres_fdw;
IMPORT FOREIGN SCHEMA public FROM SERVER outro INTO externo;

165. Generated columns

A column calculated and stored by the database, always coherent.

ALTER TABLE itens ADD COLUMN total NUMERIC GENERATED ALWAYS AS (qtd * preco) STORED;

166. Standard IDENTITY

The modern replacement for SERIAL, following the SQL standard.

CREATE TABLE t (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY);

167. MERGE

The standard command to insert/update/delete in one pass (PostgreSQL 15+, Oracle, SQL Server).

MERGE INTO estoque est
USING recebimento rec ON est.produto_id = rec.produto_id
WHEN MATCHED THEN UPDATE SET qtd = est.qtd + rec.qtd
WHEN NOT MATCHED THEN INSERT (produto_id, qtd) VALUES (rec.produto_id, rec.qtd);

168. JSON_TABLE

Converts JSON into a relational table inside the query (SQL:2016).

SELECT * FROM JSON_TABLE(
  '[{"id":1,"nome":"Ana"}]', '$[*]'
  COLUMNS (id INT PATH '$.id', nome TEXT PATH '$.nome')
) AS cli_json;

169. Parallel index and parallel query

The database uses several cores on a single query — tune the limits to the machine.

SET max_parallel_workers_per_gather = 4;
EXPLAIN ANALYZE SELECT COUNT(*) FROM eventos;

170. Column compression

TOAST compresses large values; LZ4 is faster than the default in many cases.

ALTER TABLE documentos ALTER COLUMN corpo SET COMPRESSION lz4;

Diagnostics in production

The database is slow. These are the commands that answer 'why' in minutes.

171. What is running right now

The first command of any incident.

SELECT pid, now() - query_start AS duracao, state, query
FROM pg_stat_activity WHERE state <> 'idle' ORDER BY duracao DESC;

172. Queries running for more than 1 minute

Isolates the immediate suspects.

SELECT pid, now() - query_start AS duracao, query FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > INTERVAL '1 minute';

173. Open, idle transactions

'idle in transaction' holds locks and prevents vacuum. A major cause of incidents.

SELECT pid, now() - state_change AS parado, query FROM pg_stat_activity
WHERE state = 'idle in transaction' ORDER BY parado DESC;

174. Cancel and kill

Cancel tries to end the query; terminate drops the connection.

SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE pid = 12345;
SELECT pg_terminate_backend(12345);

175. The blocking chain

Shows who is blocking whom, with each side's query.

SELECT bloqueada.pid AS vitima,
       bloqueadora.pid AS culpada,
       bloqueadora.query
FROM pg_stat_activity bloqueada
INNER JOIN pg_stat_activity bloqueadora
ON bloqueadora.pid = ANY(pg_blocking_pids(bloqueada.pid));

176. Top queries by total time

Optimising the 5 ms query called a million times pays off more than the 2 s one called three times.

SELECT query, calls, round(total_exec_time::numeric, 0) AS ms_total
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

177. Cache hit rate

Below about 99% in OLTP usually indicates insufficient memory.

SELECT round(100.0 * sum(blks_hit) / nullif(sum(blks_hit + blks_read), 0), 2) AS cache_pct
FROM pg_stat_database;

178. Tables most read from disk

It points at where the memory is not keeping up.

SELECT relname, heap_blks_read, heap_blks_hit
FROM pg_statio_user_tables ORDER BY heap_blks_read DESC LIMIT 10;

179. Frequent sequential scans

A large table with many seq scans is crying out for an index.

SELECT relname, seq_scan, seq_tup_read, idx_scan
FROM pg_stat_user_tables WHERE seq_scan > 0 ORDER BY seq_tup_read DESC LIMIT 10;

180. Connections by state

Finds out whether the problem is too many connections rather than a slow query.

SELECT state, COUNT(*) FROM pg_stat_activity GROUP BY state;

181. The size of the database and the tables

Unexpected growth is usually bloat, a new index or a forgotten log.

SELECT pg_size_pretty(pg_database_size(current_database()));
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

182. WAL space

Accumulated WAL means the archive is failing or there is an orphan replication slot — and a full disk ahead.

SELECT COUNT(*) * 16 AS mb_wal FROM pg_ls_waldir();

183. Recorded deadlocks

A growing counter indicates a conflicting access pattern in the application.

SELECT datname, deadlocks, conflicts FROM pg_stat_database;

184. Temp files

A high volume of temporary files means work_mem is low for the current queries.

SELECT datname, temp_files, pg_size_pretty(temp_bytes) FROM pg_stat_database ORDER BY temp_bytes DESC;

185. Log slow queries

Continuous collection is worth more than a one-off investigation.

ALTER SYSTEM SET log_min_duration_statement = '1000';   -- 1 s
SELECT pg_reload_conf();

186. Auto explain

Records the plan of slow queries automatically, with no reproducing by hand.

LOAD 'auto_explain';
SET auto_explain.log_min_duration = '3s';
SET auto_explain.log_analyze = on;

187. See and change parameters

Check the effective value before theorising about the cause.

SHOW work_mem;
SELECT name, setting, unit FROM pg_settings WHERE name LIKE '%mem%';

188. Reload the configuration

Many parameters apply without restarting the server.

ALTER SYSTEM SET work_mem = '32MB';
SELECT pg_reload_conf();

189. Diagnostics on MySQL

The equivalents: the process list and the performance schema.

SHOW FULL PROCESSLIST;
SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;

190. Diagnostics on SQL Server

The DMVs deliver the most expensive queries and the dominant waits.

SELECT TOP 10 total_worker_time/execution_count AS media_cpu,
       text
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY media_cpu DESC;

Production good practices

What prevents the incident before it exists.

191. Versioned migration

The structure changes through a versioned script in the repository, never through a manual change on the server.

-- migrations/0042_add_index_pedidos.sql
CREATE INDEX CONCURRENTLY idx_pedidos_status ON pedidos (status);

192. Reversible migration

Every script has its way back written before it goes up.

-- up
ALTER TABLE pedidos ADD COLUMN canal VARCHAR(20);
-- down
ALTER TABLE pedidos DROP COLUMN canal;

193. Deployment with no downtime

Add a nullable column, populate it in batches, and only then make it mandatory.

ALTER TABLE pedidos ADD COLUMN canal VARCHAR(20);           -- 1
UPDATE pedidos SET canal = 'web' WHERE canal IS NULL;       -- 2 (in batches)
ALTER TABLE pedidos ALTER COLUMN canal SET NOT NULL;        -- 3

194. Careful with locking on ALTER

On a large table, a careless ALTER locks the whole application. Check your version's behaviour.

SET lock_timeout = '3s';   -- fail fast instead of queueing production
ALTER TABLE pedidos ADD COLUMN x INT;

195. Never test in production

An UPDATE and a DELETE start life as a SELECT, and run first on staging with a similar volume.

BEGIN;
UPDATE pedidos SET status = 'x' WHERE ...;
-- check the number of rows affected
ROLLBACK;   -- or COMMIT

196. Continuous monitoring

Track connections, cache hit, replication, size and slow queries — with alerts, not with a dashboard nobody looks at.

-- Minimum alerts: replica lag, disk > 80%, connections > 80% of the limit,
-- transaction open > 5 min, backup failure.

197. Connection limits

Size the pool: more connections than cores usually makes things worse, not better.

SHOW max_connections;
SELECT COUNT(*) FROM pg_stat_activity;

198. Timeouts at every layer

The query, the transaction and the connection all need a limit. Without them, one bad query brings everything down.

SET statement_timeout = '30s';
SET idle_in_transaction_session_timeout = '60s';
SET lock_timeout = '5s';

199. Document the model

COMMENT lives alongside the schema and shows up in the tools — better than an out-of-date wiki.

COMMENT ON TABLE pedidos IS 'Sales orders; one row per completed checkout.';
COMMENT ON COLUMN pedidos.total IS 'Final amount in BRL, discount already applied.';

200. Review before applying

A database script goes through review like any other code — with an EXPLAIN of what changes and a rollback plan.

-- Checklist: backup ok? rollback written? lock estimated? window defined? monitoring on?