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
Optuna DashboardにおけるPLaMo2連携機能の紹介 / PFN LLM セミナー
pfn
PRO
1
850
履歴 on Rails: Bitemporal Data Modelで実現する履歴管理/history-on-rails-with-bitemporal-data-model
hypermkt
0
2k
AI時代だからこそ考える、僕らが本当につくりたいスクラムチーム / A Scrum Team we really want to create in this AI era
takaking22
6
3k
BtoBプロダクト開発の深層
16bitidol
0
180
許しとアジャイル
jnuank
1
110
Pythonによる契約プログラミング入門 / PyCon JP 2025
7pairs
5
2.5k
Oracle Cloud Infrastructure:2025年9月度サービス・アップデート
oracle4engineer
PRO
0
370
Access-what? why and how, A11Y for All - Nordic.js 2025
gdomiciano
1
100
about #74462 go/token#FileSet
tomtwinkle
1
280
組織観点からIAM Identity CenterとIAMの設計を考える
nrinetcom
PRO
1
160
PythonとLLMで挑む、 4コマ漫画の構造化データ化
esuji5
1
130
「技術負債にならない・間違えない」 権限管理の設計と実装
naro143
35
11k
Featured
See All Featured
Fantastic passwords and where to find them - at NoRuKo
philnash
52
3.4k
How to Ace a Technical Interview
jacobian
280
23k
Rebuilding a faster, lazier Slack
samanthasiow
84
9.2k
Practical Tips for Bootstrapping Information Extraction Pipelines
honnibal
PRO
23
1.5k
How to Create Impact in a Changing Tech Landscape [PerfNow 2023]
tammyeverts
54
3k
The Psychology of Web Performance [Beyond Tellerrand 2023]
tammyeverts
49
3.1k
KATA
mclloyd
32
15k
Producing Creativity
orderedlist
PRO
347
40k
Automating Front-end Workflow
addyosmani
1371
200k
Documentation Writing (for coders)
carmenintech
75
5k
Improving Core Web Vitals using Speculation Rules API
sergeychernyshev
19
1.2k
RailsConf & Balkan Ruby 2019: The Past, Present, and Future of Rails at GitHub
eileencodes
140
34k
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