Thoughts About Normal and Abnormal Data (PyCon UK 2017)

Transcript

1. 1.

   Thoughts About Normal and Abnormal Data Markus Holtermann @m_holtermann markusholtermann.eu

2. 2.

   @m_holtermann I am Markus Holtermann • Senior Software Engineer at

   LaterPay • Django Core Developer
3. 3.

   @m_holtermann How do we store our data?

4. 4.

   @m_holtermann Files CC-BY-NC 2.0 by Tim Gee https://flic.kr/p/rZm63

5. 5.

   @m_holtermann Document Stores CC-BY-SA 4.0 by Susan Gerbic https://commons.wikimedia.org/wiki/File%3AArchive_Room.JPG

6. 6.

   @m_holtermann Copyright Geek Batman https://www.youtube.com/watch?v=gPDx_IwdYMY

7. 7.

   @m_holtermann Name Home planet Gender Padmé Naboo Female Luke Tatooine

   Male Leia Alderaan, Naboo Female
8. 8.

   @m_holtermann First Normal Form (1NF)

9. 9.

   @m_holtermann PersonID Name Home planet Gender 1 Padmé Naboo Female

   2 Luke Tatooine Male 3 Leia Alderaan Female 3 Leia Naboo Female
10. 10.

    @m_holtermann PersonID Name Home planet Gender 3 Leia Alderaan Female

    3 Leia Naboo Male Update Anomalies
11. 11.

    @m_holtermann Second Normal Form (2NF)

12. 12.

    @m_holtermann PersonID Name Home planet Gender 1 Padmé Naboo Female

    2 Luke Tatooine Male 3 Leia Alderaan Female 3 Leia Naboo Female
13. 13.

    @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
14. 14.

    @m_holtermann PersonID Planet Name 1 Naboo 2 Tatooine 3 Alderaan

    3 Naboo ??? Dagobah Insert Anomalies
15. 15.

    @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
16. 16.

    @m_holtermann Third Normal Form (3NF)

17. 17.

    @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
18. 18.

    @m_holtermann Database normalization is great!

19. 19.

    @m_holtermann Always?

20. 20.

    @m_holtermann Yet Another Wiki

21. 21.

    @m_holtermann Page + PageID Name Slug Revision + RevisionID PageID

    Text Date Database Schema
22. 22.

    @m_holtermann Task 1: Fetch a single page and its current

    revision
23. 23.

    @m_holtermann Task 2: Fetch all page titles and the date

    of their current revision
24. 24.

    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;
25. 25.

    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;
26. 26.

    @m_holtermann Benchmark Environment • Intel i7-6600U, 2.60GHz • 8 GB

    Memory • PostgreSQL 9.6.5 • 10k pages, 6m revisions
27. 27.

    @m_holtermann Task 1: Fetch a single page Concurrent queries 10

    Pages per connection 1000 Queries per page 10 Queries total 100000
28. 28.

    @m_holtermann Task 2: Fetch all pages Concurrent queries 1 Queries

    per connection 10 Queries total 10
29. 29.

    @m_holtermann Task 1: Fetch a single page

30. 30.

    @m_holtermann Task 2: Fetch all pages

31. 31.

    @m_holtermann Rae Knowler https://speakerdeck.com/bellisk/unsafe-at-any-speed-pycon-uk-26th-october-2017

32. 32.

    @m_holtermann Database Schema Page + PageID Name Slug LastRevision Revision

    + RevisionID PageID Text Date
33. 33.

    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';
34. 34.

    Task 2: Fetch all pages SELECT page.name, revision.date FROM page

    INNER JOIN revision ON page.last_revision_id = revision.revision_id;
35. 35.

    @m_holtermann Task 1: Fetch a single page

36. 36.

    @m_holtermann Task 2: Fetch all pages

37. 37.

    @m_holtermann Conclusion

38. 38.

    Thanks Markus Holtermann @m_holtermann markusholtermann.eu