Thoughts About Normal and Abnormal Data (PyCon UK 2017)

Cd7648c536b4dbe940246b74044fbc52?s=47 Markus H
October 27, 2017

Thoughts About Normal and Abnormal Data (PyCon UK 2017)

Cd7648c536b4dbe940246b74044fbc52?s=128

Markus H

October 27, 2017
Tweet

Transcript

  1. 9.

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

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

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

    2 Luke Tatooine Male 3 Leia Alderaan Female 3 Leia Naboo Female
  3. 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
  4. 14.
  5. 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
  6. 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
  7. 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;
  8. 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;
  9. 26.

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

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

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

    Pages per connection 1000 Queries per page 10 Queries total 100000
  11. 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';
  12. 34.

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

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