Indexar JSONB con GIN: jsonb_ops frente a jsonb_path_ops

Una columna jsonb es comoda hasta que tienes que filtrar por ella a gran escala: sin indice cada consulta se convierte en un escaneo secuencial que parsea el JSON sobre la marcha. Un indice GIN soluciona eso, pero trae dos clases de operadores con comportamientos distintos, y la eleccion afecta tanto a la velocidad como al tamano.

No elijas el indice "mas potente" por si acaso. GIN acelera las lecturas, pero lo pagas en tamano, inserciones mas lentas y mantenimiento periodico. Asi que define primero los predicados reales: buscas por la forma del documento, por la existencia de una clave o por un unico campo escalar.

Por que usar GIN

Supongamos que guardamos eventos con una carga util en JSONB:

CREATE TABLE events (
    id         bigint PRIMARY KEY,
    user_id    bigint NOT NULL,
    payload    jsonb  NOT NULL,
    created_at timestamptz DEFAULT now()
);

Un B-tree sobre esa columna no sirve: compara valores completos, pero nosotros necesitamos mirar dentro del documento. GIN (Generalized Inverted Index) guarda un indice invertido de las claves y valores dentro del JSON, asi que "encuentra documentos que contengan esto" se resuelve por el indice.

CREATE INDEX idx_events_payload ON events USING GIN (payload);
SELECT id FROM events WHERE payload @> '{"status": "paid"}';

jsonb_ops frente a jsonb_path_ops

GIN sobre JSONB tiene dos clases de operadores:

• jsonb_ops (la predeterminada) indexa cada clave y cada valor como una entrada separada. Soporta mas operadores, pero el indice es mayor.
• jsonb_path_ops indexa hashes de rutas completas "clave -> valor". Es mas pequeno, normalmente mas rapido en comprobaciones de contencion, pero soporta solo un conjunto reducido de operadores.

-- default class: jsonb_ops
CREATE INDEX idx_payload_ops ON events USING GIN (payload);

-- compact class optimized for containment
CREATE INDEX idx_payload_path ON events USING GIN (payload jsonb_path_ops);

Que operadores soporta cada clase:

• @> (contiene) lo soportan ambas clases.
• @?, @@ (JSONPath) tambien los soportan ambas clases.
• ?, ?|, ?& (existencia de clave) solo estan en jsonb_ops.

Asi que la verdadera frontera es la existencia de claves: solo la clase predeterminada jsonb_ops resuelve ?, ?| y ?& desde el indice. Si todas tus consultas se basan en @> o JSONPath, elige jsonb_path_ops: las cubre todas y es mas compacto, porque no indexa claves sueltas por las que nunca buscas.

Indice por expresion frente a documento completo

No siempre hace falta indexar el documento entero. Si filtras constantemente por un unico campo escalar, un B-tree sobre esa expresion sale mas barato:

CREATE INDEX idx_events_status
    ON events ((payload ->> 'status'));

SELECT id FROM events WHERE payload ->> 'status' = 'refunded';

Ese indice es diminuto y soporta rangos y ordenacion. Unirlo con tus otras tablas relacionales es igual de sencillo:

SELECT u.email, e.created_at
FROM events e
JOIN users u ON u.id = e.user_id
WHERE e.payload ->> 'status' = 'paid'
  AND u.country = 'DE';

La regla practica: filtros de igualdad o rango sobre un solo campo -> indice por expresion; filtros arbitrarios sobre muchas claves, sobre todo @> -> GIN sobre el documento completo.

Trampas y como leer el plan

• ->> se salta GIN. El predicado payload ->> 'status' = 'paid' NO usa un indice GIN; GIN necesita payload @> '{"status":"paid"}'. Es el error mas comun de todos.
• jsonb_path_ops no conoce ?. La existencia de una clave sin valor (payload ? 'coupon') solo la indexa la clase predeterminada.
• GIN es caro al escribir. Cada INSERT actualiza muchas entradas del indice. Bajo mucha carga de insercion, fastupdate mas un VACUUM regular ayudan.
• Selectividad. GIN compensa cuando el predicado descarta la mayoria de las filas. Si @> coincide con casi todo, el planificador elegira con sensatez un seq scan.

Comprueba siempre el plan:

EXPLAIN ANALYZE
SELECT id FROM events
WHERE payload @> '{"status": "paid", "channel": "web"}';

Busca un Bitmap Index Scan sobre el indice que esperas; eso confirma que GIN se esta usando de verdad.

En que se diferencian otros motores

• MySQL no tiene GIN. Para JSON usas columnas GENERATED con un B-tree normal encima, o indices multivalor para arrays mediante MEMBER OF y JSON_CONTAINS.
• ClickHouse se apoya en sus tipos JSON/Map e indices dispersos a nivel de granulo en vez de invertidos; los filtros se aceleran con columnas materializadas o indices de salto.

Para PostgreSQL la receta base es: filtros @> -> jsonb_path_ops, igualdad puntual -> indice por expresion, y confirma siempre con EXPLAIN.