Skip to content

Instantly share code, notes, and snippets.

View kmoppel's full-sized avatar

Kaarel Moppel kmoppel

View GitHub Profile
#!/bin/bash
set -exuo pipefail
export PGPORT=5432
export PGDATABASE=postgres
export PGHOST=/var/run/postgresql
export PGUSER=postgres
ROWS=2e8
--
-- 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;
-- 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
--
-- 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;
#!/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
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"
--
-- 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
kmoppel / pg_key_val_test.py
Created May 31, 2021 10:57
A mini benchmark of Postgres in a key-value setting similar to how one would use Redis or Memcached
#!/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
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
kmoppel / oil_ext.sql
Last active December 2, 2020 08:57
Oil ext sample schema
SET search_path TO public;
SET synchronous_commit TO off;
CREATE TABLE t_oil (
region text,
country text,
year integer,
production integer,
consumption integer
);