Хабр Курсы для всех
РЕКЛАМА
Практикум, Хекслет, SkyPro, авторские курсы — собрали всех и попросили скидки. Осталось выбрать!
Хабр доступен 24/7 благодаря поддержке друзей
Комментарии 21
Спасибо за доклад. Вы как java разработчик, расскажите как агитировать использовать меньше соединений к БД PostgreSQL с типом "idle in transaction"? Pgbouncer нельзя использовать, потому что используется prepare statement.
К сожалению, у меня нет универсального рецепта.
Можно попробовать рассказывать людям про архитектуру Постгреса и правильное его использование.
У себя выставляю idle_in_transaction_session_timeout и слежу за ошибками в логах + жду тех, кто придёт с проблемами. И уже постфактум пытаемся разобраться, почему люди оставляют висеть такие транзакции.
С idle_in_transaction_session_timeout нужно быть аккуратным, так как он может сломать работу pg_repack'а.
Можно попробовать рассказывать людям про архитектуру Постгреса и правильное его использование.
У себя выставляю idle_in_transaction_session_timeout и слежу за ошибками в логах + жду тех, кто придёт с проблемами. И уже постфактум пытаемся разобраться, почему люди оставляют висеть такие транзакции.
С idle_in_transaction_session_timeout нужно быть аккуратным, так как он может сломать работу pg_repack'а.
а скрипты умеют отличать разные типы индексов на 1 поле?
Отличное замечание. Это актуально только для дублирующихся и пересекающихся индексов.
И нет, сейчас такого ограничения не заложено. Видимо, нужно ещё добавить метод доступа.
Заведу issue, проверю и доработаю.
И нет, сейчас такого ограничения не заложено. Видимо, нужно ещё добавить метод доступа.
Заведу issue, проверю и доработаю.
А можете привести сценарий, когда нужны 2 индекса с разным типом и одинаковым набором колонок?
Я пока что не уверен, что этот кейс нужно как-то особенно обрабатывать. Возможно, я что-то упускаю.
Заранее спасибо.
Я пока что не уверен, что этот кейс нужно как-то особенно обрабатывать. Возможно, я что-то упускаю.
Заранее спасибо.
сложно придумать сходу.
наверное, может быть hash для поиска по =, и btree для остального.
наверное, может быть hash для поиска по =, и btree для остального.
Вот, кстати, пример появился от Кирилла Боровикова. Btree + gin индексы. Они не будут считаться дублирующимися.
Кстати, а вот такие «похожие» индексы одинакового типа, но с разными opclass не распознаются случайно как дубли?
CREATE INDEX ON firms USING btree(lower(name));
CREATE INDEX ON firms USING btree(lower(name) text_pattern_ops);Проверил, нет, не распознаются, но кейс хороший — добавлю в тесты.
Сам запрос вот тут
Сам запрос вот тут
а какие есть преимущества у этого инструмента по сравнению с, например, schemacrawler.com/lint.html?
Спасибо за наводку. Я посмотрел SchemaCrawler и из документации не увидел самого главного для меня — умения работать с кластером.
Такие диагностики, как отсутствующие индексы или неиспользуемые индексы, могут быть получены только в результате сбора статистики со всех хостов в кластере.
Когда будет время, я постараюсь пощупать SchemaCrawler поближе.
Такие диагностики, как отсутствующие индексы или неиспользуемые индексы, могут быть получены только в результате сбора статистики со всех хостов в кластере.
Когда будет время, я постараюсь пощупать SchemaCrawler поближе.
проверил одну базу
1. на дубли индексов: нашёл индекс уникальный по полю и ограничение целостности (проверка поля на уникальность). Что из этого стоит удалить?
2. проверил на пересекающиеся индексы. есть индекс hash по полю А и btree по (A,B). ещё проблема, на индексах с условиями WHERE (условия разные, но скрипт считает их пересекающимися).
3. внешние ключи без индексов вообще не понял как работает.
ругнулся на одну табличку. но столбец на который ссылается внешний ключ хоть и не является PK, но содержит ограничение уникальности и входит на 1ой позиции в состав сложного индекса.
4. 'Tables without primary key' не находит PK на материализованных представлениях, где есть уникальный btree индекс по полю id.
Общие замечания:
1. можно что-нибудь придумать чтобы не вписывать каждый раз схему руками. например использовать дефолтную или public?
2. есть какие желаемые значения по bloat, после чего их стоит отправить на vacum?
надеюсь, нашёл что-то полезное.
1. на дубли индексов: нашёл индекс уникальный по полю и ограничение целостности (проверка поля на уникальность). Что из этого стоит удалить?
2. проверил на пересекающиеся индексы. есть индекс hash по полю А и btree по (A,B). ещё проблема, на индексах с условиями WHERE (условия разные, но скрипт считает их пересекающимися).
3. внешние ключи без индексов вообще не понял как работает.
ругнулся на одну табличку. но столбец на который ссылается внешний ключ хоть и не является PK, но содержит ограничение уникальности и входит на 1ой позиции в состав сложного индекса.
4. 'Tables without primary key' не находит PK на материализованных представлениях, где есть уникальный btree индекс по полю id.
Общие замечания:
1. можно что-нибудь придумать чтобы не вписывать каждый раз схему руками. например использовать дефолтную или public?
2. есть какие желаемые значения по bloat, после чего их стоит отправить на vacum?
надеюсь, нашёл что-то полезное.
Спасибо за обратную связь!
1. Скорее всего удалять нужно уникальный индекс, но это не точно. В таких ситуациях лучше взглянуть на схему данных и её эволюцию (миграции).
2. Вот тут не понимаю, зачем нужен hash индекс. Выглядит так, что он не нужен.
По поводу where — можете создать issue с примером sql-скрипта для воспроизведения ошибки?
3. Постараюсь пояснить. Foreign key связывает 2 таблицы. Таблица, на которую ссылаются, обязана поддерживать ограничение уникальности на столбце внешнего ключа, и там точно будет индекс. А вот в связанной таблице индекс не обязателен.
Например, таблица order (id) и таблица order_item(id, order_id). order_item::order_id ссылается на order::id. Индекс на столбец order_item::order_id не требуется и по умолчанию не создаётся, но он будет очень нужен при удалении записей из order.
4. Здесь тоже попрошу завести issue с примером скрипта для воспроизведения ошибки.
5. Если использовать java-API, то есть перегруженные методы, которые работают с public-схемой.
6. Я оцениваю bloat в том числе и по абсолютному размеру. Для таблиц я бы рассматривал bloat больше 20% и больше 500 МБайт. Для индексов — больше 30% и 500 МБайт.
Но здесь многое зависит от размера вашей базы и профиля нагрузки.
1. Скорее всего удалять нужно уникальный индекс, но это не точно. В таких ситуациях лучше взглянуть на схему данных и её эволюцию (миграции).
2. Вот тут не понимаю, зачем нужен hash индекс. Выглядит так, что он не нужен.
По поводу where — можете создать issue с примером sql-скрипта для воспроизведения ошибки?
3. Постараюсь пояснить. Foreign key связывает 2 таблицы. Таблица, на которую ссылаются, обязана поддерживать ограничение уникальности на столбце внешнего ключа, и там точно будет индекс. А вот в связанной таблице индекс не обязателен.
Например, таблица order (id) и таблица order_item(id, order_id). order_item::order_id ссылается на order::id. Индекс на столбец order_item::order_id не требуется и по умолчанию не создаётся, но он будет очень нужен при удалении записей из order.
4. Здесь тоже попрошу завести issue с примером скрипта для воспроизведения ошибки.
5. Если использовать java-API, то есть перегруженные методы, которые работают с public-схемой.
6. Я оцениваю bloat в том числе и по абсолютному размеру. Для таблиц я бы рассматривал bloat больше 20% и больше 500 МБайт. Для индексов — больше 30% и 500 МБайт.
Но здесь многое зависит от размера вашей базы и профиля нагрузки.
2. индекс по полю A при условии A > 2019, индекс по полю (A,B) при условии A < 2019. Второй покрывает 1ый?
3. разобрался. спасибо.
4. в материальных представлениях нельзя делать PK, приходится делать уникальные индексы, вместо них. а скрипт выводит их в поиске как таблицы без pk.
учетки на github нет.
3. разобрался. спасибо.
4. в материальных представлениях нельзя делать PK, приходится делать уникальные индексы, вместо них. а скрипт выводит их в поиске как таблицы без pk.
учетки на github нет.
а можно ли автоматом предлагать заменить btree на brin на больших таблицах при определённых условиях?
Спасибо за статью и интересный инструмент. Мне очень нравится, что у вас все скрипты лежат отдельно и в чистом виде в папке resource (не размазаны по java-коду, как это обычно бывает). Такие скрипты можно и DBA показать и обновлять при необходимости.
PS
Я тут занимаюсь такой штукой, которая парсит папку с SQL-скриптами и строит http-интерфейс для всего этого дела (с моделями, фильтрами, пагинацией и пр.) github.com/CrocInc/sql-boot
Если кому интересно — пишите, расскажу подробнее или readme дополню (он сейчас довольно скромный).
PS
Я тут занимаюсь такой штукой, которая парсит папку с SQL-скриптами и строит http-интерфейс для всего этого дела (с моделями, фильтрами, пагинацией и пр.) github.com/CrocInc/sql-boot
Если кому интересно — пишите, расскажу подробнее или readme дополню (он сейчас довольно скромный).
Меня попросили вынести sql-скрипты в отдельный репозиторий, так что теперь их использовать гораздо легче.
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Здоровье индексов в PostgreSQL глазами Java-разработчика