Skip to content

Instantly share code, notes, and snippets.

Avatar
🚚
Moved to GitLab.com (@NikolayS)

Nikolay Samokhvalov NikolayS

🚚
Moved to GitLab.com (@NikolayS)
View GitHub Profile
@NikolayS
NikolayS / at_time_zone.sql
Last active Sep 4, 2019
SQL (Postgres) and timestamps 🤦‍
View at_time_zone.sql
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
NikolayS / lets_reuse_same_space.md
Last active Feb 18, 2019
Why index maintenance (e.g. with pg_repack) is an inevitable thing?
View lets_reuse_same_space.md
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
NikolayS / dba_pgss.sql
Last active Nov 19, 2021
Simple pg_stat_statements snapshots
View dba_pgss.sql
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
NikolayS / queries_by_keyword.sql
Last active Jan 6, 2020
workload - pg_stat_statements
View queries_by_keyword.sql
-- 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
NikolayS / gist:cb028d6c49e6f9e3ea8e1944452d9ff5
Last active Oct 1, 2018
PostgreSQL: move all objects from one tablespace to default one
View gist:cb028d6c49e6f9e3ea8e1944452d9ff5
-- 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
NikolayS / howto.md
Last active Sep 26, 2021
log_min_duration_statement = 0 and I/O impact
View howto.md

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
NikolayS / pg_terminate_old.sql
Last active Jun 21, 2018
Terminate old Postgres sessions
View pg_terminate_old.sql
-- 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
NikolayS / becnh_m5.2xlarge.out
Last active Nov 17, 2021
pgbench: simple INSERTs, UPDATEs with and without triggers
View becnh_m5.2xlarge.out
# 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
View gist:7d1fb9015a767e4fbc3e4cbe5678ab17
sudo add-apt-repository 'deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-client-10
View alignment_padding_analysis.sql
with recursive constants as (
select 8 as chunk_size
), columns as (
select
table_schema,
table_name,
ordinal_position,
column_name,
udt_name,
typalign,