Test Script for Indexing and Query Performance of Simple PostgreSQL event storage

event_store_test.sql

/************************************************************************************
* Inserts 2-million events with some randomized data for fields that will typically
* be used for searching.
*
* Run from the command line with:
*
*   PGOPTIONS='--client-min-messages=warning' psql -X -q -v ON_ERROR_STOP=1 --pset pager=off \
*     -f event_store_test.sql | less
*
************************************************************************************/

\echo 'Inserting event data. This may take quite some time...'

\o /dev/null

drop database if exists event_store_perf_test;

create database event_store_perf_test;

\c event_store_perf_test

create extension pgcrypto;

drop table if exists test_orgs;

create temporary table test_orgs (id) as
  select gen_random_uuid() from generate_series(1,10) as id;

drop table if exists test_aggregates;

create temporary table test_aggregates as
  select
    (array['User', 'UserAssignment', 'Progress'])[floor(random() * 3 + 1)] as type,
    gen_random_uuid() as id,
    test_orgs.id as org_id
  from test_orgs, generate_series(1,1000);

drop table if exists test_event_types;

create temporary table test_event_types as
  select md5(random()::text) as name from generate_series(1,20);

drop table if exists events;

create table events (
  id bigserial primary key,
  organization_id uuid not null,
  stream_id text not null,
  event_type text not null,
  event_timestamp timestamp not null default now()
  );

create index events_stream_id_idx on events (stream_id);
create index events_organization_id_idx on events (organization_id);
create index events_event_type_idx on events (event_type);
create index events_event_type_id_idx on events (event_type, id);
create index events_event_type_org_id_id_idx on events (event_type, organization_id, id);

insert into events (organization_id, stream_id, event_type, event_timestamp)
select
  org_id,
  stream_id,
  event_type,
  clock_timestamp()
from
generate_series(1,10),
(
  select
    org_id,
    stream_id,
    event_type
  from
  (
    select
      org_id,
      (type || ':' || id) as stream_id
    from
      test_aggregates
    ) x,
  (
    select
      name as event_type
    from test_event_types
    ) as y
  ) as z
order by random();

drop table if exists test_orgs;
drop table if exists test_aggregates;
drop table if exists test_event_types;

select stream_id from events limit 1;
\gset

select event_type from events limit 1;
\gset

select organization_id from events limit 1;
\gset

\o

\echo ''
\echo ''
\echo ''
\echo '******************************************************************************************'
\echo '******************************************************************************************'
\echo ''
\echo 'Number of events inserted:'

select to_char(count(*), '999G999G999G999') from events;

\echo ''
\echo ''
\echo ''
\echo '******************************************************************************************'
\echo '******************************************************************************************'
\echo ''
\echo 'select all events for a given stream (i.e. to event-source an aggregate)'
\echo 'select * from events where stream_id = :''stream_id'' order by id;'
explain analyze select * from events where stream_id = :'stream_id' order by id;
select to_char(count(*), '999G999G999') as num_matched from events where stream_id = :'stream_id';


\echo ''
\echo ''
\echo ''
\echo '******************************************************************************************'
\echo '******************************************************************************************'
\echo ''
\echo 'select all events of a given event type (i.e. to recreate projections)'
\echo 'explain analyze select * from events where event_type = :''event_type'' order by id;'
explain analyze select * from events where event_type = :'event_type' order by id;
select to_char(count(*), '999G999G999') as num_matched from events where event_type = :'event_type';

\echo ''
\echo ''
\echo ''
\echo '******************************************************************************************'
\echo '******************************************************************************************'
\echo ''
\echo 'select all events of a given event type with position and limit (i.e. to update projection)'
\echo 'select * from events where event_type = :''event_type'' and id > 32471 order by id limit 10;'
explain analyze select * from events where event_type = :'event_type' and id > 32471 order by id limit 10;

\echo ''
\echo ''
\echo ''
\echo '******************************************************************************************'
\echo '******************************************************************************************'
\echo ''
\echo 'select all events for single organization of a given event type with position and limit'
\echo '(i.e. to update projection for a given organization)'
\echo 'explain analyze select * from events where event_type = :''event_type'' and organization_id = :''organization_id'' and id > 32471 order by id limit 10;'
explain analyze select * from events where event_type = :'event_type' and organization_id = :'organization_id' and id > 32471 order by id limit 10;