Deep EVM #27: Rendimiento de PostgreSQL a Escala — Índices, VACUUM y Optimización de Consultas

Índices: más allá de B-tree

PostgreSQL soporta múltiples tipos de índices:

B-tree (predeterminado)

Ideal para comparaciones de igualdad y rango:

CREATE INDEX idx_tx_block ON transactions (block_number);

GIN (Generalized Inverted Index)

Ideal para búsquedas en JSONB y arrays:

CREATE INDEX idx_tx_data ON transactions USING GIN (data);

BRIN (Block Range Index)

Ideal para datos naturalmente ordenados (timestamps, IDs secuenciales). Muy compacto:

CREATE INDEX idx_tx_created ON transactions USING BRIN (created_at);

Hash

Solo para igualdad exacta, más compacto que B-tree:

CREATE INDEX idx_tx_hash ON transactions USING HASH (tx_hash);

VACUUM y autovacuum

PostgreSQL usa MVCC (Multi-Version Concurrency Control). Las filas actualizadas o eliminadas no se borran físicamente — se marcan como “muertas”. VACUUM limpia estas filas muertas.

-- VACUUM manual
VACUUM (VERBOSE) transactions;

-- Configurar autovacuum agresivo para tablas de alto tráfico
ALTER TABLE transactions SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.005,
    autovacuum_vacuum_cost_delay = 5
);

EXPLAIN ANALYZE

La herramienta más importante para optimización de consultas:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM transactions
WHERE block_number BETWEEN 18000000 AND 18001000
AND from_addr = '\xABC...'::bytea;

Busca:

• Seq Scan en tablas grandes = necesitas un índice
• Bitmap Heap Scan = OK, usa índice
• Index Only Scan = óptimo, no necesita acceder a la tabla
• Nested Loop con tabla grande = considera un Hash Join

Connection pooling con PgBouncer

PostgreSQL crea un proceso por conexión. Con miles de conexiones concurrentes, esto consume memoria y CPU. PgBouncer multiplexa conexiones:

[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 50

pg_stat_statements

Extensión esencial para identificar consultas lentas:

SELECT query, calls, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

Conclusión

El rendimiento de PostgreSQL a escala requiere índices apropiados, VACUUM bien configurado, EXPLAIN ANALYZE para diagnosticar problemas, connection pooling y monitoreo continuo con pg_stat_statements.