Fast Key-Value Store With PostgreSQL

Explore Key-Value store features which augments the already fantastic NoSQL tool set that PostgreSQL offers.

12 days ago   •   5 min read

By Zach Naimon
Table of contents

Introduction to Key-Value Store

We have seen several features in PostgreSQL that goes beyond relational storage, such as:

  • JSON  
Forget SQL vs NoSQL - Get the Best of Both Worlds with JSON in PostgreSQL
Create a database schema for any situation with the power of JSON.
  • Geospatial data  
Working with geospatial data in Postgres.
PostgreSQL has several extensions so spatial and geometry data can be treated as first-class objects within your PostgreSQL database.
  • Fuzzy Search
A Handbook to Implement Fuzzy Search in PostgreSQL
Learn about PostgreSQL fuzzy search methods like trigrams, Levenshtein matching, phonetic matching, and more.
  • Full-Text Search
Probing Text Data Using PostgreSQL Full-Text Search
Learn how to leverage the power of full-text search in PostgreSQL to search through large text content in a fast and accurate way.

This article will explore the key-value store feature, which is less popular, but still plays an integral part in PostgreSQL's NoSQL capabilities.

Setting Up PostgreSQL with Extension and Data

Unlike JSON and JSONB, the key-value store comes as a separate extension called HStore. We can install it right away as it comes bundled with PostgreSQL installation itself.

Enabling HStore

CREATE EXTENSION HSTORE

This will enable the key-value store extension.

Note: Extension can be enabled only with superuser access. The system will throw an error otherwise.

Creating Table and HStore Columns

Let's create a simple demo table where we maintain scores of people in key-value fashion. To do that, we need to create the data type itself as hstore.

CREATE TABLE hstore_example (score hstore)

We do not need to specify the key-value structure type (i.e., whether the key is an int or text, as the default type will always be text). Proper datacasting is necessary either at the database level or application level for manipulation. This is similar to the JSON data type, where all of the data is just JSONB.  

Populating Test Data

Inserting data into hstore columns is pretty straightforward.

INSERT INTO hstore_example values('"Jason" => 100');
INSERT INTO hstore_example values('"Jack" => 200');
INSERT INTO hstore_example values('"Perry" => 150');
Select HStore Data
Select HStore Data

Nested data insertion is also possible. But for this example, we are inserting only one level of nesting.

Getting Started with Key-Value Queries

We can query for rows by searching for keys.

SELECT
    *
FROM
    hstore_example
WHERE
    score ? 'Jason';

Get a value for a particular key,

SELECT
    score -> 'Jason' as score
FROM
    hstore_example
WHERE
    score -> 'Jason' is NOT NULL 
Selecting Value From Key
Selecting Value From Key

An exhaustive list of operations can be found in the official PostgreSQL documentation for HStore -

F.16. hstore
F.16. hstore F.16.1. hstore External Representation F.16.2. hstore Operators and Functions F.16.3. Indexes F.16.4. Examples F.16.5. Statistics F.16.6. Compatibility F.16.7. Transforms …

There are several powerful operators present that can help us do a variety of data manipulations without having to handle them in application logic. We should always do data manipulation at the database level rather than at the application level for a variety of reasons such as performance, security, etc.,

Indexing for Faster Queries

The HStore extension supports indexes of the GIN type. We can create these indexes as follows,

CREATE INDEX hstore_example_idx ON hstore_example USING GIN (score)

This is similar in speed and power to the GIN index we can create for the JSON type. It can significantly speed up queries, particularly if the keys are nested and searching is not straightforward with normal comparisons. These indexes are considerably smaller than a JSON GIN and can efficiently operate from the memory/cache for quicker lookups.

On the other hand, an index will slow down the writes, so a proper tradeoff has to be considered before we extensively use an index. Benchmarking should be done before taking an application/solution to production.

Internal Structure of HStore

HStore is internally stored as a varlena. Similar to JSON, the whole field has to be read from the disk to do any read/modifications. As a result, HStore is not really optimized compared to traditional key-value stores such as Redis and Memcache.

The documentation about TOAST table structure is also highly recommended on why the reads/writes can only be done as a whole field rather than partial updates,

70.2. TOAST
70.2. TOAST 70.2.1. Out-of-Line, On-Disk TOAST Storage 70.2.2. Out-of-Line, In-Memory TOAST Storage This section provides an overview of TOAST (The Oversized-Attribute …

For this reason, if the K/V structure is complex and nested, we are better off storing it as a proper JSON or JSONB type.

Conclusion

Potential use case scenarios can be similar but not limited to the below examples,

  • Maintaining a cache.
  • Fast read/update scenarios such as maintaining an API rate limiter.
  • Maintaining scores/simple time-series data.

HStore is not a replacement for Key-Value stores; they are much more optimized for different use cases. But as already mentioned, these features exist in PostgreSQL because there are many situations where using a separate data store would be an overkill and a maintenance issue. We can use PostgreSQL's NoSQL capabilities as a bridge to fill that gap. If the business expands, we can migrate to a different database. Still, until then, we can comfortably use PostgreSQL, which provides ACID-compliant NoSQL features without the additional overhead of maintaining a separate database.

JOIN the Arctype Newsletter
Programming stories, tutorials, and database tips every 2 weeks

Spread the word

Keep reading