NikolayS

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# Draw psql output as iTerm2 v3 inline graph using matplotlib
# Author: Alexander Korotkov <[email protected]>
# with a few edits by [email protected] to support Python3

import sys
import re
import warnings
import matplotlib

NikolayS

When analyzing Postgres query execution plans, it is recommended using the BUFFERS option:

explain (analyze, buffers) <query>;

Example:

test=# explain (analyze, buffers) select * from t1 where num > 10000 order by num limit 1000;
                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------

NikolayS

set timezone to 'EST';

with data(t) as (
  select timestamp '2019-01-01 12:00'
)
select
  t,
  pg_typeof(t),
  t at time zone 'UTC',
  pg_typeof(t at time zone 'UTC')

NikolayS

test=# \timing
Timing is on.
test=#
test=#
test=# create table t1 as select i from generate_series(1, 1000000) _(i);
SELECT 1000000
Time: 660.804 ms
test=# create unique index i_t1 on t1(i);
CREATE INDEX

NikolayS

create schema dba;

-- on GCP's Cloud SQL for Postgres, if we work with more than one DB user,
-- we have a problem – some queries are not visible (`<insufficient privilege>`),
-- so we need to use a workaround
create or replace function dba.pgss_snapshot() returns setof pg_stat_statements as $$
declare
  rec pg_stat_statements;
  _role text;
begin

NikolayS

-- based on pg_stat_statements only
with data as (
  select
    lower(regexp_replace(query, '^\W*(\w+)\W+.*$',  '\1')) word,
    count(*) cnt,
    sum(calls) calls,
    sum(total_time) total_time
  from pg_stat_statements
  --where not query ~* '^\W*set\W+' -- uncomment this to exclude `SET ...`
  group by 1

NikolayS

-- The following code will generate a set of ALTER queries
-- to move tables and indexes from one tablespace to another.
-- WARNING: Running such ALTERs in production means huge stress for DB.
-- Use pg_repack with "-s" option (http://reorg.github.io/pg_repack/)

-- TODO: generate pg_repack call, smth like:
--   pg_repack [connection creds] -s TABLESPACENAME \
--     -t tbl1 \
--     ...
--     -i idx1 \

NikolayS

How to get an estimate of the impact of writing Postgres logs with log_min_duration_statement = 0:

1. Do select pg_stat_statements_reset(); and wait N seconds (where N >> 60 – say 1-24 hours, covering typical busy hours). Remember when it was, and write down somewhere – this timestamp will be needed!

2. Check if select count(*) from pg_stat_statements is lower than pg_stat_statements.max. If it's equal to it, then raise pg_stat_statements.max and restart with the step 1.

3. Get the estimate:

\set TS_PGSS_RESET 'XXXX-XX-XX XX:XX:XX';

NikolayS

-- This function terminates all Postgres sessions which state have been changed "age" minutes ago.
-- Usage example:
--    select * from flush_connections(60);
--
-- Or just (but result will be less readable):
--    select flush_connections(60);
--
-- By default, terminates only sessions with "state = 'idle'".
-- If needed, you can terminate ALL sessions, regardless of their states:
--    select * from flush_connections(60, true);

NikolayS

# m5.2xlarge 32.0 GiB  8 vCPUs
# s=100, n=10

*** Only SELECTs, -T 30 -j4 -c12
transaction type: <builtin: select only>
scaling factor: 100
query mode: prepared
number of clients: 12
number of threads: 4
duration: 30 s