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

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

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

Серия: "Хозяйке на заметку" про PostgreSQL.
А точнее про создание индексов.

CREATE INDEX table_idx ON books (title) 

Вроде все просто.

Но есть ряд интересных опций.
1) CONCURRENTLY
Получается так:

CREATE INDEX CONCURRENTLY table_idx ON books (title)  

Когда нужно - всегда если накат идет на активное плечо ПРОМа. Опция не блокирует изменение таблицы пользователя. Минусы: команда выполняется дольше.

2) INCLUDE
Известно, что чтение значения индекса работает быстрее, т.к. это значение хранится в индексе, не нужно ходить в таблицу. Но кроме того в индекс можно положить любые другие значения из записи таблицы. Можно, но осторожно, т.к. это в любом случае дублирование данных. Эффект надо подтверждать на НТ.
Получаем:

CREATE INDEX table_idx ON books (title) INCLUDE (isbn)

3) WHERE
Позволяет поместить в индекс не все значения столбца, имеющиеся в таблице.
Решает следующие кейсы:
а) низкая селективность индекса: выбирает только значения с высокой селективностью
б) экономия места в памяти (и возможно в каких-то случаях на диске): выбираем в индекс только необходимое
в) частичная уникальность: если нужен уникальный индекс, но он соблюдается не для всех значений.
Выглядит так:

CREATE INDEX table_idx ON books (title) WHERE country = 'Russia'

P.S. Еще есть интересная опция USING, позволяющая использовать разные типы индексов (по умолчанию в PostgreSQL используется btree), но это отдельная тема)

Детали в официальной документации https://www.postgresql.org/docs/current/sql-createindex.html

#postgresql #db #performance

Снова про индексы.

Пост навеян вот этой статьей: https://vladmihalcea.com/index-selectivity/
Рекомендую прочитать, она короткая.

Многие знают про селективность индексов. На собесах в ответ на вопрос: на какие поля нужно делать индексы - я часто слышу про низкую селективность.
Суть в том, что если в колонке значения сильно не уникальные - хорошие примеры это boolean и enum - то индекс делать не надо.

Вообще говоря да, это так. Ответ верный.

Но есть нюанс. Полный ответ такой. Проблема такого индекса не в том, что БД будет неэффективно работать. БД после первого же запроса с использованием индекса поймет, что его стоимость выше, чем у full scan. И переключит все оставшиеся запросы на full scan.
А вот в чем мы точно проиграем - индекс съест место на диске и замедлит вставку. И что более важно: введет в заблуждение разработчика, т.к. создавая индекс он очевидно хочет решить с помощью него какую-то проблему (потенциальную?) с производительностью. А индекс по факту просто не работает.

P.S. И да, если по каким-то значениям в столбце селективность хорошая - поможет частичный индекс (WHERE в индексе).

P.P.S Сломать БД сложнее, чем кажется) Но все в ваших руках) И быстрее это можно сделать отсутствием нужно индекса, чем лишним индексом.

#db #performance

PostgreSQL и кэш

Речь не про кэширование JPA, а про собственное кэширование БД.

1) на сервере есть 2 уровня кэширования: PostgreSQL buffer cache и Linux page cache. В отличие от Kafka, где он один.
А если использовать кэширование на уровне JPA, в итоге получается 4 уровня)

2) по умолчанию кэша выделяется мало, для PROD like его размер нужно увеличить.
Посмотреть сколько:

SHOW shared_buffers

Изменить:

ALTER SYSTEM SET shared_buffers TO <your_value>; -- при наличии прав

или через /etc/postgresql/../postgresql.conf
c рестартом сервера.

Универсальных рекомендаций по размеру быть не может, но можно начать с 1/4 ОЗУ.

3) размером Linux page cache напрямую управлять нельзя. Но его максимальный размер можно ограничить косвенно, через

sysctl vm.swappiness

Да, это процент использования swap файла, но как побочный эффект он ограничивает использование ОЗУ под page cache, и оставшееся пространство можно выделить PostgreSQL.

Зная эти два параметра можно рассчитать максимальное значение для кэша PostgreSQL:

shared_buffers ~= ОЗУ * vm.swappiness - резерв ОС - резерв PostgreSQL

4) со значением shared_buffers нужно быть осторожным, также как и с настройкой heap space в JVM. PostgreSQL ожидает, что вся эта память будет ему доступна.
Если ОЗУ закончится, а процесс будет все равно пытаться запрашивать память, придет злой OOM Killer и убьет его).

5) есть расширение pg_buffercache, предоставляющее одноименное view с информацией о страницах кэша PostgreSQL: к какой таблице относится, как используется.
Перед его использованием нужно само собой установить:

CREATE EXTENSION pg_buffercache;

перегрузить сервер и дать права:

GRANT pg_monitor TO xxx;

Использование:

SELECT count(*)
FROM pg_buffercache
WHERE relfilenode = pg_relation_filenode('table'::regclass);

6) при старте PostgreSQL все кэши пустые.
Интересно, что "SELECT *" не заполняет кэш на 100%, т.к. используется более хитрый механизм, когда для каждой выполняемой операции выделяется часть кэша.
А вот изменение данных, которое тоже идет через буфер, точно кэширует все изменяемые данные.

7) изменение данных - плохой способ заполнить кэш.
А часто прогреть кэш нужно, как минимум для справочных данных.
Да вообще говоря и для любых данных, т.к. если удастся поместить всю БД в кэш, то это ускорит работу с БД на пару порядков (ОЗУ vs диски).
Ну и если быть точным - ускорит серверную часть процесса, см. https://t.me/javaKotlinDevOps/487

И ... для прогрева кэша тоже есть расширение:

CREATE EXTENSION pg_prewarm;
ALTER SYSTEM SET shared_preload_libraries = 'pg_prewarm';

и рестарт.

Использование:

SELECT pg_prewarm('table');

8) PostgreSQL сам чистит кэш в фоновом режиме, а явно сбросить кэш можно только перезагрузкой сервера. Что-то мне это напоминает из мира Java)

#cache #postgresql #performance