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, pg_stat_statements 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:

1. Índices – tipos, diseño y cuándo evitarlos.
2. Queries – patrones de anti‑performance y cómo refactorizarlos paso a paso.
3. 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

Decisiones de diseño

1. Cardinalidad: Si la columna tiene pocos valores distintos (por ejemplo, status con 3 opciones) un índice B‑Tree puede ser más costoso que un bitmap (en PostgreSQL no nativo, pero simulable con GIN).
2. Selectividad: Un índice es útil cuando la cláusula WHERE reduce el conjunto a < 5 % del total. De lo contrario, el motor optará por un sequential scan.
3. 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

1. Escalado vertical (más CPU/RAM) – solución rápida pero limitada por el hardware máximo y el punto de falla único.
2. Escalado horizontal (sharding, read replicas) – permite distribuir carga de lectura y escritura, pero introduce complejidad de consistencia y routing.
3. 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:

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 payload habilita consultas como payload @> '{"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,