Índices parciales en Postgres: cuando menos índice es más

La tabla orders guarda ocho años de historial. El noventa y ocho por ciento de las filas tienen status = 'completed'. La query del dashboard filtra status = 'pending' y ordena por created_at. Un índice completo en (status, created_at) funciona — y crece con cada pedido completado para siempre. Vacuum tarda más. Los inserts en checkout pagan mantenimiento de índice en filas que la query nunca leerá. Los índices parciales en Postgres resuelven esto indexando solo la porción que la aplicación consulta de verdad: mismo access path del planner, fracción del tamaño, fracción de la amplificación de escritura.

Un índice parcial es un btree estándar (u otro tipo de índice) con una cláusula WHERE adjunta al crearlo. Solo las filas que satisfacen el predicado aparecen en el índice. El planner usa el índice parcial solo cuando las condiciones de la query implican el mismo predicado — una coincidencia que debe ser deliberada, no accidental. Los índices parciales complementan Postgres composite index patterns; no reemplazan la disciplina de orden de columnas. Responden otra pregunta: qué filas merecen indexarse.

Los índices parciales reducen el working set que busca el planner

Índices completos en columnas status de baja cardinalidad a menudo degeneran en estructuras anchas donde la mayoría de entradas apuntan a filas que la query caliente nunca toca. Los índices parciales cambian generalidad por precisión.

-- Full index: grows with every order
CREATE INDEX orders_status_created_idx
  ON orders (status, created_at);
 
-- Partial index: only pending rows
CREATE INDEX orders_pending_created_idx
  ON orders (created_at)
  WHERE status = 'pending';

El índice parcial almacena solo pedidos pending. Las filas completed y cancelled nunca entran en la estructura. Insertar un pedido completed no toca orders_pending_created_idx. Promover una fila de pending a completed la elimina del índice parcial — sigue siendo una escritura, pero acotada al pequeño conjunto pending.

La reducción de tamaño es proporcional a la selectividad. Si el uno por ciento de filas coincide con el predicado, esperar roughly dos órdenes de magnitud menos páginas de índice — menos evicciones de buffer cache, index-only scans más rápidos cuando el visibility map coopera, ciclos de autovacuum más cortos en el índice mismo.

Un índice parcial con un predicado que la aplicación olvida repetir es un índice invisible — presente en \di, ausente de EXPLAIN.

El diseño del predicado debe reflejar formas reales de query

El predicado del índice parcial debe estar implied por la cláusula WHERE de la query para que el planner lo elija. Postgres no adivina.

Works — query repeats the predicate or a stricter subset:

-- Index
CREATE INDEX users_active_email_idx
  ON users (email)
  WHERE deleted_at IS NULL;
 
-- Query: predicate matches
SELECT id FROM users
WHERE deleted_at IS NULL AND email = $1;

Fails — query omits the indexed predicate:

-- Same index
SELECT id FROM users WHERE email = $1;
-- Planner may seq scan: index does not guarantee deleted rows are excluded

Works with stable sentinels — soft-delete and archive patterns:

CREATE INDEX documents_published_slug_idx
  ON documents (slug)
  WHERE published = true;
 
CREATE INDEX events_upcoming_idx
  ON events (starts_at)
  WHERE starts_at > '1970-01-01'::timestamptz AND cancelled = false;

Evitar funciones volátiles en predicados (WHERE expires_at > now()). El conjunto indexado necesitaría mantenimiento constante; el planner no puede razonar sobre un límite móvil en plan time. Usar columnas status mantenidas por la aplicación o particionar por tiempo.

Patrones de índice parcial de alto valor comunes:

Para unicidad entre un subconjunto, un índice unique parcial reemplaza comprobaciones frágiles en aplicación:

CREATE UNIQUE INDEX one_active_sub_per_user
  ON subscriptions (user_id)
  WHERE status = 'active';

Solo una suscripción activa por user_id — filas cancelled no participan. Intentos de insertar una segunda fila activa fallan en la base con violación de constraint clara.

Los índices parciales interactúan con Row-Level Security y rutas de escritura

Las políticas Row-Level Security añaden filtros implícitos. El predicado del índice parcial debe alinearse con cómo corren de verdad las queries aseguradas — no cómo se leen en código de aplicación sin RLS activo.

Si cada query vía PostgREST o cliente Supabase aplica tenant_id = current_setting('app.tenant_id')::uuid, un índice parcial solo en WHERE status = 'open' puede ser invisible a planes que también deben probar aislamiento de tenant. Forma más segura:

CREATE INDEX tasks_open_tenant_idx
  ON tasks (tenant_id, updated_at)
  WHERE status = 'open';

Queries que filtran tenant_id y status = 'open' coinciden. La guía de Supabase RLS performance aplica: los índices deben incluir columnas que referencian las políticas RLS, y los predicados parciales deben reflejar la intersección de filtros de negocio y de política.

Advertencia en write path: actualizar una fila cruzando el límite del predicado la mueve dentro o fuera del índice. pending → completed borra una entrada de índice; completed → pending inserta una. Alto churn en la columna límite concentra carga de escritura — a veces un índice completo con mejor orden de columnas es más barato que mantenimiento constante del índice parcial.

Verificar con EXPLAIN (ANALYZE, BUFFERS):

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 50;

Esperar Index Scan o Index Only Scan sobre el nombre del índice parcial. Si el plan muestra Seq Scan, comprobar mismatch de predicado, estadísticas obsoletas (ANALYZE orders) o enable_indexscan deshabilitado en sesión (raro). Comparar shared hit y tiempo de ejecución antes y después — los índices parciales ganan en latencia de lectura y presión de cache, no por magia.

¿Cuándo debe preferir el planner un índice parcial sobre uno compuesto?

¿Puede un índice parcial servir múltiples variantes de query?

Solo cuando cada variante implica el mismo predicado. WHERE status = 'pending' y WHERE status = 'pending' AND tenant_id = $1 califican para un índice en (tenant_id, created_at) WHERE status = 'pending'. WHERE status IN ('pending', 'processing') no coincide con un índice predicado en status = 'pending' solo — usar WHERE status IN ('pending', 'processing') en el índice o mantener índices parciales separados para rutas calientes disjuntas.

¿Los índices parciales funcionan con columnas INCLUDE?

Sí. Postgres soporta CREATE INDEX ... INCLUDE (col) WHERE predicate para index-only scans que cubren columnas extra sin orden btree en ellas. Útil para SELECT id, slug cuando el predicado es published = true y slug es la clave de lookup — INCLUDE (id) evita heap fetches cuando el visibility map lo permite.

¿Qué tan pequeño debe ser el conjunto filtrado para justificar parcial?

No hay ratio fijo — medir. Por debajo de roughly cinco a diez por ciento de selectividad, los índices parciales suelen dominar en tamaño y costo de escritura. Entre diez y treinta por ciento, comparar pg_relation_size y costos de EXPLAIN contra un índice compuesto completo. Por encima de treinta por ciento, los índices parciales aún ayudan cuando la amplificación de escritura en filas excluidas es el cuello de botella (filas archivadas insertadas una vez, nunca actualizadas). La decisión depende del workload, no de reglas fijas.

A common argument runs the other way

Un argumento frecuente sostiene que los índices parciales añaden carga cognitiva — cada desarrollador debe conocer el predicado — y que particionar tablas o índices compuestos simples son más fáciles de razonar a escala.

Particionar por status o fecha es potente cuando clases enteras de query enrutan a una partición. Lleva overhead operacional: reglas de partition pruning, DDL de attachment, complejidad de backup. Los índices parciales son más ligeros cuando una porción caliente de una sola tabla impulsa el dolor — tickets abiertos, webhooks pending, entradas de ledger sin reconciliar. Índices compuestos sin predicados siguen siendo el default; los índices parciales son la herramienta de precisión cuando la selectividad es extrema y el predicado es estable en código de aplicación.

Key takeaways

• Los índices parciales en Postgres almacenan solo filas que coinciden con un predicado WHERE — índices más pequeños, menos amplificación de escritura en filas excluidas.
• Las cláusulas WHERE de query deben implicar el predicado del índice o el planner ignora el índice.
• Índices unique parciales aplican unicidad entre un subconjunto sin carreras a nivel aplicación.
• Alinear predicados con filtros RLS e incluir columnas tenant cuando las políticas lo exijan.
• Evitar funciones volátiles en predicados; preferir columnas status o particionamiento para límites temporales.
• Confirmar con EXPLAIN (ANALYZE, BUFFERS) — índices parciales son invisibles cuando están mal configurados.

Conclusion

La mayoría de tablas acumulan filas que el hot path nunca lee. Indexar todo el historial porque un dashboard consulta la cola es cómo estructuras btree se convierten en logs append-only multi-gigabyte que castigan cada insert. Los índices parciales admiten que el noventa por ciento de filas están frías — y las filas frías no pertenecen a la misma estructura que sirve lookups sub-milisegundo al dos por ciento que no lo está.

El próximo índice añadido a una tabla filtrada por status debería justificar por qué el predicado no es WHERE status = '<hot value>'. Si la respuesta es que nadie definió la porción caliente, el índice completo probablemente indexa el pasado a costa del presente.