Published at Mar 6, 2024
Database Schemahstore 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.
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.
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';
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.
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');
To learn more about Timescale and hypertables, check out our getting started guide.