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
110
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
58
Selling to Developers: Mission Impossible?
stympy
3
920
Machine Learning Techniques
stympy
8
1.3k
Rails Caching Strategies
stympy
3
470
The Money Train
stympy
0
82
Other Decks in Technology
See All in Technology
JAWS-UGの楽しみ方
schwrzktz
1
250
Databricksにおける生成AIの取り組み
taka_aki
1
150
Go初心者が開発やってみた!
azunyan
1
370
プロダクトの不具合傾向分析と改善活動について
masayuki_yamad
0
210
物流のデータモデルを探求する深遠な旅の軌跡
kenshiro382
1
180
MySQL 8.4 LTS が あらわれた
yoku0825
PRO
1
120
HeadlessなUIライブラリを利用する価値
plaidtech
PRO
10
2.7k
スタートアップが AWS FTR を取得するべき理由
izzii
0
170
左手は添えるだけ!?AWS Well-Architected Frameworkが教えてくれる大事なデータの守り方
ohtk79
0
360
組織的なクラウド統制のはじめの一歩 後編
nyankotaro
0
240
スタートアップがAWSパートナーになって得られたこと
shonansurvivors
3
510
Amazon RDS / Amazon Aurora パフォーマンスチューニングとモニタリング
twingob
4
410
Featured
See All Featured
5 minutes of I Can Smell Your CMS
philhawksworth
199
19k
[RailsConf 2023 Opening Keynote] The Magic of Rails
eileencodes
14
8.4k
From Idea to $5000 a Month in 5 Months
shpigford
377
45k
Typedesign – Prime Four
hannesfritz
36
2.1k
The Language of Interfaces
destraynor
151
23k
Java REST API Framework Comparison - PWX 2021
mraible
PRO
18
7k
Building Effective Engineering Teams - LeadDev
addyosmani
34
2k
The Power of CSS Pseudo Elements
geoffreycrofte
62
5.1k
The Mythical Team-Month
searls
217
42k
Imperfection Machines: The Place of Print at Facebook
scottboms
261
12k
How STYLIGHT went responsive
nonsquared
92
4.9k
Pencils Down: Stop Designing & Start Developing
hursman
117
11k
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