Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
Biggish Data with Rails and Postgres
Search
Benjamin Curtis
June 06, 2014
Technology
1
120
Biggish Data with Rails and Postgres
Tips and tricks for scaling Rails apps with large databases.
Given at RubyNation 2014
Benjamin Curtis
June 06, 2014
Tweet
Share
More Decks by Benjamin Curtis
See All by Benjamin Curtis
An Introduction to Rack
stympy
0
70
Selling to Developers: Mission Impossible?
stympy
3
1.1k
Machine Learning Techniques
stympy
8
1.5k
Rails Caching Strategies
stympy
3
520
The Money Train
stympy
0
97
Other Decks in Technology
See All in Technology
マネジメントって難しい、けどおもしろい / Management is tough, but fun! #em_findy
ar_tama
7
1.2k
AI専用のリンターを作る #yumemi_patch
bengo4com
6
4.3k
Sansanのデータプロダクトマネジメントのアプローチ
sansantech
PRO
0
180
React開発にStorybookとCopilotを導入して、爆速でUIを編集・確認する方法
yu_kod
1
300
How Do I Contact HP Printer Support? [Full 2025 Guide for U.S. Businesses]
harrry1211
0
120
LLM時代の検索
shibuiwilliam
2
390
fukabori.fm 出張版: 売上高617億円と高稼働率を陰で支えた社内ツール開発のあれこれ話 / 20250704 Yoshimasa Iwase & Tomoo Morikawa
shift_evolve
PRO
2
8k
DBのスキルで生き残る技術 - AI時代におけるテーブル設計の勘所
soudai
PRO
54
20k
Delta airlines Customer®️ USA Contact Numbers: Complete 2025 Support Guide
deltahelp
0
830
United Airlines Customer Service– Call 1-833-341-3142 Now!
airhelp
0
170
開発生産性を測る前にやるべきこと - 組織改善の実践 / Before Measuring Dev Productivity
kaonavi
14
6.2k
Model Mondays S2E04: AI Developer Experiences
nitya
0
200
Featured
See All Featured
Building a Modern Day E-commerce SEO Strategy
aleyda
42
7.4k
Raft: Consensus for Rubyists
vanstee
140
7k
Designing Dashboards & Data Visualisations in Web Apps
destraynor
231
53k
The Psychology of Web Performance [Beyond Tellerrand 2023]
tammyeverts
48
2.9k
The Art of Programming - Codeland 2020
erikaheidi
54
13k
ピンチをチャンスに:未来をつくるプロダクトロードマップ #pmconf2020
aki_iinuma
126
53k
Statistics for Hackers
jakevdp
799
220k
A Modern Web Designer's Workflow
chriscoyier
695
190k
Building Flexible Design Systems
yeseniaperezcruz
328
39k
Git: the NoSQL Database
bkeepers
PRO
430
65k
Understanding Cognitive Biases in Performance Measurement
bluesmoon
29
1.8k
A designer walks into a library…
pauljervisheath
207
24k
Transcript
Biggish Data with Rails and Postgres Ben Curtis /
[email protected]
/ @stympy
Yeah, I’m cool like that
Setting the Stage So you want to store some data…
Use a Real Computer Big disks — Lots of RAM
Increase Read-Ahead blockdev --setra 2048 /dev/sda
Use a modern filesystem 2ndquadrant.com/media/pdfs/talks/RightingWrites.pdf
Tell PG about all that RAM github.com/gregs1104/pgtune
Vacuum Regularly devcenter.heroku.com/articles/heroku-postgres-database-tuning
Don’t dump it, ship it github.com/wal-e/wal-e
Some Common Problems PG, Y U SLOW?
$ rails console > Error.count()
db=# EXPLAIN (format yaml) SELECT count(*) FROM errors; QUERY PLAN
-------------------------------------- - Plan: + Node Type: "Aggregate" + Total Cost: 49971.71 + Plan Rows: 1 + Plans: + - Node Type: "Seq Scan" + Relation Name: “errors" + Total Cost: 48172.96 + Plan Rows: 719496 +
$ rails console > user.errors.page(100)
db=# EXPLAIN ANALYZE SELECT id FROM errors OFFSET 500000 LIMIT
100; ! QUERY PLAN -------------------------------------- - Plan: + Node Type: "Limit" + Actual Rows: 100 + Plans: + - Node Type: "Seq Scan" + Relation Name: "errors" + Actual Rows: 500100 +
db=# EXPLAIN ANALYZE SELECT msg FROM errors WHERE id >=
500000 AND id < 500100; ! QUERY PLAN -------------------------------------------------------- - Plan: + Node Type: "Bitmap Heap Scan" + Relation Name: "errors" + Actual Rows: 100 + Plans: + - Node Type: "Bitmap Index Scan" + Index Name: "errors_pkey" + Total Cost: 5.42 + Actual Rows: 100 + Index Cond: "((id >= 500000) AND (id <= 500100))"
Long-running Migrations The only constant in life is change
class AddIndexToAsksActive < ActiveRecord::Migration disable_ddl_transaction! ! def change add_index :asks,
:active, algorithm: :concurrently end end *Rails >= 4
Deadlock? Wha?
Too Many Locks wiki.postgresql.org/wiki/Lock_Monitoring
Intensive DB Queries www.postgresql.org/docs/current/static/warm-standby.html
Too Many DB Connections www.craigkerstiens.com/2014/05/22/on-connection-pooling
$ rails console > Error.where(“created_at < ‘1/1/2012’”). delete_all
$ rails console > Error.where(project_id: 5).delete_all
Partitioning for deletion & archival github.com/keithf4/pg_partman
Distributed & Unique IDs www.wekeroad.com/2014/05/29/a-better-id-generator- for-postgresql
Don’t fear the elephant
Thanks! Ben Curtis / bencurtis.com / @stympy speakerdeck.com/stympy