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
240
Django Through The Years
andrewgodwin
0
130
Writing Maintainable Software At Scale
andrewgodwin
0
370
A Newcomer's Guide To Airflow's Architecture
andrewgodwin
0
280
Async, Python, and the Future
andrewgodwin
2
580
How To Break Django: With Async
andrewgodwin
1
630
Taking Django's ORM Async
andrewgodwin
0
650
The Long Road To Asynchrony
andrewgodwin
0
570
The Scientist & The Engineer
andrewgodwin
1
660
Other Decks in Programming
See All in Programming
Subclassing, Composition, Python, and You
hynek
3
170
自分だけの世界を創るクリエイティブコーディング / Creative Coding: Creating Your Own World
chobishiba
2
1.7k
The Myth of the Modular Monolith - Day 2 Keynote - Rails World 2024
eileencodes
10
1.5k
メルカリ ハロ アプリの技術スタック
atsumo
2
810
How to debug Xdebug... or any other weird bug in PHP
dunglas
2
1k
Unlocking Python's Core Magic
leew
0
130
もう実家に手頃な情シス娘は不要!Bedrockでもう一人の娘を作る
komakichi
0
110
Kubernetes上でOracle_Databaseの運用を楽にするOraOperatorの紹介
nnaka2992
0
150
Taking LLMs out of the black box: A practical guide to human-in-the-loop distillation
inesmontani
PRO
3
1.2k
Compose Multiplatform과 Ktor로 플랫폼의 경계를 넘어보자
kwakeuijin
0
270
선언형 UI를 학습할 때 알아둬야하는 키워드들
l2hyunwoo
0
140
PHPを書く理由、PHPを書いていて良い理由 / Reasons to write PHP and why it is good to write PHP
seike460
PRO
5
470
Featured
See All Featured
Fight the Zombie Pattern Library - RWD Summit 2016
marcelosomers
231
17k
The Brand Is Dead. Long Live the Brand.
mthomps
53
38k
Creatively Recalculating Your Daily Design Routine
revolveconf
217
12k
From Idea to $5000 a Month in 5 Months
shpigford
381
46k
The Myth of the Modular Monolith - Day 2 Keynote - Rails World 2024
eileencodes
10
1.5k
Practical Orchestrator
shlominoach
186
10k
10 Git Anti Patterns You Should be Aware of
lemiorhan
653
59k
The Invisible Side of Design
smashingmag
297
50k
Stop Working from a Prison Cell
hatefulcrawdad
267
20k
Easily Structure & Communicate Ideas using Wireframe
afnizarnur
191
16k
Designing Dashboards & Data Visualisations in Web Apps
destraynor
228
52k
VelocityConf: Rendering Performance Case Studies
addyosmani
325
23k
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: