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.

27b304f67c0cadfa2f37a19f01af8f89?s=128

Lukas Fittl

April 19, 2016
Tweet

Transcript

  1. @LukasFittl PostgreSQL at a Web Startup Lessons Learned from

  2. @LukasFittl About me

  3. @LukasFittl pganalyze

  4. @LukasFittl

  5. @LukasFittl

  6. @LukasFittl 1. Product Hunt Architecture 2. Problems Encountered 3. Developer

    Education 4. Q&A Structure of this Talk
  7. @LukasFittl 1. Product Hunt Architecture

  8. @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
  9. @LukasFittl LISTEN/NOTIFY 1. Product Hunt Architecture

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

  11. @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
  12. @LukasFittl PII-Cleaned Database Copies for Developers 1. Product Hunt Architecture

  13. @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
  14. @LukasFittl Analytics 1. Product Hunt Architecture

  15. @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
  16. @LukasFittl Periscope Data https://www.youtube.com/watch?v=jjeziUyagBI Smart PostgreSQL-based, Cross-DB Caching 1. Product

    Hunt Architecture
  17. @LukasFittl 2. Problems Encountered

  18. @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
  19. @LukasFittl Lock Contention 2. Problems Encountered

  20. @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
  21. @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
  22. @LukasFittl Waiting for 9.6: pg_stat_activity gets lock information https://github.com/postgrespro/pg_wait_sampling 2.

    Problems Encountered
  23. @LukasFittl Idle Transactions 2. Problems Encountered

  24. @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
  25. @LukasFittl Connection Limits (Rolling Deploys, Big Dynos) e.g. 200 max_connections

    = 10 connections / Dyno, 10 Dynos, Rolling Deploy (2x) 2. Problems Encountered
  26. @LukasFittl “Zero-Downtime” Schema Changes 2. Problems Encountered

  27. @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
  28. @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
  29. @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
  30. @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
  31. @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
  32. @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
  33. @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
  34. @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
  35. @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
  36. @LukasFittl Pagination 2. Problems Encountered

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

  38. @LukasFittl 3. Developer Education

  39. @LukasFittl EXPLAIN 3. Developer Education

  40. @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
  41. @LukasFittl explain.depesz.com 3. Developer Education

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

  43. @LukasFittl Indexing 3. Developer Education

  44. @LukasFittl The Jack Dorsey Case 3. Developer Education

  45. @LukasFittl Better Tools 3. Developer Education

  46. @LukasFittl dblint Checks SQL queries in your integration tests for

    issues https://github.com/lfittl/dblint 3. Developer Education
  47. @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
  48. @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
  49. @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
  50. @LukasFittl What I wish existed: Square’s “shift” for PostgreSQL https://github.com/square/shift

    3. Developer Education
  51. @LukasFittl Better Content 3. Developer Education

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

  53. @LukasFittl Postgres Weekly 3. Developer Education

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

  55. @LukasFittl Thank You! lukas@fittl.com