Upgrade to Pro — share decks privately, control downloads, hide ads and more …

PostgreSQL at a Web Startup

PostgreSQL at a Web Startup

Practical Lessons Learned from using PostgreSQL at Product Hunt in the last 2 years

Talk from PGConf US 2016.

Lukas Fittl

April 19, 2016
Tweet

More Decks by Lukas Fittl

Other Decks in Technology

Transcript

  1. @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
  2. @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
  3. @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
  4. @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
  5. @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
  6. @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
  7. @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
  8. @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
  9. @LukasFittl Connection Limits (Rolling Deploys, Big Dynos) e.g. 200 max_connections

    = 10 connections / Dyno, 10 Dynos, Rolling Deploy (2x) 2. Problems Encountered
  10. @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
  11. @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
  12. @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
  13. @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
  14. @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
  15. @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
  16. @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
  17. @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
  18. @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
  19. @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
  20. @LukasFittl dblint Checks SQL queries in your integration tests for

    issues https://github.com/lfittl/dblint 3. Developer Education
  21. @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  <top   (required)>'            #  ./spec/spec_helper.rb:78:in  `block  (2  levels)  in  <top  (required)>' Missing Indices enable_seqscan=off + EXPLAIN 3. Developer Education
  22. @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  <top   (required)>'            #  ./spec/spec_helper.rb:78:in  `block  (2  levels)  in  <top  (required)>' Long-held Locks in Transactions 3. Developer Education
  23. @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