Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Speaker Deck
PRO
Sign in
Sign up for free
Good Schema Design and Why It Matters
Andrew Godwin
May 15, 2014
Programming
12
1.1k
Good Schema Design and Why It Matters
A talk I gave at DjangoCon Europe 2014.
Andrew Godwin
May 15, 2014
Tweet
Share
More Decks by Andrew Godwin
See All by Andrew Godwin
A Newcomer's Guide To Airflow's Architecture
andrewgodwin
0
99
Async, Python, and the Future
andrewgodwin
1
380
How To Break Django: With Async
andrewgodwin
1
310
Taking Django's ORM Async
andrewgodwin
0
340
The Long Road To Asynchrony
andrewgodwin
0
390
The Scientist & The Engineer
andrewgodwin
1
380
Pioneering Real-Time
andrewgodwin
0
150
Just Add Await: Retrofitting Async Into Django
andrewgodwin
2
1.1k
Terrain, Art, Python and LiDAR
andrewgodwin
1
240
Other Decks in Programming
See All in Programming
Reactは何を提供するLibraryなのか?
taro28
3
420
プロダクトのタイプ別 GraphQL クライアントの選び方
shozawa
0
5.4k
Value and Record Types
hschwentner
0
550
GDG Seoul IO Extended 2022 - Android Compose
taehwandev
0
320
Lancersをコンテナへ本番移行する取り組み
rvirus0817
1
390
LINE Messaging APIの概要 - LINE API総復習シリーズ
uezo
1
180
Meet Swift Regex
usamik26
0
360
開発速度を5倍早くするVSCodeの拡張機能を作った
purp1eeeee
2
160
Web API連携でCSRF対策がどう実装されてるか調べた / how to implements csrf-detection on Web API
yasuakiomokawa
2
470
React Nativeアプリを DDDで開発している話
nihemak
0
150
Modern Android Developer ~ 안내서
pluu
1
650
オブジェクト指向で挫折する初学者へ
deepoil
0
180
Featured
See All Featured
Git: the NoSQL Database
bkeepers
PRO
415
59k
Adopting Sorbet at Scale
ufuk
63
7.6k
The World Runs on Bad Software
bkeepers
PRO
57
5.3k
The Cult of Friendly URLs
andyhume
68
4.8k
Facilitating Awesome Meetings
lara
29
4k
Typedesign – Prime Four
hannesfritz
34
1.4k
Embracing the Ebb and Flow
colly
73
3.4k
Art Directing for the Web. Five minutes with CSS Template Areas
malarkey
196
9.4k
ParisWeb 2013: Learning to Love: Crash Course in Emotional UX Design
dotmariusz
100
5.9k
Testing 201, or: Great Expectations
jmmastey
21
5.4k
Stop Working from a Prison Cell
hatefulcrawdad
261
17k
Why Our Code Smells
bkeepers
PRO
324
55k
Transcript
Andrew Godwin @andrewgodwin GOOD SCHEMA DESIGN WHY IT MATTERS and
Andrew Godwin Core Developer Senior Engineer Author & Maintainer
Schemas Explicit & Implicit
Explicit PostgreSQL MySQL Oracle SQLite CouchDB MongoDB Redis ZODB Implicit
Explicit Schema ID int Name text Weight uint 1 2
3 Alice Bob Charles 76 84 65 Implicit Schema { "id": 342, "name": "David", "weight": 44, }
Explicit Schema Normalised or semi normalised structure JOINs to retrieve
related data Implicit Schema Embedded structure Related data retrieved naturally with object
Silent Failure { "id": 342, "name": "David", "weight": 74, }
{ "id": 342, "name": "Ellie", "weight": "85kg", } { "id": 342, "nom": "Frankie", "weight": 77, } { "id": 342, "name": "Frankie", "weight": -67, }
Schemas inform Storage
PostgreSQL
Adding NULLable columns: instant But must be at end of
table
CREATE INDEX CONCURRENTLY Slower, and only one at a time
Constraints after column addition This is more general advice
MySQL Locks whole table Rewrites entire storage No DDL transactions
Oracle / MSSQL / etc. Look into their strengths
Changing the Schema Databases aren't code...
You can't put your database in a VCS You can
put your schema in a VCS But your data won't always survive.
Django Migrations Codified schema change format
None
Migrations aren't enough You can't automate away a social problem!
What if we got rid of the schema? That pesky,
pesky schema.
The Nesting Problem { "id": 123, "name": "Andrew", "friends": [
{"id": 456, "name": "David"}, {"id": 789, "name": "Mazz"}, ], "likes": [ {"id": 22, "liker": {"id": 789, "name", "Mazz"}}, ], }
You don't have to use a document DB (like CouchDB,
MongoDB, etc.)
Schemaless Columns ID int Name text Weight uint Data json
1 Alice 76 { "nickname": "Al", "bgcolor": "#ff0033" }
But that must be slower... Right?
Comparison (never trust benchmarks) Loading 1.2 million records PostgreSQL MongoDB
76 sec 8 min Sequential scan PostgreSQL MongoDB 980 ms 980 ms Index scan (Postgres GINhash) PostgreSQL MongoDB 0.7 ms 1 ms
Load Shapes
Read-heavy Write-heavy Large size
Read-heavy Write-heavy Large size Wikipedia TV show page Minecraft Forums
Amazon Glacier Eventbrite Logging
Read-heavy Write-heavy Large size Offline storage Append formats In-memory cache
Many indexes Fewer indexes
Your load changes over time Scaling is not just a
flat multiplier
General Advice Write heavy → Fewer indexes Read heavy →
Denormalise Keep large data away from read/write heavy data Blob stores/filesystems are DBs too
Lessons They're near the end so you remember them.
Re-evaulate as you grow Different things matter at different sizes
Adding NULL columns is great Always prefer this if nothing
else
You'll need more than one DBMS But don't use too
many, you'll be swamped
Indexes aren't free You pay the price at write/restore time
Relational DBs are flexible They can do a lot more
than JOINing normalised tables
Thanks! Andrew Godwin @andrewgodwin eventbrite.com/jobs are hiring: