Skip to content

Instantly share code, notes, and snippets.

Avatar

Kaarel Moppel kmoppel

  • Tallinn, Estonia
View GitHub Profile
@kmoppel
kmoppel / pg_key_val_test.py
Created May 31, 2021
A mini benchmark of Postgres in a key-value setting similar to how one would use Redis or Memcached
View pg_key_val_test.py
#!/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
View pw2_ss_top_alt_v9.4.sql
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 Dec 2, 2020
Oil ext sample schema
View oil_ext.sql
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
kmoppel / init-schema.sql
Created Oct 30, 2020
Insert only data modelling perf test
View init-schema.sql
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,
View hierarchy_tree.sql
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
View jsonb_generic.sql
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);
View walg_upgrade_and_retain_issue.sh
# 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
kmoppel / analytical_queries.sh
Last active Oct 23, 2019
pgbench based PostgreSQL perf testing
View analytical_queries.sh
#!/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
@kmoppel
kmoppel / postgres-brute-force.go
Last active Aug 14, 2021
Brute force password guesser for Postgres
View postgres-brute-force.go
package main
import (
"bufio"
"fmt"
"log"
"net/url"
"os"
"time"
"database/sql"
View postgres_10.5_vs_11_rc1_perf.sh
#!/bin/bash
# Assuming by default that testclusters are on localhost
# Specified DB will also be used for storing pg_stat_statement snapshots from all clusters for easier analyzing
export PGHOST=/var/run/postgresql
export PGPORT=5432
export PGUSER=postgres
export PGDATABASE=postgres
PGBENCH=/usr/lib/postgresql/11/bin/pgbench