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
Dubious Database Design
Search
Andrew Godwin
September 07, 2015
Programming
0
260
Dubious Database Design
My talk from DjangoCon US 2015.
Andrew Godwin
September 07, 2015
Tweet
Share
More Decks by Andrew Godwin
See All by Andrew Godwin
Reconciling Everything
andrewgodwin
1
270
Django Through The Years
andrewgodwin
0
170
Writing Maintainable Software At Scale
andrewgodwin
0
410
A Newcomer's Guide To Airflow's Architecture
andrewgodwin
0
330
Async, Python, and the Future
andrewgodwin
2
620
How To Break Django: With Async
andrewgodwin
1
690
Taking Django's ORM Async
andrewgodwin
0
690
The Long Road To Asynchrony
andrewgodwin
0
620
The Scientist & The Engineer
andrewgodwin
1
720
Other Decks in Programming
See All in Programming
CloudNativePGを布教したい
nnaka2992
0
120
Ça bouge du côté des animations CSS !
goetter
2
160
Boost Performance and Developer Productivity with Jakarta EE 11
ivargrimstad
0
1.1k
kintone開発を効率化するためにチームで試した施策とその結果を大放出!
oguemon
0
320
Serverless Rust: Your Low-Risk Entry Point to Rust in Production (and the benefits are huge)
lmammino
1
160
楽しく向き合う例外対応
okutsu
0
720
仕様変更に耐えるための"今の"DRY原則を考える
mkmk884
9
3.2k
お前もAI鬼にならないか?👹Bolt & Cursor & Supabase & Vercelで人間をやめるぞ、ジョジョー!👺
taishiyade
7
4.2k
DevNexus - Create AI Infused Java Apps with LangChain4j
kdubois
0
130
LINE messaging APIを使ってGoogleカレンダーと連携した予約ツールを作ってみた
takumakoike
0
130
もう少しテストを書きたいんじゃ〜 #phpstudy
o0h
PRO
20
4.3k
AIレビュー導入によるCIツールとの共存と最適化
kamo26sima
1
210
Featured
See All Featured
Save Time (by Creating Custom Rails Generators)
garrettdimon
PRO
29
1.1k
For a Future-Friendly Web
brad_frost
176
9.6k
Reflections from 52 weeks, 52 projects
jeffersonlam
348
20k
Rails Girls Zürich Keynote
gr2m
94
13k
Designing Experiences People Love
moore
140
23k
Code Review Best Practice
trishagee
67
18k
Learning to Love Humans: Emotional Interface Design
aarron
273
40k
The Cult of Friendly URLs
andyhume
78
6.2k
The Art of Programming - Codeland 2020
erikaheidi
53
13k
Facilitating Awesome Meetings
lara
53
6.3k
CoffeeScript is Beautiful & I Never Want to Write Plain JavaScript Again
sstephenson
160
15k
Bash Introduction
62gerente
611
210k
Transcript
DUBIOUS Database DESIGN
Andrew Godwin Hi, I'm Author of 1.7 Django & South
migrations Senior Software Engineer at Only hates MySQL a little
“Do this. Don't ask why.”
Learning from failure.
Spacelog 1
Spacelog 1
“Redis is fast!”
None
Spacelog 1
Read-only, forever.
“Redis is fast!”
GET chapter-1 GET chapter-2
GET entry-123 GET entry-124 GET entry-125
ZRANGEBYSCORE .... GET entry-123 GET entry-124 GET entry-125
Request page Look up key range Multi-get key range Get
speaker details
SELECT ... JOIN ... WHERE ...
Ignoring JOIN 2
“Joins are slow!”
{"id": 11, "post": "abc", "author": 1} {"id": 12, "post": "def",
"author": 2} {"id": 13, "post", "ghi", "author": 3} {"id": 1, "name": "Andrew"} {"id": 2, "name": "Brenda"} {"id": 3, "name": "Carol"}
n number of authors m × number of posts
scan all posts build dict of author -> posts scan
all authors and emit with posts
HASH JOIN
{ "id": 11, "post":"abc", "author": {"name": "Andrew"} }
{ "id": 11, "post":"abc", "author": { "name": "Andrew", "last_seen": 120993013,
} }
The server's running, it's fine! 3
Write new save file Write new save file Delete old
save file
Write new save file Write new save file ?
Tell payment processor to send Mark as processing Find unpaid
clients Mark as paid
Tell payment processor to send Mark as processing Find unpaid
clients Mark as paid
The Fastidious Modeller 4
TwitterUser FacebookUser LinkedInUser EmailUser
SELECT ... FROM TwitterUser SELECT ... FROM EmailUser SELECT ...
FROM LinkedInUser
None
None
The database isn't magic.
The Table Lover 5
“How do I make tables at runtime?”
None
Tables/columns per language Tables/columns per customer Configurable CMS columns
Columns per language 300 - 400 language variants x A
couple of translated cols per table x
DDL is very expensive.
Use JSON, hstore, or EAV-style table!
6 The Cold Boot
Decent cache hit rate Application servers mostly utilised
Great engineering!
What would happen if I deleted the entire cache?
None
The Optimist 7
Sharded PostgreSQL ElasticSearch Riak Redis Flat files + + +
+
Don't forget redundancy. And backups.
What happens if just one dies?
n services = n points of failure
The Primary Optimist 8
“The highest value PK is the most recent”
“Autoincrement will work and scale forever”
“IDs are numbers we can do maths on”
The Function Lover 9
"Why waste time fetching columns and rendering them separately?"
CREATE FUNCTION
CREATE FUNCTION ... import jinja2
bit.ly/whynotpg
None
SELECT render(template, id) FROM pages WHERE %s ~ url;
None
There's a reason behind every rule.
Ask why, or try yourself. Don't write it off without
context.
Thanks. Andrew Godwin @andrewgodwin