PostgreSQL JSONB is not a listing index
A GIN on payload looks complete. WHERE title->>'ru' still seq-scans. Containment and extract are different jobs.
The catalog listing on a shop that had moved to PostgreSQL still seq-scanned. On shops that already used MySQL JSON I sometimes moved the catalog to PostgreSQL for JSONB and GIN. The listing still filtered published rows and sorted by an editor field. The WHERE clause used title->>'ru'. EXPLAIN showed Seq Scan. The GIN sat unused.
Editors waited on the public grid. PostgreSQL burned CPU on extract. Vue SSR printed forty tiles and paid for a full table read. GIN on JSONB is built for containment: payload @> '{"tag":"sale"}'. The ->> operator produces text. That text is not what the GIN stores unless you add an expression index on the extract.
I keep JSONB for the admin document. The public list does not read it.
Generated text, btree for the grid
title_ru is a stored generated column from the JSONB. The listing SELECT is id, slug, price, title_ru. The index is (is_published, sort_order, id) and includes those fields. Sort stays on a small integer the editor sets. Title sort belongs in admin, not on the public grid.
Search that really needs JSON keeps a GIN and uses @> or jsonb_path_exists. Mixing both in one query is how you get a seq scan and a gin scan that still sorts in work_mem.
I tried an expression index on (title->>'ru'). It helped a filter on one locale. The listing still sorted sort_order and still needed published. Two indexes, two plans, and a third locale meant two more expression indexes. Generated columns plus one btree were smaller to reason about. Writes got a bit heavier. The grid got a plan I could read.
What I do not store in JSONB
Price, published flag, and sort order stay typed columns. Vue SSR receives one locale string. PHP does not json_decode three languages to print forty tiles. MySQL and PostgreSQL then look the same from the listing: covering btree, compact rows, JSON off the hot path.
The stack still has MySQL on this site. PostgreSQL is the other catalog I run. The rule does not change with the engine. JSON is the form. The list is a table.
I dumped pg_stat_user_indexes after the GIN went in. idx_scan on the GIN stayed near zero for the listing route. idx_scan on the btree moved. That is the measurement that ended the argument that JSONB was already indexed.
What I took from this
I read EXPLAIN and pg_stat_user_indexes on the listing route, not the presence of a GIN. Unused GIN plus Seq Scan is the usual shape.
I do not trust title->>'ru' to use a payload GIN. Containment is @>. Extract needs a generated column or an expression index.
The rule I keep: typed columns for price, published, sort. One locale string to Vue. JSONB stays the admin document. Do not mix listing sort and JSON search in one query.