istore

Overview

PGXN 0.1.12 source requires the PG16+ varatt/Datum compatibility patch. PostgreSQL 14-18 verified across all 16 PGSTY target platforms. Does not require hstore.

Version

Build

You can build the RPM / DEB packages for istore using pig build:

pig build pkg istore         # build RPM / DEB packages

Install

You can install istore directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install istore;          # Install for current active PG version

pig ext install -y istore -v 18  # PG 18
pig ext install -y istore -v 17  # PG 17
pig ext install -y istore -v 16  # PG 16
pig ext install -y istore -v 15  # PG 15
pig ext install -y istore -v 14  # PG 14

dnf install -y istore_18       # PG 18
dnf install -y istore_17       # PG 17
dnf install -y istore_16       # PG 16
dnf install -y istore_15       # PG 15
dnf install -y istore_14       # PG 14

apt install -y postgresql-18-istore   # PG 18
apt install -y postgresql-17-istore   # PG 17
apt install -y postgresql-16-istore   # PG 16
apt install -y postgresql-15-istore   # PG 15
apt install -y postgresql-14-istore   # PG 14

Create Extension:

CREATE EXTENSION istore;

Usage

Sources:

• Official README
• Official extension SQL
• Official extension control file

istore provides compact integer-key maps for PostgreSQL. The istore type stores integer values, while bigistore stores bigint values; both are useful for sparse counters, distributions, and analytical aggregates keyed by integer identifiers.

Core Workflow

Text input resembles hstore, and array constructors can aggregate repeated keys:

CREATE EXTENSION istore;

SELECT istore(ARRAY[1, 2, 1, 3, 2, 2]);
SELECT '1=>4,2=>5'::istore -> 1;
SELECT '1=>4,2=>5'::istore + '1=>4,3=>6'::istore;

CREATE INDEX metrics_keys_idx
  ON metrics USING gin (values istore_key_ops);

Key lookup uses ->; ?, ?&, and ?| test key presence. || concatenates stores, while arithmetic operators combine matching keys. The istore_key_ops GIN operator class accelerates key-existence predicates.

Important Objects and Caveats

Constructors and transforms include istore(...), bigistore(...), akeys, avals, each, slice, compact, fill_gaps, accumulate, clamp, and delete helpers. sum_up summarizes values, and aggregate SUM returns bigistore so that totals have a wider value type. Per-key minimum and maximum aggregates are also provided.

The cast from istore to bigistore is implicit; narrowing assignments can overflow. Arithmetic and division still require application-level checks for overflow and zero divisors. Large values can be TOASTed, and GIN indexes add write and maintenance cost, so benchmark the actual distribution and update workload instead of treating upstream examples as performance guarantees.