kmoppel

export PGHOST=/var/run/postgresql
export PGUSER=postgres
export PGDATABASE=postgres
export PGOPTIONS='-c maintenance_work_mem=4GB' # Helps to speed up CREATE INDEX for most index types
CLIENTS=2
JOBS=1
DURATION=1800


SQL_DDL=$(cat << "EOF"

kmoppel

--
-- PostgreSQL database dump
--

-- Dumped from database version 14.5 (Ubuntu 14.5-1.pgdg22.04+1)
-- Dumped by pg_dump version 14.5 (Ubuntu 14.5-1.pgdg22.04+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;

kmoppel

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

# Inspired by https://dzone.com/articles/redis-vs-memcached-2021-comparison

ROWS = [1000, 10000, 100000, 1000000] # 1mio rows will be 66 MB data size + 50 MB index size so make sure shared_buffers is 128MB+
LOOPS=10
TEST_NAME = 'run1'

import psycopg2

kmoppel

explain analyse
with q_data as (
    select
    (extract (epoch from now()) * 1e9)::int8 as epoch_ns,
    queryid::text as tag_queryid,
    /*
    NB! if security conscious about exposing query texts replace the below expression with a dash ('-') OR
    use the stat_statements_no_query_text metric instead, created specifically for this use case.
    */
    --max(ltrim(regexp_replace(query, E'[ \\t\\n\\r]+' , ' ', 'g')))::varchar(16000) as tag_query,

kmoppel

SET search_path TO public;
SET synchronous_commit TO off;

CREATE TABLE t_oil (
    region text,
    country text,
    year integer,
    production integer,
    consumption integer
);

kmoppel

DROP TABLE IF EXISTS standard_flow, alternative_flow, alternative_flow_state CASCADE;
--TRUNCATE standard_flow, alternative_flow, alternative_flow_state;

/* STANDARD UPDATES */
CREATE TABLE IF NOT EXISTS standard_flow (
  session_id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  state text NOT NULL DEFAULT 'APPSTATE_1',
  created_on timestamptz NOT NULL DEFAULT now(),
  last_state_change timestamptz,
  data1 int8,

kmoppel

create unlogged table t(id serial primary key, parent_id int references t(id));

insert into t select i, case when i > 10 /* how many top level? */ then i::int / 10 else null end from generate_series(1, 1e4) i;

create index on t(parent_id);

vacuum analyze t;

with recursive q(id, level) as (
select 1 as id, 0 as level -- all descendants of top parent with id=1

kmoppel

DROP TABLE IF EXISTS pgbench_generic_log;
CREATE TABLE pgbench_generic_log (
  mtime timestamptz not null default now(),
  action char not null check (action in ('I', 'U', 'D')),
  username text not null,
  table_name text not null,
  row_data jsonb not null
);

CREATE INDEX ON pgbench_generic_log USING brin (mtime);

kmoppel

# simulates v11 to v12 migration and 'wal-g delete retain' choosing by name rather by time
# NB! don't forget to configure ~/.walg.json and ~/.aws/credentials
# NB! default Postgres binary paths assume Debian / Ubuntu

export PGHOST=localhost
export PGBINOLD=/usr/lib/postgresql/11/bin/
export PGBINNEW=/usr/lib/postgresql/12/bin/
export PGDATAOLD=pg11
export PGDATANEW=pg12
export PGPORT=6432

kmoppel

#!/bin/bash

# Assuming that testclusters are on localhost and $USER can start / stop clusters
# Results DB used for storing pg_stat_statement snapshots from test instances should be running already
export PGHOST=/var/run/postgresql
export PGUSER=postgres
export PGDATABASE=postgres

PGBENCH=/usr/lib/postgresql/12/bin/pgbench
PSQL=/usr/lib/postgresql/12/bin/psql