PostgreSQL at a Web Startup

@LukasFittl PostgreSQL at a Web Startup Lessons Learned from

@LukasFittl About me

@LukasFittl pganalyze

@LukasFittl

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

@LukasFittl 1. Product Hunt Architecture

@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

@LukasFittl LISTEN/NOTIFY 1. Product Hunt Architecture

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

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

@LukasFittl Analytics 1. Product Hunt Architecture

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

@LukasFittl 2. Problems Encountered

@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

@LukasFittl Lock Contention 2. Problems Encountered

@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

@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

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

@LukasFittl Idle Transactions 2. Problems Encountered

@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

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

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

@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

@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

@LukasFittl Pagination 2. Problems Encountered

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

@LukasFittl 3. Developer Education

@LukasFittl EXPLAIN 3. Developer Education

@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

@LukasFittl explain.depesz.com 3. Developer Education

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

@LukasFittl Indexing 3. Developer Education

@LukasFittl The Jack Dorsey Case 3. Developer Education

@LukasFittl Better Tools 3. Developer Education

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

@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

@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

@LukasFittl marginalia Account Load (0.3ms) SELECT àccounts`.* FROM àccounts`
WHERE àccounts`.`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

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

@LukasFittl Better Content 3. Developer Education

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

@LukasFittl Postgres Weekly 3. Developer Education

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

@LukasFittl Thank You! [email protected]

PostgreSQL at a Web Startup

PostgreSQL at a Web Startup

Transcript

More Decks by Lukas Fittl

Other Decks in Technology

Featured