Module 2.1 - Indexes and Transactions

📈 Why Indexes

One index and an auxiliary structure that speeds up queries by avoiding the full table scan. Without an index, every search scans the entire table—and performance drops exponentially as data grows.

🎯 Analogy: A Book Index

Imagine looking up a topic in a 500-page book without an index - you would read page by page. With an index, you go straight to the right chapter. A database index works exactly the same way.

• B-tree: A balanced structure, ideal for ranges and sorting (standard in most DBMSs)
• Hash: Exact equality search, O(1), but no range support
• Covering index: Contains all query columns, avoiding access to the table
• Selectivity: The more unique values, the better the index works

📊 Real Impact

Table with 1 million rows: full scan ~500ms, index scan ~2ms
Each index takes up space on disk and slows writes (INSERT/UPDATE/DELETE)
Index on a column with low selectivity (e.g., boolean) usually doesn't help
PostgreSQL creates automatic indexes for PRIMARY KEY and UNIQUE

🔧 CREATE INDEX in practice

Creating an index is simple. Create the index right requires understanding the query, the data, and the trade-off between reads and writes. The wrong index is worse than no index - consumes space and slows down writes without providing any benefit.

💻 SQL Examples

-- Indice simples: acelera buscas por email
CREATE INDEX idx_cliente_email ON cliente (email);

-- Indice composto: otimiza queries com WHERE cidade AND estado
CREATE INDEX idx_endereco_cidade_estado ON endereco (cidade, estado);

-- Indice parcial: apenas registros ativos (economia de espaco)
CREATE INDEX idx_pedido_ativo ON pedido (data_criacao)
WHERE status = 'ativo';

-- Indice com INCLUDE: colunas extras sem afetar a arvore
CREATE INDEX idx_produto_categoria ON produto (categoria_id)
INCLUDE (nome, preco);

-- Indice unico: garante unicidade + acelera buscas
CREATE UNIQUE INDEX idx_usuario_cpf ON usuario (cpf);

✅ DO

• Index columns used in WHERE, JOIN, and ORDER BY
• Prefer composite indexes for multi-column queries
• Use a partial index to filter subsets
• Monitor unused indexes and remove them

❌ DO NOT DO

• Indexing low-selectivity columns (boolean, status)
• Creating an index for every column without analysis
• Ignoring the write cost in tables with many INSERTs
• Forgetting to REINDEX after large data loads

🔬 EXPLAIN and EXPLAIN ANALYZE

EXPLAIN shows the execution plan the database will use. EXPLAIN ANALYZE executes the query and shows actual timings. Without these tools, you’re optimizing in the dark. And the performance X-ray.

💻 Usage Examples

-- Ver plano estimado (nao executa a query)
EXPLAIN SELECT * FROM cliente WHERE email = 'ana@example.com';

-- Ver plano + tempo real (executa a query)
EXPLAIN ANALYZE SELECT * FROM cliente WHERE email = 'ana@example.com';

-- Exemplo de saida:
-- Index Scan using idx_cliente_email on cliente
--   (cost=0.29..8.31 rows=1 width=64)
--   (actual time=0.027..0.028 rows=1 loops=1)
-- Planning Time: 0.152 ms
-- Execution Time: 0.065 ms

🔍 Execution Plan Operators

Seq Scan

Sequential scan of the entire table. A sign that an index is missing.

Index Scan

Uses the index to locate records. What we want to see.

Nested Loop

Row-by-row JOIN. Efficient for a small number of records.

Hash Join

Builds a hash table in memory. Efficient for many records.

💡 Practical Tip

If EXPLAIN shows Seq Scan on a large table with a WHERE filter, an index is probably missing on that column. Create the index and run EXPLAIN again to confirm it changed to Index Scan.

🔒 ACID Transactions

One transaction and a set of operations that executes as an atomic unit: all or nothing. If any operation fails, they are all rolled back. A bank transfer without a transaction can lose money. ACID is essential.

💻 Transaction in Practice

-- Transferencia bancaria atomica
BEGIN;
  UPDATE conta SET saldo = saldo - 500.00 WHERE id = 1;
  UPDATE conta SET saldo = saldo + 500.00 WHERE id = 2;

  -- Verifica se saldo ficou negativo
  -- Se sim, desfaz tudo
  DO $$
  BEGIN
    IF (SELECT saldo FROM conta WHERE id = 1) < 0 THEN
      RAISE EXCEPTION 'Saldo insuficiente';
    END IF;
  END $$;
COMMIT;

-- Se algo der errado, ROLLBACK desfaz tudo
-- ROLLBACK;

🎯 The 4 ACID Properties

A - Atomicity

All or nothing. If one operation fails, they are all rolled back.

C - Consistency

The database goes from one valid state to another valid state. Constraints are respected.

I - Isolation

Concurrent transactions do not interfere with each other. Each one sees a consistent snapshot.

D - Durability

After COMMIT, the data persists even if the server crashes. WAL ensures this.

🔄 Isolation Levels

O isolation level defines what a transaction sees of changes made by other concurrent transactions. Incorrect isolation causes dirty reads, phantom reads, and inconsistent data.

💻 Configuring Isolation

-- Definir nivel para a transacao atual
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  SELECT * FROM estoque WHERE produto_id = 42;
  -- ... operacoes ...
COMMIT;

-- Definir nivel padrao da sessao
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Verificar nivel atual
SHOW transaction_isolation;

📊 Level Comparison

Level	Dirty Read	Non-Repeatable	Phantom	Usage
READ UNCOMMITTED	Yes	Yes	Yes	Almost never
READ COMMITTED	No	Yes	Yes	PG Standard
REPEATABLE READ	No	No	Yes	Reports
SERIALIZABLE	No	No	No	Critical

💡 Practical Tip

For most applications, READ COMMITTED (PostgreSQL's default) is enough. Use REPEATABLE READ for reports that need a consistent snapshot. SERIALIZABLE only for critical financial operations where total consistency is mandatory.

📋 SQL Best Practices

Poorly written SQL and technical debt. Patterns and habits for safe, high-performance, maintainable queries. Good practices today prevent incident tomorrow.

✅ DO

• Use prepared statements to prevent SQL injection
• Select only the required columns (never SELECT *)
• Use parameters instead of concatenating values
• Monitor slow queries with pg_stat_statements
• Keep transactions short and focused

❌ DO NOT DO

• SELECT * in production (transfers unnecessary data)
• Concatenate user input directly in the query
• Transactions long that hold locks for minutes
• Ignore EXPLAIN in queries running in production
• Create index without testing the impact on writes

💻 Examples of Best Practices

-- RUIM: SELECT * e concatenacao
SELECT * FROM usuario WHERE nome = '" + input + "';

-- BOM: colunas especificas e parametros
SELECT id, nome, email FROM usuario WHERE nome = $1;

-- RUIM: transacao longa com logica de negocio
BEGIN;
  SELECT * FROM pedido WHERE id = 1;
  -- ... 30 segundos de processamento em app ...
  UPDATE pedido SET status = 'aprovado' WHERE id = 1;
COMMIT;

-- BOM: transacao curta e focada
-- Faz o processamento ANTES de abrir a transacao
BEGIN;
  UPDATE pedido SET status = 'aprovado' WHERE id = 1;
  INSERT INTO log_auditoria (pedido_id, acao) VALUES (1, 'aprovado');
COMMIT;

🛡️ SQL Security Checklist

• Prepared statements: Always use for queries with external input
• Principle of least privilege: Each app connects using a database user with minimal permissions
• Monitoring: pg_stat_statements + alerts for queries over X ms
• Query review: Run EXPLAIN on every new query before it goes to production

← Back to Track 2 Module 2.2 - DBMS Types →