HStore vs JSONB¶

PostgreSQL offers two flexible data types for key-value and document storage. This page compares them to help you choose the right one.

Quick Decision¶

Use HStore when your data is flat key-value pairs, which is most metadata, tags, and product attributes. Use JSONB when you need nested structures, mixed value types, or containment queries.

Feature Matrix¶

Feature

Feature	HStore	JSONB
Flat key-value	Yes	Yes
Nested structs	No	Yes
Non-string vals	No	Yes
Simple admin UI	Yes	No
Easy validation	Yes	No
Containment	No	Yes
Smaller storage	Yes	No
GIN indexes	Yes	Yes

Storage¶

HStore is more compact because it stores flat key-value pairs without structural overhead:

टिप्पणी

HStore: ~200 bytes for 10 key-value pairs
JSONB: ~350 bytes for the same data (structure overhead)

Query Performance¶

HStore¶

# Find all red products
Product.objects.filter(attributes__color='red')

# Products with a size attribute
Product.objects.filter(attributes__has_key='size')

-- Check if a key exists
SELECT * FROM products WHERE attributes ? 'size';

-- Get a specific value
SELECT attributes->'color' FROM products;

-- Query by key-value pair
SELECT * FROM products WHERE attributes->'color' = 'red';

JSONB¶

# Nested value access
Config.objects.filter(settings__cache__ttl__gt=300)

# Containment query
Config.objects.filter(settings__contains={"enabled": True})

-- Nested value access
SELECT * FROM configs WHERE settings->'cache'->>'ttl' > 300;

-- Containment query
SELECT * FROM configs WHERE settings @> '{"enabled": true}';

Indexing¶

Both HStore and JSONB support GIN indexes for fast lookups:

CREATE INDEX products_attrs_gin ON products USING GIN (attributes);
CREATE INDEX configs_settings_gin ON configs USING GIN (settings);

Summary¶

सलाह

Choose HStore for simple metadata, tags, and product attributes. Choose JSONB for nested configurations, API responses, and document storage.