Currently Available: Need a skilled Software Developer for your next project?
Categories
Databases

Mapping Multi-Value Columns: GIN vs. B-Tree Indexes

When a PostgreSQL or Oracle query looks for a value inside an array or JSON document, choose an index based on what the query asks for. A B-tree keeps values in order, so it supports comparisons such as an exact match or a range of values. PostgreSQL’s GIN index, short for Generalized Inverted Index, links components such as array elements or JSON keys to the rows that contain them. This makes GIN a natural choice for membership searches that its operator class supports.

Oracle has related JSON indexing options, including multivalue function-based indexes for certain array searches, but Oracle does not call that feature GIN, and the two products’ indexes are not interchangeable. Use a B-tree for a known scalar value or ordering requirement, and choose an index designed for the specific multi-value predicate. Then confirm that the database uses it and test the cost of maintaining it as data changes.

Match the index to the predicate

A scalar predicate, or query condition on one value in a row, compares that value with a requested value, such as a status. A membership predicate checks whether a value appears among several values in a row, such as whether an array contains a tag. The two conditions may use similar wording, but they ask the database to find rows in different ways.

A B-tree stores keys in sorted order. PostgreSQL uses B-trees by default for common comparisons such as equality and ranges, and it can use them to return rows in sorted order. Oracle also describes B-tree as its default and most common index type. For a known JSON path that holds one value, Oracle can use a function-based index. This index stores the result of an expression, such as extracting the value with json_value. The query’s return type must match the type specified in the index for the optimizer to use it. See PostgreSQL’s index types and Oracle’s JSON indexing options.

Depending on the database product, a B-tree can index some array values or expressions, including exact matches on an entire array. These indexes do not serve the same purpose as finding one component across many arrays. For that kind of lookup in PostgreSQL, GIN is designed to find rows by array component.

Use PostgreSQL GIN for supported membership searches

GIN splits an array or JSON document into component keys and links each key to the rows where it appears. The index can then find rows that contain a requested component, even when each row stores a full array or document. Each GIN operator class defines which query operators the index supports. PostgreSQL describes this structure and its write costs in the GIN documentation.

For PostgreSQL arrays, the standard GIN array operator class supports operators for containment (checking whether one array includes another’s elements), equality, and overlap (sharing elements), such as @>, <@, =, and &&. The predicate must use an operator supported by that operator class. Creating a GIN index alone does not make every expression on the column indexable.

CREATE INDEX product_tags_gin
    ON products USING GIN (tags);

For jsonb, PostgreSQL offers two common GIN operator classes. The default jsonb_ops supports key-existence operators such as ?, ?|, and ?&, as well as containment and JSONPath match operators. jsonb_path_ops supports fewer operators: it supports containment and JSONPath matching, but not the key-existence operators. PostgreSQL reports better performance with jsonb_path_ops for the operators it supports, so choose it only when its narrower operator set matches your queries. The PostgreSQL JSON documentation lists the supported operators and index behavior.

To use a GIN index for a query, index the same expression the query uses. A GIN index on an entire jsonb document does not automatically support a key-existence test applied to an extracted subdocument, such as payload -> 'tags'. You can index that expression directly:

CREATE INDEX events_tags_gin
    ON events USING GIN ((payload -> 'tags'));

A whole-document index suits queries across different parts of the document. A targeted expression index suits a recurring predicate on a particular path and can reduce the amount of document data the index must cover.

Choose Oracle JSON indexes by path and search scope

For a known path that returns one scalar, consider a B-tree or bitmap function-based index on json_value. Oracle notes that bitmap indexes can suit expressions with a small set of possible values. For array-oriented searches, Oracle documents multivalue function-based indexes for eligible json_exists predicates on JSON stored using the JSON data type. A JSON search index is a broader option for structural and full-text searches.

Oracle’s multivalue index targets scalar values inside arrays, including fields of objects within arrays. Oracle says a B-tree or bitmap index generally performs better when the target is a single scalar. Multivalue indexes also have eligibility restrictions: the indexed value’s type must match the query result, and some composite-index patterns, including sibling nested arrays, have restrictions. Check the rules for the target Oracle release and JSON storage type in Oracle’s multivalue index documentation.

The following syntax illustrates an Oracle multivalue index on nested JSON data:

CREATE MULTIVALUE INDEX mvi
    ON parts_tab t
    (t.jparts.parts.subparts.numberOnly());

This example uses Oracle Database 26 documentation syntax. It does not establish eligibility for every Oracle release or storage format. For broader structural or full-text searches, compare a multivalue index with Oracle’s JSON search index and other tuning options.

Compare the choices by query shape

Query need PostgreSQL Oracle
Compare, range over, or sort a single scalar Start with B-tree. Start with B-tree. For a known JSON scalar path, consider a json_value function-based index.
Find an element in an SQL array Consider GIN with a supported array operator. This is not a direct index-for-index comparison; choose an Oracle feature appropriate to the data representation.
Search JSON keys, values, or containment Consider GIN on jsonb, with an operator class that supports the query operators. For an eligible array search on native JSON data, evaluate a multivalue index.
Search varying JSON structures or text Check whether the specific jsonb operators fit GIN. Consider a JSON search index for broader structural or full-text searches.

These are starting points, not a speed ranking. There is no universal winner between GIN and B-tree because they address different predicates. The right comparison also depends on the database product, release, data type, and query workload.

Include index maintenance in the decision

When a row changes, PostgreSQL may add several keys to a GIN index because the row can contain multiple components. PostgreSQL’s fastupdate feature batches new entries in a pending list before moving them into the main index. This can speed up updates. Searches must also check the pending list, and cleanup makes some writes slower than others. Autovacuum and the pending-list threshold affect this behavior. For large loads or updates, PostgreSQL documents dropping and rebuilding a GIN index as an option to consider.

Oracle also maintains indexes as associated rows change, and indexes use storage. The available documentation does not establish a quantified write-cost comparison between Oracle multivalue indexes and PostgreSQL GIN. Measure each on the target database instead of assuming their maintenance behavior is equivalent. PostgreSQL discusses GIN operations in its index maintenance documentation, while Oracle summarizes the storage and maintenance costs of managing indexes.

Before keeping an index, test representative queries and writes with realistic data. Check the database’s execution plan to see whether it uses the intended index. For Oracle multivalue indexes, the plan can show a MULTI VALUE index range scan. For PostgreSQL, compare query plans for the exact operators and expressions you expect to run. Also measure index size and the effects of inserts and updates, especially when rows contain many array elements or JSON values.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com

Email updates

Usually a new article and a few links I found interesting.

No spam. Unsubscribe with one click.

Leave a Reply

Your email address will not be published. Required fields are marked *