Database Schema

PostgreSQL Extensions: hstore

​hstore is a powerful and flexible PostgreSQL extension that allows developers to use the database as a schema-less NoSQL store while still retaining the benefits of a traditional relational database.

It provides the ability to store key-value pairs in a single PostgreSQL value. This is particularly useful for storing semi-structured data and datasets where the number of attributes (keys) can vary from record to record.

​Installing the hstore Extension

​Before you can use the hstore extension, you need to install it. Here's how:

1. First, connect to the PostgreSQL database where you want to install the hstore extension. You can do this using the psql command-line client or a graphical client like pgAdmin. On Timescale, you can find available extensions by going to Operations > Extensions from your service overview, which will also give you installation instructions.​

2. Once connected, run the following SQL command:

CREATE EXTENSION IF NOT EXISTS hstore;​

This command will install the hstore extension if it's not already installed.

Using the hstore Extension

To use the hstore extension, you need to create a column of type hstore in your table. Here's an example:

CREATE TABLE products ( id serial PRIMARY KEY, attributes hstore ); In this example, the attributes column can store any number of key-value pairs. You can insert data into the attributes column like this:

INSERT INTO products (attributes) VALUES ('"color"=>"blue", "size"=>"XL"'); You can also query the data in an hstore column using various operators and functions provided by the hstore extension. For example, you can find all products that have a "color" key like this:

SELECT * FROM products WHERE attributes ? 'color';

​Time-Series Use Cases for hstore​

The hstore extension is particularly useful for time-series data where the structure of the data can change over time. For example, you might be tracking various metrics for a set of machines, and the set of metrics can change as you add or remove sensors from the machines.​

With hstore, you can easily add or remove attributes without having to change the schema of your database. This makes it a great fit for time-series data.

​Using hstore with Timescale and time-series data

​Timescale is a time-series database built on top of PostgreSQL. It provides advanced features for handling time-series data, like automatic partitioning and aggregation.​

You can use the hstore extension with Timescale to store time-series data with a flexible schema. Just like with a regular PostgreSQL database, you can create a table with an hstore column and insert your time-series data into it.

Here's an example:

CREATE TABLE machine_metrics ( time TIMESTAMPTZ NOT NULL, machine_id int NOT NULL, metrics hstore, PRIMARY KEY(time, machine_id) );

In this example, the metrics column can store any number of key-value pairs, allowing you to easily add or remove metrics as needed.

​hstore also works on hypertables, which allows you to get all of the functionality of TimescaleDB with the flexibility of hstore. For example, you can turn machine_metrics above into a hypertable by running:

SELECT create_hypertable('machine_metrics','time'); ​

Learn More

To learn more about Timescale and hypertables, check out our getting started guide.