Использование столбцового хранилища

Оптимизация запросов в PostgreSQL

Amy McCarty

Instructor

Столбцовое хранение

Столбцовое хранилище

Связи между строками сохраняются

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

Хранится как

Две таблицы. Первая — список идентификаторов и видов: 01 - panda, 02 - zebra, 03 - zebra, 04 - koala. Вторая — список идентификаторов и возрастов: 01 - 2, 02 - 3, 03 - 10, 04 - 5.

Аналитические задачи — хорошее соответствие

Свойства столбцового хранилища

Один столбец хранится в одном месте
Быстрый возврат всех строк
Быстрые вычисления по столбцу

Аналитические задачи

Подсчёт, усреднение, вычисления
Формирование отчётов
Агрегация по столбцам

Хранится как

Три таблицы. Первая — список идентификаторов и имён: 01 - Bob, 02 - Sunny, 03 - Beco, 04 - Coco. Вторая — список идентификаторов и видов: 01 - panda, 02 - zebra, 03 - zebra, 04 - koala. Третья — список идентификаторов и возрастов: 01 - 2, 02 - 3, 03 - 10, 04 - 5.

Транзакционные задачи — плохое соответствие

Связи между строками сохраняются

Медленный возврат всех столбцов
Медленная загрузка данных

Транзакционные задачи

Быстрая вставка и удаление записей

Хранится как

Примеры баз данных


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

Информационная схема

Сокращение числа столбцов

Используйте SELECT * с осторожностью

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

Информационная схема

Сокращение числа столбцов

Используйте SELECT * с осторожностью
Используйте информационную схему

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

Написание запросов

Изучайте каждый столбец отдельным запросом

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

Давайте потренируемся!

Оптимизация запросов в PostgreSQL