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

NikolayS

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

NikolayS

with recursive constants as (
  select 8 as chunk_size
), columns as (
  select
    table_schema,
    table_name,
    ordinal_position,
    column_name,
    udt_name,
    typalign,

NikolayS

-- table to store typeform responses
create table typeform (
  id bigserial primary key,
  created_at timestamptz default clock_timestamp(),
  data jsonb
);

-- API function (PostgREST) to process callback and save data.
-- In TypeForm's interfaces, you need to setup callback, using URL: http://your_nginx_address/rpc/typeform
create or replace function v1.typeform*(q json) returns json as $$

NikolayS

-- use this to make all constraints in "public" schema `deferrable initially immediate`
-- (edit schema name if needed)

do $$
declare
  sql text;
begin
  select into sql
    string_agg(
      'alter table ' || quote_ident(ns.nspname) || '.' || quote_ident(tb.relname)

NikolayS

If you have any issue with REST API, please provide:

1) Full HTTP request:

• HTTP method (GET, POST, ..)
• Protocol+hostname+port (important! was it dev, staging, or production? Example: https://apiserver:4321
• URI (examples: /people?id=eq.1, /rpc/some_function?user_id=eq.1)
• All request headers
• Payload (in case of POST, or PUT, or PATCH, usually in JSON format)

2) The actual response from the server:

NikolayS

drop table if exists moscow_weather;

create table moscow_weather (
  id bigserial not null primary key,
  year int2 not null,
  month int2 not null,
  created timestamptz not null default clock_timestamp(),
  weather_is_fine boolean
);

NikolayS

Latency Comparison Numbers
--------------------------
L1 cache reference                           0.5 ns
Branch mispredict                            5   ns
L2 cache reference                           7   ns                      14x L1 cache
Mutex lock/unlock                           25   ns
Main memory reference                      100   ns                      20x L2 cache, 200x L1 cache
Compress 1K bytes with Zippy             3,000   ns        3 us
Send 1K bytes over 1 Gbps network       10,000   ns       10 us
Read 4K randomly from SSD*             150,000   ns      150 us          ~1GB/sec SSD