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
Schemas and Databases in an Agile World
Search
Andrew Godwin
April 24, 2014
Programming
4
240
Schemas and Databases in an Agile World
A talk I gave at CRAFT 2014 in Budapest
Andrew Godwin
April 24, 2014
Tweet
Share
More Decks by Andrew Godwin
See All by Andrew Godwin
Reconciling Everything
andrewgodwin
1
280
Django Through The Years
andrewgodwin
0
180
Writing Maintainable Software At Scale
andrewgodwin
0
410
A Newcomer's Guide To Airflow's Architecture
andrewgodwin
0
330
Async, Python, and the Future
andrewgodwin
2
630
How To Break Django: With Async
andrewgodwin
1
700
Taking Django's ORM Async
andrewgodwin
0
700
The Long Road To Asynchrony
andrewgodwin
0
620
The Scientist & The Engineer
andrewgodwin
1
730
Other Decks in Programming
See All in Programming
Introduction to C Extensions
sylph01
3
150
Accelerate your key learnings of scaling modern Android apps
aldefy
0
210
AWS Step Functions は CDK で書こう!
konokenj
5
990
AI Agentを利用したAndroid開発について
yuchan2215
0
180
気がついたら子供が社会人になって 自分と同じモバイルアプリエンジニアになった件 / Parent-Child Engineers
koishi
0
220
Swift Testingのモチベを上げたい
stoticdev
2
260
JAWS Days 2025のインフラ
komakichi
1
420
令和トラベルにおけるコンテンツ生成AIアプリケーション開発の実践
ippo012
1
210
AI時代のプログラミング教育 / programming education in ai era
kishida
19
4.8k
良いコードレビューとは
danimal141
10
10k
Amazon Bedrockマルチエージェントコラボレーションを諦めてLangGraphに入門してみた
akihisaikeda
1
200
Devin入門 〜月500ドルから始まるAIチームメイトとの開発生活〜 / Introduction Devin 〜Development With AI Teammates〜
rkaga
6
2.2k
Featured
See All Featured
Speed Design
sergeychernyshev
28
840
The Psychology of Web Performance [Beyond Tellerrand 2023]
tammyeverts
46
2.4k
Music & Morning Musume
bryan
46
6.4k
What's in a price? How to price your products and services
michaelherold
244
12k
Refactoring Trust on Your Teams (GOTO; Chicago 2020)
rmw
34
2.9k
Mobile First: as difficult as doing things right
swwweet
223
9.5k
Building Adaptive Systems
keathley
40
2.4k
Bash Introduction
62gerente
611
210k
Making Projects Easy
brettharned
116
6.1k
RailsConf & Balkan Ruby 2019: The Past, Present, and Future of Rails at GitHub
eileencodes
134
33k
Gamification - CAS2011
davidbonilla
80
5.2k
Practical Orchestrator
shlominoach
186
10k
Transcript
Andrew Godwin @andrewgodwin DATABASES SCHEMAS in an agile world &
Andrew Godwin Core Developer Senior Engineer
Schemas Explicit & 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, }
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
Workflows 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
Reasonable queries SELECT id, title FROM articles WHERE attributes->'author'->>'first_name' =
'cory'
A hybrid solution Normal columns for more static data (e.g.
id, title) Schemaless blobs for variable data (e.g. styling)
Lessons
Schemas are your friend Explicit definitions or checks will save
you
Read only mode It makes DB downtime more palatable
Work to your DBs strengths It's not just a dumb
data store
Coordinate your team A little coorindation pays big dividends
Try hybrid schemas Particularly good for CMSs or enterprise software
Thanks! Andrew Godwin @andrewgodwin eventbrite.com/jobs are hiring: