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
71
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
新アイテムをどう使っていくか?みんなであーだこーだ言ってみよう / 20250911-rpi-jam-tokyo
akkiesoft
0
280
OCI Oracle Database Services新機能アップデート(2025/06-2025/08)
oracle4engineer
PRO
0
150
会社紹介資料 / Sansan Company Profile
sansan33
PRO
6
380k
CDK CLIで使ってたあの機能、CDK Toolkit Libraryではどうやるの?
smt7174
4
180
Codeful Serverless / 一人運用でもやり抜く力
_kensh
7
430
なぜスクラムはこうなったのか?歴史が教えてくれたこと/Shall we explore the roots of Scrum
sanogemaru
5
1.6k
Automating Web Accessibility Testing with AI Agents
maminami373
0
1.3k
「Linux」という言葉が指すもの
sat
PRO
4
140
実践!カスタムインストラクション&スラッシュコマンド
puku0x
0
420
テストを軸にした生き残り術
kworkdev
PRO
0
210
KotlinConf 2025_イベントレポート
sony
1
140
Evolución del razonamiento matemático de GPT-4.1 a GPT-5 - Data Aventura Summit 2025 & VSCode DevDays
lauchacarro
0
200
Featured
See All Featured
Writing Fast Ruby
sferik
628
62k
Testing 201, or: Great Expectations
jmmastey
45
7.7k
Typedesign – Prime Four
hannesfritz
42
2.8k
GraphQLの誤解/rethinking-graphql
sonatard
72
11k
Building Flexible Design Systems
yeseniaperezcruz
328
39k
Agile that works and the tools we love
rasmusluckow
330
21k
What's in a price? How to price your products and services
michaelherold
246
12k
Large-scale JavaScript Application Architecture
addyosmani
512
110k
I Don’t Have Time: Getting Over the Fear to Launch Your Podcast
jcasabona
33
2.4k
Fantastic passwords and where to find them - at NoRuKo
philnash
52
3.4k
Principles of Awesome APIs and How to Build Them.
keavy
126
17k
Let's Do A Bunch of Simple Stuff to Make Websites Faster
chriscoyier
507
140k
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