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 (
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
# This one-liner compares your local RoR's dump (db/structure.sql) with a remote DB's schema (Postgres) | |
# | |
# It ignores: | |
# - ownership & privileges information | |
# - empty lines | |
# - SQL comments | |
# - data in the "migrations" table from db/structure.sql | |
pg_dump --no-owner --no-privileges --schema-only -U username -h hostname dbname \ | |
| egrep -v '^--|^$' | diff -b <(egrep -v '^INSERT INTO|^--|^$' db/structure.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
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 |
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 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 | |
); |
If you have any issue with REST API, please provide:
- 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
, orPUT
, orPATCH
, usually in JSON format)
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
-- 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) |
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
-- 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 $$ |
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
with recursive constants as ( | |
select 8 as chunk_size | |
), columns as ( | |
select | |
table_schema, | |
table_name, | |
ordinal_position, | |
column_name, | |
udt_name, | |
typalign, |
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
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 |
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
# 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 |