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
66
Selling to Developers: Mission Impossible?
stympy
3
1k
Machine Learning Techniques
stympy
8
1.4k
Rails Caching Strategies
stympy
3
490
The Money Train
stympy
0
84
Other Decks in Technology
See All in Technology
完全自律型AIエージェントとAgentic Workflow〜ワークフロー構築という現実解
pharma_x_tech
0
360
今年一年で頑張ること / What I will do my best this year
pauli
1
220
2024AWSで個人的にアツかったアップデート
nagisa53
1
110
【NGK2025S】動物園(PINTO_model_zoo)に遊びに行こう
kazuhitotakahashi
0
250
KMP with Crashlytics
sansantech
PRO
0
250
30分でわかる「リスクから学ぶKubernetesコンテナセキュリティ」/30min-k8s-container-sec
mochizuki875
3
450
Oracle Base Database Service 技術詳細
oracle4engineer
PRO
6
55k
メールヘッダーを見てみよう
hinono
0
110
Alignment and Autonomy in Cybozu - 300人の開発組織でアラインメントと自律性を両立させるアジャイルな組織運営 / RSGT2025
ama_ch
1
2.4k
今から、 今だからこそ始める Terraform で Azure 管理 / Managing Azure with Terraform: The Perfect Time to Start
nnstt1
0
240
Godot Engineについて調べてみた
unsoluble_sugar
0
430
技術に触れたり、顔を出そう
maruto
1
160
Featured
See All Featured
Docker and Python
trallard
43
3.2k
Improving Core Web Vitals using Speculation Rules API
sergeychernyshev
3
180
Performance Is Good for Brains [We Love Speed 2024]
tammyeverts
7
570
Building Flexible Design Systems
yeseniaperezcruz
328
38k
The Psychology of Web Performance [Beyond Tellerrand 2023]
tammyeverts
45
2.3k
Evolution of real-time – Irina Nazarova, EuRuKo, 2024
irinanazarova
6
500
The Cost Of JavaScript in 2023
addyosmani
46
7.2k
Designing for Performance
lara
604
68k
Principles of Awesome APIs and How to Build Them.
keavy
126
17k
Scaling GitHub
holman
459
140k
Refactoring Trust on Your Teams (GOTO; Chicago 2020)
rmw
33
2.7k
Become a Pro
speakerdeck
PRO
26
5.1k
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