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
67
Selling to Developers: Mission Impossible?
stympy
3
1k
Machine Learning Techniques
stympy
8
1.4k
Rails Caching Strategies
stympy
3
500
The Money Train
stympy
0
85
Other Decks in Technology
See All in Technology
スタートアップ1人目QAエンジニアが QAチームを立ち上げ、“個”からチーム、 そして“組織”に成長するまで / How to set up QA team at reiwatravel
mii3king
2
1.5k
PL900試験から学ぶ Power Platform 基礎知識講座
kumikeyy
0
130
Developers Summit 2025 浅野卓也(13-B-7 LegalOn Technologies)
legalontechnologies
PRO
0
710
オブザーバビリティの観点でみるAWS / AWS from observability perspective
ymotongpoo
8
1.5k
Tech Blogを書きやすい環境づくり
lycorptech_jp
PRO
1
240
現場で役立つAPIデザイン
nagix
33
12k
Developer Summit 2025 [14-D-1] Yuki Hattori
yuhattor
19
6.2k
人はなぜISUCONに夢中になるのか
kakehashi
PRO
6
1.6k
抽象化をするということ - 具体と抽象の往復を身につける / Abstraction and concretization
soudai
16
3.6k
Goで作って学ぶWebSocket
ryuichi1208
0
160
ホワイトボードチャレンジ 説明&実行資料
ichimichi
0
130
全文検索+セマンティックランカー+LLMの自然文検索サ−ビスで得られた知見
segavvy
2
100
Featured
See All Featured
Practical Tips for Bootstrapping Information Extraction Pipelines
honnibal
PRO
12
960
Become a Pro
speakerdeck
PRO
26
5.1k
Mobile First: as difficult as doing things right
swwweet
223
9.3k
Refactoring Trust on Your Teams (GOTO; Chicago 2020)
rmw
33
2.8k
Fontdeck: Realign not Redesign
paulrobertlloyd
83
5.4k
10 Git Anti Patterns You Should be Aware of
lemiorhan
PRO
656
59k
Agile that works and the tools we love
rasmusluckow
328
21k
Cheating the UX When There Is Nothing More to Optimize - PixelPioneers
stephaniewalter
280
13k
BBQ
matthewcrist
87
9.5k
Designing for Performance
lara
604
68k
Why You Should Never Use an ORM
jnunemaker
PRO
55
9.2k
Visualizing Your Data: Incorporating Mongo into Loggly Infrastructure
mongodb
45
9.4k
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