PostgreSQL JSONB Indexes: When to Use GIN and When to Use B-Tree

PostgreSQL makes it possible to store structured data in a JSONB column without creating a separate database column for every property. This is useful when records contain optional fields, configuration settings, product attributes, or API data that changes over time.

However, storing JSONB data is only part of the job. As the table grows, queries that search inside JSON documents can become slow if the database does not have a suitable index.

PostgreSQL supports several indexing options, but two important choices are GIN and B-tree. They solve different problems. Choosing the wrong index can waste storage without improving query performance.

In this article, we will use practical SQL examples to understand when to use each index, how to create them, and how to check whether PostgreSQL is using them.

What Is JSONB in PostgreSQL?

PostgreSQL provides two JSON data types: JSON and JSONB.

The JSON type stores the original JSON text, while JSONB stores the data in a decomposed binary format that is generally more suitable for querying and indexing.

Consider a table that stores product details:

CREATE TABLE products
(
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    details JSONB NOT NULL
);

Insert some sample records:

INSERT INTO products (name, details)
VALUES
(
    'Laptop',
    '{
        "brand": "Contoso",
        "category": "Electronics",
        "price": 1200,
        "in_stock": true,
        "tags": ["computer", "portable"]
    }'::jsonb
),
(
    'Keyboard',
    '{
        "brand": "Fabrikam",
        "category": "Accessories",
        "price": 80,
        "in_stock": true,
        "tags": ["computer", "wireless"]
    }'::jsonb
),
(
    'Monitor',
    '{
        "brand": "Contoso",
        "category": "Electronics",
        "price": 350,
        "in_stock": false,
        "tags": ["display", "computer"]
    }'::jsonb
);

You can retrieve a JSON property using the arrow operators.

SELECT
    name,
    details ->> 'brand' AS brand
FROM products;

The -> operator returns a JSON value, while ->> returns a value as text.

For example, this query searches for products from Contoso:

SELECT id, name
FROM products
WHERE details ->> 'brand' = 'Contoso';

Without a suitable index, PostgreSQL may need to examine many rows to evaluate the condition. Whether that becomes a problem depends on the table size, data distribution, and query workload.

What Is a B-tree Index?

B-tree is PostgreSQL's default index type. It is designed for comparisons such as equality, ranges, and ordered searches.

A B-tree index is a good fit when you search for a specific value in a column or use that value in range conditions.

For example, the following query searches for a product by its name:

SELECT id, name
FROM products
WHERE name = 'Laptop';

A normal B-tree index can support this query:

CREATE INDEX idx_products_name
ON products (name);

B-tree indexes also support conditions such as:

SELECT id, name
FROM products
WHERE id BETWEEN 100 AND 200
ORDER BY id;

The primary key on the id column already creates a unique B-tree index, so you would not normally create another identical index for this example.

Using B-tree with a JSONB property

You can also create an expression index on a specific JSONB property.

CREATE INDEX idx_products_brand
ON products ((details ->> 'brand'));

Now the following query can use the expression index when PostgreSQL determines that an index scan is cost-effective:

SELECT id, name
FROM products
WHERE details ->> 'brand' = 'Contoso';

The indexed expression must match the expression used by the query closely enough for PostgreSQL to recognize it.

This approach is useful when the application frequently searches or sorts by one known JSON property.

You can also index a numeric expression when queries compare numeric values:

CREATE INDEX idx_products_price
ON products (((details ->> 'price')::numeric));

Use the same cast in the query:

SELECT id, name
FROM products
WHERE (details ->> 'price')::numeric > 300;

Be careful with this design. If a row contains a missing price or a value that cannot be converted to a number, the query or index creation may fail depending on the expression and the data. Validate the JSON structure before relying on such an index.

What Is a GIN Index?

GIN stands for Generalized Inverted Index. Instead of organizing entries primarily around one scalar value, a GIN index records searchable components of composite values, such as keys and values in JSONB documents.

This makes GIN useful when queries search for the presence of JSON properties, matching JSON structures, or array elements.

Create a GIN index on the details column:

CREATE INDEX idx_products_details_gin
ON products
USING GIN (details);

You can then query for products whose JSONB document contains a particular key and value:

SELECT id, name
FROM products
WHERE details @> '{"brand": "Contoso"}'::jsonb;

The @> operator checks whether the left JSONB value contains the right JSONB value.

A GIN index can also help with queries that check whether a JSONB document contains a key:

SELECT id, name
FROM products
WHERE details ? 'in_stock';

It can be useful for arrays as well. For example:

SELECT id, name
FROM products
WHERE details @> '{"tags": ["computer"]}'::jsonb;

This query finds documents whose tags array contains the value computer.

The important point is that a GIN index is not a universal accelerator for every JSONB expression. The operator used by the query and the operator classes supported by the index matter.

GIN Operator Classes: jsonb_ops vs. jsonb_path_ops

When creating a GIN index on JSONB, PostgreSQL offers two commonly used operator classes: jsonb_ops and jsonb_path_ops.

The default is jsonb_ops.

CREATE INDEX idx_products_details_gin
ON products
USING GIN (details);

You can explicitly choose jsonb_path_ops:

CREATE INDEX idx_products_details_path_gin
ON products
USING GIN (details jsonb_path_ops);

They differ in the operators and query patterns they support.

The best choice depends on the queries your application runs.

If you need key-existence checks, use the default jsonb_ops or another suitable indexing strategy. If your workload mainly uses containment searches, jsonb_path_ops may be worth testing.

Neither operator class is automatically faster for every workload. Measure index size and query performance using representative data.

GIN vs. B-tree: Which One Should You Choose?

The choice depends on the structure of your query.

These are starting points rather than absolute rules. PostgreSQL's query planner considers table size, statistics, selectivity, and estimated costs when deciding whether to use an index.

For a small table, a sequential scan may be faster than an index scan. An index being available does not mean PostgreSQL must use it.

How to Check Whether PostgreSQL Uses Your Index

Use EXPLAIN to inspect the query plan.

EXPLAIN
SELECT id, name
FROM products
WHERE details @> '{"brand": "Contoso"}'::jsonb;

For execution details, use EXPLAIN ANALYZE:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, name
FROM products
WHERE details @> '{"brand": "Contoso"}'::jsonb;

EXPLAIN ANALYZE actually runs the query. Use care with statements that modify data, and remember that measured execution time can vary with cache state and workload.

Look for plan nodes such as:

• Bitmap Index Scan, which may retrieve matching entries from an index.
• Bitmap Heap Scan, which may fetch the matching table rows.
• Index Scan, which reads matching rows through an index.
• Seq Scan, which reads the table sequentially.

A sequential scan is not necessarily a problem. If a query returns a large percentage of the table, reading the table directly may be cheaper than following index entries and fetching many rows.

For a meaningful test, use enough data to represent the production workload. A three-row example table is useful for learning SQL but is too small to demonstrate realistic index-selection behavior.

Common Indexing Mistakes

Creating a GIN index for every JSONB query

A GIN index is helpful for supported containment and key-search operations, but it may not be the best fit for scalar comparisons or sorting by one property.

Use a B-tree expression index when a query repeatedly filters on a specific extracted value.

Using a different expression in the query

If you create an index on details ->> 'brand', but the query uses a different expression or cast, PostgreSQL may not use the index.

Keep the indexed expression consistent with the query and confirm the plan.

Forgetting the cost of index maintenance

Indexes take disk space and add work to INSERT, UPDATE, and DELETE operations. GIN indexes can be especially expensive to maintain when indexed JSONB documents change frequently.

Do not create indexes simply because a column contains JSONB. Build indexes around actual query patterns.

Assuming all JSON values have the same type

JSONB allows values with different types. A property may be missing, contain a string, or contain a number. Casting arbitrary text to numeric can fail if the data is inconsistent.

Validate incoming JSON and keep frequently queried fields consistent.

Ignoring statistics

After substantial data changes, make sure PostgreSQL has up-to-date statistics. PostgreSQL normally maintains statistics through autovacuum and auto-analyze, but manual analysis may be useful after certain bulk operations.

ANALYZE products;

Best Practices for Production Applications

• Start with the queries your application actually runs.
• Use B-tree expression indexes for frequently queried scalar JSON properties.
• Use GIN for supported containment, key-existence, and JSONPath operations.
• Choose the GIN operator class based on required operators.
• Validate JSON structure before indexing expressions that cast values.
• Use EXPLAIN and EXPLAIN ANALYZE to evaluate query plans.
• Test with production-like data volumes and distributions.
• Monitor index size and write performance.
• Remove unused indexes after confirming they are not needed by important workloads.
• Consider moving stable, frequently queried fields into regular table columns when that makes the schema and indexing strategy simpler.

Summary

PostgreSQL JSONB provides flexible storage for structured data, but query performance depends on choosing indexes that match the operators and expressions used by your application.

B-tree is usually a strong starting point for exact matches, ranges, and ordering on individual JSON properties. GIN is designed for supported searches involving JSON structure, key presence, and containment.

Before adding an index, identify the slow query, select an index strategy that supports its conditions, and check the execution plan with realistic data. The goal is not to create the most indexes, but to create the right indexes for the workload.