Nov 20, 20265 min read

Postgres JSONB Indexing with GIN and Expression Indexes

How to index JSONB columns in Postgres: GIN with jsonb_ops vs jsonb_path_ops, which operators each index supports, expression indexes for single keys, generated columns for hot fields, and the query shapes that silently skip the index.

Illustration for Postgres JSONB Indexing with GIN and Expression Indexes

A jsonb column is the pragmatic place for data whose shape varies by customer or event type: product attributes, webhook payloads, feature settings. It stays fast until the table reaches a few million rows and a filter on one key turns into a sequential scan.

Postgres has three different ways to index JSONB, and each one serves a different query shape.

The three options

IndexExampleServesSize
GIN jsonb_ops (default)USING gin (attrs)@>, ?, ?|, ?&, @?, @@Largest
GIN jsonb_path_opsUSING gin (attrs jsonb_path_ops)@>, @?, @@Smaller, faster lookups
B-tree expression((attrs->>'color'))=, <, >, ORDER BY on that keySmall

The JSONB indexing section of the Postgres docs describes both GIN operator classes, and the built-in GIN operator classes table lists exactly which operators each one supports.

GIN for containment

Containment asks "does this document include this fragment", and it is what a GIN index on the column accelerates:

CREATE INDEX products_attrs_gin ON products USING gin (attrs jsonb_path_ops); -- uses the index SELECT id, name FROM products WHERE attrs @> '{"color": "red", "size": "M"}';

jsonb_path_ops stores a hash for each path to a value, so {"color": "red"} becomes one index entry rather than separate entries for the key and the value. That makes it smaller and more selective than jsonb_ops, and it is the right default unless you query for the existence of keys with ?.

The query shape that skips the index

The most common surprise is a query that reads naturally but cannot use a GIN index:

-- does NOT use the GIN index: ->> returns text, and = on text is a B-tree operation SELECT id FROM products WHERE attrs->>'color' = 'red'; -- does use it: same meaning, expressed as containment SELECT id FROM products WHERE attrs @> '{"color": "red"}';

The two are equivalent for string values, but only the second matches an operator in the GIN operator class. Run EXPLAIN after writing any new JSONB filter and look for Bitmap Index Scan on the GIN index rather than Seq Scan.

Expression indexes for one key

When queries always filter or sort on one key, a B-tree index on the extracted value is smaller and supports ranges and ordering, which GIN cannot:

CREATE INDEX products_price_idx ON products (((attrs->>'price_cents')::bigint)); SELECT id FROM products WHERE (attrs->>'price_cents')::bigint BETWEEN 1000 AND 5000 ORDER BY (attrs->>'price_cents')::bigint;

The query must repeat the expression exactly, including the cast, or the planner will not match it to the index.

Promote hot fields to columns

The planner keeps limited statistics on values inside JSONB, so estimates for containment filters are often generic guesses, which can lead to poor join orders. A key that is filtered on in most queries is usually better as a regular column. A stored generated column does that without changing writers:

ALTER TABLE products ADD COLUMN brand text GENERATED ALWAYS AS (attrs->>'brand') STORED; CREATE INDEX products_brand_idx ON products (brand);

The column gets normal statistics, can be indexed with a plain B-tree, and stays in sync with the JSON automatically. Adding a stored generated column rewrites the table, so do it in a maintenance window on large tables.

Write cost

GIN indexes are expensive to update, since one document produces many index entries. Postgres buffers new entries in a pending list when fastupdate is on, which keeps inserts fast but makes some queries scan the pending list until vacuum or gin_pending_list_limit flushes it. For write-heavy tables, index only the keys you query with expression indexes or a GIN index on a sub-object, such as USING gin ((attrs->'tags')), rather than the whole document.

Best fit: GIN jsonb_path_ops for flexible containment filters, expression indexes for ranges and sorting on one key, generated columns for fields most queries use.

JSONB in an Encore service

Encore

Encore is a backend framework with built-in infrastructure, and a service's database comes with migrations that run locally under encore run and on every deploy, so the GIN index ships in the same change as the endpoint whose filter it serves:

Get started
brew install encoredev/tap/encore &&
encore app create
Copy

Run in your terminal to get started locally.

-- catalog/migrations/2_attrs_index.up.sql CREATE INDEX products_attrs_gin ON products USING gin (attrs jsonb_path_ops);
// catalog/search.ts import { api } from "encore.dev/api"; import { SQLDatabase } from "encore.dev/storage/sqldb"; const db = new SQLDatabase("catalog", { migrations: "./migrations" }); interface SearchParams { // Attribute filters, e.g. { "color": "red", "size": "M" } attrs: Record<string, string>; } interface Product { id: number; name: string; } export const search = api( { expose: true, method: "POST", path: "/products/search" }, async ({ attrs }: SearchParams): Promise<{ products: Product[] }> => { // Containment (@>) so the jsonb_path_ops GIN index can serve the filter. const filter = JSON.stringify(attrs); const products = await db.queryAll<Product>` SELECT id, name FROM products WHERE attrs @> ${filter}::jsonb LIMIT 100 `; return { products }; }, );

The tagged template sends the filter as a bound parameter, so user input never becomes SQL. Each search request produces a trace with the query duration, and encore db shell catalog opens psql against the local database for EXPLAIN. In production the same migration runs on RDS or Cloud SQL in your own account, as the AWS infrastructure docs describe, and API validation can restrict which attribute keys a client may send.

An AI coding agent adding a new filter can write it as containment, add the index migration and confirm the plan in the local shell, all in one change a reviewer reads top to bottom.

Best fit: services storing flexible attributes in Postgres that want indexes versioned with the queries they serve.

How to approach it

Start with a jsonb_path_ops GIN index and write filters as containment. Add expression indexes for keys you sort or range over, promote keys that most queries use to generated columns, and check every new JSONB query with EXPLAIN before it ships.

Frequently asked questions

What index should I use for a JSONB column in Postgres?

Use a GIN index with jsonb_path_ops when queries filter with containment (@>), since it is smaller and faster than the default. Use the default jsonb_ops when you also need key-existence operators like ?, and a B-tree expression index when queries always filter or sort on one specific key.

Why is my JSONB query not using the GIN index?

Usually because it uses ->> equality, such as data->>'status' = 'paid', which a GIN index on the column cannot serve. Rewrite it as containment, data @> '{"status": "paid"}', or add a B-tree index on the expression (data->>'status').

What is the difference between jsonb_ops and jsonb_path_ops?

jsonb_ops indexes every key and value separately and supports @>, ?, ?|, ?&, @? and @@. jsonb_path_ops indexes hashes of paths to values, which makes it smaller and faster for @>, @? and @@, but it cannot answer key-existence queries.

How do I add a JSONB index in an Encore app?

Write CREATE INDEX ... USING gin (attrs jsonb_path_ops) in a migration file in the service's migrations directory. encore run applies it to the local Postgres, and the same migration runs on deploy.

Deploy to your cloud on AWS and Google Cloud
without managing infrastructure

Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.

~/orders — claude
Claude Code
Claude Code v2.1.180Opus 5.5~/orders