`hstore` vs JSONB: why JSONB wins almost every time

hstore is an extension that provides key-value storage in a column. It's been around since PostgreSQL 8.4 (2009). It's the ancestor of the JSONB you already saw in module 1 (PG 9.4, 2014).

The valid question: why does hstore still exist if we have JSONB? Two reasons: (1) legacy in applications that adopted it before JSONB, (2) smaller overhead for purely string key-value cases.

This capsule is decision-driven and short: the simple rule is JSONB always, hstore only if you run into legacy code that uses it. But knowing it saves you from confusion when it shows up.

---

What hstore is

CREATE EXTENSION IF NOT EXISTS hstore;

-- The hstore type is key-value, strings only
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    attributes HSTORE
);

INSERT INTO products (name, attributes) VALUES
    ('iPhone', 'color => "black", storage => "256GB", brand => "Apple"');

-- Query
SELECT * FROM products WHERE attributes -> 'brand' = 'Apple';
SELECT * FROM products WHERE attributes ? 'storage';  -- has key

Characteristics:

• Key and value are always strings. No nesting, no arrays, no numbers/booleans.
• Specific operators: ->, ?, ?& (has all keys), ?| (has any).
• Index with GIN: CREATE INDEX ON products USING gin (attributes).

---

hstore vs JSONB: comparison

Aspect	hstore	JSONB
Value types	Strings only	string, number, bool, null, array, object
Nesting	❌ Flat only	✅ Recursive
Arrays	❌	✅
Storage size	Smaller (flat strings)	Larger (JSON structure overhead)
Query operators	->, ?, ?&, `?	, @>, <@`
GIN indexing	✅	✅ Better (several opclasses)
ORM support	✅ SQLAlchemy HSTORE	✅ JSONB
Ecosystem	Limited	Modern standard
Performance	Slightly faster on flat key-value	Comparable, generally fine

---

When (rarely) hstore can win

Case 1: extreme storage on flat key-value.

If you have BILLIONS of rows with flat key-value metadata (all strings), hstore saves ~10-20% storage vs JSONB.

-- Same data
attributes_hstore: 'a => "1", b => "2", c => "3"'  -- ~30 bytes
attributes_jsonb:  '{"a":"1","b":"2","c":"3"}'      -- ~30 bytes

Minimal difference in this case. It only matters at extreme scale (>1 billion rows).

Case 2: legacy app with consolidated hstore.

Your old app uses hstore. Migrating to JSONB requires code changes. If it works, don't migrate for the sake of migrating.

Case 3: documentation/tutorials that showed hstore.

Some old tutorials. If you run into hstore in tutorial code, now you know what it is.

---

When JSONB wins (almost always)

1. You need more than strings.

-- JSONB
{"price": 299.99, "in_stock": true, "tags": ["new", "sale"]}

-- hstore (not directly representable)
'price => "299.99", in_stock => "true", tags => "?"'
-- tags as an array isn't possible

2. You need nesting.

-- JSONB
{"address": {"street": "Main", "city": "NY"}}

-- hstore: impossible directly

3. You want modern tooling.

JSONB is the standard. Better support in frameworks, ORMs, monitoring tools.

4. You want compatibility with JSON APIs.

The client sends JSON, the server stores it directly. No transformations.

5. Pre-existing JSONB elsewhere in the schema.

Consistency: if you already use JSONB in other tables, keep the same type.

---

The simple rule

For new code: ALWAYS JSONB.

For legacy: keep hstore if it works, migrate ONLY if:

• You need JSONB features (nesting, arrays).
• You're doing a major refactor.
• There's a specific performance issue that JSONB solves.

Do NOT migrate from hstore to JSONB "for progress" — it's work with no benefit if the legacy works.

---

Implementation with SQLAlchemy 2.0

JSONB (recommended)

from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.orm import Mapped, mapped_column


class Product(Base):
    __tablename__ = "products"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    attributes: Mapped[dict] = mapped_column(JSONB, default=dict)

Usage:

product = Product(
    name="iPhone",
    attributes={
        "color": "black",
        "storage": "256GB",
        "price": 999.99,
        "tags": ["new"]
    }
)

hstore (legacy)

from sqlalchemy.dialects.postgresql import HSTORE


class ProductLegacy(Base):
    __tablename__ = "products_legacy"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    attributes: Mapped[dict] = mapped_column(HSTORE, default=dict)


# Usage
product = ProductLegacy(
    name="iPhone",
    attributes={"color": "black", "storage": "256GB"}
    # You CAN'T put numbers or arrays — everything to string
)

SQLAlchemy converts the Python dict to hstore. But the dict must be flat (no nesting) and all values must be strings (or None).

---

Migrating from hstore to JSONB

If you decide to migrate (rare but sometimes necessary):

# Migration
def upgrade() -> None:
    # 1. Add the new JSONB column
    op.add_column(
        'products',
        sa.Column('attributes_new', postgresql.JSONB)
    )

    # 2. Backfill: convert hstore → jsonb
    op.execute("""
        UPDATE products
        SET attributes_new = hstore_to_jsonb(attributes)
    """)

    # 3. Drop the old column, rename the new one
    op.drop_column('products', 'attributes')
    op.alter_column('products', 'attributes_new', new_column_name='attributes')

hstore_to_jsonb() is a built-in function that converts. All values end up as strings in JSONB (because hstore has no types).

For cases where you want a richer conversion (numbers from strings), a Python script:

async def migrate_hstore_to_jsonb(session):
    products = await session.scalars(select(Product))
    for product in products:
        new_attrs = {}
        for k, v in product.attributes_hstore.items():
            # Try to parse numbers
            try:
                new_attrs[k] = float(v) if "." in v else int(v)
            except ValueError:
                if v.lower() in ("true", "false"):
                    new_attrs[k] = v.lower() == "true"
                else:
                    new_attrs[k] = v
        product.attributes_jsonb = new_attrs
    await session.commit()

---

The "what to do if you find hstore" pattern

Code review on an unfamiliar project. You see:

attributes: Mapped[dict] = mapped_column(HSTORE)

Reactions depending on context:

If it works and there are no issues: leave it. Migrating to JSONB is work with no benefit.

If you need to add nesting/arrays: a case for migrating. Plan the migration carefully.

If performance is a problem: investigate first. JSONB isn't necessarily faster for flat key-value.

If there's modern tooling that fails with hstore: a case for migrating.

If it's simply "not standard": do NOT migrate for aesthetics. Resist the urge.

---

Traps and common mistakes

1. Assuming hstore is "JSONB but with different syntax".

No. hstore is flat key-value, strings only. JSONB is a full JSON document. Different capabilities.

2. Migrating from hstore to JSONB without testing.

hstore_to_jsonb() converts all values to strings. If your code expected numbers or booleans, it breaks.

3. hstore with special characters in keys.

-- Works but requires escaping
INSERT INTO ... VALUES ('"key with spaces" => "value"');

JSONB is more comfortable for complicated keys.

4. Using a GIN index without a specific opclass.

-- hstore default
CREATE INDEX ON products USING gin (attributes);

-- JSONB has options
CREATE INDEX ON products USING gin (attributes jsonb_ops);  -- default
CREATE INDEX ON products USING gin (attributes jsonb_path_ops);  -- smaller, specific queries

JSONB with jsonb_path_ops is ~50% smaller for containment queries (@>).

5. JSON vs JSONB confusion.

PostgreSQL has two types:

• JSON: text storage, validated. Slow to index/query.
• JSONB: binary storage, indexable. Almost always the right choice.

JSON rarely makes sense (when preserving the exact format matters, e.g., archiving a payload).

6. Mixing types in JSONB.

# Pydantic — type checking in Python
{"price": 999.99}    # number
{"price": "999.99"}  # string

# JSONB accepts both but queries may behave differently
WHERE attributes->>'price' > '500'  # string comparison "999" vs "500" — works
WHERE (attributes->>'price')::numeric > 500  # numeric comparison — works

Be consistent with types when writing.

7. hstore default '' vs NULL.

-- empty hstore vs NULL
attributes = ''        -- empty hstore
attributes IS NULL    -- no value

Decide and keep consistency.

---

Exercise: compare structures

Setup:

CREATE EXTENSION IF NOT EXISTS hstore;

-- hstore version
CREATE TABLE products_hstore (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    attributes HSTORE
);

-- JSONB version
CREATE TABLE products_jsonb (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    attributes JSONB
);

-- Insert the same data
INSERT INTO products_hstore (name, attributes) VALUES
    ('iPhone', 'color => "black", storage => "256GB", brand => "Apple"');

INSERT INTO products_jsonb (name, attributes) VALUES
    ('iPhone', '{"color": "black", "storage": "256GB", "brand": "Apple"}');

Step 1: compare equivalent queries.

-- hstore
SELECT * FROM products_hstore WHERE attributes -> 'brand' = 'Apple';
SELECT * FROM products_hstore WHERE attributes ? 'storage';

-- JSONB
SELECT * FROM products_jsonb WHERE attributes ->> 'brand' = 'Apple';
SELECT * FROM products_jsonb WHERE attributes ? 'storage';

Step 2: try to add nesting.

-- hstore: CAN'T
-- ?

-- JSONB: trivial
UPDATE products_jsonb
SET attributes = jsonb_set(attributes, '{specs,weight}', '"180g"')
WHERE name = 'iPhone';

Step 3: compare sizes.

SELECT pg_size_pretty(pg_relation_size('products_hstore'));
SELECT pg_size_pretty(pg_relation_size('products_jsonb'));

A significant difference for simple data? (Not usually).

Step 4: simulate an hstore → JSONB migration.

-- Convert
SELECT name, hstore_to_jsonb(attributes) FROM products_hstore;
-- Output: '{"color":"black","storage":"256GB","brand":"Apple"}' — all strings

-- If you want types, manual conversion
SELECT name, hstore_to_jsonb_loose(attributes) FROM products_hstore;
-- Similar output but with basic type detection (numbers, booleans)

See discussion

Step 1: similar queries but different operators:

• hstore: -> returns text (always).
• JSONB: -> returns jsonb, ->> returns text.

To compare against a string, JSONB requires ->>.

Step 2: hstore CAN'T do nesting. For "specs.weight" in hstore, the key would be 'specs_weight' => "180g" (flat with an underscore).

Step 3: similar sizes for simple data (~30 bytes each). A significant difference only on very large data.

Step 4: hstore_to_jsonb is literal (all strings). hstore_to_jsonb_loose tries to convert basic types.

Key takeaways:

1. hstore is a subset of JSONB capability.
2. JSONB is a reasonable default for everything new.
3. Migrating hstore → JSONB is relatively easy with hstore_to_jsonb.
4. Do NOT migrate stable legacy that works.

---

Summary and next step

What you learned:

• hstore = flat key-value, strings only. PostgreSQL 8.4+.
• JSONB = full JSON document, various types, nesting, arrays. PostgreSQL 9.4+.
• Rule: JSONB always for new code. hstore only for stable legacy.
• SQLAlchemy: JSONB and HSTORE both available.
• Migration hstore → JSONB: hstore_to_jsonb() or hstore_to_jsonb_loose().
• Do NOT migrate stable legacy without a concrete reason.

In the next capsule we go to pg_trgm advanced cases — beyond the fuzzy search of module 3. You'll learn deduplication of similar records and "did you mean" suggestions.

---

Resources

1. PostgreSQL Docs — hstore — reference.
2. PostgreSQL Docs — JSONB — reference.
3. SQLAlchemy — HSTORE type — reference.
4. SQLAlchemy — JSONB type — reference.
5. Crunchy Data — JSONB vs hstore — comparative analysis.

---

Capsule 05 of 08 — Module 7 — Advanced PostgreSQL for Backend Guide