Archiviazione per righe e partizioni

Migliorare le prestazioni delle query in PostgreSQL

Amy McCarty

Instructor

Tipi di archiviazione nei database

Archiviazione orientata alle righe

  • Si mantiene la relazione tra colonne

Archiviazione orientata alle colonne

  • Si mantiene la relazione tra righe
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
Migliorare le prestazioni delle query in PostgreSQL

Orientata alle righe

Archiviazione orientata alle righe

  • Si mantiene la relazione tra colonne

 

id name species age habitat received
01 Bob panda 2 Asia 2018
Migliorare le prestazioni delle query in PostgreSQL

Orientata alle colonne

Archiviazione orientata alle colonne

  • Si mantiene la relazione tra righe  

Due tabelle. La prima è un elenco di id e nomi. 01 - Bob. 02 - Sunny. 03 - Beco. 04 - Coco. La seconda ha gli stessi id con le specie. 01 - panda. 02 - zebra. 03 - zebra. 04 - koala.

Migliorare le prestazioni delle query in PostgreSQL

Archiviazione orientata alle righe

 

  • Un’intera riga è nello stesso punto
  • Veloce aggiungere o cancellare record interi
  • Rapida nel restituire tutte le colonne
    • Lenta nel restituire tutte le righe
Migliorare le prestazioni delle query in PostgreSQL

Ridurre le righe

Riduci il numero di righe

  • Filtro WHERE
  • INNER JOIN
  • DISTINCT
  • LIMIT
Migliorare le prestazioni delle query in PostgreSQL

Metodi nei database orientati alle righe

Partizioni

  • Metodo per dividere una tabella (padre) in molte tabelle più piccole (figlie)

Indici

  • Metodo per creare chiavi di colonna ordinate per velocizzare la ricerca

 

Uso di partizioni e indici

  • Richiedono setup e manutenzione
  • L’esistenza è nota da admin del database o documentazione
Migliorare le prestazioni delle query in PostgreSQL

Struttura delle partizioni

Una tabella padre di animali dello zoo con quattro record che mostrano id, nome, specie, habitat e anno di arrivo. 01 - Bob - Panda - età - habitat - arrivo. 02 - Sunny - Zebra - 3 - Africa - 2018. 03 - Beco - Zebra - 10 - Africa - 2017. 04 - Coco - Ara - 5 - Sud America - 2016. Ci sono 3 tabelle figlie corrispondenti alla colonna habitat. La tabella Asia ha un record per Bob. La tabella Africa ha due record per Sunny e Beco. La tabella Sud America ha un record per Coco.

  • Tabella padre
    • Visibile nel front-end del database
    • Scrivi query
  • Tabelle figlie
    • Non visibili nel front-end del database
    • Le query cercano qui
Migliorare le prestazioni delle query in PostgreSQL

Struttura delle partizioni

Una tabella padre di animali dello zoo con quattro record che mostrano id, nome, specie, habitat e anno di arrivo. 01 - Bob - Panda - età - habitat - arrivo. 02 - Sunny - Zebra - 3 - Africa - 2018. 03 - Beco - Zebra - 10 - Africa - 2017. 04 - Coco - Ara - 5 - Sud America - 2016. Ci sono 3 tabelle figlie corrispondenti alla colonna habitat. La tabella Asia ha un record per Bob. La tabella Africa ha due record per Sunny e Beco. La tabella Sud America ha un record per Coco. La tabella Africa è evidenziata in rosso.

SELECT species
FROM zoo_animals
WHERE habitat = 'Africa'
Migliorare le prestazioni delle query in PostgreSQL

Panoramica sulle partizioni

Cosa

  • Divisione di una tabella in molte tabelle più piccole

Perché

  • Flessibilità di archiviazione
  • Query più veloci

Dove

  • Colonne comuni per il filtro
    • Data, luogo
Migliorare le prestazioni delle query in PostgreSQL

Valutare le query con partizioni

Query planner

Più chef attorno a una grande pentola

EXPLAIN
SELECT species
FROM zoo_animals
WHERE habitat = 'Africa'

Query plan

Seq Scan on zoo_animals  (cost=0.00..
17.70 rows=2 width=182)
  Filter: (state_code = 15)
  • Stime di costo (tempo)
Migliorare le prestazioni delle query in PostgreSQL

Vamos praticar!

Migliorare le prestazioni delle query in PostgreSQL

Preparing Video For Download...