NikolayS

-- Use this in your matview/report as a tool to fetch all IDs that "slightly" overlap "N last days" of data

-- This recursive CTE walks back step by step, assuming that orders of "id" and "created" fields are the same.
-- Step size is 50,000 here,
-- time window is 30 days
-- (edit both params and/or embed them to SQL code itself, if needed).
-- [TODO] Warning: in case of missing IDs in the table, line #26 can lead to a bit incorrect result

SET user_vars.reporting_window = '30 day';
SET user_vars.reporting_scan_stepsize = 50000;

NikolayS

--util function
create or replace function random_string(length integer) returns text as
$$
declare
  chars text[] := '{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z}';
  result text := '';
  i integer := 0;
begin
  if length < 0 then
    raise exception 'Given length cannot be less than 0';

NikolayS

create extension file_fdw;
create server csvfile foreign data wrapper file_fdw;

create foreign table contacts_exported(
  "First Name" text not null default '',
  "Middle Name" text not null default '',
  "Last Name" text not null default '',
  "Title" text not null default '',
  "Suffix" text not null default '',
  "Initials" text not null default '',

NikolayS

create table multi(id bigserial primary key, data text);
create table multi1(instance int2 not null default 1) inherits(multi);
create rule domultiply as on insert to multi do instead insert into multi1(data) values('auto1'), ('auto2') returning id, data;
insert into multi(data) values('manual') returning *; -- returns 2 rows!

NikolayS

# Other settings have been omitted, the below changes are relevant
machine:
  pre:
    - sudo service postgresql stop
    - sudo apt-get purge -y postgresql*
    - sudo apt-get update
    - sudo apt-get install postgresql
    - sudo service postgresql start
    - sudo su - postgres -c "echo \"create user ubuntu with password 'ubuntu';\" | psql"
    - sudo su - postgres -c "echo \"alter user ubuntu with superuser;\" | psql"

NikolayS

shared_buffers = 900MB
work_mem = 32MB
maintenance_work_mem = 128MB

checkpoint_timeout = 10min
checkpoint_completion_target = 0.7

effective_cache_size = 2.8GB

log_min_duration_statement = 100ms

NikolayS

with usr as (
  select 1111111 as id  -- <<< EDIT USER ID HERE!
), collections_d as (
  delete from collection
  where user_id in (select id from usr) returning id
), posts_d as (
  delete from post
  where user_id in (select id from usr) returning id
), comments_d as (

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

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

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: