Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Speaker Deck
PRO
Sign in
Sign up for free
Schemas and Databases in an Agile World
Andrew Godwin
April 24, 2014
Programming
4
210
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
A Newcomer's Guide To Airflow's Architecture
andrewgodwin
0
99
Async, Python, and the Future
andrewgodwin
1
380
How To Break Django: With Async
andrewgodwin
1
310
Taking Django's ORM Async
andrewgodwin
0
340
The Long Road To Asynchrony
andrewgodwin
0
390
The Scientist & The Engineer
andrewgodwin
1
380
Pioneering Real-Time
andrewgodwin
0
150
Just Add Await: Retrofitting Async Into Django
andrewgodwin
2
1.1k
Terrain, Art, Python and LiDAR
andrewgodwin
1
240
Other Decks in Programming
See All in Programming
Managing Error Messages with your Oracle Database REST APIs
thatjeffsmith
0
150
Java初心者が知っておくべきプログラミングのこと - JJUG CCC 2022 Spring
kishida
5
550
即、New Relic / New Relic NOW!
uzulla
0
330
Get Ready for Jakarta EE 10
ivargrimstad
0
760
Jetpack Compose, 어디까지 알고 있을까?
jisungbin
0
120
Swift Regex
usamik26
0
190
チームでカレーを作ろう!アジャイルカレークッキング
akitotsukahara
0
850
Why Airflow? & What's new in Airflow 2.3?
kaxil
0
120
Independently together: better developer experience & App performance
bcinarli
0
180
io22 extended What's new in app performance
veronikapj
0
340
How we run a Realtime Puzzle Fighting Game on AWS Serverless
falken
0
250
CSE360 Tutorial 07
javiergs
PRO
0
100
Featured
See All Featured
What’s in a name? Adding method to the madness
productmarketing
11
1.6k
Infographics Made Easy
chrislema
233
17k
Pencils Down: Stop Designing & Start Developing
hursman
112
9.8k
How to name files
jennybc
40
61k
A designer walks into a library…
pauljervisheath
196
16k
Design and Strategy: How to Deal with People Who Don’t "Get" Design
morganepeng
105
16k
Producing Creativity
orderedlist
PRO
334
37k
Creating an realtime collaboration tool: Agile Flush - .NET Oxford
marcduiker
5
510
Bootstrapping a Software Product
garrettdimon
296
110k
Automating Front-end Workflow
addyosmani
1351
200k
Art Directing for the Web. Five minutes with CSS Template Areas
malarkey
196
9.4k
Why You Should Never Use an ORM
jnunemaker
PRO
47
7.6k
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: