Rijgeoriënteerde opslag en partities

Queryprestaties verbeteren in PostgreSQL

Amy McCarty

Instructor

Soorten database-opslag

Rijgeoriënteerde opslag

  • Relatie tussen kolommen behouden

Kolomgeoriënteerde opslag

  • Relatie tussen rijen behouden
id name species age habitat received
01 Bob panda 2 Asia 2018
02 Sunny zebra 3 Africa 2018
03 Beco zebra 10 Africa 2017
04 Coco koala 5 Australia 2016
Queryprestaties verbeteren in PostgreSQL

Rijgeoriënteerd

Rijgeoriënteerde opslag

  • Relatie tussen kolommen behouden

 

id name species age habitat received
01 Bob panda 2 Asia 2018
Queryprestaties verbeteren in PostgreSQL

Kolomgeoriënteerd

Kolomgeoriënteerde opslag

  • Relatie tussen rijen behouden  

Twee tabellen. De eerste tabel is een lijst met ids en namen. 01 - Bob. 02 - Sunny. 03 - Beco. 04 - Coco. De tweede tabel heeft dezelfde ids met een lijst van soorten. 01 - panda. 02 - zebra. 03 - zebra. 04 - koala.

Queryprestaties verbeteren in PostgreSQL

Rijgeoriënteerde opslag

 

  • Eén rij staat op dezelfde locatie
  • Snel hele records toevoegen of verwijderen
  • Snel alle kolommen ophalen
    • Traag om alle rijen op te halen
Queryprestaties verbeteren in PostgreSQL

Rijen verminderen

Verminder het aantal rijen

  • WHERE-filter
  • INNER JOIN
  • DISTINCT
  • LIMIT
Queryprestaties verbeteren in PostgreSQL

Methoden voor rijgeoriënteerde databases

Partities

  • Manier om één (ouder) tabel te splitsen in meerdere, kleinere (kind-)tabellen

Indexen

  • Manier om gesorteerde kolomsleutels te maken voor snellere zoekacties

 

Partities en indexen gebruiken

  • Vereist inrichting en onderhoud
  • Bekend via databasebeheerder of documentatie
Queryprestaties verbeteren in PostgreSQL

Partitiestructuur

Een oudertabel met zoo-dieren met vier records: id, name, species, habitat en received. 01 - Bob - Panda - age - habitat - received. 02 - Sunny - Zebra - 3 - Africa - 2018. 03 - Beco - Zebra - 10 - Africa - 2017. 04 - Coco - Macaw - 5 - South America - 2016. Er zijn 3 kindtabellen die overeenkomen met de kolom habitat. De Asia-tabel heeft één record voor Bob. De Africa-tabel heeft twee records voor Sunny en Beco. De South America-tabel heeft één record voor Coco.

  • Odertabel
    • Zichtbaar in de database-frontend
    • Hier schrijf je queries
  • Kindtabellen
    • Niet zichtbaar in de database-frontend
    • Queries doorzoeken deze
Queryprestaties verbeteren in PostgreSQL

Partitiestructuur

Een oudertabel met zoo-dieren met vier records: id, name, species, habitat en received. 01 - Bob - Panda - age - habitat - received. 02 - Sunny - Zebra - 3 - Africa - 2018. 03 - Beco - Zebra - 10 - Africa - 2017. 04 - Coco - Macaw - 5 - South America - 2016. Er zijn 3 kindtabellen die overeenkomen met habitat. De Asia-tabel heeft één record voor Bob. De Africa-tabel heeft twee records voor Sunny en Beco. De South America-tabel heeft één record voor Coco. De Africa-tabel is rood omlijnd.

SELECT species
FROM zoo_animals
WHERE habitat = 'Africa'
Queryprestaties verbeteren in PostgreSQL

Overzicht van partities

Wat

  • Één tabel splitsen in meerdere kleinere tabellen

Waarom

  • Flexibele opslag
  • Snellere queries

Waar

  • Veelgebruikte filterkolommen
    • Datum, locatie
Queryprestaties verbeteren in PostgreSQL

Beoordeling query met partitie

Query planner

Meerdere koks rond een grote kookpot

EXPLAIN
SELECT species
FROM zoo_animals
WHERE habitat = 'Africa'

Queryplan

Seq Scan on zoo_animals  (cost=0.00..
17.70 rows=2 width=182)
  Filter: (state_code = 15)
  • Schattingen van kosten (tijd)
Queryprestaties verbeteren in PostgreSQL

Laten we oefenen!

Queryprestaties verbeteren in PostgreSQL

Preparing Video For Download...