×
Copy
Open
Link
Embed
Share
Beginning
This slide
Copy link URL
Copy link URL
Copy iframe embed code
Copy iframe embed code
Copy javascript embed code
Copy javascript embed code
Share
Tweet
Share
Tweet
Slide 1
Slide 1 text
DUBIOUS Database DESIGN
Slide 2
Slide 2 text
Andrew Godwin Hi, I'm Author of 1.7 Django & South migrations Senior Software Engineer at Only hates MySQL a little
Slide 3
Slide 3 text
“Do this. Don't ask why.”
Slide 4
Slide 4 text
Learning from failure.
Slide 5
Slide 5 text
Spacelog 1
Slide 6
Slide 6 text
Spacelog 1
Slide 7
Slide 7 text
“Redis is fast!”
Slide 8
Slide 8 text
No content
Slide 9
Slide 9 text
Spacelog 1
Slide 10
Slide 10 text
Read-only, forever.
Slide 11
Slide 11 text
“Redis is fast!”
Slide 12
Slide 12 text
GET chapter-1 GET chapter-2
Slide 13
Slide 13 text
GET entry-123 GET entry-124 GET entry-125
Slide 14
Slide 14 text
ZRANGEBYSCORE .... GET entry-123 GET entry-124 GET entry-125
Slide 15
Slide 15 text
Request page Look up key range Multi-get key range Get speaker details
Slide 16
Slide 16 text
SELECT ... JOIN ... WHERE ...
Slide 17
Slide 17 text
Ignoring JOIN 2
Slide 18
Slide 18 text
“Joins are slow!”
Slide 19
Slide 19 text
{"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"}
Slide 20
Slide 20 text
n number of authors m × number of posts
Slide 21
Slide 21 text
scan all posts build dict of author -> posts scan all authors and emit with posts
Slide 22
Slide 22 text
HASH JOIN
Slide 23
Slide 23 text
{ "id": 11, "post":"abc", "author": {"name": "Andrew"} }
Slide 24
Slide 24 text
{ "id": 11, "post":"abc", "author": { "name": "Andrew", "last_seen": 120993013, } }
Slide 25
Slide 25 text
The server's running, it's fine! 3
Slide 26
Slide 26 text
Write new save file Write new save file Delete old save file
Slide 27
Slide 27 text
Write new save file Write new save file ?
Slide 28
Slide 28 text
Tell payment processor to send Mark as processing Find unpaid clients Mark as paid
Slide 29
Slide 29 text
Tell payment processor to send Mark as processing Find unpaid clients Mark as paid
Slide 30
Slide 30 text
The Fastidious Modeller 4
Slide 31
Slide 31 text
TwitterUser FacebookUser LinkedInUser EmailUser
Slide 32
Slide 32 text
SELECT ... FROM TwitterUser SELECT ... FROM EmailUser SELECT ... FROM LinkedInUser
Slide 33
Slide 33 text
No content
Slide 34
Slide 34 text
No content
Slide 35
Slide 35 text
The database isn't magic.
Slide 36
Slide 36 text
The Table Lover 5
Slide 37
Slide 37 text
“How do I make tables at runtime?”
Slide 38
Slide 38 text
No content
Slide 39
Slide 39 text
Tables/columns per language Tables/columns per customer Configurable CMS columns
Slide 40
Slide 40 text
Columns per language 300 - 400 language variants x A couple of translated cols per table x
Slide 41
Slide 41 text
DDL is very expensive.
Slide 42
Slide 42 text
Use JSON, hstore, or EAV-style table!
Slide 43
Slide 43 text
6 The Cold Boot
Slide 44
Slide 44 text
Decent cache hit rate Application servers mostly utilised
Slide 45
Slide 45 text
Great engineering!
Slide 46
Slide 46 text
What would happen if I deleted the entire cache?
Slide 47
Slide 47 text
No content
Slide 48
Slide 48 text
The Optimist 7
Slide 49
Slide 49 text
Sharded PostgreSQL ElasticSearch Riak Redis Flat files + + + +
Slide 50
Slide 50 text
Don't forget redundancy. And backups.
Slide 51
Slide 51 text
What happens if just one dies?
Slide 52
Slide 52 text
n services = n points of failure
Slide 53
Slide 53 text
The Primary Optimist 8
Slide 54
Slide 54 text
“The highest value PK is the most recent”
Slide 55
Slide 55 text
“Autoincrement will work and scale forever”
Slide 56
Slide 56 text
“IDs are numbers we can do maths on”
Slide 57
Slide 57 text
The Function Lover 9
Slide 58
Slide 58 text
"Why waste time fetching columns and rendering them separately?"
Slide 59
Slide 59 text
CREATE FUNCTION
Slide 60
Slide 60 text
CREATE FUNCTION ... import jinja2
Slide 61
Slide 61 text
bit.ly/whynotpg
Slide 62
Slide 62 text
No content
Slide 63
Slide 63 text
SELECT render(template, id) FROM pages WHERE %s ~ url;
Slide 64
Slide 64 text
No content
Slide 65
Slide 65 text
There's a reason behind every rule.
Slide 66
Slide 66 text
Ask why, or try yourself. Don't write it off without context.
Slide 67
Slide 67 text
Thanks. Andrew Godwin @andrewgodwin