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
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
720
Taking Django's ORM Async
andrewgodwin
0
720
The Long Road To Asynchrony
andrewgodwin
0
650
The Scientist & The Engineer
andrewgodwin
1
760
Other Decks in Programming
See All in Programming
git worktree × Claude Code × MCP ~生成AI時代の並列開発フロー~
hisuzuya
1
420
AWS CDKの推しポイント 〜CloudFormationと比較してみた〜
akihisaikeda
3
310
Rubyでやりたい駆動開発 / Ruby driven development
chobishiba
1
340
Elixir で IoT 開発、 Nerves なら簡単にできる!?
pojiro
1
150
技術同人誌をMCP Serverにしてみた
74th
0
280
地方に住むエンジニアの残酷な現実とキャリア論
ichimichi
5
1.3k
Team topologies and the microservice architecture: a synergistic relationship
cer
PRO
0
1k
PHP 8.4の新機能「プロパティフック」から学ぶオブジェクト指向設計とリスコフの置換原則
kentaroutakeda
2
470
なぜ適用するか、移行して理解するClean Architecture 〜構造を超えて設計を継承する〜 / Why Apply, Migrate and Understand Clean Architecture - Inherit Design Beyond Structure
seike460
PRO
1
660
なぜ「共通化」を考え、失敗を繰り返すのか
rinchoku
1
470
Bytecode Manipulation 으로 생산성 높이기
bigstark
2
370
KotlinConf 2025 現地で感じたServer-Side Kotlin
n_takehata
1
230
Featured
See All Featured
Being A Developer After 40
akosma
90
590k
The Straight Up "How To Draw Better" Workshop
denniskardys
233
140k
[RailsConf 2023 Opening Keynote] The Magic of Rails
eileencodes
29
9.5k
GraphQLとの向き合い方2022年版
quramy
47
14k
Into the Great Unknown - MozCon
thekraken
39
1.9k
Java REST API Framework Comparison - PWX 2021
mraible
31
8.6k
How to train your dragon (web standard)
notwaldorf
92
6.1k
GraphQLの誤解/rethinking-graphql
sonatard
71
11k
Building a Modern Day E-commerce SEO Strategy
aleyda
41
7.3k
We Have a Design System, Now What?
morganepeng
53
7.7k
The MySQL Ecosystem @ GitHub 2015
samlambert
251
13k
Become a Pro
speakerdeck
PRO
28
5.4k
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: