hstore vs jsonb performance in PostgreSQL, measured

hstore is the key-value type PostgreSQL had before jsonb, and for flat data it is still faster on most of what we measured. On a million documents with ten keys each, in PostgreSQL 17.11, reading a key took 59 ms with hstore against 68 ms with jsonb, changing one key in 100,000 rows 407 against 458 ms with jsonb_set, and loading the table took half the time. What hstore gives up is types: every value is text, which changes how values sort, cast and convert back to JSON. And on PostgreSQL 17 a GIN index on either type helped a query that matched one row in a hundred little or not at all; on 18 it did help.

We came to this from our audit log measurement, where depesz showed that an hstore diff beats a jsonb diff in a trigger. This article asks the broader question for the case where hstore is a real alternative: flat attributes, no nesting.

The setup

PostgreSQL 17.11 in a throwaway container with default settings (shared_buffers 128 MB), on one machine with 16 cores. Storage, reads and GIN indexes were repeated on PostgreSQL 18.6; loading, updates and the type checks ran on 17 only. Three tables with the same million rows: hstore, jsonb with real numbers and booleans, and jsonb with every value as a string, which is what hstore stores. Each row has ten flat keys: five text values (a colour out of 20, a size out of 5, a 12-character SKU, a 30 to 50 character note, a brand out of 50), three integers, a price with two decimals and a boolean. One row in a hundred has an eleventh key. A row looks like this in jsonb:

{"qty": 389, "sku": "FEBD3F948188", "note": "3a80e983750ac502b235bc760c7c3d7559", "rank": 753957, "size": "M", "brand": "brand_25", "color": "maroon", "price": 284.76, "stock": 5755, "active": false}

Every timing is the median of three interleaved runs, each in its own psql session. Queries had one untimed warm-up; loads, index builds and updates started from a CHECKPOINT. The tables are larger than shared_buffers, so “warm” means the operating system’s page cache. The scripts are linked at the end.

Is hstore faster than jsonb?

For flat data, on almost everything we timed except building a GIN index. PostgreSQL 17.11, one million rows:

MB is a million bytes. The reads ran as a parallel sequential scan with two workers. The update ran inside a transaction that we rolled back, with only the primary key on the table.

Three things in that table are worth a closer look. The size difference is the types, not the format: jsonb with every value as a string took exactly as much room as hstore, byte for byte, and the typed jsonb was 18 bytes a row larger. The load measures building the value as much as storing it: hstore(array[…], array[…]) against jsonb_build_object(…) on the same source table, so read it as the cost of producing a million documents, not of writing them. And for changing a key in jsonb, jsonb_set was faster than concatenating with || in our run, 458 against 556 ms.

On PostgreSQL 18.6 the order of the reads was the same: reading a key took 71.2 ms with hstore, 80.8 with jsonb and 83.9 with string jsonb.

Does a GIN index make hstore or jsonb queries fast?

On PostgreSQL 17, hardly, for a query that matches one row in a hundred on a table of a million. With a GIN index the planner used a bitmap index scan for every variant. The index part took 6 to 19 ms. The rest of the time went to reading 8,561 to 8,652 table blocks, a quarter of the table. The bitmap plans ran in a single process; the sequential scans had two parallel workers. We did not isolate how much of the gap that explains:

On PostgreSQL 18.6 the same query was faster with every index: hstore 46.5 ms with GIN against 66.4 without, jsonb_ops 51.8 against 96.0, jsonb_path_ops 39.5. So the version matters as much as the type here.

For hstore the planner estimated 100 rows every time, where 9,913 and 10,147 came back. For jsonb the estimate swung between 100, 10,101 and 20,202 from one ANALYZE to the next, whatever the operator class. Where it estimated 100, it expected a far smaller result than it got; the one case where the index lost, hstore on 17, was one of those. We did not measure at what selectivity the index starts to win clearly.

What the index costs to build and to keep:

The hstore index took 2.4 times as long to build as jsonb_ops and came out the same size. jsonb_path_ops was a third smaller and took less than half the time of jsonb_ops. It does not support the key-exists operator ?, as the documentation says: the planner did a sequential scan for that query instead. On PostgreSQL 18.6 every GIN index built faster and came out 24 to 28% larger than on 17.11, from the same data; we did not look into why.

A B-tree expression index on one key worked the same for both types: 7.3 MB on ((attrs -> 'qty')::int) for hstore and on ((attrs ->> 'qty')::int) for jsonb, and about 50 ms for a range of 10,007 rows. The query has to use exactly the indexed expression. With the index on (attrs ->> 'qty')::int, a query on (attrs -> 'qty')::int got a sequential scan of 156 ms.

What do you lose with hstore?

The types. The documentation says it in one sentence: “Keys and values are simply text strings.” That shows up in practice in these places:

Both types gave 9999 for max() on a text extraction and 10000 after a cast to int: in jsonb, too, ->> returns text. The difference is that jsonb knows the value was a number, so jsonb_typeof and a JSON consumer see it as one. Casting on jsonb was faster through ->> than directly from jsonb, 75 against 111 ms on the full table. A jsonb string does not cast to a number at all: (attrs -> 'qty')::numeric on the all-strings table failed with cannot cast jsonb string to type numeric.

Converting hstore back to typed JSON is a guess. hstore_to_jsonb_loose “attempts to distinguish numerical and Boolean values”, in the words of the documentation, and in our run it turned 389 and 1e3 into numbers and t and f into booleans. It left true and false as strings, and also 042. A boolean column that went into hstore through a cast to text comes out as "false", a string, not false.

Two more limits. An hstore value is flat: no nested objects and no arrays. And hstore is an extension. The documentation marks it as trusted, so a role with CREATE privilege on the database can install it without being a superuser. Which extensions that is true for, and what pgvector and PostGIS need instead, is in permission denied to create extension. In the audit log measurement we also found that hstore stores timestamps in the text form of the session that writes them, which made one date come back with day and month swapped; that is in the audit log article.

When would we use hstore over jsonb?

For flat text attributes that are written often and read by key: tags, labels, settings per tenant. There hstore was as small as jsonb with string values, loaded in half the time, scanned faster and updated about as fast, and the type loss costs nothing because the values were text to begin with. For the changed columns in an audit trigger it was the fastest option we measured, as long as you accept the timestamp caveat above.

For anything with numbers you compute with, nested structure, or data that leaves the database as JSON, we would use jsonb. And if the column needs an index for containment queries, use jsonb_path_ops, which was the smallest and fastest to build of the three and the fastest to query, unless you also need ?.

Reproduce the benchmark

Two scripts: setup.sql generates the data from a fixed seed into a source table, and run.sh loads the three tables and runs every measurement. Start a container with docker run -d --name hstore-bench -e POSTGRES_PASSWORD=x postgres:17 and run ./run.sh; on our machine that took under seven minutes. run.sh writes the results and every psql session next to the script, and a fingerprint of the generated data to raw/pg17/setup.out, so you can check you are measuring the same million rows: our sums were qty 499,145,982, stock 4,997,788,569 and price 249,928,244.02, identical on 17.11 and 18.6.

What we did not measure

Nested documents, large documents that go to TOAST, jsonpath queries, concurrent writers, a cold cache, and tuned settings. On a server where the table fits in shared_buffers, the gap between the bitmap scan and the sequential scan will differ. All timings are from one machine; read them as relative, not absolute, and the differences under 10% as a tie.

Sources: the PostgreSQL 17 documentation on hstore and on JSON types (GIN operator classes), read on 18 October 2026. All measurements are our own, on PostgreSQL 17.11 and 18.6 in throwaway containers, on 18 October 2026.