1. Introducción contextual
En los últimos años el volumen de datos que las aplicaciones web manejan ha crecido de forma exponencial. Según el informe Data Age 2024, más del 70 % de las empresas consideran que la latencia de sus consultas es el principal cuello de botella para la experiencia de usuario. En entornos de comercio electrónico, SaaS y plataformas de contenidos, una respuesta de 300 ms frente a 1 s puede significar la diferencia entre una conversión y una pérdida de cliente.
A nivel de arquitectura, la tendencia hacia micro‑servicios y serverless ha llevado a que cada equipo sea dueño de su propio esquema de base de datos. Esto otorga flexibilidad, pero también la responsabilidad de mantener la performance bajo control. Los índices y la forma en que redactamos nuestras queries siguen siendo los pilares clásicos de la optimización, mientras que la decisión de escalar (vertical u horizontal) se vuelve cada vez más estratégica, sobre todo cuando el coste de la infraestructura empieza a impactar el margen de negocio.
Históricamente, la optimización de bases de datos se centró en la teoría de relational algebra y en técnicas manuales de tuning. Hoy, con herramientas como EXPLAIN ANALYZE, pgstatstatements y plataformas de observabilidad (Datadog, New Relic), podemos cuantificar el impacto de cada cambio y automatizar gran parte del proceso. Sin embargo, la base del éxito sigue siendo la comprensión profunda de los índices, la escritura de queries eficientes y la planificación de escalado basada en métricas reales (CPU, I/O, latencia).
Este artículo aborda esos tres ejes de forma práctica:
- Índices – tipos, diseño y cuándo evitarlos.
- Queries – patrones de anti‑performance y cómo refactorizarlos paso a paso.
- Escalado – criterios para pasar de un nodo a una arquitectura distribuida.
Todo ello con ejemplos de código listos para copiar en producción, casos de uso reales y una comparativa de enfoques que te permitirá elegir la solución adecuada para tu proyecto.
2. Fundamentos y Arquitectura
2.1. Tipos de índices y su comportamiento
| Tipo de índice | Motor (PostgreSQL, MySQL…) | Ventajas | Desventajas | Uso típico |
|---|---|---|---|---|
| B‑Tree | Ambos | Soporta igualdad, rango, ordenamiento. Muy estable. | No óptimo para columnas con alta cardinalidad y valores nulos. | PK, FK, columnas de filtro frecuente. |
| Hash | PostgreSQL (solo en versiones < 13) | Búsqueda de igualdad extremadamente rápida. | No soporta rangos ni ordenamiento. | Tablas de lookup estáticas. |
| GIN | PostgreSQL | Ideal para columnas JSONB, arrays, full‑text search. | Consumo de espacio y tiempo de inserción mayor. | Búsqueda de documentos, tags. |
| BRIN | PostgreSQL, MySQL 8+ | Índice compacto para datos ordenados físicamente. | Precisión limitada, depende de correlación. | Series temporales, data warehouses. |
| Covering (incluye columnas) | MySQL (InnoDB), PostgreSQL (incluye columnas en INCLUDE) | Evita “heap fetch” al cubrir todas las columnas usadas. | Aumenta tamaño del índice. | Queries que solo leen unas pocas columnas. |
Decisiones de diseño
- Cardinalidad: Si la columna tiene pocos valores distintos (por ejemplo,
statuscon 3 opciones) un índice B‑Tree puede ser más costoso que un bitmap (en PostgreSQL no nativo, pero simulable con GIN). - Selectividad: Un índice es útil cuando la cláusula
WHEREreduce el conjunto a < 5 % del total. De lo contrario, el motor optará por un sequential scan. - Frecuencia de escritura: Cada inserción o actualización implica actualizar los índices. En tablas con alta tasa de escritura (logs, eventos) es preferible desnormalizar o usar particiones para limitar el impacto.
2.2. Arquitectura de consultas y planes de ejecución
Un plan de ejecución se compone de nodos (Seq Scan, Index Scan, Hash Join, Merge Join, etc.). El costo estimado es una combinación de I/O estimado y CPU. En PostgreSQL, el comando EXPLAIN (ANALYZE, BUFFERS) muestra:
Seq Scan on orders (cost=0.00..12345.67 rows=1000 width=128) (actual time=0.012..12.345 rows=987 loops=1)
Filter: (status = 'completed'::text)
Buffers: shared hit=150 read=20
Si el plan muestra un Seq Scan y la tabla tiene > 1 M de filas, es una señal de que falta un índice o la estadística está desactualizada.
2.3. Estrategia de escalado
- Escalado vertical (más CPU/RAM) – solución rápida pero limitada por el hardware máximo y el punto de falla único.
- Escalado horizontal (sharding, read replicas) – permite distribuir carga de lectura y escritura, pero introduce complejidad de consistencia y routing.
- Hybrid – combinar replicas de solo lectura con particiones por rango o hash para distribuir escrituras.
En la práctica, el umbral de escalado se define con métricas:
| Métrica | Umbral de alerta | Acción recomendada |
|---|---|---|
| CPU % | > 80 % sostenido 10 min | Revisar queries, considerar read replica |
| I/O latency (ms) | > 5 ms promedio | Analizar índices, habilitar partition pruning |
| Conexiones activas | > 70 % del pool | Aumentar pool o distribuir en replicas |
| Tamaño de tabla (GB) | > 80 % del disco | Evaluar particionamiento o archiving |
3. Implementación práctica
A continuación se muestra la evolución de una consulta típica en una API Next.js que lista órdenes filtradas por estado y rango de fechas. Partimos de una versión naïve, pasamos a una versión con índices y terminamos con una variante optimizada usando covering index y prepared statements.
3.1. Versión 1 – Consulta naïve (sin índices)
// src/pages/api/orders.ts
import type { NextApiRequest, NextApiResponse } from 'next';
import { Pool } from 'pg';
// Pool configurado en .env
const pool = new Pool({
connectionString: process.env.DATABASE_URL,
});
/**
* Handler API que devuelve órdenes según estado y rango de fechas.
* No hay índices ni validaciones; se ejecuta un seq scan completo.
*/
export default async function handler(
req: NextApiRequest,
res: NextApiResponse
) {
const { status, start, end } = req.query;
// Validación mínima de parámetros
if (!status || !start || !end) {
return res.status(400).json({ error: 'Missing required query params' });
}
const client = await pool.connect();
try {
const sql = `
SELECT id, user_id, total, created_at, status
FROM orders
WHERE status = $1
AND created_at BETWEEN $2 AND $3
ORDER BY created_at DESC
LIMIT 100
`;
const result = await client.query(sql, [status, start, end]);
res.status(200).json(result.rows);
} catch (error) {
console.error('DB error:', error);
res.status(500).json({ error: 'Internal server error' });
} finally {
client.release();
}
}
Problemas detectados
- Seq Scan en
orders(tabla de > 5 M filas). - No se controla la cardinalidad del
status, lo que lleva a lecturas innecesarias. - No hay índices que soporten el filtro
status+created_at.
3.2. Versión 2 – Añadiendo índices y estadísticas
-- scripts/2024-08-create-indexes.sql
-- Índice compuesto que cubre los filtros y el ORDER BY
CREATE INDEX IF NOT EXISTS idx_orders_status_created_at
ON orders (status, created_at DESC);
-- Índice covering para evitar heap fetch (solo PostgreSQL >= 11)
CREATE INDEX IF NOT EXISTS idx_orders_covering
ON orders (status, created_at DESC) INCLUDE (id, user_id, total);
// src/pages/api/orders.ts (versión 2)
import type { NextApiRequest, NextApiResponse } from 'next';
import { Pool } from 'pg';
import { z } from 'zod';
// Esquema de validación robusta con Zod
const querySchema = z.object({
status: z.enum(['pending', 'completed', 'canceled']),
start: z.string().refine((val) => !isNaN(Date.parse(val)), { message: 'Invalid start date' }),
end: z.string().refine((val) => !isNaN(Date.parse(val)), { message: 'Invalid end date' }),
});
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
export default async function handler(req: NextApiRequest, res: NextApiResponse) {
const parseResult = querySchema.safeParse(req.query);
if (!parseResult.success) {
return res.status(400).json({ error: parseResult.error.errors });
}
const { status, start, end } = parseResult.data;
const client = await pool.connect();
try {
const sql = `
SELECT id, user_id, total, created_at, status
FROM orders
WHERE status = $1
AND created_at BETWEEN $2 AND $3
ORDER BY created_at DESC
LIMIT 100
`;
const result = await client.query(sql, [status, start, end]);
res.status(200).json(result.rows);
} catch (error) {
console.error('DB error:', error);
res.status(500).json({ error: 'Internal server error' });
} finally {
client.release();
}
}
Mejoras
- El índice compuesto permite al planner usar Index Scan y reducir I/O.
- El covering index elimina la necesidad de acceder al heap para las columnas solicitadas.
- Validación con Zod evita consultas malformadas que podrían generar planes de ejecución costosos.
3.3. Versión 3 – Optimización avanzada con prepared statements y caching
// src/lib/db.ts
import { Pool, QueryConfig } from 'pg';
import LRU from 'lru-cache';
// Pool singleton (Patrón Singleton)
class Database {
private static instance: Database;
private pool: Pool;
private cache: LRU<string, any>;
private constructor() {
this.pool = new Pool({ connectionString: process.env.DATABASE_URL });
// Cache de 5 min, 500 entradas max
this.cache = new LRU({ max: 500, ttl: 1000 * 60 * 5 });
}
public static getInstance(): Database {
if (!Database.instance) {
Database.instance = new Database();
}
return Database.instance;
}
public async query<T>(config: QueryConfig): Promise<T[]> {
const cacheKey = JSON.stringify(config);
const cached = this.cache.get(cacheKey);
if (cached) {
return cached as T[];
}
const client = await this.pool.connect();
try {
const result = await client.query<T>(config);
this.cache.set(cacheKey, result.rows);
return result.rows;
} finally {
client.release();
}
}
}
export const db = Database.getInstance();
// src/pages/api/orders.ts (versión 3)
import type { NextApiRequest, NextApiResponse } from 'next';
import { db } from '@/lib/db';
import { z } from 'zod';
const querySchema = z.object({
status: z.enum(['pending', 'completed', 'canceled']),
start: z.string().refine((v) => !isNaN(Date.parse(v)), { message: 'Invalid start' }),
end: z.string().refine((v) => !isNaN(Date.parse(v)), { message: 'Invalid end' }),
});
export default async function handler(req: NextApiRequest, res: NextApiResponse) {
const parse = querySchema.safeParse(req.query);
if (!parse.success) {
return res.status(400).json({ error: parse.error.errors });
}
const { status, start, end } = parse.data;
// Prepared statement reutilizable (nombre estático)
const query = {
name: 'fetch-orders-by-status',
text: `
SELECT id, user_id, total, created_at, status
FROM orders
WHERE status = $1
AND created_at BETWEEN $2 AND $3
ORDER BY created_at DESC
LIMIT 100
`,
values: [status, start, end],
};
try {
const rows = await db.query(query);
res.status(200).json(rows);
} catch (err) {
console.error('DB error:', err);
res.status(500).json({ error: 'Internal server error' });
}
}
Ventajas
- Singleton garantiza un único pool y evita fugas de conexiones.
- LRU cache reduce la carga en lecturas repetidas (ej.: panel de admin).
- Prepared statements mejoran la planificación y evitan el costo de parseo en cada request.
3.4. Ejemplo de indexación en tabla de eventos con particionamiento (SQL)
-- 1. Crear tabla particionada por rango de fecha
CREATE TABLE events (
id BIGSERIAL PRIMARY KEY,
type TEXT NOT NULL,
payload JSONB NOT NULL,
created_at TIMESTAMP WITH TIME ZONE NOT NULL
) PARTITION BY RANGE (created_at);
-- 2. Crear particiones mensuales (script de mantenimiento)
DO $$
DECLARE
start_date DATE := DATE '2024-01-01';
end_date DATE := DATE '2025-01-01';
part_date DATE;
BEGIN
part_date := start_date;
WHILE part_date < end_date LOOP
EXECUTE format(
'CREATE TABLE IF NOT EXISTS events_%s PARTITION OF events
FOR VALUES FROM (%L) TO (%L);',
to_char(part_date, 'YYYY_MM'),
part_date,
part_date + INTERVAL '1 month'
);
part_date := part_date + INTERVAL '1 month';
END LOOP;
END $$;
-- 3. Índice GIN en la columna JSONB para búsquedas por atributos
CREATE INDEX IF NOT EXISTS idx_events_payload_gin
ON events USING GIN (payload);
Razonamiento
- Particionamiento permite que los scans se limiten a una fracción (p. ej., 1 mes) y reduce I/O dramáticamente.
- GIN sobre
payloadhabilita consultas comopayload @> '{"user_id":123}'sin full table scan.
3.5. Script Bash para monitorizar uso de índices (Linux)
#!/usr/bin/env bash
# monitor-index-usage.sh
# Detecta índices sin uso en PostgreSQL y los reporta.
# Requiere psql y acceso al cluster.
set -euo pipefail
DB_URL="${DATABASE_URL:-postgres://user:pass@localhost:5432/app}"
THRESHOLD=0 # 0 = nunca usado
psql "$DB_URL" -Atc "
SELECT
schemaname,
relname,
indexrelname,
idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan <= $THRESHOLD
ORDER BY idx_scan ASC;
" | while IFS='|' read -r schema table index scans; do
echo "⚠️ Índice sin uso: $schema.$table -> $index (scans=$scans)"
done
Este script ayuda a identificar anti‑patrones de índices creados por exceso de precaución y que solo penalizan los writes.
4. Casos de uso reales
4.1. Plataforma de e‑commerce (Shopify‑like)
- Contexto: Catálogo de 2 M de productos,