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
230
Django Through The Years
andrewgodwin
0
110
Writing Maintainable Software At Scale
andrewgodwin
0
350
A Newcomer's Guide To Airflow's Architecture
andrewgodwin
0
270
Async, Python, and the Future
andrewgodwin
2
560
How To Break Django: With Async
andrewgodwin
1
610
Taking Django's ORM Async
andrewgodwin
0
620
The Long Road To Asynchrony
andrewgodwin
0
540
The Scientist & The Engineer
andrewgodwin
1
630
Other Decks in Programming
See All in Programming
なぜ宣言的 UI は壊れにくいのか / Why declarative UI is less fragile
uenitty
29
13k
Introduction of Happy Eyeballs Version 2 (RFC8305) to the Socket library
coe401_
1
220
君たちはどうコードをレビューする (される) か / 大吉祥寺.pm
utgwkk
15
8.5k
Folding Cheat Sheet #7
philipschwarz
PRO
0
150
Rubyのパフォーマンスプロファイリングの改善 / Enhancing performance profiling for Ruby
osyoyu
1
410
feature環境をGitHub ActionsとCloudFormationでいい感じに管理する
nealle
2
310
Ruby メモリ管理 プログラミング
megmogmog1965
0
130
Modern Angular: Renovation for Your Applications
manfredsteyer
PRO
0
140
Microservices rules (July 2024) : what good looks like
cer
PRO
0
1.6k
今こそ始める、CDKコンストラクトライブラリ開発 ― 入門から実践まで
tmokmss
1
930
Advanced App Shrinking Techniques
cbeyls
2
150
ピグパーティにおけるMongoDB CommunityバージョンからAtlasへの移行事例
10969hotaka
0
130
Featured
See All Featured
Unsuck your backbone
ammeep
666
57k
How GitHub Uses GitHub to Build GitHub
holman
471
290k
Building Better People: How to give real-time feedback that sticks.
wjessup
357
18k
From Idea to $5000 a Month in 5 Months
shpigford
377
46k
Templates, Plugins, & Blocks: Oh My! Creating the theme that thinks of everything
marktimemedia
23
1.9k
StorybookのUI Testing Handbookを読んだ
zakiyama
15
4.9k
Why You Should Never Use an ORM
jnunemaker
PRO
51
8.9k
A better future with KSS
kneath
231
17k
Build The Right Thing And Hit Your Dates
maggiecrowley
28
2.2k
Keith and Marios Guide to Fast Websites
keithpitt
408
22k
How to Create Impact in a Changing Tech Landscape [PerfNow 2023]
tammyeverts
34
1.9k
Imperfection Machines: The Place of Print at Facebook
scottboms
262
13k
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: