GIN for Arrays/JSONB/Text Search and GiST for Ranges/Geometry-Like Semantics

Learning outcomes

B-tree shines when the useful question is equality/range/order over scalar keys. ServiceHub also needs “array contains this skill,” “JSON document contains these attributes,” “text matches this search query,” and “reservation range overlaps this time window.” Those are not simply different data types; they are different operators. PostgreSQL's Generalized Inverted Index (GIN) and Generalized Search Tree (GiST) frameworks become useful when the chosen operator class supports those semantics.

1. GIN mental model: values produce searchable keys

GIN is an inverted index. One row value can generate many index keys. An array can yield its elements; a tsvector yields lexemes; JSONB operator classes extract keys/values according to their own rules. That is why GIN can be excellent for membership/containment and why inserts can perform much more index work than a one-key scalar B-tree.

DROP TABLE IF EXISTS app.ch10_search_lab;CREATE TABLE app.ch10_search_lab (    work_order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    skills text[] NOT NULL,    attributes jsonb NOT NULL,    notes text NOT NULL,    notes_tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', notes)) STORED);INSERT INTO app.ch10_search_lab(skills, attributes, notes)SELECT ARRAY[         CASE g % 4 WHEN 0 THEN 'hvac' WHEN 1 THEN 'electrical' WHEN 2 THEN 'network' ELSE 'plumbing' END,         CASE g % 3 WHEN 0 THEN 'night' WHEN 1 THEN 'onsite' ELSE 'remote' END       ],       jsonb_build_object(         'region', CASE g % 5 WHEN 0 THEN 'north' WHEN 1 THEN 'south' WHEN 2 THEN 'east' WHEN 3 THEN 'west' ELSE 'central' END,         'priority', (g % 5) + 1,         'verified', (g % 7 <> 0)       ) || CASE WHEN g % 1000 = 0 THEN '{"special":true}'::jsonb ELSE '{}'::jsonb END,       CASE g % 4         WHEN 0 THEN 'compressor vibration inspection and refrigerant pressure check'         WHEN 1 THEN 'electrical panel breaker and voltage diagnosis'         WHEN 2 THEN 'network latency packet loss router troubleshooting'         ELSE 'water pressure valve leak and pipe inspection'       ENDFROM generate_series(1,100000) AS g;ANALYZE app.ch10_search_lab;

2. Arrays: operator support comes from array_ops

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)SELECT count(*)FROM app.ch10_search_labWHERE skills @> ARRAY['hvac'];CREATE INDEX ch10_skills_gin_idxON app.ch10_search_lab USING gin(skills);ANALYZE app.ch10_search_lab;EXPLAIN (ANALYZE, BUFFERS, SETTINGS)SELECT count(*)FROM app.ch10_search_labWHERE skills @> ARRAY['hvac'];

The built-in GIN array_ops class supports overlap (&&), contains (@>), contained-by (<@), and equality. An operator absent from the class cannot use the index just because the left operand is an array.

3. JSONB: jsonb_ops versus jsonb_path_ops

The default jsonb_ops supports containment plus key-existence operators such as ?, ?|, and ?&, along with supported jsonpath matches. jsonb_path_ops supports a narrower operator set—principally containment/jsonpath—but can produce a smaller and more specific index for those supported searches. “path_ops is faster” is incomplete because it may not support the operator your application needs.

CREATE INDEX ch10_attributes_jsonb_ops_idxON app.ch10_search_lab USING gin(attributes);CREATE INDEX ch10_attributes_path_ops_idxON app.ch10_search_lab USING gin(attributes jsonb_path_ops);SELECT indexrelid::regclass AS index_name,       pg_size_pretty(pg_relation_size(indexrelid)) AS sizeFROM pg_stat_user_indexesWHERE indexrelid IN (  'app.ch10_attributes_jsonb_ops_idx'::regclass,  'app.ch10_attributes_path_ops_idx'::regclass)ORDER BY pg_relation_size(indexrelid);

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)SELECT work_order_idFROM app.ch10_search_labWHERE attributes @> '{"region":"north","priority":1,"verified":true}'::jsonb;

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)SELECT count(*)FROM app.ch10_search_labWHERE attributes ? 'special';

Do not force both indexes into production solely because both are technically usable. Measure workload frequency, selectivity, size, write rate, cache pressure, and supported operator requirements.

4. Full-text search: tsvector + GIN

Full-text search is not equivalent to LIKE '%word%'. PostgreSQL parses documents into lexemes and evaluates a tsquery. A GIN index over tsvector supports the @@ search operator.

CREATE INDEX ch10_notes_fts_gin_idxON app.ch10_search_lab USING gin(notes_tsv);EXPLAIN (ANALYZE, BUFFERS, SETTINGS)SELECT work_order_idFROM app.ch10_search_labWHERE notes_tsv @@ plainto_tsquery('english', 'network latency');

GIN can use a pending list to make updates faster and consolidate work later. That is a maintenance/read-latency tradeoff, not something to tune blindly. Inspect fastupdate and gin_pending_list_limit only when measurements point to GIN write or cleanup behavior.

5. GiST: generalized search semantics for ranges

GiST is a balanced search-tree framework whose operator class defines the actual semantics. PostgreSQL's built-in range classes make overlap, containment, adjacency, and relative-position operators indexable. That is far more useful for time windows than the arbitrary scalar ordering a B-tree can assign to ranges.

DROP TABLE IF EXISTS app.ch10_reservation_lab;CREATE TABLE app.ch10_reservation_lab (    reservation_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    technician_id integer NOT NULL,    during tstzrange NOT NULL);INSERT INTO app.ch10_reservation_lab(technician_id, during)SELECT (g % 100) + 1,       tstzrange(         timestamptz '2026-08-01 00:00+00' + g * interval '20 minutes',         timestamptz '2026-08-01 00:00+00' + g * interval '20 minutes' + interval '90 minutes',         '[)'       )FROM generate_series(1,60000) AS g;ANALYZE app.ch10_reservation_lab;CREATE INDEX ch10_reservation_gist_idxON app.ch10_reservation_lab USING gist(during);

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)SELECT reservation_id, technician_id, duringFROM app.ch10_reservation_labWHERE during && tstzrange(  timestamptz '2026-08-20 08:00+00',  timestamptz '2026-08-20 12:00+00',  '[)');

Depending on selectivity, PostgreSQL may choose an index/bitmap path or a sequential scan. An overlap operator being indexable does not guarantee using the index when a large fraction of the table overlaps the search window.

6. K-nearest-neighbor capability is operator-class specific

Some GiST operator classes define ordering operators such as geometric distance <->, enabling K-nearest-neighbor-style ordered scans. This is a property of that operator class, not a generic promise that every GiST index can sort by “distance.”

DROP TABLE IF EXISTS app.ch10_point_lab;CREATE TABLE app.ch10_point_lab(id integer PRIMARY KEY, location point NOT NULL);INSERT INTO app.ch10_point_labSELECT g, point((g % 1000)::double precision, (g / 1000)::double precision)FROM generate_series(1,100000) AS g;CREATE INDEX ch10_point_gist_idxON app.ch10_point_lab USING gist(location);ANALYZE app.ch10_point_lab;EXPLAIN (ANALYZE, BUFFERS, SETTINGS)SELECT id, location, location <-> point(500,50) AS distanceFROM app.ch10_point_labORDER BY location <-> point(500,50)LIMIT 10;

Expect an ordered GiST index scan when the planner recognizes the ordering operator. Again, this demonstrates built-in point semantics; it is not a substitute for a domain-specific geospatial model or external extension when true geographic coordinates are required.

7. Rechecks and lossy evidence are normal

Bitmap scans may report Recheck Cond, and some access methods/operator classes are intrinsically lossy for certain searches. “Recheck” does not mean PostgreSQL is returning incorrect rows. It means the index narrows candidates and the executor verifies the original condition against heap tuples.

SELECT am.amname, opc.opcname, opc.opcintype::regtypeFROM pg_opclass AS opcJOIN pg_am AS am ON am.oid = opc.opcmethodWHERE am.amname IN ('gin','gist')  AND opc.opcname IN ('array_ops','jsonb_ops','jsonb_path_ops','tsvector_ops','range_ops','point_ops')ORDER BY am.amname, opc.opcname;

8. Wrong approach: “JSONB means GIN; range means GiST”

Suppose the only JSONB query is equality on the entire document and the range column is used only for exact equality. A specialized containment index may add write/storage cost without helping the actual operators. Conversely, a B-tree on JSONB cannot replace GIN containment semantics merely because B-tree supports some ordering/equality for JSONB.

The repaired workflow inventories predicates first: Which operators occur? How selective are they? What is the read/write ratio? Which operator class supports those operators? Is recheck acceptable? How large is the index? What does the plan actually do?

9. Production judgment and cleanup

GIN is often appropriate when one indexed value yields many searchable keys—arrays, JSONB containment/key search, full text. GiST is a general framework useful for range/geometry-like relationships and operator-specific ordering. Both can be write-heavier than a scalar B-tree. Test plan shape, candidate rechecks, size, build time, update volume, vacuum/maintenance behavior, and concurrency under the actual workload.

DROP TABLE IF EXISTS app.ch10_point_lab;DROP TABLE IF EXISTS app.ch10_reservation_lab;DROP TABLE IF EXISTS app.ch10_search_lab;

Authoritative references

Index behavior depends on PostgreSQL major version, operator class, collation, statistics, data distribution, visibility, and workload. Use the documentation for the exact major you operate.

• PostgreSQL 18 — GIN Indexes
• PostgreSQL 18 — JSONB Indexing
• PostgreSQL 18 — GiST Indexes
• PostgreSQL 18 — Range Types / Indexing
• PostgreSQL 18 — Locking and Indexes