V Volkanic
backend

PostgreSQL para backend engineers: más allá del SELECT básico

Las funcionalidades de PostgreSQL que marcan la diferencia en sistemas de producción: índices, JSONB, CTEs, particionado y diagnóstico de queries lentas.

11 min de lectura
postgresqlbackendbases de datossqlrendimiento

Después de años trabajando con PostgreSQL en sistemas con millones de registros y carga real, hay un patrón claro: la mayoría de los problemas de rendimiento no son de base de datos sino de cómo los ingenieros interactúan con ella.

Este artículo no es un tutorial de SQL básico. Es una selección de características y patrones de PostgreSQL que uso regularmente en producción y que hacen la diferencia entre una base de datos que aguanta y una que explota a escala.

Índices que realmente importan

Todo el mundo sabe que los índices son importantes. Lo que no siempre está claro es cuándo y de qué tipo.

Índices parciales

Para columnas con alta cardinalidad donde filtras siempre por un subconjunto específico, un índice parcial es mucho más eficiente:

-- ❌ Índice en toda la tabla (incluye registros históricos que rara vez se consultan)
CREATE INDEX idx_bookings_status ON bookings(status);

-- ✅ Solo indexa el 5% de registros que están activos
CREATE INDEX idx_bookings_pending ON bookings(created_at, user_id)
WHERE status = 'pending';

En una tabla con 10M de reservas donde el 95% están en estado completed, este índice parcial es 20x más pequeño y significativamente más rápido para las queries operacionales.

Índices compuestos: el orden importa

PostgreSQL puede usar un índice compuesto para queries que solo filtran por el primer campo. Pero no al revés.

CREATE INDEX idx_bookings_user_date ON bookings(user_id, created_at DESC);

-- ✅ Puede usar el índice
SELECT * FROM bookings WHERE user_id = 123;
SELECT * FROM bookings WHERE user_id = 123 AND created_at > '2024-01-01';

-- ❌ No puede usar el índice
SELECT * FROM bookings WHERE created_at > '2024-01-01';

Regla práctica: ordena los campos del índice de más a menos selectivo, con los campos de filtro igualdad primero y los de rango al final.

Índices de expresión

Para queries que filtran por una transformación del valor:

-- Query problemática: no puede usar índice estándar
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- Solución: índice en la expresión
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

JSONB: SQL con flexibilidad de documento

PostgreSQL no es una base de datos documental, pero con JSONB se acerca bastante para casos de uso concretos. Lo uso para:

  • Metadatos de entidades que varían por tipo
  • Configuración flexible por cliente
  • Datos de terceros que no quieres normalizar completamente
-- Tabla con campo JSONB para metadatos de proveedor
CREATE TABLE provider_responses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    booking_id UUID REFERENCES bookings(id),
    provider TEXT NOT NULL,
    payload JSONB NOT NULL,
    received_at TIMESTAMPTZ DEFAULT NOW()
);

-- Crear índice GIN para búsquedas dentro del JSONB
CREATE INDEX idx_provider_payload ON provider_responses USING GIN(payload);

-- Query que filtra dentro del JSON
SELECT * FROM provider_responses
WHERE payload @> '{"status": "confirmed", "provider_ref": "SB-12345"}';

-- Extraer valor específico
SELECT
    booking_id,
    payload->>'provider_ref' AS provider_ref,
    (payload->'pricing'->>'total_amount')::numeric AS total
FROM provider_responses
WHERE provider = 'sidetours' AND payload->>'status' = 'confirmed';

Una advertencia: JSONB no reemplaza el diseño relacional correcto. Si consultas frecuentemente los mismos campos del JSON, deberían ser columnas normales con su tipo correcto.

CTEs y window functions para queries complejas

Las Common Table Expressions (CTEs) hacen que las queries complejas sean legibles. Las window functions añaden cálculos sobre grupos de filas sin colapsar el resultado.

-- CTE para analytics: revenue por proveedor con comparación mensual
WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', confirmed_at) AS month,
        provider_id,
        SUM(amount) AS revenue,
        COUNT(*) AS bookings
    FROM bookings
    WHERE status = 'confirmed' AND confirmed_at >= NOW() - INTERVAL '12 months'
    GROUP BY 1, 2
),
ranked AS (
    SELECT
        *,
        RANK() OVER (PARTITION BY month ORDER BY revenue DESC) AS rank_in_month,
        LAG(revenue) OVER (PARTITION BY provider_id ORDER BY month) AS prev_month_revenue
    FROM monthly_revenue
)
SELECT
    month,
    provider_id,
    revenue,
    bookings,
    rank_in_month,
    ROUND(((revenue - prev_month_revenue) / prev_month_revenue * 100)::numeric, 1) AS growth_pct
FROM ranked
WHERE rank_in_month <= 5
ORDER BY month DESC, rank_in_month;

Esto reemplaza fácilmente varios queries en aplicación o subconsultas anidadas ilegibles.

EXPLAIN ANALYZE: tu mejor herramienta de diagnóstico

Antes de añadir un índice o reescribir una query, siempre ejecuta EXPLAIN ANALYZE:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT b.*, u.email, u.name
FROM bookings b
JOIN users u ON b.user_id = u.id
WHERE b.status = 'pending'
  AND b.created_at > NOW() - INTERVAL '7 days'
ORDER BY b.created_at DESC
LIMIT 50;

Las señales de alarma en el output:

-- 🔴 Sequential scan en tabla grande
Seq Scan on bookings  (cost=0.00..85432.00 rows=12 width=156)
                       Filter: ((status = 'pending') AND (created_at > ...))
                       Rows Removed by Filter: 4823100

-- 🟢 Index scan esperado
Index Scan using idx_bookings_pending on bookings  (cost=0.43..24.18 rows=12)
                                                     Index Cond: (...)

Un sequential scan en una tabla de millones de filas casi siempre indica que falta un índice o que la query no lo puede aprovechar.

Transacciones y niveles de aislamiento

El nivel de aislamiento por defecto de PostgreSQL es READ COMMITTED. Para la mayoría de operaciones está bien, pero hay casos donde necesitas más garantías:

-- REPEATABLE READ: garantiza que lees los mismos datos en toda la transacción
-- Útil para reports que deben ser consistentes en el tiempo
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    SELECT SUM(amount) FROM bookings WHERE month = '2024-11';
    SELECT COUNT(*) FROM bookings WHERE month = '2024-11';
    -- Ambas queries ven el mismo snapshot, aunque haya inserts mientras tanto
COMMIT;

-- SERIALIZABLE: el nivel más estricto, detecta anomalías de concurrencia
-- Solo cuando realmente lo necesitas (penaliza el rendimiento)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    -- Operaciones críticas donde el orden importa absolutamente
COMMIT;

El error más común: usar transacciones largas con READ COMMITTED para operaciones que asumen que los datos no cambian entre queries dentro de la misma transacción. No lo hacen.

Particionado de tablas para volumen

Cuando una tabla supera los 50-100M de registros y la mayoría de queries son sobre datos recientes, el particionado por rango de fecha es la solución más efectiva:

-- Tabla principal particionada
CREATE TABLE events (
    id BIGSERIAL,
    user_id UUID NOT NULL,
    event_type TEXT NOT NULL,
    payload JSONB,
    occurred_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (occurred_at);

-- Particiones mensuales (automátizar con pg_partman en producción)
CREATE TABLE events_2024_11 PARTITION OF events
    FOR VALUES FROM ('2024-11-01') TO ('2024-12-01');

CREATE TABLE events_2024_12 PARTITION OF events
    FOR VALUES FROM ('2024-12-01') TO ('2025-01-01');

-- PostgreSQL enruta automáticamente al insertar
INSERT INTO events (user_id, event_type, occurred_at) VALUES (...);

-- Y solo escanea la partición relevante al consultar
SELECT * FROM events WHERE occurred_at BETWEEN '2024-11-01' AND '2024-11-30';

La ventaja más infrautilizada del particionado: borrar datos históricos es instantáneo con DROP TABLE events_2023_01, frente a un DELETE que puede tardar horas y generar enorme dead tuple bloat.

Autovacuum y mantenimiento

PostgreSQL usa MVCC (Multi-Version Concurrency Control), lo que significa que las filas borradas o actualizadas dejan “dead tuples” que el autovacuum debe limpiar. Si no funciona correctamente, el rendimiento degrada silenciosamente.

-- Verificar tabla con mucho bloat
SELECT
    schemaname,
    tablename,
    n_dead_tup,
    n_live_tup,
    ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_ratio,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Si una tabla tiene dead_ratio > 10% y autovacuum no está corriendo suficientemente:
-- 1. Forzar vacuum manual
VACUUM ANALYZE bookings;

-- 2. O ajustar los umbrales de autovacuum para esa tabla
ALTER TABLE bookings SET (autovacuum_vacuum_scale_factor = 0.01);  -- Default: 0.2

En tablas de alta frecuencia de escritura, bajar el scale_factor del autovacuum es casi siempre necesario.

Conclusión

PostgreSQL es una base de datos extraordinariamente capaz, pero sus características avanzadas requieren conocerlas para aprovecharlas. Las que más impacto tienen en sistemas de producción:

  1. Índices parciales y de expresión — para queries específicas sobre subconjuntos
  2. JSONB con índices GIN — para metadatos flexibles sin renunciar a SQL
  3. EXPLAIN ANALYZE — diagnóstico de rendimiento antes de cualquier optimización
  4. Particionado por fecha — para tablas de eventos de gran volumen
  5. Autovacuum tuning — para tablas de alta escritura

El rendimiento no es algo que se añade al final. Se diseña desde el inicio con las herramientas correctas.