Índices parciales en PostgreSQL: indexar solo las filas calientes

La mayoría de los índices cubren toda la tabla: cada fila, cada valor. Pero las consultas rara vez se reparten de forma uniforme por tus datos. Constantemente accedes a pedidos pending, usuarios activos, tareas abiertas, mientras que una cola histórica de millones de filas terminadas se queda ahí como peso muerto. Un índice parcial indexa solo el subconjunto que realmente te importa. La recompensa: un índice más pequeño que se actualiza más rápido y tiene muchas más probabilidades de permanecer en caché.

Qué es un índice parcial

Un índice parcial es un índice corriente con un predicado WHERE. Solo las filas que satisfacen la condición acaban en el árbol.

-- Full index: every row of orders
CREATE INDEX idx_orders_status ON orders (status);

-- Partial index: only the "hot" orders
CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

Si la tabla guarda 50M de pedidos pero solo unos pocos miles están en pending, el segundo índice es órdenes de magnitud más pequeño. Insertar un pedido completado nunca lo toca: la fila no cumple el predicado. Eso ahorra tanto espacio en disco como tiempo de escritura.

Para que el planificador lo use, la condición de la consulta debe coincidir lógicamente con el predicado del índice (o ser un subconjunto suyo):

-- Uses idx_orders_pending
SELECT id, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at;

Por qué es más rápido y más barato

Las ventajas vienen de pura aritmética: el índice contiene físicamente menos entradas:

• Menor tamaño. Menos páginas en disco, más probabilidades de que el índice entero quepa en shared_buffers.
• Escrituras más rápidas. Los INSERT/UPDATE de filas fuera del predicado no actualizan el índice en absoluto.
• Estadísticas más limpias. El planificador estima la cardinalidad sobre un subconjunto estrecho y se equivoca con menos frecuencia.
• Mantenimiento más barato. VACUUM y las reconstrucciones operan sobre un conjunto de datos mucho menor.

El caso clásico es una cola de trabajos. Los workers solo leen filas sin terminar:

CREATE INDEX idx_jobs_queue
  ON jobs (priority DESC, created_at)
  WHERE state IN ('queued', 'running');

Incluso con cientos de millones de trabajos completados en la tabla, el índice de la cola se mantiene diminuto y caliente.

Trampa: el predicado debe ser determinista e inmutable. No puedes escribir WHERE created_at > now() - interval '7 days': now() cambia, así que el índice de hoy cubriría las filas equivocadas mañana. Usa comparaciones contra valores estáticos: WHERE status = 'pending', WHERE deleted_at IS NULL.

UNIQUE parcial para borrados lógicos

El uso más potente es una restricción UNIQUE parcial. Supón que los usuarios deben tener un correo único, pero nunca borras filas de forma física: en su lugar marcas deleted_at. Un UNIQUE (email) corriente impediría registrar una cuenta nueva con el correo de un usuario borrado.

-- Won't work: an old deleted row blocks the email forever
CREATE UNIQUE INDEX ON users (email);

-- Uniqueness only among live rows
CREATE UNIQUE INDEX idx_users_email_active
  ON users (email)
  WHERE deleted_at IS NULL;

Ahora puedes conservar cualquier cantidad de filas borradas que compartan un correo, mientras que entre las filas activas (deleted_at IS NULL) el correo se mantiene único. El mismo truco impone "un único valor predeterminado por usuario":

-- At most one primary address per user
CREATE UNIQUE INDEX idx_one_primary_address
  ON addresses (user_id)
  WHERE is_primary = true;

Esa es una regla de integridad casi imposible de expresar con una restricción UNIQUE normal sin recurrir a triggers.

Cuándo no ayuda, y diferencias entre motores

Un índice parcial no es una bala de plata. Es inútil cuando:

• las consultas recorren todo el rango de una columna en lugar de un subconjunto;
• el predicado cubre la mayor parte de la tabla (el beneficio "parcial" se esfuma);
• el predicado de la consulta no coincide con el del índice: el planificador simplemente lo ignora.

Diferencias entre motores:

• PostgreSQL — soporte completo para índices parciales y UNIQUE parcial mediante CREATE INDEX ... WHERE. La implementación de referencia.
• SQLite — admite la misma sintaxis CREATE INDEX ... WHERE, incluido el UNIQUE parcial.
• MySQL/InnoDB — sin índices parciales. Alternativas: una columna generada más un índice sobre ella, o una tabla separada de "filas calientes". Nota: KEY (col(10)) en MySQL es un índice de prefijo (parte del valor), no uno parcial.
• SQL Server — tiene un equivalente llamado índice filtrado: CREATE INDEX ... WHERE.
• ClickHouse — un motor de otra naturaleza; en lugar de índices con predicado usas particionado (PARTITION BY) e índices de salto de datos.

Una receta práctica: encuentra una consulta que filtre una y otra vez sobre la misma condición estrecha (status, deleted_at, is_active) y traslada esa condición al WHERE del índice. Confirma con EXPLAIN (ANALYZE) que el planificador elige el índice y que el tamaño en disco bajó. Suele ser la aceleración más barata disponible.