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
470
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
markush
0
240
markush
0
19
markush
1
120
markush
0
250
markush
0
360
markush
0
300
markush
0
380
markush
1
50
markush
3
140
Other Decks in Technology
See All in Technology
khrd
1
670
akitok_
2
800
oracle4engineer
0
3.8k
iwashi
1
210
uzabase_saas_product
0
110
kaori_cho
1
290
eller86
1
240
ippey
2
210
dena_tech
15
3.5k
no24oka
0
100
kanaugust
PRO
0
120
ayatokura
0
100
Featured
See All Featured
carmenhchung
31
1.5k
hatefulcrawdad
257
17k
vanstee
117
4.9k
jrom
116
7.2k
keathley
20
710
morganepeng
18
1.2k
kneath
219
15k
destraynor
222
47k
caitiem20
308
17k
zenorocha
296
40k
morganepeng
93
14k
yeseniaperezcruz
302
31k
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