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
69
Selling to Developers: Mission Impossible?
stympy
3
1k
Machine Learning Techniques
stympy
8
1.4k
Rails Caching Strategies
stympy
3
500
The Money Train
stympy
0
87
Other Decks in Technology
See All in Technology
Medmain FACTBOOK
akinaootani
0
120
React Server Componentは 何を解決し何を解決しないのか / What do React Server Components solve, and what do they not solve?
kaminashi
6
1.3k
ソフトウェアプロジェクトの成功率が上がらない原因-「社会価値を考える」ということ-
ytanaka5569
0
130
Explainable Software Engineering in the Public Sector
avandeursen
0
370
Vision Language Modelを活用した メルカリの類似画像レコメンドの性能改善
yadayuki
9
1.3k
SaaSプロダクト開発におけるバグの早期検出のためのAcceptance testの取り組み
kworkdev
PRO
0
470
一人QA時代が終わり、 QAチームが立ち上がった話
ma_cho29
0
290
ひまプロプレゼンツ 「エンジニア格付けチェック 〜春の公開収録スペシャル〜」
kaaaichi
0
160
OPENLOGI Company Profile for engineer
hr01
1
22k
バクラクでのSystem Risk Records導入による変化と改善の取り組み/Changes and Improvement Initiatives Resulting from the Implementation of System Risk Records
taddy_919
0
220
TopAppBar Composableをカスタムする
hunachi
0
120
17年のQA経験が導いたスクラムマスターへの道 / 17 Years in QA to Scrum Master
toma_sm
0
420
Featured
See All Featured
Distributed Sagas: A Protocol for Coordinating Microservices
caitiem20
331
21k
4 Signs Your Business is Dying
shpigford
183
22k
Building Better People: How to give real-time feedback that sticks.
wjessup
367
19k
For a Future-Friendly Web
brad_frost
176
9.6k
CoffeeScript is Beautiful & I Never Want to Write Plain JavaScript Again
sstephenson
160
15k
Facilitating Awesome Meetings
lara
53
6.3k
Visualizing Your Data: Incorporating Mongo into Loggly Infrastructure
mongodb
45
9.5k
Dealing with People You Can't Stand - Big Design 2015
cassininazir
367
25k
GraphQLの誤解/rethinking-graphql
sonatard
70
10k
Gamification - CAS2011
davidbonilla
81
5.2k
KATA
mclloyd
29
14k
How GitHub (no longer) Works
holman
314
140k
Transcript
Biggish Data with Rails and Postgres Ben Curtis / ben@honeybadger.io
/ @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