Before jsonb existed, PostgreSQL developers who needed to store flexible, schema-less key-value data inside a single column reached for hstore. It’s an older extension, and these days jsonb has taken over as the default choice for most new schema-less data needs — but hstore is still actively maintained, genuinely fast, and remains a solid, lightweight option for one specific, common case: simple flat key-value pairs where both keys and values are text. This article covers how hstore works, its syntax and functions, indexing, and an honest comparison with jsonb so you know exactly when it’s still the right call.
What Is HSTORE?
hstore is a PostgreSQL extension type that stores a set of key-value pairs within a single column, where both keys and values are text (values can also be NULL, but keys cannot). It’s flat by design — no nested objects, no arrays, no data types other than text. That simplicity is exactly why it’s fast and compact for the specific use case it targets.
Enabling the Extension
Unlike jsonb, which is built into PostgreSQL core, hstore is a contrib extension that needs to be enabled per-database:
CREATE EXTENSION IF NOT EXISTS hstore;
Once enabled, the hstore type is available for use in that database.
Basic Syntax
CREATE TABLE products (
product_id serial PRIMARY KEY,
product_name text NOT NULL,
attributes hstore
);
INSERT INTO products (product_name, attributes)
VALUES ('T-Shirt', 'color=>blue, size=>medium, material=>cotton');
The key=>value literal syntax, comma-separated, is the classic hstore literal format. You can also build values using the hstore() function:
INSERT INTO products (product_name, attributes)
VALUES ('Hoodie', hstore(ARRAY['color', 'size'], ARRAY['black', 'large']));
Or build from two matching arrays directly:
SELECT hstore(ARRAY['a','b'], ARRAY['1','2']);
-- "a"=>"1", "b"=>"2"
Accessing Values
The -> operator retrieves the value for a given key:
SELECT attributes -> 'color' FROM products WHERE product_id = 1;
-- blue
If the key doesn’t exist, you get NULL rather than an error:
SELECT attributes -> 'weight' FROM products WHERE product_id = 1;
-- NULL
Checking Key Existence
SELECT attributes ? 'color' FROM products WHERE product_id = 1;
-- true
SELECT attributes ?& ARRAY['color', 'size'] FROM products WHERE product_id = 1;
-- true, if ALL listed keys exist
SELECT attributes ?| ARRAY['weight', 'color'] FROM products WHERE product_id = 1;
-- true, if ANY listed key exists
Updating HSTORE Values
Adding or updating keys with the concatenation operator:
UPDATE products
SET attributes = attributes || 'weight=>medium'::hstore
WHERE product_id = 1;
Removing a key:
UPDATE products
SET attributes = delete(attributes, 'size')
WHERE product_id = 1;
-- or using the operator form
UPDATE products
SET attributes = attributes - 'size'
WHERE product_id = 1;
Removing multiple keys at once:
UPDATE products
SET attributes = attributes - ARRAY['size', 'weight']
WHERE product_id = 1;
Containment Queries
Just like jsonb, hstore supports containment operators, which are genuinely the most useful part of the type for real querying:
SELECT product_name
FROM products
WHERE attributes @> 'color=>blue';
-- returns rows where attributes contains this exact key-value pair
SELECT product_name
FROM products
WHERE attributes <@ 'color=>blue, size=>medium, material=>cotton, weight=>medium'::hstore;
-- returns rows whose attributes are entirely contained within this set
Converting HSTORE to Other Formats
Getting all keys or all values:
SELECT akeys(attributes) FROM products WHERE product_id = 1;
-- {color,size,material}
SELECT avals(attributes) FROM products WHERE product_id = 1;
-- {blue,medium,cotton}
Expanding into rows:
SELECT * FROM each('color=>blue, size=>medium'::hstore);
-- returns key/value pairs as separate rows: (color, blue), (size, medium)
Converting to and from jsonb:
SELECT hstore_to_jsonb('color=>blue, size=>medium'::hstore);
-- {"color": "blue", "size": "medium"}
SELECT hstore('{"color":"blue","size":"medium"}'::jsonb);
This bidirectional conversion is genuinely useful if you’re migrating an existing hstore-based schema toward jsonb gradually, or interoperating between systems that use different formats.
Indexing HSTORE Columns
hstore supports GiST and GIN indexes, giving you efficient lookups for containment and existence queries:
CREATE INDEX idx_products_attributes ON products USING gin (attributes);
With a GIN index in place, both @> containment queries and ? key-existence queries can be served efficiently, rather than scanning every row and evaluating the key-value pairs one at a time.
For a specific single key you query very frequently, a targeted expression index can outperform a general GIN index for that specific access pattern:
CREATE INDEX idx_products_color ON products ((attributes -> 'color'));
HSTORE vs. JSONB: The Honest Comparison
This is the question everyone actually needs answered, since both types solve overlapping problems.
Reasons to use hstore:
- Your data is genuinely flat — simple key-value pairs, no nesting, no arrays, no numbers/booleans as distinct types (everything is text or null).
- You’re working in a codebase that already uses
hstoreextensively and there’s no compelling reason to migrate. - You want a marginally simpler, slightly more compact representation for the specific flat-key-value case, without paying for JSON’s more general (and therefore slightly heavier) structure.
- Some
hstore-specific functions (likeeach(),akeys(),avals()) offer a genuinely convenient, purpose-built API for flat key-value manipulation that feels a bit more natural than the equivalentjsonbfunctions for this narrow case.
Reasons to use jsonb instead (the more common choice for new projects):
- You need nested structures — objects within objects, arrays of objects, mixed data types (numbers, booleans, nested arrays).
- You want broader tooling and library support — virtually every application framework and driver has first-class JSON support;
hstoresupport is comparatively less universal. - You’re storing data that might naturally evolve to need more structure later — starting with
jsonbavoids a migration if that happens. jsonbis part of PostgreSQL core;hstoreis a contrib extension that must be explicitly enabled, which matters in restricted or managed environments where extension installation might require additional permissions or approval.- Native JSON path querying (
jsonb_path_query, the@?and@@operators withjsonpath) gives you more expressive query capability thanhstoreoffers for anything beyond flat key lookups.
The honest, practical takeaway: for genuinely new schema-less data needs today, jsonb is the more broadly useful default. hstore remains a perfectly solid, still-maintained choice specifically for the narrow, flat, text-only key-value case — and if that’s precisely your use case, it’s not a mistake to reach for it; it’s just no longer the default recommendation it once was.
Practical Use Cases
1. Simple Product or Entity Attributes
SELECT product_name FROM products WHERE attributes @> 'material=>cotton';
Product variants, configuration flags, or simple metadata where every value is naturally a string and there’s no nesting need.
2. Session or Request Metadata
CREATE TABLE sessions (
session_id uuid PRIMARY KEY,
metadata hstore
);
3. Legacy Systems Already Using HSTORE
If you’re maintaining an existing application already built around hstore, there’s often little practical benefit to migrating purely for its own sake — the type is stable, well-supported, and not going away.
4. Simple Tag-Like Key-Value Pairs in Analytics or Logging
Where you want fast containment queries on flat metadata without the overhead of a fully general nested document format.
Troubleshooting Common Issues
“Type hstore does not exist” errors. You haven’t run CREATE EXTENSION hstore; in the current database yet. Extensions are per-database, so this needs to be done in every database (and often every schema search-path context) where you use the type.
Unexpected NULL results from ->. This is expected behavior for missing keys, not an error — always account for NULL in application logic reading hstore values, the same way you would for any nullable expression.
Confusing hstore literal syntax with JSON syntax. The key=>value format is specific to hstore and is easy to typo if you’re used to writing JSON regularly. Double-check you’re not accidentally writing "key":"value" JSON-style syntax into an hstore literal.
Slow containment queries without an index. As with jsonb, containment (@>) and existence (?) operators need a GIN (or GiST) index to perform well at any real scale — verify with EXPLAIN ANALYZE.
Data type coercion surprises. Every value in hstore is text, even if it looks numeric. attributes -> 'quantity' returns the text '5', not the integer 5 — you’ll need an explicit cast ((attributes -> 'quantity')::integer) if you need to do numeric comparisons or arithmetic.
Best Practices
- Reach for
hstorespecifically when your data is genuinely flat, text-only key-value pairs — don’t force nested data into it. - Use
jsonbas your default for new schema-less data needs unless you have a specific reason to preferhstore‘s simplicity. - Always index with GIN if you’re doing containment or key-existence queries against a table of meaningful size.
- Remember every value is text — cast explicitly when you need numeric or boolean semantics.
- If migrating away from
hstore, usehstore_to_jsonb()to convert existing data cleanly rather than reparsing it manually.
Using HSTORE Inside Functions
Like most types, hstore becomes considerably more useful once you’re building reusable logic around it rather than writing one-off queries. Here’s a function that merges a partial update into an existing hstore value while explicitly protecting a set of keys from being overwritten — a genuinely common real-world need when handling partial updates from user input:
CREATE OR REPLACE FUNCTION safe_merge_attributes(
p_existing hstore,
p_updates hstore,
p_protected_keys text[]
)
RETURNS hstore AS $$
DECLARE
filtered_updates hstore;
BEGIN
filtered_updates := p_updates - p_protected_keys;
RETURN p_existing || filtered_updates;
END;
$$ LANGUAGE plpgsql;
SELECT safe_merge_attributes(
'color=>blue, size=>medium, sku=>ABC123'::hstore,
'color=>red, sku=>ZZZ999'::hstore,
ARRAY['sku']
);
-- "color"=>"red", "size"=>"medium", "sku"=>"ABC123"
Notice that sku retained its original value even though the update tried to change it, because it was in the protected key list — a pattern genuinely useful for guarding system-managed fields against accidental overwrite from user-submitted partial updates.
Full-Text-Search-Style Queries Over HSTORE
While hstore doesn’t have the richer path-based querying of jsonb, it does support a reasonably capable set of operators for matching against multiple keys and values at once, which covers a surprising amount of practical filtering need:
-- Find rows where 'color' is one of several acceptable values
SELECT product_name FROM products
WHERE attributes -> 'color' = ANY(ARRAY['blue', 'black', 'navy']);
-- Find rows matching several key-value pairs simultaneously (all must match)
SELECT product_name FROM products
WHERE attributes @> hstore(ARRAY['color','material'], ARRAY['blue','cotton']);
Combined with a GIN index on the attributes column, both of these query patterns scale well against larger tables without needing to fall back to sequential scans.
Populating HSTORE from Query Results
Building an hstore value dynamically from a row’s columns is a common pattern when you want to snapshot a flexible set of fields into a single auditable column — for example, capturing “what changed” in a trigger-based audit log:
CREATE OR REPLACE FUNCTION audit_changes()
RETURNS trigger AS $$
BEGIN
INSERT INTO audit_log (table_name, row_id, old_values, new_values, changed_at)
VALUES (
TG_TABLE_NAME,
NEW.id,
hstore(OLD.*),
hstore(NEW.*),
now()
);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
The hstore(record) form used here — passing an entire row into the hstore() function — automatically converts every column of that row into a key-value pair, which is a genuinely convenient shortcut for building lightweight audit trails without needing to enumerate every column by name in the trigger function, and without needing to update the trigger every time a column is added to the audited table.
CREATE TABLE audit_log (
log_id serial PRIMARY KEY,
table_name text,
row_id integer,
old_values hstore,
new_values hstore,
changed_at timestamptz
);
-- Later, see exactly which fields changed between old and new
SELECT (each(new_values - old_values)).*
FROM audit_log
WHERE log_id = 1;
That last query — subtracting the old hstore from the new one — is a neat trick worth remembering: hstore subtraction (-) between two hstore values removes matching key-value pairs, so what’s left after new_values - old_values is exactly the set of key-value pairs that actually changed, which is genuinely useful for building human-readable change summaries without writing custom diff logic.
Populating a Table from HSTORE for Analysis
If you need to run standard aggregate analysis over values that are locked inside hstore columns, expanding the data into a normal relational shape first (even temporarily, in a CTE) makes ordinary SQL tools available again:
WITH expanded AS (
SELECT product_id, (each(attributes)).key AS attr_key, (each(attributes)).value AS attr_value
FROM products
)
SELECT attr_key, count(DISTINCT attr_value) AS distinct_values
FROM expanded
GROUP BY attr_key
ORDER BY distinct_values DESC;
This kind of “which attribute keys exist and how varied are their values” query is a genuinely useful first step when you’re inheriting a schema with a loosely-defined hstore column and need to understand what’s actually being stored in it before deciding whether some of those keys deserve to be promoted into first-class typed columns.
Migrating from HSTORE to Real Columns
When analysis like the query above reveals that a particular key inside an hstore column is present on effectively every row and always holds the same kind of value, that’s usually a sign it should graduate into its own properly typed column rather than staying inside the flexible key-value blob:
ALTER TABLE products ADD COLUMN color text;
UPDATE products SET color = attributes -> 'color' WHERE attributes ? 'color';
UPDATE products SET attributes = attributes - 'color';
This incremental promotion pattern — start flexible with hstore, then extract fields into real columns once their shape stabilizes and proves to be universal — is a genuinely practical way to let a schema evolve without committing to full structure before you actually understand your data’s real shape.
Wrapping Up
hstore occupies a smaller niche today than it once did, now that jsonb handles both flat and nested schema-less data natively within PostgreSQL core. But for the specific, common case of simple flat key-value metadata — product attributes, session data, tags — it remains a genuinely solid, fast, well-indexed option, and it’s not a legacy mistake to keep using it where it fits. Know the trade-off clearly: hstore for simple flat text pairs, jsonb for anything with real structure or a need for broader tooling support, and you’ll pick the right one without second-guessing it later.