Skip to content

Instantly share code, notes, and snippets.

View kmoppel's full-sized avatar

Kaarel Moppel kmoppel

  • Independent Postgres Consultant
  • Tallinn, Estonia
View GitHub Profile
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
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);
# 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 October 23, 2019 11:43
pgbench based PostgreSQL perf testing
#!/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 October 11, 2023 21:49
Brute force password guesser for Postgres
package main
import (
"bufio"
"fmt"
"log"
"net/url"
"os"
"time"
"database/sql"
#!/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
#!/bin/bash
SCALE=100
LOOPS=3
DURATION=7200
TEST_NAME=pgbench_default_vs_ins_3_rows
export PGDATABASE=postgres
export PGHOST=localhost
export PGPORT=5432
export PGUSER=$USER
@kmoppel
kmoppel / simple_postgres_c_audit_trigger.c
Last active May 29, 2018 05:23
Simple Postgres audit trigger written in C. Tested with PG 10.4
#include "postgres.h"
#include "access/htup_details.h"
#include "catalog/pg_type.h"
#include "commands/trigger.h"
#include "executor/spi.h"
#include "miscadmin.h"
#include "utils/builtins.h"
#include "utils/rel.h"
#include "utils/datetime.h"
#!/bin/bash
SCALE=100
LOOPS=3
DURATION=7200
PGBENCH_SEED=2018
for loop in $(seq 1 ${LOOPS}) ; do
echo "doing loop $loop ..."
@kmoppel
kmoppel / abfragen.sql
Last active December 1, 2017 09:50
SQL aufbau
-- #1 durschnittsgehalt nach länder, wenn keine mitarbeiter dann 0
select
country_name,
coalesce(avg(salary)::int, 0)
from
countries
left join
locations using (country_id)
left join