How not to step on elephant’s trunk

HOW NOT TO STEP ON ELEPHANT’S TRUNK Sergey Ponomarev Based
on true stories

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup 2 @sponomarev @bufo_alvarius Sergey Ponomarev

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup 3

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup 5 evilmartians.com

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup evilmartians.com/chronicles 6

ELEPHANTS

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup TYPES 8

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup FACTS ‣ The largest land
animal in the world ‣ They are fast ‣ They are clever 9

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup TRUNK IS THE FEATURE ‣
Extremely functional ‣ Extremely complex ‣ Essential for elephants’ life 10

POSTGRESQL

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup The World's Most Advanced Open
Source Relational Database 14

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup RELATIONAL DATABASE ‣ records are
organised into rows ‣ group of records are organised into tables ‣ attributes are stored in table columns ‣ there is an ability to deﬁne relationships ‣ query language is provided, usually SQL 15

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup ACID ‣ Atomic ‣ Consistent
‣ Isolated ‣ Durable 16

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup ATOMICITY CREATE TABLE test (id
SERIAL PRIMARY KEY); BEGIN; INSERT INTO test VALUES (1); INSERT INTO test VALUES (2); COMMIT; BEGIN; INSERT INTO test VALUES (3); INSERT INTO test VALUES (4); ROLLBACK; SELECT * FROM test; -- id -- ---- -- 1 -- 2 -- (2 rows) 17

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup CONSISTENCY Column | Type |
Modifiers --------+---------+--------------------------------------------------- id | integer | not null default nextval('test_id_seq' ::regclass) Indexes: "test_pkey" PRIMARY KEY, btree (id) BEGIN; INSERT INTO test VALUES (1); INSERT INTO test VALUES (2); COMMIT; BEGIN; INSERT INTO test VALUES (2); -- ERROR: duplicate key value violates unique constraint "test_pkey" -- DETAIL: Key (id)=(2) already exists. ROLLBACK; 18

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup ISOLATION BEGIN; INSERT INTO test
VALUES (1); INSERT INTO test VALUES (2); -- Concurrent call proceeds here COMMIT; -- Concurrent call SELECT COUNT(*) FROM test; -- 0 19

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup DURABILITY BEGIN; INSERT INTO test
VALUES (4); COMMIT; -- COMMIT -- Time: 3.089 ms • Сбой в оборудовании • Отключение электричества   20

LOST IN DISK SPACE Story 1

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup $ df -h Filesystem Size
Used Avail Use% Mounted on /dev/sda1 500G 467G 33G 93% / udev 64M 0 64M 0% /dev tmpfs 1000M 0 1000M 0% /sys/fs/cgroup shm 64M 0 64M 0% /dev/shm tmpfs 1000M 0 1000M 0% /proc/acpi tmpfs 1000M 0 1000M 0% /sys/firmware 23

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup DELETE FROM logs WHERE id
< 100000000; -- DELETE 1000000000 24

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup ❓❓❓ $ df -h Filesystem
Size Used Avail Use% Mounted on /dev/sda1 500G 467G 33G 93% / udev 64M 0 64M 0% /dev tmpfs 1000M 0 1000M 0% /sys/fs/cgroup shm 64M 0 64M 0% /dev/shm tmpfs 1000M 0 1000M 0% /proc/acpi tmpfs 1000M 0 1000M 0% /sys/firmware 25

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup Isolation/Atomicity trade-off 27

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup Multi version concurrency control 28
MVCC

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup DISK LAYOUT ‣ 8kb pages
‣ One page consist of one or more records/rows = tuples ‣ Tuple is “immutable” ‣ UPDATE = INSERT + DELETE 29

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup TUPLE ‣ ctid – page
id and index on that page ‣ xmin – insert transaction id ‣ xmax – “delete” transaction id 30

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup TUPLE 31 BEGIN; SELECT txid_current();
-- 31337 INSERT INTO test VALUES (DEFAUL, 30); SELECT ctid, *, xmin, xmax FROM test;

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup TUPLE 32 -- Simply SELECT
* FROM test; -- Is equal SELECT * FROM test WHERE xmin <= txid_current() AND (xmax = 0 OR txid_current() < xmax); 

DEEP CLEANING Story 2

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup VACUUM is a garbage collector
for PostgreSQL data 34

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup VACUUM 35 VACUUM VERBOSE records;
INFO: vacuuming "public.records" INFO: index "records_pkey" now contains 471203743 row versions in 2352308 pages DETAIL: 0 index row versions were removed. 999999 index pages have been deleted, 999999 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "index_records_user_id" now contains 471203743 row versions in 23423 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. Skipped 0 pages due to buffer pins. 0 pages are entirely empty. CPU 2425.00s/0.00u sec elapsed 2525.00 sec. VACUUM …………

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup ❓❓❓ $ df -h Filesystem
Size Used Avail Use% Mounted on /dev/sda1 500G 467G 33G 93% / udev 64M 0 64M 0% /dev tmpfs 1000M 0 1000M 0% /sys/fs/cgroup shm 64M 0 64M 0% /dev/shm tmpfs 1000M 0 1000M 0% /proc/acpi tmpfs 1000M 0 1000M 0% /sys/firmware 36

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup DISK UTILISATION 37 DISK 0
% 25 % 50 % 75 % 100 % Reusable Data Freeable

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup VACUUM FULL 38 VACUUM FULL
VERBOSE records; INFO: vacuuming "public.records" INFO: index "records_pkey" now contains 471203743 row versions in 2352308 pages DETAIL: 0 index row versions were removed. 999999 index pages have been deleted, 999999 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "index_records_user_id" now contains 471203743 row versions in 23423 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. Skipped 0 pages due to buffer pins. 0 pages are entirely empty. …………

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup SOLUTIONS ‣ Copy to another
table ‣ pg_repack ‣ AUTOVACUUM tuning for prevention ‣ 40

BOOST Story 3

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup EXPLAIN SELECT * FROM records
WHERE user_id = 31337; QUERY PLAN ------------------------------------------------------------------- ---- Seq Scan on records (cost=0.00 ..239783.28 rows=174 width=195) Filter: (user_id = 31337) 42

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup INDEX 43 CREATE INDEX ON
users(account_id);

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup Consistency trade-off 45

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup CONCURRENT INDEX 46 CREATE INDEX
CONCURRENTLY ON users(account_id);

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup ✅ ✅ ✅ EXPLAIN SELECT
* FROM records WHERE user_id = 31337; QUERY PLAN ------------------------------------------------------------- Index Scan using index_records_user_id on records (cost=0.43 ..524.33 rows=174 width=195) Index Cond: (user_id = 31337) 47

GOT STUCK Story 4

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup REFACTORING ALTER TABLE users ALTER
COLUMN trial SET NOT NULL; 50

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup Consistency trade-off 52

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup TABLE-LEVEL LOCKS 53

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup TABLE-LEVEL LOCKS ‣ ACCESS SHARE
// SELECT ‣ ROW SHARE // SELECT FOR UPDATE, FOR SHARE ‣ ROW EXCLUSIVE // UDPATE, DELETE, INSERT ‣ SHARE UPDATE EXCLUSIVE // VACUUM, ALTER, CREATE INDEX CONCURRENTLY ‣ SHARE // CREATE INDEX ‣ SHARE ROW EXCLUSIVE // ALTER ‣ EXCLUSIVE // REFRESH MATERIALIZED VIEW ‣ ACCESS EXCLUSIVE // DROP, TRUNCATE, VACUUM FULL, ALTER TABLE 54

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup 55 DEAL WITH IT

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup SOLUTION ALTER TABLE users ADD
CONSTRAINT not_null_trial CHECK (trial IS NOT NULL) NOT VALID; ALTER TABLE users VALIDATE CONSTRAINT not_null_trial; 56

NOT A MANAGER’S FAULT Story 5

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup PARANOID MODE ON 59 $
rails c --sandbox Loading development environment in sandbox (Rails 5.2.0) Any modifications you make will be rolled back on exit [1] (rails_new) main: 0> User.count (17.7ms) SELECT COUNT(*) FROM "users" => 1 [2] (rails_new) main: 0> User.destroy_all User Load (0.4ms) SELECT "users".* FROM "users" (1.5ms) SAVEPOINT active_record_1 User Destroy (7.4ms) DELETE FROM "users" WHERE "users"."id" = $1 [["id", 1]] (0.7ms) RELEASE SAVEPOINT active_record_1 => [#] [3] (rails_new) main: 0> User.count (0.3ms) SELECT COUNT(*) FROM "users" => 0 [4] (rails_new) main: 0> (0.8ms) ROLLBACK

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup SCENARIO ‣ $ rails -c
sandbox ‣ User.count ‣ ☎ ‣ … 60

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup SCENARIO ‣ $ rails -c
sandbox ‣ User.count ‣ ☎ ‣ ALTER TABLE users ADD COLUMN lol VARCHAR; 61

MAIN SENSE

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup Know your tools 64

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup PG BIBLES ‣ Alexey Vasiliev
– Safe and unsafe operations for high volume PostgreSQL ‣ Илья Космодемьянский – Внутреннее устройство PostgreSQL для практикующих инженеров ‣ Tom Lane – Transaction Processing in PostgreSQL ‣ Dmitri Fountaine – Mastering PostgreSQL in Application Development 65

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup Think twice 66

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup Be ready for worst-case scenario
67

@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup PARANOID MODE -- Limit potentially
dangerous operations -- (inside current transaction) SET lock_timeout = X -- default: 0 -- Logging slow locks (server level) SET log_lock_waits = true -- default: false SET deadlock_timeout = 1s -- default: 1s 68

CULT OF MARTIANS.COM

https://www.worldwildlife.org/species/elephant

THANKS! Sergey Ponomarev evilmartians.com/chronicles @sponomarev @bufo_alvarius @evilmartians

How not to step on elephant’s trunk

How not to step on elephant’s trunk

More Decks by Sergey Ponomarev

Other Decks in Programming

Featured

Transcript