postgresqlbase de datosperformanceoptimización

PostgreSQL en Producción: Optimización que Realmente Funciona

Publicado el 3 de agosto de 2026Por Binary Core

PostgreSQL es increíblemente potente por defecto, pero en producción requiere optimización específica para tu workload. En Binary Core, gestionamos bases de datos con millones de registros. Aquí compartimos las técnicas que realmente marcan la diferencia.

Índices Efectivos

Cuándo Crear Índices

No crees índices al azar. Analiza tus queries primero:

sql
-- Identificar queries lentas
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Ver qué índices se usan
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan;

Tipos de Índices

sql
-- B-tree (default, para igualdad y rangos)
CREATE INDEX idx_users_email ON users(email);

-- Para columnas con muchos valores duplicados
CREATE INDEX idx_orders_status ON orders(status) WHERE status = 'pending';

-- Para búsqueda de texto completo
CREATE INDEX idx_products_fts ON products 
USING gin(to_tsvector('english', name || ' ' || description));

-- Para arrays
CREATE INDEX idx_tags ON posts USING gin(tags);

-- Para JSONB
CREATE INDEX idx_metadata ON users USING gin(metadata);

Índices Parciales

Los índices parciales son más pequeños y más rápidos:

sql
-- Solo indexar usuarios activos
CREATE INDEX idx_active_users ON users(email) 
WHERE is_active = true AND deleted_at IS NULL;

-- Solo indexar pedidos recientes
CREATE INDEX idx_recent_orders ON orders(user_id, created_at) 
WHERE created_at > NOW() - INTERVAL '6 months';

Índices Compuestos

El orden de las columnas importa:

sql
-- Bueno para queries que filtran por ambas columnas
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Queries que usan este índice eficientemente:
SELECT * FROM orders WHERE user_id = 123 AND created_at > '2024-01-01';
SELECT * FROM orders WHERE user_id = 123;

-- NO usa el índice eficientemente:
SELECT * FROM orders WHERE created_at > '2024-01-01';

EXPLAIN ANALYZE

Interpretar Planes de Ejecución

sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id;

Métricas Clave

Seq Scan           → Escaneo secuencial (malo para tablas grandes)
Index Scan         → Escaneo de índice (bueno)
Index Only Scan    → Solo lee índice, no tabla (excelente)
Hash Join          → Join con hash (bueno para datasets grandes)
Nested Loop        → Join iterativo (malo si no hay índices)
Bitmap Heap Scan   → Escaneo bitmap (intermedio)

Identificar Problemas

sql
-- Missing index en JOIN
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- Si ves "Seq Scan" en users, necesitas índice en users.id

-- Missing index en WHERE
EXPLAIN SELECT * FROM orders WHERE status = 'pending';
-- Si ves "Seq Scan" en orders, necesitas índice en orders.status

-- N+1 problem
EXPLAIN SELECT * FROM comments WHERE post_id IN (1, 2, 3, ...);
-- Si ves múltiples "Index Scan", considera JOIN o subquery

Connection Pooling con PgBouncer

PostgreSQL crea un proceso por conexión, lo cual es costoso. PgBouncer soluciona esto:

Instalación y Configuración

bash
# Instalar PgBouncer
sudo apt-get install pgbouncer

# Configurar pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600

Modos de Pooling

ini
# Session mode: una conexión por cliente (como sin pooler)
pool_mode = session

# Transaction mode: conexión por transacción (recomendado)
pool_mode = transaction

# Statement mode: conexión por statement (avanzado)
pool_mode = statement

Monitorear PgBouncer

sql
-- Conectar a PgBouncer (no a PostgreSQL)
psql -h localhost -p 6432 pgbouncer

-- Ver estadísticas
SHOW STATS;
SHOW POOLS;
SHOW LISTS;

Particionamiento de Tablas

Para tablas muy grandes (>100M filas), el particionamiento es esencial:

Particionamiento por Rango

sql
-- Tabla maestra
CREATE TABLE orders (
    id BIGSERIAL,
    user_id INTEGER NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    amount DECIMAL(10,2),
    status VARCHAR(50)
) PARTITION BY RANGE (created_at);

-- Particiones por año
CREATE TABLE orders_2024 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE orders_2025 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

-- Partición automática para nuevos datos
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

Particionamiento por Lista

sql
CREATE TABLE orders (
    id BIGSERIAL,
    user_id INTEGER NOT NULL,
    region VARCHAR(50) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
) PARTITION BY LIST (region);

CREATE TABLE orders_europe PARTITION OF orders
    FOR VALUES IN ('ES', 'FR', 'DE', 'IT');

CREATE TABLE orders_americas PARTITION OF orders
    FOR VALUES IN ('US', 'MX', 'BR', 'AR');

Beneficios del Particionamiento

Queries más rápidas (solo escanean partición relevante)
Mantenimiento más fácil (DROP TABLE vs DELETE)
Mejor uso de caché (particiones calientes en memoria)
Archivado simple (mover particiones viejas a storage barato)

Monitoreo de Queries Lentas

Habilitar pg_stat_statements

sql
-- Habilitar extensión
CREATE EXTENSION pg_stat_statements;

-- Configurar postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000

Queries Más Lentas

sql
SELECT 
    query,
    calls,
    total_exec_time / 1000 / 60 as total_minutes,
    mean_exec_time as mean_ms,
    stddev_exec_time as stddev_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

Queries Más Frecuentes

sql
SELECT 
    query,
    calls,
    total_exec_time / 1000 / 60 as total_minutes
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 20;

Queries con Más I/O

sql
SELECT 
    query,
    calls,
    shared_blks_hit,
    shared_blks_read,
    (shared_blks_hit::float / NULLIF(shared_blks_hit + shared_blks_read, 0)) * 100 as cache_hit_ratio
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 20;

Configuración de PostgreSQL

postgresql.conf Clave

ini
# Memoria
shared_buffers = 4GB              # 25% de RAM
effective_cache_size = 12GB       # 75% de RAM
work_mem = 64MB                   # Por operación de sort/hash
maintenance_work_mem = 1GB        # Para VACUUM, CREATE INDEX

# WAL
wal_buffers = 16MB
min_wal_size = 1GB
max_wal_size = 4GB
wal_compression = on

# Planner
random_page_cost = 1.1            # Para SSDs (default 4.0 para HDD)
effective_io_concurrency = 200    # Para SSDs

# Conexiones
max_connections = 200

Autovacuum

ini
# Ajustar para tablas grandes
autovacuum = on
autovacuum_max_workers = 4
autovacuum_naptime = 1min

# Para tablas específicas
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.1,
    autovacuum_analyze_scale_factor = 0.05
);

Herramientas de Monitoreo

pg_stat_activity

sql
-- Ver queries activos
SELECT 
    pid,
    now() - query_start as duration,
    query,
    state
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- Matar query problemático
SELECT pg_terminate_backend(pid);

pg_stat_user_tables

sql
-- Tamaño de tablas
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size,
    n_live_tup,
    n_dead_tup
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Estrategias de Mantenimiento

VACUUM MANUAL

sql
-- VACUUM completo (bloquea tabla)
VACUUM FULL orders;

-- VACUUM regular (no bloquea)
VACUUM orders;

-- VACUUM con análisis
VACUUM ANALYZE orders;

-- VACUUM de tabla específica
VACUUM (VERBOSE, ANALYZE) orders;

REINDEX

sql
-- Reindexar tabla completa
REINDEX TABLE orders;

-- Reindexar índice específico
REINDEX INDEX idx_orders_user_id;

-- Reindexar concurrentemente (no bloquea)
REINDEX INDEX CONCURRENTLY idx_orders_user_id;

CLUSTER

sql
-- Reorganizar tabla físicamente según índice
CLUSTER orders USING idx_orders_user_id;

-- Cluster todas las tablas
CLUSTER;

Backup y Restore

pg_dump

bash
# Backup completo
pg_dump -h localhost -U user -d mydb -F c -f backup.dump

# Backup solo esquema
pg_dump -h localhost -U user -d mydb --schema-only -f schema.sql

# Backup solo datos
pg_dump -h localhost -U user -d mydb --data-only -f data.sql

# Backup tabla específica
pg_dump -h localhost -U user -d mydb -t orders -f orders.sql

pg_restore

bash
# Restore desde formato custom
pg_restore -h localhost -U user -d mydb backup.dump

# Restore solo esquema
pg_restore -h localhost -U user -d mydb --schema-only backup.dump

# Restore con jobs paralelos
pg_restore -h localhost -U user -d mydb -j 4 backup.dump

Mejores Prácticas de Binary Core

Siempre usa EXPLAIN ANALYZE antes de crear índices
PgBouncer es obligatorio en producción
Monitorea pg_stat_statements semanalmente
Particiona tablas >100M filas por fecha
Ajusta work_mem según tu workload
Usa índices parciales para datos filtrados
Habilita WAL compression para reducir I/O
Configura autovacuum agresivo para tablas con alta rotación
Backups diarios + WAL archiving para recuperación point-in-time
Monitorea cache hit ratio — debe ser >99%

Conclusión

PostgreSQL es increíblemente potente, pero requiere configuración específica para producción. Los índices correctos, PgBouncer, particionamiento y monitoreo continuo son la diferencia entre una base de datos lenta y una que escala sin problemas.

En Binary Core, estas técnicas nos han permitido manejar bases de datos con cientos de millones de registros con latencias consistentemente bajas. La clave es entender tu workload específico y optimizar en consecuencia — no hay configuración única que sirva para todos.

Binary Core

Equipo Binary Core

← Volver al blog