Hstore

The hstore extension provides a data type for storing sets of key-value pairs within a single PostgreSQL value. This can be particularly useful when dealing with semi-structured data or when you need to store attributes that don’t warrant their own columns. Your Nile database arrives with hstore extension already enabled.

Creating Tables with hstore

Here’s how to create a table that includes an hstore column:

CREATE TABLE products (
    id int,
    tenant_id uuid NOT NULL,
    name text,
    attributes hstore,
    PRIMARY KEY(tenant_id, id)
);

Inserting Data

First, create a tenant:

INSERT INTO tenants (id, name)
VALUES ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 'Example Store');

Then you can insert products for this tenant:

-- Using the => operator
INSERT INTO products (tenant_id, id, name, attributes)
VALUES ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 1, 'Laptop', 'color=>silver, RAM=>16GB, storage=>512GB');

-- Using the hstore function
INSERT INTO products (tenant_id, id, name, attributes)
VALUES ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 2, 'Phone', hstore(ARRAY['color', 'black', 'storage', '256GB']));

Querying hstore Data

The hstore data type comes with several useful operators and functions:

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

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

-- Check if a key/value pair exists
SELECT * FROM products WHERE attributes @> 'color=>silver';

-- Get all keys
SELECT akeys(attributes) FROM products;

-- Get all values
SELECT avals(attributes) FROM products;

-- Get key/value pairs as a set
SELECT skeys(attributes), svals(attributes) FROM products;

Updating hstore Values

You can update individual key/value pairs or the entire hstore. We use SET nile.tenant_id to guarantee the operation is performed on the correct tenant:

SET nile.tenant_id = 'd1c06023-3421-4fbb-9dd1-c96e42d2fd02';
-- Update a single key/value pair
UPDATE products
SET attributes = attributes || 'RAM=>32GB'::hstore
WHERE id = 1;

-- Delete a key
UPDATE products
SET attributes = delete(attributes, 'storage')
WHERE id = 1;

Combining hstore Values

You can combine multiple hstore values using the concatenation operator (||):

SELECT 'a=>1, b=>2'::hstore || 'c=>3'::hstore;

Converting To/From JSON

hstore can be converted to and from JSON:

-- Convert hstore to JSON
SELECT hstore_to_json(attributes) FROM products;

-- Convert JSON to hstore using the array syntax
SELECT hstore(ARRAY['color', 'red', 'size', 'large']);

Best Practices

Use hstore when dealing with dynamic attributes that don’t require strict schema validation.
Consider using JSON/JSONB instead if you need to store nested structures or arrays.
Create indexes on frequently queried keys using GiST or GIN indexes:

CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

Performance Considerations

hstore is generally more efficient than JSON for simple key-value pairs
GIN indexes can significantly improve query performance on hstore columns
The storage size of hstore is typically smaller than equivalent JSON storage

Limitations

Keys and values must be text strings
No support for nested structures
No array support within values
Maximum size is limited by the maximum TOAST size in PostgreSQL

For more details, refer to the hstore documentation.

Get Started

Tenant Virtualization

Postgres Concepts

Extensions

Creating Tables with hstore

Inserting Data

Querying hstore Data

Updating hstore Values

Combining hstore Values

Converting To/From JSON

Best Practices

Performance Considerations

Limitations

​Creating Tables with hstore

​Inserting Data

​Querying hstore Data

​Updating hstore Values

​Combining hstore Values

​Converting To/From JSON

​Best Practices

​Performance Considerations

​Limitations

Creating Tables with hstore

Inserting Data

Querying hstore Data

Updating hstore Values

Combining hstore Values

Converting To/From JSON

Best Practices

Performance Considerations

Limitations