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
260
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
320
Django Through The Years
andrewgodwin
0
210
Writing Maintainable Software At Scale
andrewgodwin
0
450
A Newcomer's Guide To Airflow's Architecture
andrewgodwin
0
360
Async, Python, and the Future
andrewgodwin
2
680
How To Break Django: With Async
andrewgodwin
1
730
Taking Django's ORM Async
andrewgodwin
0
730
The Long Road To Asynchrony
andrewgodwin
0
660
The Scientist & The Engineer
andrewgodwin
1
780
Other Decks in Programming
See All in Programming
アルテニア コンサル/ITエンジニア向け 採用ピッチ資料
altenir
0
100
そのAPI、誰のため? Androidライブラリ設計における利用者目線の実践テクニック
mkeeda
2
260
print("Hello, World")
eddie
1
520
Cache Me If You Can
ryunen344
1
600
AIと私たちの学習の変化を考える - Claude Codeの学習モードを例に
azukiazusa1
8
3.4k
TDD 実践ミニトーク
contour_gara
1
290
How Android Uses Data Structures Behind The Scenes
l2hyunwoo
0
390
Vue・React マルチプロダクト開発を支える Vite
andpad
0
110
Rancher と Terraform
fufuhu
2
240
請來的 AI Agent 同事們在寫程式時,怎麼用 pytest 去除各種幻想與盲點
keitheis
0
100
パッケージ設計の黒魔術/Kyoto.go#63
lufia
3
430
Improving my own Ruby thereafter
sisshiki1969
1
160
Featured
See All Featured
Reflections from 52 weeks, 52 projects
jeffersonlam
352
21k
The Language of Interfaces
destraynor
161
25k
Let's Do A Bunch of Simple Stuff to Make Websites Faster
chriscoyier
507
140k
A Modern Web Designer's Workflow
chriscoyier
696
190k
jQuery: Nuts, Bolts and Bling
dougneiner
64
7.9k
Documentation Writing (for coders)
carmenintech
74
5k
ピンチをチャンスに:未来をつくるプロダクトロードマップ #pmconf2020
aki_iinuma
126
53k
VelocityConf: Rendering Performance Case Studies
addyosmani
332
24k
Designing for Performance
lara
610
69k
KATA
mclloyd
32
14k
Exploring the Power of Turbo Streams & Action Cable | RailsConf2023
kevinliebholz
34
6k
Git: the NoSQL Database
bkeepers
PRO
431
66k
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: