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
95
Other Decks in Technology
See All in Technology
M3 Expressiveの思想に迫る
chnotchy
0
100
25分で解説する「最小権限の原則」を実現するための AWS「ポリシー」大全 / 20250625-aws-summit-aws-policy
opelab
9
1.1k
Welcome to the LLM Club
koic
0
170
“社内”だけで完結していた私が、AWS Community Builder になるまで
nagisa53
1
390
How Community Opened Global Doors
hiroramos4
PRO
1
120
Uniadex__公開版_20250617-AIxIoTビジネス共創ラボ_ツナガルチカラ_.pdf
iotcomjpadmin
0
160
Understanding_Thread_Tuning_for_Inference_Servers_of_Deep_Models.pdf
lycorptech_jp
PRO
0
120
Claude Code Actionを使ったコード品質改善の取り組み
potix2
PRO
6
2.3k
エンジニア向け技術スタック情報
kauche
1
260
解析の定理証明実践@Lean 4
dec9ue
0
180
Oracle Audit Vault and Database Firewall 20 概要
oracle4engineer
PRO
3
1.7k
Amazon ECS & AWS Fargate 運用アーキテクチャ2025 / Amazon ECS and AWS Fargate Ops Architecture 2025
iselegant
16
5.5k
Featured
See All Featured
GraphQLの誤解/rethinking-graphql
sonatard
71
11k
jQuery: Nuts, Bolts and Bling
dougneiner
63
7.8k
Large-scale JavaScript Application Architecture
addyosmani
512
110k
Art, The Web, and Tiny UX
lynnandtonic
299
21k
The Straight Up "How To Draw Better" Workshop
denniskardys
234
140k
Keith and Marios Guide to Fast Websites
keithpitt
411
22k
The Success of Rails: Ensuring Growth for the Next 100 Years
eileencodes
45
7.4k
It's Worth the Effort
3n
185
28k
The Illustrated Children's Guide to Kubernetes
chrisshort
48
50k
StorybookのUI Testing Handbookを読んだ
zakiyama
30
5.8k
Code Reviewing Like a Champion
maltzj
524
40k
Agile that works and the tools we love
rasmusluckow
329
21k
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