Indexing Hot JSON Properties for Dashboards in PostgreSQL

Dashboards over JSON events slow down in a predictable way. The table grows, the panel that filters on one property gets slower, and someone suggests an index. Sometimes that is the right fix, and sometimes it makes things worse. Which one depends on how selective the filter is and which kind of index you build, and both are easy to measure.

I tested three kinds of index on a 2.25-million-row event table, with a property that matches few rows and one that matches many.

The queries

Two kinds of filter appear on dashboards. A selective one asks for a rare value: one search term, one SKU, one campaign. A broad one asks for a common value: a plan, a country, a status. The first is where indexes shine and the second is where they mislead.

-- selective: 2,000 of 2,250,000 events
select count(*) from an_events where props->>'query' = 'q45';

-- broad: 83,333 events (about 4 percent)
select count(*) from an_events where props->>'plan' = 'pro';

Without any index, both took about the same time, 65 ms and 85 ms. PostgreSQL scanned the table in parallel and parsed the JSON of every row. 2.25 million rows is small enough that this is tolerable. At 200 million it is not.

Expression index

An index on the expression the query uses:

create index ev_q_expr on an_events ((props->>'query'));

It built in 598 ms and was 15 MB. The selective lookup dropped from 65 ms to 4.7 ms. The plan shows a bitmap index scan on the expression:

Aggregate
  ->  Bitmap Heap Scan on an_events
        Recheck Cond: ((props ->> 'query') = 'q45')
        ->  Bitmap Index Scan on ev_q_expr

The index is used only when the query writes the same expression. props->>'query' matches. props->'query' = '"q45"' or props @> '{"query":"q45"}' would not. The expression must be spelled the same way, including the cast: an index on ((props->>'amount')::numeric) serves queries that cast the same way.

The broad filter got slower

I then built the same kind of index for plan and ran the broad query:

create index ev_plan_expr on an_events ((props->>'plan'));
select count(*) from an_events where props->>'plan' = 'pro';

85 ms without the index, 148 ms with it. The plan was a bitmap heap scan: the index finds the 83,333 matching rows and then the table pages that hold them, which are spread through the table. Reading that many scattered pages through an index costs more than a sequential pass over the table, which is exactly what the planner would have chosen given better estimates; here it believed the index was cheaper. The index also cost 550 ms to build and 14 MB.

The rule of thumb is that an index helps when the filter returns a small share of the table, in the low single-digit percent at most, and hurts or does nothing beyond that. For a broad filter, what helps is not an index on the property but a table organized for the question: a summary, or a typed column in a smaller table (extracting properties into typed columns every night).

Partial index

Many properties exist only on some event types. The query property only occurs on search events. An index on all rows indexes the other 87 percent of events as nulls. A partial index limits it:

create index ev_q_partial on an_events ((props->>'query')) where name = 'search';

It was 1,816 kB, not 15 MB, and built in 216 ms, not 598 ms. A query has to include the condition name = 'search' (or one that implies it) for the planner to use it, which dashboards for one event type already do. The saving is both space and write cost: rows that are not searches do not touch the index at all. That is why partial indexes are a good default for properties that belong to one event type.

GIN for any key

When you do not know in advance which property will be filtered, a GIN index covers the whole document:

create index ev_gin2 on an_events using gin (props jsonb_path_ops);

It built in 839 ms and was 26 MB, nearly twice the single-property btree. The selective query, written as containment, took 6.1 ms:

select count(*) from an_events where props @> '{"query": "q45"}';

The same GIN index was ignored when the query was written with ->>: the plan for props->>'plan' = 'pro' stayed a parallel sequential scan. GIN answers @> (and a few other operators), not the arrow operators. If you choose GIN, the dashboards must be written with containment. The operator classes and what they support are covered in indexing JSONB with GIN.

GIN also costs on writes. Inserting 100,000 rows into the table took 341 ms with no JSON index and 535 ms with the GIN index in place: a 57 percent increase. Event tables are write heavy, so that cost is paid constantly. B-tree expression indexes add a smaller cost per index, and a partial index adds none for rows that do not match.

A choice that holds up

For a dashboard on a JSON event table, a decision order that has held in my tests:

1. Is the filter selective? If it returns more than a few percent of the table, an index will not help. Look at a summary table or typed columns.

2. Is it for one event type? Use a partial index on the expression.

3. Is it a known property used by recurring queries? A btree expression index, with the query written to match it.

4. Is it unpredictable? GIN with jsonb_path_ops, and write queries with @>. Accept the write cost.

5. Does the value need a cast? Index the cast expression, and guard against bad values, since a cast that fails inside an index build aborts it.

Always check with EXPLAIN that the plan uses the index you built, and compare the time with and without it. An index that the planner ignores costs space and write speed for nothing. For range filters on numbers (amount > 90), a btree on the cast expression works, and a GIN index does not.

Related: designing JSON event properties for analysis argues for deciding the shape before you index, and handling properties that change type covers why a cast in an index definition can fail. The PostgreSQL docs on JSON indexing and partial indexes describe the options in full. To test an expression on a sample document, use the JSON path tester.

When no index is the right answer

The table is small. Under a few million rows a parallel scan is fast enough, and every index you add makes inserts slower and takes memory from the cache.

The filter is broad. As the 4 percent example showed, an index can be slower than a scan.

The query is a report, not a lookup. A nightly aggregate that reads the whole table does not use an index. Make the table smaller or typed.

Writes dominate. On an ingest path doing tens of thousands of inserts a second, do not put GIN on the raw table. Index a derived table instead, or accept sequential scans for rare queries.