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
PHPからはじめるコンピュータアーキテクチャ / From Scripts to Silicon: A Journey Through the Layers of Computing Hiroshima 2025 Edition
tomzoh
0
140
やる気のない自分との向き合い方/How to Deal with Your Unmotivated Self
sanogemaru
0
510
アイテムレビュー機能導入からの学びと改善
zozotech
PRO
0
170
HR Force における DWH の併用事例 ~ サービス基盤としての BigQuery / 分析基盤としての Snowflake ~@Cross Data Platforms Meetup #2「BigQueryと愉快な仲間たち」
ryo_suzuki
0
220
研究開発部メンバーの働き⽅ / Sansan R&D Profile
sansan33
PRO
3
20k
難しいセキュリティ用語をわかりやすくしてみた
yuta3110
0
110
GoでもGUIアプリを作りたい!
kworkdev
PRO
0
140
React19.2のuseEffectEventを追う
maguroalternative
0
250
WEBサービスを成り立たせるAWSサービス
takano0131
1
150
20251007: What happens when multi-agent systems become larger? (CyberAgent, Inc)
ornew
1
290
Introduction to Sansan for Engineers / エンジニア向け会社紹介
sansan33
PRO
5
43k
Contract One Engineering Unit 紹介資料
sansan33
PRO
0
8.8k
Featured
See All Featured
4 Signs Your Business is Dying
shpigford
185
22k
Leading Effective Engineering Teams in the AI Era
addyosmani
6
440
Practical Orchestrator
shlominoach
190
11k
Building Adaptive Systems
keathley
44
2.8k
Documentation Writing (for coders)
carmenintech
75
5.1k
Unsuck your backbone
ammeep
671
58k
Save Time (by Creating Custom Rails Generators)
garrettdimon
PRO
32
1.6k
RailsConf & Balkan Ruby 2019: The Past, Present, and Future of Rails at GitHub
eileencodes
140
34k
Why Our Code Smells
bkeepers
PRO
340
57k
10 Git Anti Patterns You Should be Aware of
lemiorhan
PRO
657
61k
Side Projects
sachag
455
43k
BBQ
matthewcrist
89
9.8k
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