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
Good Schema Design and Why It Matters
Search
Andrew Godwin
May 15, 2014
Programming
12
1.2k
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
Reconciling Everything
andrewgodwin
1
230
Django Through The Years
andrewgodwin
0
120
Writing Maintainable Software At Scale
andrewgodwin
0
360
A Newcomer's Guide To Airflow's Architecture
andrewgodwin
0
280
Async, Python, and the Future
andrewgodwin
2
570
How To Break Django: With Async
andrewgodwin
1
620
Taking Django's ORM Async
andrewgodwin
0
630
The Long Road To Asynchrony
andrewgodwin
0
560
The Scientist & The Engineer
andrewgodwin
1
650
Other Decks in Programming
See All in Programming
最高の構文木の設計 2024年版
yui_knk
7
2.4k
数値を文字列に整形する際の落とし穴とその解決策(iOSDC2024 ルーキーズLT) / iOSDC Japan 2024 Formatting Floating-Point Numbers
glassfiber
0
250
iOSの隠されたAPIを解明し、開発効率を向上させる方法/iOSDC24
noppefoxwolf
2
120
RAGの回答精度評価用のQAデータセットを生成AIに作らせた話
kurahara
0
210
BasicBasic認証
sadnessojisan
5
2.5k
LangChainの現在とv0.3にむけて
os1ma
3
650
労務ドメインを快適に開発する方法 / How to Comfortably Develop in the Labor Domain
yuki21
1
250
Wallet API, Verifier APIで実現するIDカード on iPhoneの世界
shitamori1272
1
320
楽しく簡単に!QRコードの読み取り機能を実装しよう
penguinsan_pg
1
190
快適な開発と高セキュリティを実現するCryptoKitを活用したCoreDataのデータ暗号化術
grandbig
1
300
REXML改善のその後
naitoh
0
150
詳解UIWindow
natmark
3
2k
Featured
See All Featured
Designing Dashboards & Data Visualisations in Web Apps
destraynor
226
52k
Typedesign – Prime Four
hannesfritz
38
2.3k
The Illustrated Children's Guide to Kubernetes
chrisshort
46
48k
Robots, Beer and Maslow
schacon
PRO
157
8.1k
For a Future-Friendly Web
brad_frost
173
9.3k
Speed Design
sergeychernyshev
18
400
Adopting Sorbet at Scale
ufuk
72
8.9k
Sharpening the Axe: The Primacy of Toolmaking
bcantrill
33
1.7k
Scaling GitHub
holman
458
140k
Principles of Awesome APIs and How to Build Them.
keavy
125
16k
Mobile First: as difficult as doing things right
swwweet
220
8.8k
Fight the Zombie Pattern Library - RWD Summit 2016
marcelosomers
230
17k
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: