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
440
A Newcomer's Guide To Airflow's Architecture
andrewgodwin
0
350
Async, Python, and the Future
andrewgodwin
2
660
How To Break Django: With Async
andrewgodwin
1
730
Taking Django's ORM Async
andrewgodwin
0
720
The Long Road To Asynchrony
andrewgodwin
0
660
The Scientist & The Engineer
andrewgodwin
1
760
Other Decks in Programming
See All in Programming
Google Agent Development Kit でLINE Botを作ってみた
ymd65536
2
250
git worktree × Claude Code × MCP ~生成AI時代の並列開発フロー~
hisuzuya
1
580
レベル1の開発生産性向上に取り組む − 日々の作業の効率化・自動化を通じた改善活動
kesoji
0
230
技術同人誌をMCP Serverにしてみた
74th
1
650
Deep Dive into ~/.claude/projects
hiragram
14
2.6k
AIプログラマーDevinは PHPerの夢を見るか?
shinyasaita
1
230
明示と暗黙 ー PHPとGoの インターフェイスの違いを知る
shimabox
2
520
0626 Findy Product Manager LT Night_高田スライド_speaker deck用
mana_takada
0
180
たった 1 枚の PHP ファイルで実装する MCP サーバ / MCP Server with Vanilla PHP
okashoi
1
260
なぜ「共通化」を考え、失敗を繰り返すのか
rinchoku
1
650
Porting a visionOS App to Android XR
akkeylab
0
480
ふつうの技術スタックでアート作品を作ってみる
akira888
1
890
Featured
See All Featured
CSS Pre-Processors: Stylus, Less & Sass
bermonpainter
357
30k
KATA
mclloyd
30
14k
Being A Developer After 40
akosma
90
590k
RailsConf & Balkan Ruby 2019: The Past, Present, and Future of Rails at GitHub
eileencodes
138
34k
Stop Working from a Prison Cell
hatefulcrawdad
271
21k
The Art of Programming - Codeland 2020
erikaheidi
54
13k
Keith and Marios Guide to Fast Websites
keithpitt
411
22k
Reflections from 52 weeks, 52 projects
jeffersonlam
351
20k
Build The Right Thing And Hit Your Dates
maggiecrowley
36
2.8k
Music & Morning Musume
bryan
46
6.6k
Bootstrapping a Software Product
garrettdimon
PRO
307
110k
Automating Front-end Workflow
addyosmani
1370
200k
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