hstore

What hstore does

hstore adds a data type to PostgreSQL that holds an unordered set of zero or more key-value pairs. Each key is unique within a value — duplicates are silently deduplicated, with the last value winning. Keys and values are both plain text strings.

The extension provides a rich set of operators for querying hstore values: extracting values by key, testing for key existence, checking containment, merging values, and converting to and from other formats (arrays, JSON, rows). It also provides GIN and GiST index support, making it possible to efficiently query hstore columns even in large tables.

Because the data model is strictly flat, hstore is simpler than JSONB. There are no nested objects, no arrays, no typed values — just string-to-string mappings. This simplicity is both its limitation and its advantage: queries are straightforward, storage is compact, and the mental model is easy to reason about. In a world that reaches for nested JSON at the first opportunity, there is something admirable about a tool that refuses to complicate matters.

When to use hstore

Allow me to be specific. hstore fits well when the data is genuinely flat key-value and you do not need the richer type system of JSONB.

• Product attributes and metadata — color, size, material, and other properties that vary across rows but are always simple string values
• EAV (Entity-Attribute-Value) replacement — instead of a separate attributes table with entity_id/key/value rows, store all attributes in a single hstore column
• Configuration and settings — user preferences, feature flags, or application config stored as key-value pairs
• Tags and labels — when tags need associated values (e.g., 'priority => high, team => backend') rather than being a simple array
• Legacy data migration — importing key-value data from systems that do not have structured schemas

I should be straightforward: if your data has nested objects, arrays, numeric values that need arithmetic, or boolean logic, use JSONB instead. For new projects where you are unsure, JSONB is the safer default — you can always simplify later, but adding nesting to hstore requires a migration. Choosing hstore for data that will inevitably grow nested is an unkindness to your future self.

Installation and setup

hstore is a contrib module that ships with PostgreSQL — no separate download required. It does not need shared_preload_libraries, so no restart is necessary. The extension is trusted, meaning non-superusers with CREATE privilege on the database can install it. One statement and it is at your service.

-- hstore ships with PostgreSQL (contrib module)
-- No shared_preload_libraries needed — just create the extension
CREATE EXTENSION hstore;

-- Verify it's working
SELECT 'name => Alice, role => admin'::hstore;

Basic operations

Creating tables with hstore columns, inserting data, and extracting values.

-- Create a table with an hstore column
CREATE TABLE products (
  id serial PRIMARY KEY,
  name text NOT NULL,
  attributes hstore
);

-- Insert key-value pairs
INSERT INTO products (name, attributes) VALUES
  ('Widget A', 'color => blue, weight => 150g, material => steel'),
  ('Widget B', 'color => red, weight => 200g, finish => matte'),
  ('Gadget C', 'color => black, battery => yes, waterproof => yes');

-- Get a single value by key
SELECT name, attributes -> 'color' AS color
FROM products;

-- Get multiple values as an array
SELECT name, attributes -> ARRAY['color', 'weight'] AS vals
FROM products;

Key operators

hstore provides operators for existence checks, containment, and merging — all of which can be accelerated with GIN or GiST indexes.

-- Check if a key exists (? operator)
SELECT name FROM products
WHERE attributes ? 'battery';

-- Check if ALL keys exist (?& operator)
SELECT name FROM products
WHERE attributes ?& ARRAY['color', 'weight'];

-- Check if ANY key exists (?| operator)
SELECT name FROM products
WHERE attributes ?| ARRAY['battery', 'waterproof'];

-- Containment: does hstore contain these pairs? (@> operator)
SELECT name FROM products
WHERE attributes @> 'color => blue';

-- Concatenate / merge two hstore values (|| operator)
SELECT 'a => 1'::hstore || 'b => 2'::hstore;
-- "a"=>"1", "b"=>"2"

Indexing

hstore supports GIN, GiST, btree, and hash indexes. GIN is the most common choice for production workloads that query by key existence or containment.

-- GIN index: best for frequent containment and existence queries
CREATE INDEX idx_products_attrs_gin ON products USING GIN (attributes);

-- Supports: @>, ?, ?&, ?|

-- GiST index: alternative with smaller size, lossy matching
CREATE INDEX idx_products_attrs_gist ON products USING GiST (attributes);

-- Supports the same operators, but uses bitmap signatures
-- (may require recheck against the heap)

-- btree index: only for = equality, allows UNIQUE / ORDER BY
CREATE INDEX idx_products_attrs_btree ON products USING BTREE (attributes);

For most applications, a GIN index on the hstore column is the right default. It accelerates the four most common hstore query patterns (@>, ?, ?&, ?|) with good write performance and compact size. GiST is worth considering when you need to combine hstore predicates with other GiST-indexable types in a multi-column index. An unindexed hstore column on a large table is, if you'll forgive me, rather like filing correspondence in an unlabelled drawer — everything is there, but finding it takes longer than it should.

Conversion and utility functions

hstore values can be converted to JSON, expanded into rows, or decomposed into separate key and value arrays.

-- Convert hstore to a set of key-value rows
SELECT (each(attributes)).key, (each(attributes)).value
FROM products
WHERE name = 'Widget A';

-- Convert hstore to JSON
SELECT name, hstore_to_json(attributes) AS attrs_json
FROM products;

-- Convert hstore to a flat array of alternating keys and values
SELECT name, akeys(attributes) AS keys, avals(attributes) AS values
FROM products;

-- Build hstore from parallel key and value arrays
SELECT hstore(ARRAY['color', 'size'], ARRAY['green', 'large']);

Modifying hstore values

Keys can be deleted individually or in batches, and subsets of keys can be extracted with slice().

-- Delete a key from an hstore value
UPDATE products
SET attributes = delete(attributes, 'finish')
WHERE name = 'Widget B';

-- Delete multiple keys
UPDATE products
SET attributes = delete(attributes, ARRAY['battery', 'waterproof'])
WHERE name = 'Gadget C';

-- Slice: extract a subset of keys
SELECT slice(attributes, ARRAY['color', 'weight'])
FROM products;

hstore vs JSONB

For flat key-value data where both sides are strings, hstore is slightly more concise in syntax and marginally more compact in storage. But JSONB is the more versatile choice and does not require an extension. In practice, many teams use JSONB exclusively for new work and encounter hstore only in existing schemas. I would not advise migrating a working hstore column to JSONB out of fashion — that is effort spent for the benefit of no one. But for new columns, JSONB is the reasonable default unless you are certain the data will stay flat.

Cloud availability

As a contrib module that ships with PostgreSQL itself, hstore is available on virtually every managed PostgreSQL service. No custom builds or third-party packages are needed.

How Gold Lapel relates

Gold Lapel sees hstore columns the same way it sees any other queryable type — no special configuration, no additional setup. When I observe queries filtering on hstore values with containment, existence, or key extraction operators, those patterns enter the workload analysis alongside everything else. If an appropriate GIN or GiST index is missing, I will note that in my recommendations.

I should be clear that there is nothing hstore-specific about this. hstore columns appear in query plans like any other indexed type, and the proxy handles them without ceremony. Which, I find, is exactly the sort of treatment a reliable veteran deserves.