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
330
Django Through The Years
andrewgodwin
0
220
Writing Maintainable Software At Scale
andrewgodwin
0
460
A Newcomer's Guide To Airflow's Architecture
andrewgodwin
0
370
Async, Python, and the Future
andrewgodwin
2
680
How To Break Django: With Async
andrewgodwin
1
740
Taking Django's ORM Async
andrewgodwin
0
740
The Long Road To Asynchrony
andrewgodwin
0
680
The Scientist & The Engineer
andrewgodwin
1
790
Other Decks in Programming
See All in Programming
実践Claude Code:20の失敗から学ぶAIペアプログラミング
takedatakashi
18
8.5k
Claude Agent SDK を使ってみよう
hyshu
0
1.4k
3年ぶりにコードを書いた元CTOが Claude Codeと30分でMVPを作った話
maikokojima
0
630
CSC305 Lecture 09
javiergs
PRO
0
310
あなたとKaigi on Rails / Kaigi on Rails + You
shimoju
0
190
理論と実務のギャップを超える
eycjur
0
180
What Spring Developers Should Know About Jakarta EE
ivargrimstad
0
460
はじめてのDSPy - 言語モデルを『プロンプト』ではなく『プログラミング』するための仕組み
masahiro_nishimi
4
14k
monorepo の Go テストをはやくした〜い!~最小の依存解決への道のり~ / faster-testing-of-monorepos
convto
2
540
Catch Up: Go Style Guide Update
andpad
0
250
バッチ処理を「状態の記録」から「事実の記録」へ
panda728
PRO
0
190
オープンソースソフトウェアへの解像度🔬
utam0k
17
3.1k
Featured
See All Featured
ピンチをチャンスに:未来をつくるプロダクトロードマップ #pmconf2020
aki_iinuma
127
54k
Bootstrapping a Software Product
garrettdimon
PRO
307
110k
Writing Fast Ruby
sferik
630
62k
[RailsConf 2023] Rails as a piece of cake
palkan
57
5.9k
Agile that works and the tools we love
rasmusluckow
331
21k
How To Stay Up To Date on Web Technology
chriscoyier
791
250k
The Language of Interfaces
destraynor
162
25k
Cheating the UX When There Is Nothing More to Optimize - PixelPioneers
stephaniewalter
285
14k
Facilitating Awesome Meetings
lara
57
6.6k
Music & Morning Musume
bryan
46
6.9k
Designing Dashboards & Data Visualisations in Web Apps
destraynor
231
53k
Building Flexible Design Systems
yeseniaperezcruz
329
39k
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: