PostgreSQL hstore: Key-Value Storage

Tip: Enable hstore

CREATE EXTENSION hstore;

Gotcha: hstore vs JSONB

hstore only supports string key-value pairs. JSONB supports nested structures and numbers. Prefer JSONB for new projects.

Tip: Query hstore

SELECT * FROM products WHERE attributes -> 'color' = 'red';

Gotcha: hstore Indexing

CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

GIN index makes hstore queries fast.

Tip: Update hstore

UPDATE products SET attributes = attributes || '"weight" => "2.5"'::hstore;

Adds or updates a key.

Gotcha: Delete from hstore

UPDATE products SET attributes = delete(attributes, 'color');

Removes a key from the hstore.

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

hstore is PostgreSQL's key-value store within a single column. I've used it for flexible attributes in legacy systems where schema changes were expensive. But hstore has limitations: all values are strings, there's no nesting, and the index size can be large. For new projects, JSONB is strictly better — it supports nesting, type differentiation, and more index types. I only recommend hstore for maintaining compatibility with pre-9.4 PostgreSQL installations.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)