На какие столбцы повесить индексы?

Есть несколько способов это определить.

1) экспертное мнение. Подходит для простых случаев. Ну и ограничение - нужно быть экспертом)

2) спросить условный ChatGPT, скормив ему код. Стильно, модно, молодёжно. Но с текущим уровнем развития LLM видится, что точность не гарантирована)

3) использовать план выполнения запроса, чтобы найти там full scan (seq scan).
Но тут возникает вопрос - на каких запросах его выполнять?
На медленных либо сильно нагружающих СУБД.
Есть несколько вариантов их найти:
а) slow log - отбрасывание наиболее медленных запросов в лог. Что считать медленным - настраивается через граничное время выполнения.
Может быть включён как на уровне Hibernate https://vladmihalcea.com/hibernate-slow-query-log/, так и на уровне базы данных https://www.cybertec-postgresql.com/en/3-ways-to-detect-slow-queries-in-postgresql/ (нужен VPN).
При наличии такой возможности - лучше не уровне БД, например, во время НТ.
Данный способ хорош тем, что прямо указывает на медленные запросы. И этим же плох, т.к. он не покажет массовый запрос, который выполняется быстро, но много.

б) более подробную информацию можно получить с помощью сбора статистики выполнения запросов. Для PostgreSQL это делает модуль pg_stat_statements. Детали тут https://habr.com/ru/articles/488968/
Модуль формирует табличку с данными, в которой можно отсортировать запросы по общему времени выполнения, среднему и максимальному времени, по величине отклонения от среднего, по числу вызовов и даже по нагрузке на процессор и дисковую подсистему.
В общем куча полезной информации, с которой придётся поработать)
Также рекомендую включить его на НТ. А потом измерить влияние включённого модуля на производительность и если оно в районе 1% - включить и на ПРОМе.

P.S. У MySQL аналога pg_stat не нашёл. У Oracle - AWR. У MSSQL - Query Store.

#db #performance

Вдогонку про storage движки PostgreSQL

Во-первых - если говорить о классических СУБД, то storage движки - тема не новая, можно вспомнить MySQL, который исторически имел несколько движков. InnoDB - самый известный, а вот полный список из официальной поставки:
https://dev.mysql.com/doc/refman/8.4/en/storage-engines.html
Из интересного:
blackhole - ничего не сохраняет
csv - сохраняет понятно куда)
memory - аналогично)
federated - удаленные сервера, горизонтальное масштабирование, но похоже с рядом ограничений (не копал глубоко).
Плюс есть реализации от внешних поставщиков.

Во-вторых, возможность создания storage движков в PostgreSQL дает следующая фича - Table Access Method (TAM) Interface https://www.postgresql.org/docs/current/tableam.html
Это слой абстракции между storage и compute при работе с таблицами. И расширение PostgreSQL может переопределить метод. Например, для реализации принципиально другого способа хранения записей таблицы и конкурентной модификации данных (MVCC). Или реализации шардирования. Или сжатия данных.

Тут стоит упомянуть, что есть похожая технология Foreign Data Wrapper (FDW).
Это реализация стандарта SQL/MED, позволяющая подключать к PostgreSQL внешние удаленные хранилища и, соответственно, выполнять к ним SQL запросы. Т.е. дать унифицированный SQL API для внутренних и внешних таблиц.
Тут детали https://www.percona.com/blog/foreign-data-wrappers-postgresql-postgres_fdw/
В отличие от Table Access Method Interface данная технология сделана для подключения внешних источников данных, в то время как для TAM подразумевает внутреннее хранение. Или локально, или в случае cloud native - использование пусть и облачного, но своего хранилища.

Соответственно, полноценные storage движки - это движки, меняющие ТАМ.
Вот какие бы я выделил:

1) Orioledb, уже упомянутый ранее - ускорение сохранения данных за счет другой реализации MVCC. Еще умеют сжимать данные и работают над cloud ready storage с разделением compute и storage. В последнем случае позиционируют себя как конкурент Neon, но еще неготовый к production https://www.orioledb.com/blog/orioledb-neon-differences#orioledb-1.
Кандидат на замену основного движка PostgreSQL, но команда PostgreSQL пока сопротивляется)
Если нужно больше информации - лучше, чем описано тут я описать не смогу) https://t.me/rybakalexey/240
Из важных особенностей - требует патча ядра PostgreSQL, что нарушает идею бесшовной замены движков

2) TimescaleDB - оптимизация под хранение временных рядов. Если обычные данные PostgreSQL хранятся построчно (row storage), то новый движок добавляет column storage. Конкурент для InfluxDB и Prometheus. Тут можно найти больше деталей https://deepwiki.com/timescale/timescaledb/12-hypercore-and-columnar-storage

3) Citus - горизонтальное масштабирование, но без разделения compute и storage, а путем возможности создания shared-nothing шард и распределенного выполнения запросов. Это дает возможность безопасно хранить данных для разных потребителей на разных шардах, но при этом иметь возможность выполнять аналитические запросы по всему объему данных. Подходит для SaaS систем.

Для справки - есть достаточно много реализаций Foreign Data Wrapper, предоставляющие доступ к файлам:
0) PostgreSQL - единственный FDW, входящий в поставку PostgreSQL. Эталонная реализация.
1) Oracle
2) MSSQL
3) MySQL
4) parquet (parquet - стандартный формат для Data Lake - аналитических БД, Hadoop, spark и вот это все)
...

Маленькое замечание - в отличие от технологии dblink в Oracle, FDW хранит метаданные удаленной таблицы внутри, что позволяет оптимизировать план выполнения запроса. Например, выполнять какую-то агрегацию на удаленном сервере. Или вытаскивать с удаленного сервера только необходимые данные. Это плюс. А минус тот же, что у dblink - ходить в чужую БД плохо, и противоречит микросервисной архитектуре.
Поэтому видится, что основной способ применения FDW - прототипирование, какие-то временные решения или миграции. Вот пример использования для миграции данных из Oracle в PostgreSQL https://habr.com/ru/companies/vtb/articles/819133/

#db #cloud #postgresql

Как нам улучшить PostgreSQL?

Исходя из предудущих моих постов ответ напрашивается такой - установкой расширений (extension).

Вот так это примерно делается:

CREATE EXTENSION IF NOT EXISTS extension_name
WITH SCHEMA schema_name
VERSION version
CASCADE

Вот так обновление:
ALTER EXTENSION extension_name
UPDATE TO version

И удаление:
DROP EXTENSION IF EXISTS extension_name
CASCADE | RESTRICT

Из важного - расширения тоже имеют транзитивные зависимости, их установка/удаление решается опцией CASCADE.
Проблема несовместимых версий видимо решается установкой совместимых версий)
Расширение ставится в конкретную схему.
Всегда можно сделать условное обновление\удаление - с проверкой на существование.

Что меняют расширения:
1) механизм хранения данных в таблицах (Table Access Method Interface)
2) аналогично для индексов, т.е. новые типы индексов (Index Access Method Interface)
3) новые типы данных
4) новые функции
5) внешние источники данных (Foreign Data Wrapper)
6) SQL синтаксис

И к делу.
Вот что интересного я нашел (orioledb, timescaledb, citus и прочие Foreign Data Wrapper уже описал ранее, исключаем):

1) pg_stat_statements - статистика по времени и ресурсам, затраченным на выполнение запросов (где-то год назад уже писал о нем)

Выборка 10 "тяжелых" запросов по общему времени выполнения:
SELECT query, total_time, calls, rows FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

2) PostGIS - новые типы данных в столбцах, индексы и функции для работы с геоданными (point, line, polygon)

Создание таблицы со геоданными:
CREATE TABLE spatial_data ( id SERIAL PRIMARY KEY, name VARCHAR, location GEOMETRY(Point, 4326);

Поиск объектов в радиусе 1000 метров от точки с определенными координатами:
SELECT * FROM spatial_data WHERE ST_DWithin(location, ST_GeomFromText('POINT(-73.975972 40.782865)', 4326), 1000);

3) HypoPG - создание гипотетических индексов. Т.е. индекса физически нет, но планировщик запросов думает, что он есть.

Создание гипотетического индекса:
SELECT * FROM hypopg_create_index('CREATE INDEX ON mytable (cardId)');

Проверка, подействовал ли он:
EXPLAIN SELECT * FROM mytable WHERE cardId = 1;

4) jsquery - более лучший поиск в стиле XPath по JSONB столбцам (столбцам, с бинарным оптимизированным хранением json) Детали: https://habr.com/ru/companies/selectel/articles/928922/

Поиск пользователей с именем «Иван» старше 30 лет или тех, у кого есть тег «vip»:

SELECT * FROM users WHERE data @@ '((name = "Иван" AND age > 30) OR tags.# = "vip")';

5) pgvector - работа с векторными типами данных, PostgreSQL как RAG, AI и все такое. Детали: https://habr.com/ru/companies/selectel/articles/920824/

Создание таблицы для хранения векторов:
CREATE TABLE items (
id SERIAL PRIMARY KEY,
title TEXT,
embedding VECTOR(1536)
);

Вставка данных :
INSERT INTO items (title, embedding) VALUES
('PostgreSQL embeddings', '[0.10, -0.80, 0.45]'),
('Neural image processing', '[0.42, 0.18, -0.35]'),
('Sound pattern matching', '[-0.20, 0.70, 0.60]'),
('Document clustering', '[0.09, -0.79, 0.48]');

Нахождение похожих векторов:

SELECT
a.title AS title_a,
b.title AS title_b,
a.embedding <-> b.embedding AS distance
FROM items a
JOIN items b ON a.id < b.id
ORDER BY distance;

6) pgcrypto - криптографические функции в PostgreSQL. Детали: https://habr.com/ru/companies/selectel/articles/925848/

Сохранение хэшированного пароля:
INSERT INTO users (username, password_hash) VALUES ('new_user', crypt('highly-secure-password123', gen_salt('bf', 10)));

7) hstore - key-value тип данных. На оф.сайте https://www.postgresql.org/docs/current/hstore.html

Создание таблицы с key-value столбцом для хранения атрибутов книги:
CREATE TABLE books (
id serial PRIMARY KEY,
name varchar,
attributes hstore
);

Вставка:
INSERT INTO books (name, attributes) VALUES (
'Harry Potter and the Philosophers Stone',
'author => "J. K. Rowling", pages => 223, series => "Harry Potter"'
);

и выборка по ключу атрибута:
SELECT name, attributes->'author' as author
FROM books
WHERE attributes->'series' = 'Harry Potter'

To be continued...
#PostgreSQL #db

Продолжение поста про расширения PostgreSQL.

Все что не влезло в предыдущий)

8) citext - case-insensitive хранение, а скорее сравнение строк в БД.

Создание таблицы с case-insensitive столбцом и вставка данных:

CREATE TABLE users (user_id SERIAL PRIMARY KEY, username CITEXT, email CITEXT);
INSERT INTO users (username, email) VALUES ('Иван Смирнов', 'ivan.smironoff@mail.ru');

Поиск без учета регистра:

SELECT * FROM users WHERE username = 'иван смирнов';

9) pg_stat_kcache - еще больше статистики по выполнению запросов, в особенности про работу с файловой системой - например, чтение из кэша ОС vs чтение с диска.
Что за данные собираются - можно узнать тут https://github.com/powa-team/pg_stat_kcache

Пример запроса: 5 самых затратных по времени запросов, их частоту выполнения, объемы обращений к диску и оперативной памяти, а также количество переключений контекста ядра ОС.

SELECT
round(total_exec_time::numeric, 0) AS time,
calls,
pg_size_pretty(exec_minflts * 4096) AS reclaim, pg_size_pretty(exec_majflts * 4096) AS faults,
pg_size_pretty(exec_reads) AS reads, pg_size_pretty(exec_writes) AS writes,
round(exec_user_time::numeric, 2) AS user_time, round(exec_system_time::numeric, 2) AS sys_time,
exec_nvcsws AS virtual_switches, exec_nivcsws AS involuntary_switches,
LEFT(query, 27) AS query_text
FROM pg_stat_statements s
JOIN pg_stat_kcache() k
USING(userid, dbid, queryid)
ORDER BY total_exec_time DESC
LIMIT 5;

10) isn - добавляет новые типы данных для проверки разного рода штрих кодов EAN13, UPC, ISBN (books), ISMN (music), and ISSN (serials) на допустимые префиксы. У модуля есть минус - набор допустимых префиксов постоянном меняется, следовательно, надо поддерживать актуальность расширения и может быть гэп по времени по поступлению обновлений.

Создание и вставка данных в таблицу с ISBN столбцом (уникальный номер книги):

CREATE TABLE test (id isbn);
INSERT INTO test VALUES('9780393040029');

11) pg_hint_plan - позволяет "прибить гвоздями" кусок плана выполнения запроса

Тут мы указываем, что надо использовать HashJoin и сразу смотрим реальный план выполнения запроса:

/*+ HashJoin(pt st) */
EXPLAIN SELECT * FROM s1.t1 st
JOIN public.t1 pt ON (st.id=pt.id);

12) pg_cron - что делает - понятно. Можно подумать, что расширение нарушает принцип - никакой логики в БД. Но оно полезно для другого - для служебных задач, например, периодически делать VACUUM:

SELECT cron.schedule('59 23 * * *', 'VACUUM');

На цифре 12 пожалуй и остановлюсь.

Что еще важно.
Часть расширений уже входит в поставку.
Какие именно - можно узнать выполнив команду:

SELECT * FROM pg_available_extensions;

Набор может различаться, в официальном дистрибутиве их на данный момент 60, в PostgrePro - 48, Сбер Pangolin - 119.

#postgresql #db #plugins