I am on a spree to explore PostgreSQL internals, and

I am on a spree to explore PostgreSQL internals, and today, let me talk about how PG handles JSONB data with GIN (Generalized Inverted Index) indexes.

When I was digging deeper, I found two operator classes, jsonb_ops and jsonb_path_ops, each serving different purposes and optimizing different types of queries. Here’s a quick write-up about it

1. jsonb_ops

By default, when you create a GIN index on your JSONB column, PostgreSQL uses the jsonb_ops class. This class indexes every attribute of the JSON object, literally every single key value, including nested objects and arrays.

You can pass this class when you are creating your index, as shown below

CREATE INDEX idx_users_addr ON users USING gin (addr jsonb_ops);

Because it indexes broadly, it supports all types of queries on the column, like, checking the existence of keys inside the JSON or checking if a particular value is present on a specific JSON path.

The breadth of its support makes it incredibly versatile, but it comes with the downside of larger index sizes, which can slow down data insertion and modifications.

2. jsonb_path_ops

If your primary use case is to make quick containment checks, jsonb_path_ops is what you should prefer. This class optimizes specifically for such queries by indexing only the paths to terminal values (the ultimate leaf nodes in the JSON structure).

You can pass this class when you are creating your index, as shown below

CREATE INDEX idx_users_addr ON users USING gin (addr jsonb_path_ops);

It is not suitable for queries checking key existence or array memberships as those aren’t supported by this class, i.e., you cannot get all the documents in which the key “status” exists using this operator, because it indexes only the terminal values <status = “active”>.

Digging deeper into nuances is always fun :)