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
70
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
Agent Development Kitで始める生成 AI エージェント実践開発
danishi
0
160
JAWS-UG のイベントで使うハンズオンシナリオを Amazon Q Developer for CLI で作ってみた話
kazzpapa3
0
120
あとはAIに任せて人間は自由に生きる
kentaro
3
680
GISエンジニアよ 現場に行け!
sudataka
1
140
Observability for LLM Application lifecycle
ivry_presentationmaterials
0
120
Amazon Bedrock AgentCore でプロモーション用動画生成エージェントを開発する
nasuvitz
6
220
ロールが細分化された組織でSREと協働するインフラエンジニアは何をするか? / SRE Lounge #18
kossykinto
0
240
サイボウズフロントエンドの横断活動から考える AI時代にできること
mugi_uno
3
1.2k
20250818_KGX・One Hokkaidoコラボイベント
tohgeyukihiro
0
120
メルカリIBIS:AIが拓く次世代インシデント対応
0gm
2
460
[kickflow]20250319_少人数チームでのAutify活用
otouhujej
0
170
Exadata Database Service on Dedicated Infrastructure セキュリティ、ネットワーク、および管理について
oracle4engineer
PRO
1
330
Featured
See All Featured
The Pragmatic Product Professional
lauravandoore
36
6.8k
Learning to Love Humans: Emotional Interface Design
aarron
273
40k
StorybookのUI Testing Handbookを読んだ
zakiyama
30
6k
Building a Scalable Design System with Sketch
lauravandoore
462
33k
The Straight Up "How To Draw Better" Workshop
denniskardys
236
140k
Balancing Empowerment & Direction
lara
2
570
Scaling GitHub
holman
462
140k
Faster Mobile Websites
deanohume
309
31k
Build The Right Thing And Hit Your Dates
maggiecrowley
37
2.8k
Cheating the UX When There Is Nothing More to Optimize - PixelPioneers
stephaniewalter
283
13k
The Success of Rails: Ensuring Growth for the Next 100 Years
eileencodes
46
7.6k
Docker and Python
trallard
45
3.5k
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