Uso dell’archiviazione per colonne

Migliorare le prestazioni delle query in PostgreSQL

Amy McCarty

Instructor

Orientata alle colonne

Archiviazione per colonne

  • Relazione tra righe mantenuta
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

Memorizzata come

Due tabelle. La prima elenca id e specie. 01 - panda. 02 - zebra. 03 - zebra. 04 - koala. La seconda elenca id ed età. 01 - 2. 02 - 3. 03 - 10. 04 - 5.

Migliorare le prestazioni delle query in PostgreSQL

Focus analitico: ottima scelta

Proprietà dell’archiviazione per colonne

  • Una colonna nello stesso luogo
  • Veloce a restituire tutte le righe
  • Veloce nei calcoli sulle colonne

Focus analitico

  • Conteggi, medie, calcoli
  • Reportistica
  • Aggregazioni per colonna

Memorizzata come

Tre tabelle. La prima elenca id e nomi. 01 - Bob. 02 - Sunny. 03 - Beco. 04 - Coco. La seconda elenca id e specie. 01 - panda. 02 - zebra. 03 - zebra. 04 - koala. La terza elenca id ed età. 01 - 2. 02 - 3. 03 - 10. 04 - 5.

Migliorare le prestazioni delle query in PostgreSQL

Focus transazionale: scelta poco adatta

Relazioni per riga mantenute

  • Lenta a restituire tutte le colonne
  • Lenta a caricare dati

Focus transazionale

  • Insert e delete veloci

Memorizzata come

Tre tabelle. La prima elenca id e nomi. 01 - Bob. 02 - Sunny. 03 - Beco. 04 - Coco. La seconda elenca id e specie. 01 - panda. 02 - zebra. 03 - zebra. 04 - koala. La terza elenca id ed età. 01 - 2. 02 - 3. 03 - 10. 04 - 5.

Migliorare le prestazioni delle query in PostgreSQL

Esempi di database

 
Postgres Citus Data, Greenplum, Amazon Redshift
MySQL MariaDB
Oracle Oracle In-Memory Cloud Store
Clickhouse, Apache Druid, CrateDB
Migliorare le prestazioni delle query in PostgreSQL

Information schema

Ridurre le colonne

  • Usa SELECT * con parsimonia
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_catalog = 'schema_name'
AND table_name = 'zoo_animals'
column_name data_type
id integer
name text
species text
Migliorare le prestazioni delle query in PostgreSQL

Information schema

Ridurre le colonne

  • Usa SELECT * con parsimonia
  • Usa l’information schema
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_catalog = 'schema_name'
AND table_name = 'zoo_animals'
column_name data_type
id integer
name text
species text
Migliorare le prestazioni delle query in PostgreSQL

Scrivere le query

 

  • Analizza ogni colonna con una query dedicata
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 
-- Struttura per archiviazione per colonne
SELECT MIN(age), MAX(age)
FROM zoo_animals
WHERE species = 'zebra'

-- Struttura per archiviazione per righe
SELECT *
FROM zoo_animals
WHERE species = 'zebra'
ORDER BY age
Migliorare le prestazioni delle query in PostgreSQL

Passiamo alla pratica !

Migliorare le prestazioni delle query in PostgreSQL

Preparing Video For Download...