My talk from DjangoCon US 2015.
DUBIOUSDatabaseDESIGN
View Slide
Andrew GodwinHi, I'mAuthor of 1.7 Django & South migrationsSenior Software Engineer atOnly hates MySQL a little
“Do this. Don't ask why.”
Learning from failure.
Spacelog1
“Redis is fast!”
Read-only, forever.
GET chapter-1GET chapter-2
GET entry-123GET entry-124GET entry-125
ZRANGEBYSCORE ....GET entry-123GET entry-124GET entry-125
Request pageLook up key rangeMulti-get key rangeGet speaker details
SELECT ... JOIN ... WHERE ...
Ignoring JOIN2
“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"}
nnumber of authorsm×number of posts
scan all postsbuild dict of author -> postsscan 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 fileWrite new save fileDelete old save file
Write new save fileWrite new save file?
Tell payment processor to sendMark as processingFind unpaid clientsMark as paid
The Fastidious Modeller4
TwitterUserFacebookUserLinkedInUserEmailUser
SELECT ... FROM TwitterUserSELECT ... FROM EmailUserSELECT ... FROM LinkedInUser
The database isn't magic.
The Table Lover5
“How do I make tables at runtime?”
Tables/columns per languageTables/columns per customerConfigurable CMS columns
Columns per language300 - 400 language variantsxA couple of translated cols per tablex
DDL is very expensive.
Use JSON, hstore, or EAV-style table!
6The Cold Boot
Decent cache hit rateApplication servers mostly utilised
Great engineering!
What would happen if I deletedthe entire cache?
The Optimist7
Sharded PostgreSQLElasticSearchRiakRedisFlat files++++
Don't forget redundancy.And backups.
What happens if just one dies?
n services = n points of failure
The Primary Optimist8
“The highest value PKis the most recent”
“Autoincrement will workand scale forever”
“IDs are numbers wecan do maths on”
The Function Lover9
"Why waste time fetching columnsand rendering them separately?"
CREATE FUNCTION
CREATE FUNCTION ... import jinja2
bit.ly/whynotpg
SELECT render(template, id)FROM pagesWHERE %s ~ url;
There's a reason behindevery rule.
Ask why, or try yourself.Don't write it off without context.
Thanks.Andrew Godwin@andrewgodwin
andrewgodwin
soutaro
katsyoshi
progate
munetoshi
ivargrimstad
maaretp
ledsun
slobodan
hideg
alli83
squer
jmatsu
philhawksworth
thoeni
carmenintech
akosma
bkeepers
productmarketing
danielanewman
jennybc
aki_iinuma
holman
chriscoyier