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
220
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
200
Django Through The Years
andrewgodwin
0
86
Writing Maintainable Software At Scale
andrewgodwin
0
330
A Newcomer's Guide To Airflow's Architecture
andrewgodwin
0
240
Async, Python, and the Future
andrewgodwin
2
540
How To Break Django: With Async
andrewgodwin
1
570
Taking Django's ORM Async
andrewgodwin
0
580
The Long Road To Asynchrony
andrewgodwin
0
510
The Scientist & The Engineer
andrewgodwin
1
570
Other Decks in Programming
See All in Programming
GraphQLサーバの構成要素を整理する #ハッカー鮨 #tsukijigraphql / graphql server technology selection
izumin5210
4
820
ADRを一年運用してみた/adr_after_a_year
hanhan1978
7
2.4k
使ってみよう Azure AI Document Intelligence
kosmosebi
2
300
Ruby Function Composition
bkuhlmann
1
330
try! Swift Tokyo 2024 参加報告 / try! Swift Tokyo 2024 Report
hironytic
0
200
From Spring Boot 2 to Spring Boot 3 with Java 22 and Jakarta EE
ivargrimstad
0
1.2k
Rethinking UI building strategies @ SFI 2024
letelete
0
270
try!Swift Tokyo 2024 参加報告 LT
akidon0000
1
220
Goのmultiple errorsについて (2024年4月版)
syumai
3
650
新宿ダンジョンを可視化してみた
satoshi7190
2
240
入門 AWS Amplify Gen2 / Introduction to AWS Amplify Gen2
genkiogasawara
1
330
AWS Application Composerで始める、 サーバーレスなデータ基盤構築 / 20240406-jawsug-hokuriku-shinkansen
kasacchiful
1
260
Featured
See All Featured
GraphQLとの向き合い方2022年版
quramy
32
12k
Put a Button on it: Removing Barriers to Going Fast.
kastner
58
3k
Rails Girls Zürich Keynote
gr2m
91
13k
VelocityConf: Rendering Performance Case Studies
addyosmani
320
23k
Intergalactic Javascript Robots from Outer Space
tanoku
266
26k
Teambox: Starting and Learning
jrom
128
8.4k
The MySQL Ecosystem @ GitHub 2015
samlambert
243
12k
Statistics for Hackers
jakevdp
789
220k
Helping Users Find Their Own Way: Creating Modern Search Experiences
danielanewman
20
1.9k
Building Effective Engineering Teams - LeadDev
addyosmani
28
1.8k
Fantastic passwords and where to find them - at NoRuKo
philnash
37
2.5k
Testing 201, or: Great Expectations
jmmastey
28
6.3k
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: