istore

The idea of istore is to have an integer based hstore (thus the name) which supports operators like + and aggregates like SUM.

The textual representation of an istore is the same as for hstore e.g.

"-5"=>"-25", "0"=>"10", "5"=>"0", "100"=>"30"

On istore both keys and values are represented and stored as integer. The extension comes with two types istore and bigistore the former having int and the latter bigint as values, keys are int for both.

The use case an optimization for istore is an analytical workload. Think of it as showing distributions of whatever can be represented as integer.

Example

Say you have an event log table where you'd aggregate events with some id and segmentation by date.

CREATE TABLE event_log AS
SELECT
  d::date as date,
  j as segment,
  i as id, (random()*1000)::int as count,
  (random()*100000)::int as revenues
FROM
  generate_series(1,50) i,
  generate_series(1,1000) j,
  generate_series(current_date - 99, current_date, '1 day') d;

Using istore you would use the id as key and count / revenues as values.

CREATE TABLE istore_event_log AS
SELECT
  date,
  segment,
  istore(array_agg(id), array_agg(count)) as counts,
  istore(array_agg(id), array_agg(revenues)) as revenues
FROM event_log
GROUP BY date, segment;

To summarize the count for a specific id you would write

istore_test=# SELECT SUM(counts->35) from istore_event_log ;
    sum
----------
  50213687
(1 row)

Time: 29,032 ms

instead of

istore_test=# SELECT SUM(count) from event_log where id = 35;
    sum
----------
  50213687
(1 row)

Time: 374,806 ms

Where you can already see the performance benefit.

Which is mostly due to the reduced IO.

SELECT pg_size_pretty(pg_table_size('event_log')) as "without istore", pg_size_pretty(pg_table_size('istore_event_log')) as "with istore";
  without istore | with istore
----------------+-------------
  249 MB         | 87 MB

The following functions and operators apply to both istore and bigistore types.

istore Operators

istore Functions

istore Aggregate Functions

Indexes

istore has GIN index support for the ? operators For example:

CREATE INDEX hidx ON testistore USING GIN (i);

Authors

Alex Kliukin alex.kliukin@adjust.com, Berlin, adjust GmbH, Germany

Ildar Musin ildar@adjust.com, Berlin, adjust GmbH, Germany

Manuel Kniep manuel@adjust.com, Berlin, adjust GmbH, Germany

Robert Abraham robert@adust.com, Berlin, adjust GmbH, Germany

GIN index support by Emre Hasegeli emre@hasegeli.com, Hamburg, Germany