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
270
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
310
Django Through The Years
andrewgodwin
0
200
Writing Maintainable Software At Scale
andrewgodwin
0
430
A Newcomer's Guide To Airflow's Architecture
andrewgodwin
0
350
Async, Python, and the Future
andrewgodwin
2
650
How To Break Django: With Async
andrewgodwin
1
720
Taking Django's ORM Async
andrewgodwin
0
720
The Long Road To Asynchrony
andrewgodwin
0
650
The Scientist & The Engineer
andrewgodwin
1
750
Other Decks in Programming
See All in Programming
JSAI2025 RecSysChallenge2024 優勝報告
unonao
1
440
Passkeys for Java Developers
ynojima
2
830
Using AI Tools Around Software Development
inouehi
0
1.1k
Rails産でないDBを Railsに引っ越すHACK - Omotesando.rb #110
lnit
1
160
從零到一:搭建你的第一個 Observability 平台
blueswen
1
790
KotlinConf 2025 現地で感じたServer-Side Kotlin
n_takehata
1
180
Bytecode Manipulation 으로 생산성 높이기
bigstark
1
190
Practical Tips and Tricks for Working with Compose Multiplatform Previews (mDevCamp 2025)
stewemetal
0
120
レガシーシステムの機能調査・開発におけるAI利活用
takuya_ohtonari
0
560
型安全RESTで爆速プロトタイピング – Hono RPC実践
tacke_jp
0
110
Cloudflare Realtime と Workers でつくるサーバーレス WebRTC
nekoya3
0
370
💎 My RubyKaigi Effect in 2025: Top Ruby Companies 🌐
yasulab
PRO
1
130
Featured
See All Featured
Design and Strategy: How to Deal with People Who Don’t "Get" Design
morganepeng
130
19k
jQuery: Nuts, Bolts and Bling
dougneiner
63
7.8k
GraphQLの誤解/rethinking-graphql
sonatard
71
11k
Designing for Performance
lara
609
69k
Rebuilding a faster, lazier Slack
samanthasiow
81
9k
Speed Design
sergeychernyshev
30
990
Fireside Chat
paigeccino
37
3.5k
Bash Introduction
62gerente
614
210k
Improving Core Web Vitals using Speculation Rules API
sergeychernyshev
16
910
Navigating Team Friction
lara
186
15k
Save Time (by Creating Custom Rails Generators)
garrettdimon
PRO
31
1.2k
KATA
mclloyd
29
14k
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