PostgreSQL is well known for its relational features, but it also offers powerful tools for working with key-value pairs. If you want to store flexible, schema-less data in a Postgres column, you can use either the hstore extension or the built-in JSONB type.

Steps

Install and enable hstore

To create columns with the hstore type, you need to install the extension. In Neon, hstore is already installed, you just need to enable it with the following command.

CREATE EXTENSION IF NOT EXISTS hstore;

Create a table with an hstore column

You can store key-value pairs in a column using the hstore type. Here’s an example of a products table with an attributes column of type hstore.

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  attributes HSTORE
);

Insert and query key-value data with hstore

Each row in the attributes column can store a map of keys and values as strings as follows. Keep in mind that hstore can only store string keys and string values: you can't store numbers or objects as hstore values.

INSERT INTO products (name, attributes)
VALUES ('Backpack', 'color => blue, size => large'),
       ('Jacket', 'color => red, waterproof => yes');

You can then query based on the attributes row's keys and values using operators like -> and ? as follows.

-- Get products with a 'color' key
SELECT * FROM products WHERE attributes ? 'color';

-- Get products where color is 'blue'
SELECT * FROM products WHERE attributes -> 'color' = 'blue';

Using JSONB

PostgreSQL’s JSONB type stores structured JSON data in a binary format. It supports indexing and nesting, making it great for more complex data structures.

Create a table with a JSONB column

The following command creates a similar products table where attributes has type JSONB instead of hstore.

CREATE TABLE products_json (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  attributes JSONB
);

Insert and query key-value data with JSONB

You can then insert products with attributes using JSON syntax as follows.

INSERT INTO products_json (name, attributes)
VALUES ('Backpack', '{"color": "blue", "size": "large"}'),
       ('Jacket', '{"color": "red", "waterproof": true}');

You can then query the attributes row by keys and values using operators like ?, @>, and ->>.

-- Get products with a 'waterproof' key
SELECT * FROM products_json WHERE attributes ? 'waterproof';

-- Get products where color is 'blue'
SELECT * FROM products_json
WHERE attributes @> '{"color": "blue"}';

-- Get products where color is 'blue' using `->>`
SELECT * FROM products_json
WHERE attributes->>'color' = 'blue';

Index key-value data for performance

GIN indexes allow you to index hstore and JSONB properties, which can make your queries faster as your data grows. The following command shows how you can create a GIN index on the attributes property for both tables - GIN indexes work on both hstore and JSONB rows.

-- hstore
CREATE INDEX idx_hstore_attrs ON products USING GIN (attributes);

-- JSONB
CREATE INDEX idx_jsonb_attrs ON products_json USING GIN (attributes);

hstore vs JSONB

Both hstore and JSONB offer an alternative to external key-value stores and enable you to keep your key-value data in the same place as the rest of your data. Whether you choose hstore or JSONB depends on your needs:

  • If you need nested data, arrays, or any data type beyond strings, use JSONB.
  • If you need extra performance and are certain you don't need more complex data types, hstore can be faster for very simple key-value lookups and use less disk space.