Używanie kolumnowego przechowywania danych

Optymalizacja wydajności zapytań w PostgreSQL

Amy McCarty

Instructor

Zorientowane kolumnowo

Kolumnowe przechowywanie danych

Relacje między wierszami zachowane

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

Przechowywane jako

Dwie tabele. Pierwsza zawiera listę identyfikatorów i gatunków: 01 - panda, 02 - zebra, 03 - zebra, 04 - koala. Druga zawiera listę identyfikatorów i wieku: 01 - 2, 02 - 3, 03 - 10, 04 - 5.

Fokus analityczny – dobre dopasowanie

Właściwości kolumnowego przechowywania danych

Jedna kolumna przechowywana w tym samym miejscu
Szybki odczyt wszystkich wierszy
Szybkie obliczenia na kolumnach

Fokus analityczny

Liczby, średnie, obliczenia
Raportowanie
Agregacje kolumn

Przechowywane jako

Trzy tabele. Pierwsza zawiera listę identyfikatorów i imion: 01 - Bob, 02 - Sunny, 03 - Beco, 04 - Coco. Druga zawiera listę identyfikatorów i gatunków: 01 - panda, 02 - zebra, 03 - zebra, 04 - koala. Trzecia zawiera listę identyfikatorów i wieku: 01 - 2, 02 - 3, 03 - 10, 04 - 5.

Fokus transakcyjny – słabe dopasowanie

Relacje między wierszami zachowane

Wolny odczyt wszystkich kolumn
Wolne ładowanie danych

Fokus transakcyjny

Szybkie wstawianie i usuwanie rekordów

Przechowywane jako

Przykłady baz danych


Postgres	Citus Data, Greenplum, Amazon Redshift
MySQL	MariaDB
Oracle	Oracle In-Memory Cloud Store
	Clickhouse, Apache Druid, CrateDB

Schemat informacyjny

Ograniczanie kolumn

Używać SELECT * oszczędnie

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

Schemat informacyjny

Ograniczanie kolumn

Używać SELECT * oszczędnie
Używać schematu informacyjnego

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

Pisanie zapytań

Każda kolumna analizowana osobnym zapytaniem

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

-- Structure for column oriented
SELECT MIN(age), MAX(age)
FROM zoo_animals
WHERE species = 'zebra'

-- Structure for row-oriented
SELECT *
FROM zoo_animals
WHERE species = 'zebra'
ORDER BY age

Czas na ćwiczenia!

Optymalizacja wydajności zapytań w PostgreSQL