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
Writing Maintainable Software At Scale
andrewgodwin
0
190
A Newcomer's Guide To Airflow's Architecture
andrewgodwin
0
150
Async, Python, and the Future
andrewgodwin
2
440
How To Break Django: With Async
andrewgodwin
1
420
Taking Django's ORM Async
andrewgodwin
0
400
The Long Road To Asynchrony
andrewgodwin
0
420
The Scientist & The Engineer
andrewgodwin
1
440
Pioneering Real-Time
andrewgodwin
0
190
Just Add Await: Retrofitting Async Into Django
andrewgodwin
2
1.2k
Other Decks in Programming
See All in Programming
Hono v3 - Do Everything, Run Anywhere, But Small, And Faster
yusukebe
4
130
OSC大阪 パスワード認証は人類には早すぎる ~ IDaaSを使ったソーシャルログインのすすめ ~
authyasan
7
1.3k
2023年にクル(かもしれない)通信ミドルウェア技術(仮)
s_hosoai
0
180
Swift Expression Macros: a practical introduction
kishikawakatsumi
2
720
Cloudflare WorkersでGoを動かすライブラリを作っている話
syumai
1
310
レガシーフレームワークからの移行
ug
0
110
Remote SSHで行うVS Codeリモートホスト開発とトラブルシューティング
smt7174
1
440
Workshop on Jetpack compose
aldefy
0
140
T3 Stack and TypeScript ecosystem
quramy
3
720
新卒でサービス立ち上げから Hasuraを使って3年経った振り返り
yutorin
0
220
状態ってなに?🙃
taro28
0
260
Amazon QuickSightのアップデート -re:Invent 2022の復習&2022年ハイライト-
shogo452
0
210
Featured
See All Featured
ReactJS: Keep Simple. Everything can be a component!
pedronauck
657
120k
Easily Structure & Communicate Ideas using Wireframe
afnizarnur
182
15k
The Straight Up "How To Draw Better" Workshop
denniskardys
226
130k
No one is an island. Learnings from fostering a developers community.
thoeni
12
1.5k
Designing the Hi-DPI Web
ddemaree
273
32k
Typedesign – Prime Four
hannesfritz
34
1.5k
How To Stay Up To Date on Web Technology
chriscoyier
779
250k
Streamline your AJAX requests with AmplifyJS and jQuery
dougneiner
128
8.8k
Learning to Love Humans: Emotional Interface Design
aarron
263
38k
Raft: Consensus for Rubyists
vanstee
130
5.7k
Fashionably flexible responsive web design (full day workshop)
malarkey
396
63k
Building Adaptive Systems
keathley
27
1.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: