Tweet
Tweet

Slide 1

Slide 1 text

@LukasFittl PostgreSQL at a Web Startup Lessons Learned from

Slide 2

Slide 2 text

@LukasFittl About me

Slide 3

Slide 3 text

@LukasFittl pganalyze

Slide 4

Slide 4 text

@LukasFittl

Slide 5

Slide 5 text

@LukasFittl

Slide 6

Slide 6 text

@LukasFittl 1. Product Hunt Architecture 2. Problems Encountered 3. Developer Education 4. Q&A Structure of this Talk

Slide 7

Slide 7 text

@LukasFittl 1. Product Hunt Architecture

Slide 8

Slide 8 text

@LukasFittl PostgreSQL HA Replica Analytics Replica Redshift Periscope Data pg_dump Backup Dev User User User Redis Feed Service Ruby on Rails Biz 1. Product Hunt Architecture

Slide 9

Slide 9 text

@LukasFittl LISTEN/NOTIFY 1. Product Hunt Architecture

Slide 10

Slide 10 text

@LukasFittl In-Memory Cache for Social Context 1. Product Hunt Architecture

Slide 11

Slide 11 text

@LukasFittl PostgreSQL HA Replica Analytics Replica Redshift Periscope Data pg_dump Backup Dev User User User Redis Feed Service Ruby on Rails Biz 1. Product Hunt Architecture

Slide 12

Slide 12 text

@LukasFittl PII-Cleaned Database Copies for Developers 1. Product Hunt Architecture

Slide 13

Slide 13 text

@LukasFittl PostgreSQL HA Replica Analytics Replica Redshift Periscope Data pg_dump Backup Dev User User User Redis Feed Service Ruby on Rails Biz 1. Product Hunt Architecture

Slide 14

Slide 14 text

@LukasFittl Analytics 1. Product Hunt Architecture

Slide 15

Slide 15 text

@LukasFittl PostgreSQL HA Replica Analytics Replica Redshift Periscope Data pg_dump Backup Dev User User User Redis Feed Service Ruby on Rails Biz 1. Product Hunt Architecture

Slide 16

Slide 16 text

@LukasFittl Periscope Data https://www.youtube.com/watch?v=jjeziUyagBI Smart PostgreSQL-based, Cross-DB Caching 1. Product Hunt Architecture

Slide 17

Slide 17 text

@LukasFittl 2. Problems Encountered

Slide 18

Slide 18 text

@LukasFittl Rolling Deploys & Data Migrations 1. Initial Structure Change 2. Code deploy 3. Data migration 4. Final Structure Change (e.g. NOT NULL) 2. Problems Encountered

Slide 19

Slide 19 text

@LukasFittl Lock Contention 2. Problems Encountered

Slide 20

Slide 20 text

@LukasFittl Long Held Locks in Transactions Rails Counter Cache & Timestamps BEGIN   SELECT  1  AS  one  FROM  "post_votes"    WHERE  (…)  LIMIT  1   SELECT  "posts".*  FROM  "posts"    WHERE  "posts"."id"  =  $1  LIMIT  1   INSERT  INTO  "notifications"  (…)  VALUES  (…)  RETURNING  "id"   SELECT  "users".*  FROM  "users"    WHERE  "users"."id"  =  $1  LIMIT  1   UPDATE  "users"  SET  "updated_at"  =  ?  WHERE  "users"."id"  =  ?   INSERT  INTO  "post_votes"  (…)  VALUES  (…)  RETURNING  "id"   UPDATE  "posts"  SET  "votes"  =  COALESCE("votes",  0)  +  1  WHERE  "posts"."id"  =  ?   UPDATE  "posts"  SET  "credible_post_votes_count"  =  …  WHERE  "posts"."id"  =  ?   UPDATE  "users"  SET  "updated_at"  =  ?  WHERE  "users"."id"  =  ?   UPDATE  "posts"  SET  "updated_at"  =  ?  WHERE  "posts"."id"  =  ?   COMMIT 2. Problems Encountered

Slide 21

Slide 21 text

@LukasFittl log_lock_waits = on deadlock_timeout = 1000 ms process  20679  still  waiting  for  ExclusiveLock  on  tuple  (566,1)  of  relation  16421  after  1000.115  ms   process  20678  still  waiting  for  ExclusiveLock  on  tuple  (566,1)  of  relation  16421  after  1000.126  ms   process  15533  still  waiting  for  ExclusiveLock  on  tuple  (566,1)  of  relation  16421  1000.129  ms   process  20663  still  waiting  for  ExclusiveLock  on  tuple  (566,1)  of  relation  16421  1000.100  ms   process  15537  still  waiting  for  ExclusiveLock  on  tuple  (566,1)  of  relation  16421  1000.130  ms   process  15536  still  waiting  for  ExclusiveLock  on  tuple  (566,1)  of  relation  16421  1000.222  ms   process  20734  still  waiting  for  ExclusiveLock  on  tuple  (566,1)  of  relation  16421  1000.130  ms   process  15538  still  waiting  for  ExclusiveLock  on  tuple  (566,1)  of  relation  16421  1000.136  ms   process  15758  still  waiting  for  ShareLock  on  transaction  250175899  after  1000.073  ms 2. Problems Encountered

Slide 22

Slide 22 text

@LukasFittl Waiting for 9.6: pg_stat_activity gets lock information https://github.com/postgrespro/pg_wait_sampling 2. Problems Encountered

Slide 23

Slide 23 text

@LukasFittl Idle Transactions 2. Problems Encountered

Slide 24

Slide 24 text

@LukasFittl RED:  Long  Queries   Pid        Duration                  Query   -­‐-­‐-­‐-­‐-­‐    -­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐    -­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐   31868    00:10:52.165883    UPDATE  "posts"  SET  "link_visits"  =  COALESCE("link_visits",  0)  +  1  WHERE  "posts"."id"  =  $1   28835    01:06:16.505554    UPDATE  "posts"  SET  "link_visits"  =  COALESCE("link_visits",  0)  +  1  WHERE  "posts"."id"  =  $1   28836    01:06:16.486394    UPDATE  "posts"  SET  "link_visits"  =  COALESCE("link_visits",  0)  +  1  WHERE  "posts"."id"  =  $1   28837    01:06:16.41853      UPDATE  "posts"  SET  "link_visits"  =  COALESCE("link_visits",  0)  +  1  WHERE  "posts"."id"  =  $1   28838    01:06:16.380474    UPDATE  "posts"  SET  "link_visits"  =  COALESCE("link_visits",  0)  +  1  WHERE  "posts"."id"  =  $1   31869    00:10:26.068636    UPDATE  "posts"  SET  "link_visits"  =  COALESCE("link_visits",  0)  +  1  WHERE  "posts"."id"  =  $1   31870    00:08:47.790245    UPDATE  "posts"  SET  "link_visits"  =  COALESCE("link_visits",  0)  +  1  WHERE  "posts"."id"  =  $1   23781    01:12:14.603475    UPDATE  "posts"  SET  "link_visits"  =  COALESCE("link_visits",  0)  +  1  WHERE  "posts"."id"  =  $1   31862    00:10:47.98641      UPDATE  "posts"  SET  "link_visits"  =  COALESCE("link_visits",  0)  +  1  WHERE  "posts"."id"  =  $1   31863    00:11:05.921372    UPDATE  "posts"  SET  "link_visits"  =  COALESCE("link_visits",  0)  +  1  WHERE  "posts"."id"  =  $1   27648    01:17:38.773909    UPDATE  "posts"  SET  "link_visits"  =  COALESCE("link_visits",  0)  +  1  WHERE  "posts"."id"  =  $1   27098    01:28:51.216489    UPDATE  "posts"  SET  "link_visits"  =  COALESCE("link_visits",  0)  +  1  WHERE  "posts"."id"  =  $1   27106    01:27:18.455351    UPDATE  "posts"  SET  "link_visits"  =  COALESCE("link_visits",  0)  +  1  WHERE  "posts"."id"  =  $1   31880    00:10:52.877779    UPDATE  "posts"  SET  "link_visits"  =  COALESCE("link_visits",  0)  +  1  WHERE  "posts"."id"  =  $1   31881    00:10:52.168877    UPDATE  "posts"  SET  "link_visits"  =  COALESCE("link_visits",  0)  +  1  WHERE  "posts"."id"  =  $1   RED:  Idle  in  Transaction   23729    01:37:12.566497    UPDATE  "posts"  SET  "link_unique_visits"  =  COALESCE("link_unique_visits",  0)  +  1  WHERE   "posts"."id"  =  $1   RED:  Blocking  Queries            23760    UPDATE  "posts"  SET  "link_unique_visits"  =  COALESCE("link_unique_visits",  0)  +  1  WHERE  "posts"."id"  =  $1     01:37:12.646868                      23729    UPDATE  "posts"  SET  "link_visits"  =  COALESCE("link_visits",  0)  +  1  WHERE  "posts"."id"  =   $1    01:31:59.088208   Dead Background Worker caused silent queue back-up for 1hr+ 2. Problems Encountered

Slide 25

Slide 25 text

@LukasFittl Connection Limits (Rolling Deploys, Big Dynos) e.g. 200 max_connections = 10 connections / Dyno, 10 Dynos, Rolling Deploy (2x) 2. Problems Encountered

Slide 26

Slide 26 text

@LukasFittl “Zero-Downtime” Schema Changes 2. Problems Encountered

Slide 27

Slide 27 text

@LukasFittl 1. Don’t remove columns on large tables 2. Don’t rename columns 3. Always index CONCURRENTLY 4. Carefully change column type 5. Carefully add columns with a DEFAULT 6. Carefully add NOT NULL columns (7. Sometimes use disable_ddl_transaction) Preach to the Devs 2. Problems Encountered

Slide 28

Slide 28 text

@LukasFittl 1. Don’t remove columns on large tables 2. Don’t rename columns 3. Always index CONCURRENTLY 4. Carefully change column type 5. Carefully add columns with a DEFAULT 6. Carefully add NOT NULL columns (7. Sometimes use disable_ddl_transaction) Preach to the Devs 2. Problems Encountered

Slide 29

Slide 29 text

@LukasFittl 1. Don’t remove columns on large tables 2. Don’t rename columns 3. Always index CONCURRENTLY 4. Carefully change column type 5. Carefully add columns with a DEFAULT 6. Carefully add NOT NULL columns (7. Sometimes use disable_ddl_transaction) Preach to the Devs 2. Problems Encountered

Slide 30

Slide 30 text

@LukasFittl 1. Don’t remove columns on large tables 2. Don’t rename columns 3. Always index CONCURRENTLY 4. Carefully change column type 5. Carefully add columns with a DEFAULT 6. Carefully add NOT NULL columns (7. Sometimes use disable_ddl_transaction) Preach to the Devs 2. Problems Encountered

Slide 31

Slide 31 text

@LukasFittl 1. Don’t remove columns on large tables 2. Don’t rename columns 3. Always index CONCURRENTLY 4. Carefully change column type 5. Carefully add columns with a DEFAULT 6. Carefully add NOT NULL columns (7. Sometimes use disable_ddl_transaction) Preach to the Devs 2. Problems Encountered

Slide 32

Slide 32 text

@LukasFittl 1. Don’t remove columns on large tables 2. Don’t rename columns 3. Always index CONCURRENTLY 4. Carefully change column type 5. Carefully add columns with a DEFAULT 6. Carefully add NOT NULL columns (7. Sometimes use disable_ddl_transaction) Preach to the Devs 2. Problems Encountered

Slide 33

Slide 33 text

@LukasFittl 1. Don’t remove columns on large tables 2. Don’t rename columns 3. Always index CONCURRENTLY 4. Carefully change column type 5. Carefully add columns with a DEFAULT 6. Carefully add NOT NULL columns (7. Sometimes use disable_ddl_transaction) Preach to the Devs 2. Problems Encountered

Slide 34

Slide 34 text

@LukasFittl 1. Don’t remove columns on large tables 2. Don’t rename columns 3. Always index CONCURRENTLY 4. Carefully change column type 5. Carefully add columns with a DEFAULT 6. Carefully add NOT NULL columns (7. Sometimes use disable_ddl_transaction) Preach to the Devs 2. Problems Encountered

Slide 35

Slide 35 text

@LukasFittl Stats from ~2 years: 70 tables added 15 tables removed 107 indices added 186 columns added 45 columns removed 6 column defaults changed 24 column NULLs changed 2. Problems Encountered

Slide 36

Slide 36 text

@LukasFittl Pagination 2. Problems Encountered

Slide 37

Slide 37 text

@LukasFittl ?page=X vs ?older_than_id=Y 2. Problems Encountered

Slide 38

Slide 38 text

@LukasFittl 3. Developer Education

Slide 39

Slide 39 text

@LukasFittl EXPLAIN 3. Developer Education

Slide 40

Slide 40 text

@LukasFittl EXPLAIN  (ANALYZE,  BUFFERS)   SELECT  *  FROM  snapshots  WHERE  collected_at  BETWEEN  now()  -­‐  '24  hours'::interval  AND  now();                                                                                    QUERY  PLAN                                                                                               -­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐    Index  Scan  using  index_snapshots_on_collected_at  on  snapshots    (cost=0.30..8.32  rows=1   width=116)  (actual  time=0.078..0.078  rows=0  loops=1)        Index  Cond:  ((collected_at  >=  (now()  -­‐  '24:00:00'::interval))  AND  (collected_at  <=  now()))        Buffers:  shared  hit=5    Planning  time:  0.286  ms    Execution  time:  0.115  ms   (5  rows) psql 3. Developer Education

Slide 41

Slide 41 text

@LukasFittl explain.depesz.com 3. Developer Education

Slide 42

Slide 42 text

@LukasFittl Pev http://tatiyants.com/postgres-query-plan-visualization/ 3. Developer Education

Slide 43

Slide 43 text

@LukasFittl Indexing 3. Developer Education

Slide 44

Slide 44 text

@LukasFittl The Jack Dorsey Case 3. Developer Education

Slide 45

Slide 45 text

@LukasFittl Better Tools 3. Developer Education

Slide 46

Slide 46 text

@LukasFittl dblint Checks SQL queries in your integration tests for issues https://github.com/lfittl/dblint 3. Developer Education

Slide 47

Slide 47 text

@LukasFittl Failures:      1)  FeedsController#show              Failure/Error:  get  :show,  format:  :atom            Dblint::Checks::MissingIndex::Error:                Missing  index  on  oauth_applications  for  '((twitter_app_name)::text  =  'My  Feed   App'::text)'  in  'SELECT    "oauth_applications".*  FROM  "oauth_applications"  WHERE   "oauth_applications"."twitter_app_name"  =  $1  LIMIT  1',  called  by  app/controllers/ feeds_controller.rb:6:in  `show'            #  ./app/controllers/feeds_controller.rb:6:in  `show'            #  ./spec/controllers/feeds_controller_spec.rb:12:in  `block  (3  levels)  in  '            #  ./spec/spec_helper.rb:78:in  `block  (2  levels)  in  ' Missing Indices enable_seqscan=off + EXPLAIN 3. Developer Education

Slide 48

Slide 48 text

@LukasFittl  1)  Invites::AcceptInvite  test            Failure/Error:  described_class.call(user:  invited_user)            Dblint::LongHeldLock:                Lock  on  ["users",  3]  held  for  29  statements  (0.13  ms)  by  'UPDATE  "users"  SET   "invited_by_id"  =  $1,  "role"  =  $2,  "updated_at"  =  $3  WHERE  "users"."id"  =  $4',  transaction started  by  app/services/invites/accept_invite.rb:20:in  `run'            #  ./app/services/invites/accept_invite.rb:20:in  `run'            #  ./app/services/invites/accept_invite.rb:7:in  `call'            #  ./spec/services/invites/accept_invite_spec.rb:8:in  `accept_invite'            #  ./spec/services/invites/accept_invite_spec.rb:64:in  `block  (3  levels)  in  '            #  ./spec/spec_helper.rb:78:in  `block  (2  levels)  in  ' Long-held Locks in Transactions 3. Developer Education

Slide 49

Slide 49 text

@LukasFittl marginalia Account  Load  (0.3ms)    SELECT  `accounts`.*  FROM  `accounts`     WHERE  `accounts`.`queenbee_id`  =  1234567890     LIMIT  1     /*application:BCX,controller:project_imports,action:show*/ Annotates your SQL queries with who called them https://github.com/basecamp/marginalia 3. Developer Education

Slide 50

Slide 50 text

@LukasFittl What I wish existed: Square’s “shift” for PostgreSQL https://github.com/square/shift 3. Developer Education

Slide 51

Slide 51 text

@LukasFittl Better Content 3. Developer Education

Slide 52

Slide 52 text

@LukasFittl PostgreSQL Documentation (its already awesome!) 3. Developer Education

Slide 53

Slide 53 text

@LukasFittl Postgres Weekly 3. Developer Education

Slide 54

Slide 54 text

@LukasFittl Use The Index Luke http://use-the-index-luke.com/ 3. Developer Education

Slide 55

Slide 55 text

@LukasFittl Thank You! lukas@fittl.com