Should I use a GIN index or a generated column to filter on fields inside a JSONB column?

Short answer: if you filter on a couple of known keys, the answer isn’t GIN.

Short answer

For fixed keys the lightest option is an expression index; if you also want the value as a real column, a generated column. Reserve GIN for dynamic keys and containment queries.

Why

1. Two different query shapes, two different tools. GIN shines on operators like @> containment and ? existence, and on keys you don’t know ahead of time or that are numerous. Generated columns and expression indexes are the right tool for equality and range on a fixed, known set of keys. Your case is the second one.

2. Your case is “a couple of fixed keys” → GIN is overkill. A GIN index is large, expensive to update on writes, and useless for range/ordering (BETWEEN, ORDER BY). Indexing the whole blob for just two keys builds a structure bigger and slower than you need.

What to do

1. The lightest option: an expression index. You index the value straight off the expression — no schema change, no extra column. As long as your query uses the exact same expression as the index, the planner will use it.

   CREATE INDEX idx_meta_status ON items ((metadata->>'status'));
   CREATE INDEX idx_meta_region ON items ((metadata->>'region'));
   -- The query must match the expression exactly:
   SELECT * FROM items WHERE metadata->>'status' = 'active';

2. When to use a generated column: you want the value as a real column too. A STORED generated column + b-tree index gives healthier planner statistics (n_distinct, histograms), reads more naturally, and lets you reference the column directly in joins. The cost: a schema change per key and duplicated storage.

   ALTER TABLE items
     ADD COLUMN status text GENERATED ALWAYS AS (metadata->>'status') STORED;
   CREATE INDEX ON items (status);

3. Mind the cast trap. ->>' always returns text. For range queries on a numeric field ((metadata->>'age')::int > 18), the cast must be written identically in both the index definition and the query — otherwise the index won’t be used. Watch out for NULLs and empty values too.

4. Keep GIN in reserve, don’t dismiss it. If keys are dynamic, users type in free-form metadata, or you do containment like metadata @> '{"tags":["x"]}', GIN is the right tool — and if you only do containment, the jsonb_path_ops variant is smaller and faster.

Bottom line: personally I’d create two expression indexes for the two fixed keys and confirm with EXPLAIN (ANALYZE, BUFFERS) that the plan drops from a Seq Scan to an Index Scan. If I also use those values heavily in reports or joins, I’d promote them to a generated column. I’d only bring in GIN if the keys became unpredictable or I moved to containment queries. On fixed keys, GIN is cracking a nut with a sledgehammer.