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
260
Django Through The Years
andrewgodwin
0
160
Writing Maintainable Software At Scale
andrewgodwin
0
390
A Newcomer's Guide To Airflow's Architecture
andrewgodwin
0
310
Async, Python, and the Future
andrewgodwin
2
610
How To Break Django: With Async
andrewgodwin
1
660
Taking Django's ORM Async
andrewgodwin
0
670
The Long Road To Asynchrony
andrewgodwin
0
590
The Scientist & The Engineer
andrewgodwin
1
690
Other Decks in Programming
See All in Programming
KubeCon + CloudNativeCon NA 2024 Overviewat Kubernetes Meetup Tokyo #68 / amsy810_k8sjp68
masayaaoyama
0
250
今年一番支援させていただいたのは認証系サービスでした
satoshi256kbyte
1
250
CSC305 Lecture 26
javiergs
PRO
0
140
HTTP compression in PHP and Symfony apps
dunglas
2
1.7k
Symfony Mapper Component
soyuka
2
730
これが俺の”自分戦略” プロセスを楽しんでいこう! - Developers CAREER Boost 2024
niftycorp
PRO
0
190
The Efficiency Paradox and How to Save Yourself and the World
hollycummins
1
440
103 Early Hints
sugi_0000
1
220
testcontainers のススメ
sgash708
1
120
これでLambdaが不要に?!Step FunctionsのJSONata対応について
iwatatomoya
2
3.6k
layerx_20241129.pdf
kyoheig3
2
290
クリエイティブコーディングとRuby学習 / Creative Coding and Learning Ruby
chobishiba
0
3.9k
Featured
See All Featured
The MySQL Ecosystem @ GitHub 2015
samlambert
250
12k
How to Think Like a Performance Engineer
csswizardry
22
1.2k
Adopting Sorbet at Scale
ufuk
73
9.1k
Building an army of robots
kneath
302
44k
Gamification - CAS2011
davidbonilla
80
5.1k
Docker and Python
trallard
41
3.1k
Into the Great Unknown - MozCon
thekraken
33
1.5k
Intergalactic Javascript Robots from Outer Space
tanoku
270
27k
ReactJS: Keep Simple. Everything can be a component!
pedronauck
665
120k
No one is an island. Learnings from fostering a developers community.
thoeni
19
3k
Java REST API Framework Comparison - PWX 2021
mraible
PRO
28
8.3k
How to train your dragon (web standard)
notwaldorf
88
5.7k
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