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
Generative AI Japan 第一回生成AI実践研究会「AI駆動開発の現在地──ブレイクスルーの鍵を握るのはデータ領域」
shisyu_gaku
0
160
サラリーマンの小遣いで作るtoCサービス - Cloudflare Workersでスケールする開発戦略
shinaps
2
430
Snowflakeの生成AI機能を活用したデータ分析アプリの作成 〜Cortex AnalystとCortex Searchの活用とStreamlitアプリでの利用〜
nayuts
1
480
Evolución del razonamiento matemático de GPT-4.1 a GPT-5 - Data Aventura Summit 2025 & VSCode DevDays
lauchacarro
0
180
新アイテムをどう使っていくか?みんなであーだこーだ言ってみよう / 20250911-rpi-jam-tokyo
akkiesoft
0
230
La gouvernance territoriale des données grâce à la plateforme Terreze
bluehats
0
160
Autonomous Database - Dedicated 技術詳細 / adb-d_technical_detail_jp
oracle4engineer
PRO
4
10k
要件定義・デザインフェーズでもAIを活用して、コミュニケーションの密度を高める
kazukihayase
0
110
バイブスに「型」を!Kent Beckに学ぶ、AI時代のテスト駆動開発
amixedcolor
2
540
Platform開発が先行する Platform Engineeringの違和感
kintotechdev
4
560
20250903_1つのAWSアカウントに複数システムがある環境におけるアクセス制御をABACで実現.pdf
yhana
3
550
Snowflake Intelligenceにはこうやって立ち向かう!クラシルが考えるAI Readyなデータ基盤と活用のためのDataOps
gappy50
0
170
Featured
See All Featured
Java REST API Framework Comparison - PWX 2021
mraible
33
8.8k
Design and Strategy: How to Deal with People Who Don’t "Get" Design
morganepeng
131
19k
Refactoring Trust on Your Teams (GOTO; Chicago 2020)
rmw
34
3.1k
How GitHub (no longer) Works
holman
315
140k
VelocityConf: Rendering Performance Case Studies
addyosmani
332
24k
実際に使うSQLの書き方 徹底解説 / pgcon21j-tutorial
soudai
PRO
188
55k
[Rails World 2023 - Day 1 Closing Keynote] - The Magic of Rails
eileencodes
36
2.5k
RailsConf & Balkan Ruby 2019: The Past, Present, and Future of Rails at GitHub
eileencodes
139
34k
Sharpening the Axe: The Primacy of Toolmaking
bcantrill
44
2.5k
Cheating the UX When There Is Nothing More to Optimize - PixelPioneers
stephaniewalter
285
13k
Practical Tips for Bootstrapping Information Extraction Pipelines
honnibal
PRO
23
1.4k
Stop Working from a Prison Cell
hatefulcrawdad
271
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