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
260
Django Through The Years
andrewgodwin
0
160
Writing Maintainable Software At Scale
andrewgodwin
0
390
A Newcomer's Guide To Airflow's Architecture
andrewgodwin
0
310
Async, Python, and the Future
andrewgodwin
2
610
How To Break Django: With Async
andrewgodwin
1
660
Taking Django's ORM Async
andrewgodwin
0
670
The Long Road To Asynchrony
andrewgodwin
0
590
The Scientist & The Engineer
andrewgodwin
1
690
Other Decks in Programming
See All in Programming
Go の GC の不得意な部分を克服したい
taiyow
2
760
ソフトウェアの振る舞いに着目し 複雑な要件の開発に立ち向かう
rickyban
0
890
短期間での新規プロダクト開発における「コスパの良い」Goのテスト戦略」 / kamakura.go
n3xem
2
160
良いユニットテストを書こう
mototakatsu
4
1.5k
Recoilを剥がしている話
kirik
5
6.6k
フロントエンドのディレクトリ構成どうしてる? Feature-Sliced Design 導入体験談
osakatechlab
8
4.1k
CSC305 Lecture 25
javiergs
PRO
0
130
事業成長を爆速で進めてきたプロダクトエンジニアたちの成功談・失敗談
nealle
3
1.4k
Асинхронность неизбежна: как мы проектировали сервис уведомлений
lamodatech
0
600
似たもの同士のPerlとPHP
uzulla
1
130
return文におけるstd::moveについて
onihusube
1
610
CSC509 Lecture 14
javiergs
PRO
0
130
Featured
See All Featured
Into the Great Unknown - MozCon
thekraken
33
1.5k
How to train your dragon (web standard)
notwaldorf
88
5.7k
Visualizing Your Data: Incorporating Mongo into Loggly Infrastructure
mongodb
44
9.3k
For a Future-Friendly Web
brad_frost
175
9.4k
Testing 201, or: Great Expectations
jmmastey
40
7.1k
How to Ace a Technical Interview
jacobian
276
23k
Helping Users Find Their Own Way: Creating Modern Search Experiences
danielanewman
29
2.3k
Let's Do A Bunch of Simple Stuff to Make Websites Faster
chriscoyier
507
140k
Building Your Own Lightsaber
phodgson
103
6.1k
Learning to Love Humans: Emotional Interface Design
aarron
273
40k
The Power of CSS Pseudo Elements
geoffreycrofte
73
5.4k
Gamification - CAS2011
davidbonilla
80
5.1k
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: