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
250
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
210
Django Through The Years
andrewgodwin
0
90
Writing Maintainable Software At Scale
andrewgodwin
0
330
A Newcomer's Guide To Airflow's Architecture
andrewgodwin
0
250
Async, Python, and the Future
andrewgodwin
2
540
How To Break Django: With Async
andrewgodwin
1
580
Taking Django's ORM Async
andrewgodwin
0
590
The Long Road To Asynchrony
andrewgodwin
0
520
The Scientist & The Engineer
andrewgodwin
1
580
Other Decks in Programming
See All in Programming
Go製Webアプリケーションのエラーとの向き合い方大全、あるいはやっぱりスタックトレース欲しいやん / Kyoto.go #50
utgwkk
6
2k
哲学史とモデリング
tanakahisateru
2
430
酒飲んでたらテックリードになった話
spbaya0141
0
210
Next.js App Router
quramy
14
2.3k
otelcol receiver 自作RTA / Pepabo Tech Conference #22 春のSREまつり
arthur1
0
980
ts-morphを使ってコードリプレイスとASTへのハードルを下げる!
nyawach
5
330
Implementing Design Systems in Swift
seyfoyun
2
530
JavaScript Closure
asoluka
0
2k
The Final Frontier of Web Development: React Server Components vs Jakarta EE
ivargrimstad
0
120
『WordPressコミュニティで学ぶ』OSS貢献の多様性
ippey
0
260
Amazon Aurora Serverless v2が意外と高かった話と、AWS Database Migration Serviceの話
satoshi256kbyte
1
110
欠陥を早期に発見するための Software Engineer in Test とその重要性 / What is Software Engineer in Test and How they works
orgachem
PRO
17
2.4k
Featured
See All Featured
How to train your dragon (web standard)
notwaldorf
75
5.2k
Stop Working from a Prison Cell
hatefulcrawdad
266
19k
10 Git Anti Patterns You Should be Aware of
lemiorhan
649
58k
What’s in a name? Adding method to the madness
productmarketing
PRO
17
2.7k
A Tale of Four Properties
chriscoyier
153
22k
Debugging Ruby Performance
tmm1
70
11k
It's Worth the Effort
3n
180
27k
Easily Structure & Communicate Ideas using Wireframe
afnizarnur
188
16k
A Modern Web Designer's Workflow
chriscoyier
689
190k
Rails Girls Zürich Keynote
gr2m
91
13k
Reflections from 52 weeks, 52 projects
jeffersonlam
345
19k
The Mythical Team-Month
searls
217
42k
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