Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Speaker Deck
PRO
Sign in
Sign up for free
Dubious Database Design
Andrew Godwin
September 07, 2015
Programming
0
220
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
A Newcomer's Guide To Airflow's Architecture
andrewgodwin
0
87
Async, Python, and the Future
andrewgodwin
1
360
How To Break Django: With Async
andrewgodwin
1
280
Taking Django's ORM Async
andrewgodwin
0
330
The Long Road To Asynchrony
andrewgodwin
0
380
The Scientist & The Engineer
andrewgodwin
1
370
Pioneering Real-Time
andrewgodwin
0
150
Just Add Await: Retrofitting Async Into Django
andrewgodwin
2
1.1k
Terrain, Art, Python and LiDAR
andrewgodwin
1
230
Other Decks in Programming
See All in Programming
확장 가능한 테라폼 코드 관리 (Scalable Terraform Code Management)
posquit0
1
310
WindowsコンテナDojo : 第1回 Visual StudioでWindowsコンテナアプリ作成
oniak3ibm
PRO
0
320
Practical Advanced Kotlin in Practice
rock3r
3
130
Angular's new Standalone Components: How Will They Affect My Architecture? @iJS London 2022
manfredsteyer
PRO
0
380
【Qiita Night】新卒エンジニアによるSwift6与太予想
eiji127
0
100
WindowsコンテナDojo:第2回 Windowsコンテナアプリのビルド、公開、デプロイ
oniak3ibm
PRO
0
130
脱オブジェクト指向講座(5分LT資料)
kishida
8
11k
Kotlin KSP - Intro
taehwandev
1
450
The future of trust stores in Python
sethmlarson
0
180
Testing, how hard can it be? (Droidcon Lisbon 2022)
dpreussler
3
200
microCMS × Shopifyで、ECサイトがリニューアル後急成長した話
microcms
0
450
You CANt teach an old dog new tricks
michaelbukachi
0
110
Featured
See All Featured
No one is an island. Learnings from fostering a developers community.
thoeni
9
1.1k
How STYLIGHT went responsive
nonsquared
85
3.9k
Rails Girls Zürich Keynote
gr2m
86
12k
Designing for Performance
lara
596
63k
Building Your Own Lightsaber
phodgson
94
4.6k
What's new in Ruby 2.0
geeforr
336
30k
Learning to Love Humans: Emotional Interface Design
aarron
261
37k
The Power of CSS Pseudo Elements
geoffreycrofte
46
3.9k
Testing 201, or: Great Expectations
jmmastey
21
5.4k
XXLCSS - How to scale CSS and keep your sanity
sugarenia
236
1M
WebSockets: Embracing the real-time Web
robhawkes
57
5k
実際に使うSQLの書き方 徹底解説 / pgcon21j-tutorial
soudai
38
12k
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