This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
package main | |
import ( | |
"bufio" | |
"fmt" | |
"log" | |
"net/url" | |
"os" | |
"time" | |
"database/sql" |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#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" |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/bin/bash | |
SCALE=100 | |
LOOPS=3 | |
DURATION=7200 | |
PGBENCH_SEED=2018 | |
for loop in $(seq 1 ${LOOPS}) ; do | |
echo "doing loop $loop ..." |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- #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 |