Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Speaker Deck
PRO
Sign in
Sign up for free
Thoughts About Normal and Abnormal Data (PyCon UK 2017)
Markus H
October 27, 2017
Technology
0
10k
Thoughts About Normal and Abnormal Data (PyCon UK 2017)
Speaker notes at
https://markusholtermann.eu/2017/10/thoughts-about-normal-and-abnormal-data/
Markus H
October 27, 2017
Tweet
Share
More Decks by Markus H
See All by Markus H
🐍 ❤️ 🦀 — Python loves Rust
markush
0
98
Knock! Knock! Who's There?
markush
0
42
An Introduction To Kubernetes ☸
markush
0
61
Writing Safe Database Migrations (DjangoCon Europe 2021)
markush
0
11k
A Pony On The Move: How Migrations Work In Django 🐎
markush
0
11k
All Hands on Deck — Handling Security Issues
markush
0
11k
Logging Rethought 2: The Actions of Frank Taylor Jr. (PyCon UK 2019)
markush
0
35
Logging Rethought 2: The Actions of Frank Taylor Jr. (PyCon Australia 2019)
markush
1
150
Logging Rethought 2: The Actions of Frank Taylor Jr. (DjangoCon Europe 2019)
markush
0
11k
Other Decks in Technology
See All in Technology
DNS権威サーバのクラウドサービス向けに行われた攻撃および対策 / DNS Pseudo-Random Subdomain Attack and mitigations
kazeburo
5
1.2k
グローバルチームことはじめ / Bootstrapping a global team
tasshi
1
650
FlexScan HD2452Wの 後継を探して
tring
0
5.4k
230125 モニターマウントLT ITガジェット翁(Ryu.Cyber)さん
comucal
PRO
0
4.1k
エンタープライズ領域でのブロックチェーン・インターオペラビリティの発展 / Enterprise Blockchain Interoperability
gakumura
0
130
JAWS-UG 横浜 #54 資料
takakuni
0
190
230120 ガンダムの事例にみる自動化の対象 Haruka Oh!さん
comucal
PRO
0
120
地方自治体業務あるある ーアナログ最適化編-
y150saya
1
110
230125 古いタブレットの活用 かーでぃさん
comucal
PRO
0
13k
データベースの発表には RDBMS 以外もありますよ
maroon1st
0
230
目指せCoverage100%! AutoScale環境におけるSavings Plans購入戦略 / JAWS-UG_SRE_Coverage
taishin
0
420
それでもどうしてRecoilを使うのか / Harajuku.ts Meetup Recoil
okunokentaro
13
3.7k
Featured
See All Featured
The Pragmatic Product Professional
lauravandoore
21
3.4k
Java REST API Framework Comparison - PWX 2021
mraible
PRO
13
5.4k
Atom: Resistance is Futile
akmur
256
24k
Fashionably flexible responsive web design (full day workshop)
malarkey
396
63k
Documentation Writing (for coders)
carmenintech
51
2.9k
Building Adaptive Systems
keathley
27
1.3k
Dealing with People You Can't Stand - Big Design 2015
cassininazir
351
21k
Agile that works and the tools we love
rasmusluckow
320
20k
The Illustrated Children's Guide to Kubernetes
chrisshort
22
42k
Designing with Data
zakiwarfel
91
4.2k
実際に使うSQLの書き方 徹底解説 / pgcon21j-tutorial
soudai
44
14k
The Brand Is Dead. Long Live the Brand.
mthomps
48
2.9k
Transcript
Thoughts About Normal and Abnormal Data Markus Holtermann @m_holtermann markusholtermann.eu
@m_holtermann I am Markus Holtermann • Senior Software Engineer at
LaterPay • Django Core Developer
@m_holtermann How do we store our data?
@m_holtermann Files CC-BY-NC 2.0 by Tim Gee https://flic.kr/p/rZm63
@m_holtermann Document Stores CC-BY-SA 4.0 by Susan Gerbic https://commons.wikimedia.org/wiki/File%3AArchive_Room.JPG
@m_holtermann Copyright Geek Batman https://www.youtube.com/watch?v=gPDx_IwdYMY
@m_holtermann Name Home planet Gender Padmé Naboo Female Luke Tatooine
Male Leia Alderaan, Naboo Female
@m_holtermann First Normal Form (1NF)
@m_holtermann PersonID Name Home planet Gender 1 Padmé Naboo Female
2 Luke Tatooine Male 3 Leia Alderaan Female 3 Leia Naboo Female
@m_holtermann PersonID Name Home planet Gender 3 Leia Alderaan Female
3 Leia Naboo Male Update Anomalies
@m_holtermann Second Normal Form (2NF)
@m_holtermann PersonID Name Home planet Gender 1 Padmé Naboo Female
2 Luke Tatooine Male 3 Leia Alderaan Female 3 Leia Naboo Female
@m_holtermann PersonID Planet Name 1 Naboo 2 Tatooine 3 Alderaan
3 Naboo PersonID Name Gender 1 Padmé Female 2 Luke Male 3 Leia Female
@m_holtermann PersonID Planet Name 1 Naboo 2 Tatooine 3 Alderaan
3 Naboo ??? Dagobah Insert Anomalies
@m_holtermann Deletion Anomalies PersonID Planet Name 1 Naboo 2 Tatooine
3 Alderaan 3 Naboo PersonID Name Gender 1 Padmé Female 2 Luke Male 3 Leia Female
@m_holtermann Third Normal Form (3NF)
@m_holtermann PlanetID Name Water 10 Naboo 85% 11 Tatooine 1%
12 Alderaan 78% 13 Dagobah 88% PersonID Name Gender 1 Padmé Female 2 Luke Male 3 Leia Female PersonID PlanetID 1 10 2 11 3 10 3 12
@m_holtermann Database normalization is great!
@m_holtermann Always?
@m_holtermann Yet Another Wiki
@m_holtermann Page + PageID Name Slug Revision + RevisionID PageID
Text Date Database Schema
@m_holtermann Task 1: Fetch a single page and its current
revision
@m_holtermann Task 2: Fetch all page titles and the date
of their current revision
Task 1: Fetch a single page SELECT * FROM page
INNER JOIN revision ON page.page_id = revision.page_id WHERE page.slug = 'some-slug' ORDER BY revision.date DESC LIMIT 1;
Task 2: Fetch all pages SELECT page.name, last_revs.date FROM page
INNER JOIN ( SELECT revision.page_id, MAX(revision.date) date FROM revision GROUP BY revision.page_id ) last_revs ON page.page_id = last_revs.page_id;
@m_holtermann Benchmark Environment • Intel i7-6600U, 2.60GHz • 8 GB
Memory • PostgreSQL 9.6.5 • 10k pages, 6m revisions
@m_holtermann Task 1: Fetch a single page Concurrent queries 10
Pages per connection 1000 Queries per page 10 Queries total 100000
@m_holtermann Task 2: Fetch all pages Concurrent queries 1 Queries
per connection 10 Queries total 10
@m_holtermann Task 1: Fetch a single page
@m_holtermann Task 2: Fetch all pages
@m_holtermann Rae Knowler https://speakerdeck.com/bellisk/unsafe-at-any-speed-pycon-uk-26th-october-2017
@m_holtermann Database Schema Page + PageID Name Slug LastRevision Revision
+ RevisionID PageID Text Date
Task 1: Fetch a single page SELECT * FROM page
INNER JOIN revision ON page.last_revision_id = revision.revision_id WHERE page.slug = 'some-slug';
Task 2: Fetch all pages SELECT page.name, revision.date FROM page
INNER JOIN revision ON page.last_revision_id = revision.revision_id;
@m_holtermann Task 1: Fetch a single page
@m_holtermann Task 2: Fetch all pages
@m_holtermann Conclusion
Thanks Markus Holtermann @m_holtermann markusholtermann.eu