kmoppel

--
-- PostgreSQL database dump
--

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

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

kmoppel

package main

import (
	"bufio"
	"fmt"
	"log"
	"net/url"
	"os"
	"time"
	"database/sql"

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

-- more parts penalty
/*
   test_name    │ part_method │    avg_plan_time     │ avg_plan_time_diff │ avg_plan_time_stddev_diff │    avg_exec_time    │  avg_exec_time_diff   │ avg_exec_time_stddev_diff 
────────────────┼─────────────┼──────────────────────┼────────────────────┼───────────────────────────┼─────────────────────┼───────────────────────┼───────────────────────────
 random_access  │ hash        │ 0.036001358353539424 │  19.22491740688769 │        32.181566052208396 │ 0.24820840462700047 │   -0.8716309424183029 │         1.236449844148022
 random_access  │ range       │  0.03714072543338436 │  20.41650568434875 │        36.069893809198994 │ 0.25118521676332045 │   -1.2993096120041625 │       -0.8905487092585993
 zipfian_access │ hash        │  0.03310657188586292 │  18.83769228595254 │        37.139484335097094 │ 0.04842279570874101 │   -0.1236185797740346 │       -0.0648544720406091
 zipfian_access │ range       │ 0.030341247036364927 │ 13.961726095155843 │        38.035038463010075 │  0.0484473166

kmoppel

--
-- PostgreSQL database dump
--

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

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

kmoppel

#!/bin/bash

set -eu -o pipefail

export PGHOST=localhost
export PGPORT=5432
export PGDATABASE=postgres
export PGUSER=postgres
export PGPASSWORD=postgres
export PATH=/usr/lib/postgresql/15/bin:$PATH

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

create unlogged table int4_aa (id int, id2 int, col1 int, col2 int, col3 text, col4 text, col5 timestamptz, col6 timestamptz);
create unlogged table int4_bb (id int, id2 int, col1 int, col2 int, col3 text, col4 text, col5 timestamptz, col6 timestamptz);

insert into int4_aa
  select i, i, i, i, i, i, now(), now()
from generate_series(1, 5*1e6) i;

insert into int4_bb
  select * from int4_aa;

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);