Optimización de Consultas PostgreSQL

El rendimiento de la base de datos es fundamental para el éxito de cualquier aplicación. PostgreSQL, aunque potente y flexible, requiere una configuración y optimización adecuadas para alcanzar su máximo potencial. Esta guía cubre técnicas esenciales, mejores prácticas y estrategias avanzadas para optimizar tus consultas y mejorar el rendimiento general.

Fundamentos de Optimización

La optimización de PostgreSQL no es una tarea única, sino un proceso continuo que requiere análisis, medición y ajustes constantes. Los tres pilares fundamentales son:

1. Consultas Eficientes

Escribir SQL que aproveche los índices y evite operaciones costosas.

2. Indexación Estratégica

Balance entre velocidad de lectura y sobrecarga de escritura.

3. Configuración Apropiada

Ajustar parámetros según el hardware y tipo de carga de trabajo.

EXPLAIN y EXPLAIN ANALYZE

Estas herramientas son esenciales para entender cómo PostgreSQL ejecuta tus consultas.

EXPLAIN: Planificación de Consultas

EXPLAIN muestra el plan de ejecución sin ejecutar la consulta:

sql

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

Salida típica:

plaintext

HashAggregate  (cost=234.56..245.67 rows=1000 width=64)
  Group Key: u.name
  ->  Hash Left Join  (cost=45.00..200.00 rows=5000 width=36)
        Hash Cond: (o.user_id = u.id)
        ->  Seq Scan on orders o  (cost=0.00..100.00 rows=5000 width=8)
        ->  Hash  (cost=30.00..30.00 rows=1000 width=36)
              ->  Index Scan using idx_users_created_at on users u
                    (cost=0.29..30.00 rows=1000 width=36)
                    Index Cond: (created_at > '2024-01-01'::date)
HashAggregate  (cost=234.56..245.67 rows=1000 width=64)
  Group Key: u.name
  ->  Hash Left Join  (cost=45.00..200.00 rows=5000 width=36)
        Hash Cond: (o.user_id = u.id)
        ->  Seq Scan on orders o  (cost=0.00..100.00 rows=5000 width=8)
        ->  Hash  (cost=30.00..30.00 rows=1000 width=36)
              ->  Index Scan using idx_users_created_at on users u
                    (cost=0.29..30.00 rows=1000 width=36)
                    Index Cond: (created_at > '2024-01-01'::date)

EXPLAIN ANALYZE: Ejecución Real

EXPLAIN ANALYZE ejecuta la consulta y muestra estadísticas reales:

sql

EXPLAIN ANALYZE
SELECT * FROM users 
WHERE email = 'user@example.com';
EXPLAIN ANALYZE
SELECT * FROM users 
WHERE email = 'user@example.com';

Información clave a buscar:

Seq Scan: Escaneo secuencial (generalmente lento en tablas grandes)
Index Scan: Uso de índice (generalmente rápido)
cost: Estimación de costo relativo
actual time: Tiempo real de ejecución
rows: Número de filas estimadas vs. reales

EXPLAIN con Opciones Detalladas

sql

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON)
SELECT p.*, c.name as category_name
FROM products p
INNER JOIN categories c ON p.category_id = c.id
WHERE p.price > 100
ORDER BY p.created_at DESC
LIMIT 20;
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON)
SELECT p.*, c.name as category_name
FROM products p
INNER JOIN categories c ON p.category_id = c.id
WHERE p.price > 100
ORDER BY p.created_at DESC
LIMIT 20;

Opciones útiles:

ANALYZE: Ejecuta y muestra tiempos reales
BUFFERS: Muestra uso de buffer cache
VERBOSE: Incluye información adicional
FORMAT JSON: Salida en formato JSON para análisis

Estrategias de Indexación

Los índices son cruciales para el rendimiento, pero deben usarse estratégicamente.

Tipos de Índices

1. B-Tree (Por Defecto)

El tipo más común, ideal para comparaciones y rangos:

sql

-- Índice simple
CREATE INDEX idx_users_email ON users(email);

-- Índice compuesto (orden importa)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- Índice con cláusula WHERE (índice parcial)
CREATE INDEX idx_active_users_email 
ON users(email) 
WHERE is_active = true;

-- Índice con INCLUDE (covering index)
CREATE INDEX idx_users_email_covering 
ON users(email) 
INCLUDE (name, created_at);
-- Índice simple
CREATE INDEX idx_users_email ON users(email);

-- Índice compuesto (orden importa)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- Índice con cláusula WHERE (índice parcial)
CREATE INDEX idx_active_users_email 
ON users(email) 
WHERE is_active = true;

-- Índice con INCLUDE (covering index)
CREATE INDEX idx_users_email_covering 
ON users(email) 
INCLUDE (name, created_at);

2. GIN (Generalized Inverted Index)

Ideal para búsquedas de texto completo y arrays:

sql

-- Para búsqueda de texto completo
CREATE INDEX idx_products_search 
ON products 
USING GIN(to_tsvector('spanish', name || ' ' || description));

-- Búsqueda optimizada
SELECT * FROM products
WHERE to_tsvector('spanish', name || ' ' || description) 
      @@ to_tsquery('spanish', 'laptop & gaming');

-- Para columnas JSONB
CREATE INDEX idx_metadata ON events USING GIN(metadata);

-- Búsqueda en JSONB
SELECT * FROM events 
WHERE metadata @> '{"user_type": "premium"}';

-- Para arrays
CREATE INDEX idx_product_tags ON products USING GIN(tags);
-- Para búsqueda de texto completo
CREATE INDEX idx_products_search 
ON products 
USING GIN(to_tsvector('spanish', name || ' ' || description));

-- Búsqueda optimizada
SELECT * FROM products
WHERE to_tsvector('spanish', name || ' ' || description) 
      @@ to_tsquery('spanish', 'laptop & gaming');

-- Para columnas JSONB
CREATE INDEX idx_metadata ON events USING GIN(metadata);

-- Búsqueda en JSONB
SELECT * FROM events 
WHERE metadata @> '{"user_type": "premium"}';

-- Para arrays
CREATE INDEX idx_product_tags ON products USING GIN(tags);

3. GiST (Generalized Search Tree)

Útil para datos geométricos y rangos:

sql

-- Para geometría (con PostGIS)
CREATE INDEX idx_locations_geom 
ON locations 
USING GIST(geom);

-- Para rangos de fechas
CREATE INDEX idx_events_date_range 
ON events 
USING GIST(tsrange(start_date, end_date));

-- Para búsqueda de texto (alternativa a GIN)
CREATE INDEX idx_articles_search 
ON articles 
USING GIST(to_tsvector('spanish', content));
-- Para geometría (con PostGIS)
CREATE INDEX idx_locations_geom 
ON locations 
USING GIST(geom);

-- Para rangos de fechas
CREATE INDEX idx_events_date_range 
ON events 
USING GIST(tsrange(start_date, end_date));

-- Para búsqueda de texto (alternativa a GIN)
CREATE INDEX idx_articles_search 
ON articles 
USING GIST(to_tsvector('spanish', content));

4. BRIN (Block Range Index)

Excelente para tablas grandes con datos ordenados naturalmente:

sql

-- Ideal para logs ordenados por timestamp
CREATE INDEX idx_logs_created 
ON logs 
USING BRIN(created_at);

-- Ocupa mucho menos espacio que B-Tree
-- Perfecto para tablas con millones de filas
-- Ideal para logs ordenados por timestamp
CREATE INDEX idx_logs_created 
ON logs 
USING BRIN(created_at);

-- Ocupa mucho menos espacio que B-Tree
-- Perfecto para tablas con millones de filas

5. Hash

Solo para igualdad exacta (raramente usado):

sql

CREATE INDEX idx_users_uuid 
ON users 
USING HASH(uuid);
CREATE INDEX idx_users_uuid 
ON users 
USING HASH(uuid);

Índices Únicos y Constraints

sql

-- Índice único
CREATE UNIQUE INDEX idx_users_email_unique 
ON users(email);

-- Índice único parcial
CREATE UNIQUE INDEX idx_active_users_username 
ON users(username) 
WHERE is_active = true;

-- Índice para foreign key (mejora JOINs)
CREATE INDEX idx_orders_user_id 
ON orders(user_id);
-- Índice único
CREATE UNIQUE INDEX idx_users_email_unique 
ON users(email);

-- Índice único parcial
CREATE UNIQUE INDEX idx_active_users_username 
ON users(username) 
WHERE is_active = true;

-- Índice para foreign key (mejora JOINs)
CREATE INDEX idx_orders_user_id 
ON orders(user_id);

Mejores Prácticas de Indexación

✅ Hacer

Indexar columnas en WHERE, JOIN, ORDER BY

sql

-- Si usas frecuentemente:
SELECT * FROM orders 
WHERE user_id = 123 
ORDER BY created_at DESC;

-- Crea:
CREATE INDEX idx_orders_user_date 
ON orders(user_id, created_at DESC);
-- Si usas frecuentemente:
SELECT * FROM orders 
WHERE user_id = 123 
ORDER BY created_at DESC;

-- Crea:
CREATE INDEX idx_orders_user_date 
ON orders(user_id, created_at DESC);

Usar índices compuestos estratégicamente

sql

-- Para consultas con múltiples filtros
CREATE INDEX idx_products_category_price 
ON products(category_id, price) 
WHERE is_active = true;
-- Para consultas con múltiples filtros
CREATE INDEX idx_products_category_price 
ON products(category_id, price) 
WHERE is_active = true;

Índices parciales para subconjuntos comunes

sql

-- Solo indexar registros activos
CREATE INDEX idx_active_orders 
ON orders(created_at) 
WHERE status IN ('pending', 'processing');
-- Solo indexar registros activos
CREATE INDEX idx_active_orders 
ON orders(created_at) 
WHERE status IN ('pending', 'processing');

INCLUDE para covering indexes

sql

-- Index-only scan posible
CREATE INDEX idx_users_email_data 
ON users(email) 
INCLUDE (name, phone, created_at);
-- Index-only scan posible
CREATE INDEX idx_users_email_data 
ON users(email) 
INCLUDE (name, phone, created_at);

❌ Evitar

Índices en columnas de baja selectividad

sql

-- MAL: Pocas variaciones de valor
CREATE INDEX idx_users_is_active ON users(is_active);

-- MEJOR: Índice parcial si realmente necesitas
CREATE INDEX idx_active_users 
ON users(id) 
WHERE is_active = true;
-- MAL: Pocas variaciones de valor
CREATE INDEX idx_users_is_active ON users(is_active);

-- MEJOR: Índice parcial si realmente necesitas
CREATE INDEX idx_active_users 
ON users(id) 
WHERE is_active = true;

Demasiados índices en tablas con muchas escrituras

sql

-- Cada índice ralentiza INSERT, UPDATE, DELETE
-- Analiza cuáles realmente se usan:
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY schemaname, tablename;
-- Cada índice ralentiza INSERT, UPDATE, DELETE
-- Analiza cuáles realmente se usan:
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY schemaname, tablename;

Índices redundantes

sql

-- Si tienes idx_users_email_name (email, name)
-- NO necesitas idx_users_email (email)
-- El índice compuesto puede usarse para consultas de solo email
-- Si tienes idx_users_email_name (email, name)
-- NO necesitas idx_users_email (email)
-- El índice compuesto puede usarse para consultas de solo email

Mantenimiento de Índices

sql

-- Reindexar tabla completa
REINDEX TABLE users;

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

-- Reindexar concurrentemente (sin bloquear)
REINDEX INDEX CONCURRENTLY idx_users_email;

-- Ver tamaño de índices
SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes
WHERE tablename = 'users'
ORDER BY pg_relation_size(indexname::regclass) DESC;
-- Reindexar tabla completa
REINDEX TABLE users;

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

-- Reindexar concurrentemente (sin bloquear)
REINDEX INDEX CONCURRENTLY idx_users_email;

-- Ver tamaño de índices
SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes
WHERE tablename = 'users'
ORDER BY pg_relation_size(indexname::regclass) DESC;

Optimización de Consultas

Evitar SELECT *

sql

-- ❌ MAL: Trae todas las columnas
SELECT * FROM users WHERE id = 123;

-- ✅ BIEN: Solo columnas necesarias
SELECT id, name, email FROM users WHERE id = 123;
-- ❌ MAL: Trae todas las columnas
SELECT * FROM users WHERE id = 123;

-- ✅ BIEN: Solo columnas necesarias
SELECT id, name, email FROM users WHERE id = 123;

Usar LIMIT Apropiadamente

sql

-- Para paginación eficiente
SELECT id, name, email 
FROM users 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 0;

-- Mejor con keyset pagination para grandes offsets
SELECT id, name, email 
FROM users 
WHERE created_at < '2024-01-01 12:00:00'
ORDER BY created_at DESC 
LIMIT 20;
-- Para paginación eficiente
SELECT id, name, email 
FROM users 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 0;

-- Mejor con keyset pagination para grandes offsets
SELECT id, name, email 
FROM users 
WHERE created_at < '2024-01-01 12:00:00'
ORDER BY created_at DESC 
LIMIT 20;

Optimizar JOINs

sql

-- ✅ BIEN: JOIN con índices apropiados
SELECT u.name, COUNT(o.id) as order_count
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.is_active = true
GROUP BY u.id, u.name;

-- Asegúrate de tener:
-- CREATE INDEX idx_orders_user_id ON orders(user_id);
-- CREATE INDEX idx_users_active ON users(id) WHERE is_active = true;
-- ✅ BIEN: JOIN con índices apropiados
SELECT u.name, COUNT(o.id) as order_count
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.is_active = true
GROUP BY u.id, u.name;

-- Asegúrate de tener:
-- CREATE INDEX idx_orders_user_id ON orders(user_id);
-- CREATE INDEX idx_users_active ON users(id) WHERE is_active = true;

Evitar Subconsultas Correlacionadas

sql

-- ❌ MAL: Subconsulta se ejecuta por cada fila
SELECT u.name,
    (SELECT COUNT(*) FROM orders WHERE user_id = u.id) as order_count
FROM users u;

-- ✅ BIEN: JOIN es más eficiente
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;
-- ❌ MAL: Subconsulta se ejecuta por cada fila
SELECT u.name,
    (SELECT COUNT(*) FROM orders WHERE user_id = u.id) as order_count
FROM users u;

-- ✅ BIEN: JOIN es más eficiente
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

Usar CTEs (Common Table Expressions)

sql

-- Para consultas complejas más legibles
WITH active_users AS (
    SELECT id, name, email
    FROM users
    WHERE is_active = true
),
recent_orders AS (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    WHERE created_at > CURRENT_DATE - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT 
    au.name,
    au.email,
    COALESCE(ro.order_count, 0) as recent_orders
FROM active_users au
LEFT JOIN recent_orders ro ON au.id = ro.user_id
ORDER BY ro.order_count DESC NULLS LAST;
-- Para consultas complejas más legibles
WITH active_users AS (
    SELECT id, name, email
    FROM users
    WHERE is_active = true
),
recent_orders AS (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    WHERE created_at > CURRENT_DATE - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT 
    au.name,
    au.email,
    COALESCE(ro.order_count, 0) as recent_orders
FROM active_users au
LEFT JOIN recent_orders ro ON au.id = ro.user_id
ORDER BY ro.order_count DESC NULLS LAST;

Window Functions para Análisis

sql

-- Ranking y análisis sin subqueries
SELECT 
    product_id,
    order_date,
    amount,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY order_date) as running_total,
    ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY amount DESC) as rank_by_amount,
    LAG(amount) OVER (PARTITION BY product_id ORDER BY order_date) as previous_amount
FROM sales
WHERE order_date >= '2024-01-01';
-- Ranking y análisis sin subqueries
SELECT 
    product_id,
    order_date,
    amount,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY order_date) as running_total,
    ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY amount DESC) as rank_by_amount,
    LAG(amount) OVER (PARTITION BY product_id ORDER BY order_date) as previous_amount
FROM sales
WHERE order_date >= '2024-01-01';

Prepared Statements

sql

-- Reduce overhead de parsing
PREPARE get_user_by_email (text) AS
    SELECT id, name, email FROM users WHERE email = $1;

EXECUTE get_user_by_email('user@example.com');
EXECUTE get_user_by_email('another@example.com');

-- Liberar cuando no se necesite más
DEALLOCATE get_user_by_email;
-- Reduce overhead de parsing
PREPARE get_user_by_email (text) AS
    SELECT id, name, email FROM users WHERE email = $1;

EXECUTE get_user_by_email('user@example.com');
EXECUTE get_user_by_email('another@example.com');

-- Liberar cuando no se necesite más
DEALLOCATE get_user_by_email;

Configuración de Parámetros PostgreSQL

Parámetros de Memoria

shared_buffers

Memoria para cachear datos. Regla general: 25% de RAM:

bash

# Para servidor con 16 GB RAM
shared_buffers = 4GB
# Para servidor con 16 GB RAM
shared_buffers = 4GB

work_mem

Memoria por operación de sort/hash:

bash

# Cuidado: es POR OPERACIÓN
# Para OLTP: 4-16 MB
work_mem = 16MB

# Para OLAP/Analytics: mayor
work_mem = 64MB
# Cuidado: es POR OPERACIÓN
# Para OLTP: 4-16 MB
work_mem = 16MB

# Para OLAP/Analytics: mayor
work_mem = 64MB

maintenance_work_mem

Para operaciones de mantenimiento (VACUUM, CREATE INDEX):

bash

# Generalmente: 256 MB - 1 GB
maintenance_work_mem = 512MB
# Generalmente: 256 MB - 1 GB
maintenance_work_mem = 512MB

effective_cache_size

Estimación de memoria disponible para cache del SO:

bash

# Generalmente: 50-75% de RAM total
# Para 16 GB RAM:
effective_cache_size = 12GB
# Generalmente: 50-75% de RAM total
# Para 16 GB RAM:
effective_cache_size = 12GB

Parámetros de Conexiones

bash

# No establecer demasiado alto
max_connections = 100

# Timeout para sesiones idle
idle_in_transaction_session_timeout = '5min'
# No establecer demasiado alto
max_connections = 100

# Timeout para sesiones idle
idle_in_transaction_session_timeout = '5min'

Parámetros de Checkpoint

bash

# Tiempo máximo entre checkpoints
checkpoint_timeout = 10min

# Objetivo de completar checkpoint en este porcentaje del intervalo
checkpoint_completion_target = 0.9

# Tamaño máximo de WAL
max_wal_size = 2GB
min_wal_size = 1GB
# Tiempo máximo entre checkpoints
checkpoint_timeout = 10min

# Objetivo de completar checkpoint en este porcentaje del intervalo
checkpoint_completion_target = 0.9

# Tamaño máximo de WAL
max_wal_size = 2GB
min_wal_size = 1GB

Parámetros de Autovacuum

bash

# Habilitar autovacuum (default: on)
autovacuum = on

# Número de workers
autovacuum_max_workers = 3

# Threshold para vacuum
autovacuum_vacuum_scale_factor = 0.1
autovacuum_vacuum_threshold = 50

# Para tablas con muchas actualizaciones
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 200
# Habilitar autovacuum (default: on)
autovacuum = on

# Número de workers
autovacuum_max_workers = 3

# Threshold para vacuum
autovacuum_vacuum_scale_factor = 0.1
autovacuum_vacuum_threshold = 50

# Para tablas con muchas actualizaciones
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 200

Parámetros de Paralelismo

bash

# Máximo workers paralelos por consulta
max_parallel_workers_per_gather = 4

# Total de workers paralelos
max_parallel_workers = 8

# Para mantenimiento paralelo
max_parallel_maintenance_workers = 4
# Máximo workers paralelos por consulta
max_parallel_workers_per_gather = 4

# Total de workers paralelos
max_parallel_workers = 8

# Para mantenimiento paralelo
max_parallel_maintenance_workers = 4

Parámetros de Costos

bash

# Importante para SSDs modernos
random_page_cost = 1.1  # Default: 4.0

# Costo de páginas secuenciales
seq_page_cost = 1.0
# Importante para SSDs modernos
random_page_cost = 1.1  # Default: 4.0

# Costo de páginas secuenciales
seq_page_cost = 1.0

Mantenimiento y VACUUM

VACUUM Regular

sql

-- Vacuum normal (no bloquea)
VACUUM users;

-- Vacuum con análisis de estadísticas
VACUUM ANALYZE users;

-- Vacuum verbose para ver detalles
VACUUM VERBOSE users;
-- Vacuum normal (no bloquea)
VACUUM users;

-- Vacuum con análisis de estadísticas
VACUUM ANALYZE users;

-- Vacuum verbose para ver detalles
VACUUM VERBOSE users;

VACUUM FULL

sql

-- Recupera espacio pero bloquea tabla
VACUUM FULL users;

-- Mejor alternativa: pg_repack (sin bloqueo)
-- Recupera espacio pero bloquea tabla
VACUUM FULL users;

-- Mejor alternativa: pg_repack (sin bloqueo)

Monitoring de Bloat

sql

-- Ver dead tuples
SELECT 
    schemaname,
    tablename,
    n_live_tup,
    n_dead_tup,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;
-- Ver dead tuples
SELECT 
    schemaname,
    tablename,
    n_live_tup,
    n_dead_tup,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

ANALYZE para Estadísticas

sql

-- Actualizar estadísticas del query planner
ANALYZE users;

-- Para tabla específica
ANALYZE users(email, created_at);
-- Actualizar estadísticas del query planner
ANALYZE users;

-- Para tabla específica
ANALYZE users(email, created_at);

Connection Pooling

PgBouncer

Configuración recomendada para reducir overhead de conexiones:

ini

[databases]
mydb = host=localhost dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# Pool mode
pool_mode = transaction

# Tamaño de pools
default_pool_size = 25
max_client_conn = 1000
[databases]
mydb = host=localhost dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# Pool mode
pool_mode = transaction

# Tamaño de pools
default_pool_size = 25
max_client_conn = 1000

Modos de pooling:

session: Una conexión por sesión (más seguro)
transaction: Conexión por transacción (más eficiente)
statement: Conexión por statement (raramente usado)

En Django (Python)

python

# settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'mydb',
        'USER': 'user',
        'PASSWORD': 'password',
        'HOST': 'localhost',
        'PORT': '6432',  # Puerto de PgBouncer
        'CONN_MAX_AGE': 600,  # Reusar conexiones
        'OPTIONS': {
            'connect_timeout': 10,
            'options': '-c statement_timeout=30000'  # 30 segundos
        }
    }
}
# settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'mydb',
        'USER': 'user',
        'PASSWORD': 'password',
        'HOST': 'localhost',
        'PORT': '6432',  # Puerto de PgBouncer
        'CONN_MAX_AGE': 600,  # Reusar conexiones
        'OPTIONS': {
            'connect_timeout': 10,
            'options': '-c statement_timeout=30000'  # 30 segundos
        }
    }
}

Monitoreo y Herramientas

pg_stat_statements

Extensión esencial para identificar consultas lentas:

sql

-- Habilitar extensión
CREATE EXTENSION pg_stat_statements;

-- Ver consultas más lentas
SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    max_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Reset estadísticas
SELECT pg_stat_statements_reset();
-- Habilitar extensión
CREATE EXTENSION pg_stat_statements;

-- Ver consultas más lentas
SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    max_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Reset estadísticas
SELECT pg_stat_statements_reset();

Monitoreo de Conexiones

sql

-- Ver conexiones activas
SELECT 
    datname,
    usename,
    application_name,
    state,
    query_start,
    state_change,
    query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

-- Conexiones por estado
SELECT state, COUNT(*) 
FROM pg_stat_activity 
GROUP BY state;

-- Matar consulta específica
SELECT pg_cancel_backend(pid);  -- Intenta cancelar
SELECT pg_terminate_backend(pid);  -- Fuerza terminación
-- Ver conexiones activas
SELECT 
    datname,
    usename,
    application_name,
    state,
    query_start,
    state_change,
    query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

-- Conexiones por estado
SELECT state, COUNT(*) 
FROM pg_stat_activity 
GROUP BY state;

-- Matar consulta específica
SELECT pg_cancel_backend(pid);  -- Intenta cancelar
SELECT pg_terminate_backend(pid);  -- Fuerza terminación

Locks y Deadlocks

sql

-- Ver locks actuales
SELECT 
    l.locktype,
    l.database,
    l.relation::regclass,
    l.mode,
    l.granted,
    a.usename,
    a.query,
    a.query_start
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted
ORDER BY a.query_start;

-- Configurar logging de deadlocks
-- En postgresql.conf:
log_lock_waits = on
deadlock_timeout = 1s
-- Ver locks actuales
SELECT 
    l.locktype,
    l.database,
    l.relation::regclass,
    l.mode,
    l.granted,
    a.usename,
    a.query,
    a.query_start
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted
ORDER BY a.query_start;

-- Configurar logging de deadlocks
-- En postgresql.conf:
log_lock_waits = on
deadlock_timeout = 1s

Tamaños de Tablas e Índices

sql

-- Top 10 tablas más grandes
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;

-- Índices no usados (candidatos para eliminar)
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelid NOT IN (
    SELECT conindid FROM pg_constraint
)
ORDER BY pg_relation_size(indexrelid) DESC;
-- Top 10 tablas más grandes
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;

-- Índices no usados (candidatos para eliminar)
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelid NOT IN (
    SELECT conindid FROM pg_constraint
)
ORDER BY pg_relation_size(indexrelid) DESC;

Optimización por Tipo de Workload

OLTP (Online Transaction Processing)

Características: muchas transacciones cortas, alta concurrencia.

bash

# Configuración recomendada
shared_buffers = 4GB
work_mem = 16MB
maintenance_work_mem = 512MB
effective_cache_size = 12GB

max_connections = 200
random_page_cost = 1.1

# Autovacuum agresivo
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
# Configuración recomendada
shared_buffers = 4GB
work_mem = 16MB
maintenance_work_mem = 512MB
effective_cache_size = 12GB

max_connections = 200
random_page_cost = 1.1

# Autovacuum agresivo
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02

OLAP (Online Analytical Processing)

Características: consultas complejas, análisis de grandes volúmenes.

bash

# Configuración recomendada
shared_buffers = 8GB
work_mem = 64MB  # Mayor para sorts grandes
maintenance_work_mem = 2GB
effective_cache_size = 24GB

max_connections = 50  # Menos conexiones
random_page_cost = 1.1

# Paralelismo habilitado
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
# Configuración recomendada
shared_buffers = 8GB
work_mem = 64MB  # Mayor para sorts grandes
maintenance_work_mem = 2GB
effective_cache_size = 24GB

max_connections = 50  # Menos conexiones
random_page_cost = 1.1

# Paralelismo habilitado
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

High-Write Systems

Para sistemas con muchas escrituras:

bash

# WAL optimizado
max_wal_size = 4GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9

# Autovacuum muy agresivo
autovacuum_max_workers = 6
autovacuum_vacuum_cost_delay = 5ms
# WAL optimizado
max_wal_size = 4GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9

# Autovacuum muy agresivo
autovacuum_max_workers = 6
autovacuum_vacuum_cost_delay = 5ms

Mejores Prácticas Generales

1. Diseño de Base de Datos

Normalizar apropiadamente: Balance entre normalización y rendimiento
Usar tipos de datos correctos: integer vs bigint, timestamp vs date
Particionamiento para tablas grandes:

sql

-- Particionamiento por rango
CREATE TABLE logs (
    id SERIAL,
    created_at TIMESTAMP NOT NULL,
    message TEXT
) PARTITION BY RANGE (created_at);

CREATE TABLE logs_2024_01 PARTITION OF logs
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE logs_2024_02 PARTITION OF logs
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Índice en cada partición
CREATE INDEX idx_logs_2024_01_created ON logs_2024_01(created_at);
-- Particionamiento por rango
CREATE TABLE logs (
    id SERIAL,
    created_at TIMESTAMP NOT NULL,
    message TEXT
) PARTITION BY RANGE (created_at);

CREATE TABLE logs_2024_01 PARTITION OF logs
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE logs_2024_02 PARTITION OF logs
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Índice en cada partición
CREATE INDEX idx_logs_2024_01_created ON logs_2024_01(created_at);

2. Seguridad de Consultas

sql

-- Usar parámetros, no concatenación
-- ❌ MAL: SQL Injection vulnerable
query = f"SELECT * FROM users WHERE email = '{email}'"

-- ✅ BIEN: Usar parámetros
cursor.execute("SELECT * FROM users WHERE email = %s", (email,))
-- Usar parámetros, no concatenación
-- ❌ MAL: SQL Injection vulnerable
query = f"SELECT * FROM users WHERE email = '{email}'"

-- ✅ BIEN: Usar parámetros
cursor.execute("SELECT * FROM users WHERE email = %s", (email,))

3. Transacciones Apropiadas

sql

-- Mantener transacciones cortas
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Evitar transacciones largas con:
idle_in_transaction_session_timeout = '5min'
-- Mantener transacciones cortas
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Evitar transacciones largas con:
idle_in_transaction_session_timeout = '5min'

4. Batch Operations

python

# En Django
# ❌ MAL: N queries
for data in items:
    User.objects.create(**data)

# ✅ BIEN: Bulk insert
User.objects.bulk_create([User(**data) for data in items])

# ✅ BIEN: Bulk update
User.objects.bulk_update(users, ['status', 'updated_at'])
# En Django
# ❌ MAL: N queries
for data in items:
    User.objects.create(**data)

# ✅ BIEN: Bulk insert
User.objects.bulk_create([User(**data) for data in items])

# ✅ BIEN: Bulk update
User.objects.bulk_update(users, ['status', 'updated_at'])

5. Monitoreo Continuo

Configurar alertas para consultas lentas (> 1 segundo)
Revisar pg_stat_statements semanalmente
Monitorear tamaño de tablas e índices mensualmente
Analizar conexiones y locks regularmente

Herramientas Recomendadas

Análisis y Tuning

pgTune: Genera configuración inicial basada en hardware
pghero: Dashboard de rendimiento
pgBadger: Análisis de logs
explain.depesz.com: Visualización de EXPLAIN

Monitoreo

Percona Monitoring and Management (PMM): Monitoreo completo
pganalyze: Análisis de consultas y índices
Datadog/New Relic: Monitoreo de aplicación con PostgreSQL
pgwatch2: Monitoreo open-source

Extensiones Útiles

sql

-- Estadísticas de consultas
CREATE EXTENSION pg_stat_statements;

-- Análisis de bloat
CREATE EXTENSION pgstattuple;

-- Búsqueda de texto completo
CREATE EXTENSION pg_trgm;  -- Para LIKE '%pattern%'

-- Cron jobs en PostgreSQL
CREATE EXTENSION pg_cron;
-- Estadísticas de consultas
CREATE EXTENSION pg_stat_statements;

-- Análisis de bloat
CREATE EXTENSION pgstattuple;

-- Búsqueda de texto completo
CREATE EXTENSION pg_trgm;  -- Para LIKE '%pattern%'

-- Cron jobs en PostgreSQL
CREATE EXTENSION pg_cron;

Conclusión

La optimización de PostgreSQL es un proceso continuo que requiere:

Medición constante: Usa EXPLAIN, pg_stat_statements, y herramientas de monitoreo
Indexación estratégica: Índices apropiados en las columnas correctas
Consultas eficientes: SELECT específico, JOINs optimizados, evitar subconsultas correlacionadas
Configuración adecuada: Parámetros ajustados a tu workload y hardware
Mantenimiento regular: VACUUM, ANALYZE, y revisión de índices
Connection pooling: PgBouncer para manejar conexiones eficientemente

Checklist de Optimización

Habilitar y monitorear pg_stat_statements
Revisar y optimizar top 10 consultas más lentas
Crear índices para columnas frecuentes en WHERE/JOIN
Eliminar índices no usados
Configurar shared_buffers (25% RAM)
Ajustar work_mem según workload
Configurar autovacuum apropiadamente
Implementar connection pooling (PgBouncer)
Reducir random_page_cost a 1.1-2.0 para SSDs
Monitorear dead tuples y bloat
Configurar timeouts para transacciones largas
Particionar tablas muy grandes (> 100 GB)
Establecer alertas para consultas > 1s
Revisar logs de deadlocks
Benchmark antes y después de cambios

Recuerda: optimiza basándote en métricas reales, no en suposiciones. Mide, ajusta, y mide nuevamente.

Recursos Adicionales

Documentación oficial: postgresql.org/docs
PostgreSQL Wiki: wiki.postgresql.org/wiki/Performance_Optimization
Percona Blog: Artículos sobre optimización
Cybertec Blog: Tutoriales avanzados de PostgreSQL
Use The Index, Luke: Guía definitiva sobre índices

La optimización es un viaje, no un destino. Con las herramientas y conocimientos adecuados, puedes mantener tu base de datos PostgreSQL rápida y eficiente a medida que crece.