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
2025 AWS Jr. Championが振り返るAWS Summit
kazukiadachi
0
110
スタートアップに選択肢を 〜生成AIを活用したセカンダリー事業への挑戦〜
nstock
0
130
Tokyo_reInforce_2025_recap_iam_access_analyzer
hiashisan
0
180
Backlog ユーザー棚卸しRTA、多分これが一番早いと思います
__allllllllez__
1
150
「良さそう」と「とても良い」の間には 「良さそうだがホンマか」がたくさんある / 2025.07.01 LLM品質Night
smiyawaki0820
1
540
Reach American Airlines®️ Instantly: 19 Calling Methods for Fast Support in the USA
flyamerican
1
140
Oracle Database@Google Cloud:サービス概要のご紹介
oracle4engineer
PRO
0
110
生成AI時代 文字コードを学ぶ意義を見出せるか?
hrsued
1
820
ビズリーチにおけるリアーキテクティング実践事例 / JJUG CCC 2025 Spring
visional_engineering_and_design
1
120
ビズリーチが挑む メトリクスを活用した技術的負債の解消 / dev-productivity-con2025
visional_engineering_and_design
3
7.4k
2025-07-06 QGIS初級ハンズオン「はじめてのQGIS」
kou_kita
0
170
Operating Operator
shhnjk
1
580
Featured
See All Featured
Documentation Writing (for coders)
carmenintech
72
4.9k
Measuring & Analyzing Core Web Vitals
bluesmoon
7
510
Become a Pro
speakerdeck
PRO
29
5.4k
Sharpening the Axe: The Primacy of Toolmaking
bcantrill
44
2.4k
A Modern Web Designer's Workflow
chriscoyier
695
190k
Docker and Python
trallard
44
3.5k
[Rails World 2023 - Day 1 Closing Keynote] - The Magic of Rails
eileencodes
35
2.4k
Being A Developer After 40
akosma
90
590k
Principles of Awesome APIs and How to Build Them.
keavy
126
17k
Rebuilding a faster, lazier Slack
samanthasiow
82
9.1k
VelocityConf: Rendering Performance Case Studies
addyosmani
332
24k
Adopting Sorbet at Scale
ufuk
77
9.5k
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