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
98
Other Decks in Technology
See All in Technology
Wasmで社内ツールを作って配布しよう
askua
0
130
株式会社島津製作所_研究開発(集団協業と知的生産)の現場を支える、OSS知識基盤システムの導入
akahane92
1
1.2k
「AI駆動開発」のボトルネック『言語化』を効率化するには
taniiicom
1
150
スプリントゴール未達症候群に送る処方箋
kakehashi
PRO
1
220
P2P通信の標準化 WebRTCを知ろう
faithandbrave
6
2.3k
From Live Coding to Vibe Coding with Firebase Studio
firebasethailand
1
160
OTel 公式ドキュメント翻訳 PJ から始めるコミュニティ活動/Community activities starting with the OTel official document translation project
msksgm
0
260
スプリントレビューを効果的にするために
miholovesq
9
1.6k
メモ整理が苦手な者による頑張らないObsidian活用術
optim
0
130
M365アカウント侵害時の初動対応
lhazy
7
4.6k
Jitera Company Deck / JP
jitera
0
150
AI エンジニアの立場からみた、AI コーディング時代の開発の品質向上の取り組みと妄想
soh9834
7
370
Featured
See All Featured
Making Projects Easy
brettharned
116
6.3k
Cheating the UX When There Is Nothing More to Optimize - PixelPioneers
stephaniewalter
282
13k
Why You Should Never Use an ORM
jnunemaker
PRO
58
9.5k
Being A Developer After 40
akosma
90
590k
YesSQL, Process and Tooling at Scale
rocio
173
14k
Why Our Code Smells
bkeepers
PRO
337
57k
Building Flexible Design Systems
yeseniaperezcruz
328
39k
Fight the Zombie Pattern Library - RWD Summit 2016
marcelosomers
234
17k
ピンチをチャンスに:未来をつくるプロダクトロードマップ #pmconf2020
aki_iinuma
126
53k
Writing Fast Ruby
sferik
628
62k
I Don’t Have Time: Getting Over the Fear to Launch Your Podcast
jcasabona
33
2.4k
Producing Creativity
orderedlist
PRO
346
40k
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