Why should you care?

Without GIN, PostgresVerse @> brand filter scans 2M jsonb rows — unusable at Flipkart traffic.

See it live — copy this example

Run in pgAdmin or psql.

CREATE INDEX idx_products_specs_path
ON products USING GIN (specs jsonb_path_ops);

CREATE INDEX idx_products_brand_expr
ON products ((specs->>'brand'));

SELECT name FROM products WHERE specs @> '{"brand":"Nova"}';

What happened?

• path_ops index supports containment queries compactly.
• Expression index on specs->>brand helps equality GROUP BY brand even without full document index.

Practice next

1. CREATE both indexes CONCURRENTLY on staging.
2. EXPLAIN ANALYZE @> query with path_ops index.
3. EXPLAIN equality on specs->>'brand' using expr index.
4. Partial GIN: WHERE (specs ? 'searchable') on subset of catalog.
5. Multicolumn: (category, (specs->>'brand')) composite.

Remember

GIN jsonb_path_ops for @> filters. Expression B-Tree on ->> for single-key equality. REINDEX if operator class choice was wrong.

PostgresVerse facet index rollout

DBA adds path_ops GIN plus brand expression index; PLP latency drops 800ms to 40ms.

Outcome: Marketing enables json-driven filters for festival sale.