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 МБайт.
Но здесь многое зависит от размера вашей базы и профиля нагрузки.
Всё же уметь написать quick/merge sort и обойти дерево надо. Без этого нет смысла приходить на собеседование в Яндекс.
Правда есть другая проблема — это никоим образом не помогает в реальной работе. За год с небольшим всего один раз потребовалось придумать и закодировать действительно сложный алгоритм. 90% времени просто читаешь/пишешь в БД и шину.
Спасибо за наводку. Я посмотрел SchemaCrawler и из документации не увидел самого главного для меня — умения работать с кластером.
Такие диагностики, как отсутствующие индексы или неиспользуемые индексы, могут быть получены только в результате сбора статистики со всех хостов в кластере.
Когда будет время, я постараюсь пощупать SchemaCrawler поближе.
А можете привести сценарий, когда нужны 2 индекса с разным типом и одинаковым набором колонок?
Я пока что не уверен, что этот кейс нужно как-то особенно обрабатывать. Возможно, я что-то упускаю.
Заранее спасибо.
Отличное замечание. Это актуально только для дублирующихся и пересекающихся индексов.
И нет, сейчас такого ограничения не заложено. Видимо, нужно ещё добавить метод доступа.
Заведу issue, проверю и доработаю.
К сожалению, у меня нет универсального рецепта.
Можно попробовать рассказывать людям про архитектуру Постгреса и правильное его использование.
У себя выставляю idle_in_transaction_session_timeout и слежу за ошибками в логах + жду тех, кто придёт с проблемами. И уже постфактум пытаемся разобраться, почему люди оставляют висеть такие транзакции.
С idle_in_transaction_session_timeout нужно быть аккуратным, так как он может сломать работу pg_repack'а.
Спасибо! Отличная статья. Узнал несколько интересных фишек, но сам пока что релиз создаю и публикую вручную.
У меня ещё была проблема с публикацией второго релиза из-за 6-го Гредла. Пришлось отключить вычисление sha256 и sha512 через systemProp.org.gradle.internal.publish.checksums.insecure
К сожалению, на реальной нагруженной БД pg_stat_statements быстро «забивается». Его нужно тюнить, регулярно сбрасывать, но даже это не всегда помогает.
В итоге ClickHouse и сбор статистики на уровне приложения зачастую более масштабируемый и удобный вариант.
С почином на Хабре. А вот код с бенчмарками на каком-нибудь GitHub'е не помешал бы. Народ это любит. Может получится к следующей публикации подготовить?
Извините, но когда вы пишете про 5000 коннектов к БД, вы понимаете, как PG устроен под капотом?
Держать блокировку воркером на протяжении всего времени выполнения задачи — это совсем не масштабируемое решение. Для использования в продакшене оно мало подходит.
А если отпускать блокировку после захвата (как правильно делать), то нужно очень аккуратно реализовывать перезапуск\смену статуса у тасок, что, собственно, обсуждается в треде выше.
Бессмысленный индекс в таком виде. Значение 2 (выполнена) из него нужно выкинуть сразу. В штатном режиме все задачи будут иметь финальный статус 2, их будет много, индекс не будет использоваться.
2.
error_text text null
Лучше так не делать в production'е.
Люди начинают писать в поле длинные стек-трейсы, таблица тостируется, вы теряете в перфомансе.
Ограничивайте длину поля явно.
3. Что касается очереди с повторами «упавших» задач, то не увидел самого главного: как отличить упавший воркер от долго работающего воркера?
Если воркер упал во время работы, кто перезапустит таску на выполнение? Кто проставит ошибку?
Спасибо за отсутствующие индексы, взял на вооружение.
Везде предлагают один и тот же достаточно примитивный запрос на неиспользуемые индексы. В жизни его применять практически нереально.
Так, например, нет смысла удалять индексы на внешние ключи, даже если они не используются в данный момент.
Чтение данных может выполняться на репликах, тогда на мастере индекс использоваться не будет, то есть проверку нужно делать на всех хостах.
Если индекс использовался раньше, а сейчас перестал совсем, запросом его, увы, не найти.
Мой вариант запроса
with forein_key_indexes as (
select i.indexrelid
from pg_constraint c
join lateral unnest(c.conkey) with ordinality as u(attnum, attposition) on true
join pg_index i on i.indrelid = c.conrelid and (c.conkey::int[] <@ indkey::int[])
where c.contype = 'f'
)
select
psui.relname as table_name,
psui.indexrelname as index_name,
pg_relation_size(i.indexrelid) as index_size,
pg_size_pretty(pg_relation_size(i.indexrelid)) as index_size_pretty,
psui.idx_scan as index_scans
from pg_stat_user_indexes psui
join pg_index i on psui.indexrelid = i.indexrelid
where
psui.schemaname = 'public'::text and
not i.indisunique and
i.indexrelid not in (select * from forein_key_indexes) and -- retain indexes on foreign keys
psui.idx_scan < 50 and
pg_relation_size(psui.relid) >= 5 * 8192 -- skip small tables
and pg_relation_size(psui.indexrelid) >= 5 * 8192 -- skip small indexes
order by psui.relname, pg_relation_size(i.indexrelid) desc
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 МБайт.
Но здесь многое зависит от размера вашей базы и профиля нагрузки.
Правда есть другая проблема — это никоим образом не помогает в реальной работе. За год с небольшим всего один раз потребовалось придумать и закодировать действительно сложный алгоритм. 90% времени просто читаешь/пишешь в БД и шину.
Сам запрос вот тут
Такие диагностики, как отсутствующие индексы или неиспользуемые индексы, могут быть получены только в результате сбора статистики со всех хостов в кластере.
Когда будет время, я постараюсь пощупать SchemaCrawler поближе.
Я пока что не уверен, что этот кейс нужно как-то особенно обрабатывать. Возможно, я что-то упускаю.
Заранее спасибо.
И нет, сейчас такого ограничения не заложено. Видимо, нужно ещё добавить метод доступа.
Заведу issue, проверю и доработаю.
Можно попробовать рассказывать людям про архитектуру Постгреса и правильное его использование.
У себя выставляю idle_in_transaction_session_timeout и слежу за ошибками в логах + жду тех, кто придёт с проблемами. И уже постфактум пытаемся разобраться, почему люди оставляют висеть такие транзакции.
С idle_in_transaction_session_timeout нужно быть аккуратным, так как он может сломать работу pg_repack'а.
У меня ещё была проблема с публикацией второго релиза из-за 6-го Гредла. Пришлось отключить вычисление sha256 и sha512 через systemProp.org.gradle.internal.publish.checksums.insecure
В итоге ClickHouse и сбор статистики на уровне приложения зачастую более масштабируемый и удобный вариант.
pg-index-health
Держать блокировку воркером на протяжении всего времени выполнения задачи — это совсем не масштабируемое решение. Для использования в продакшене оно мало подходит.
А если отпускать блокировку после захвата (как правильно делать), то нужно очень аккуратно реализовывать перезапуск\смену статуса у тасок, что, собственно, обсуждается в треде выше.
Бессмысленный индекс в таком виде. Значение 2 (выполнена) из него нужно выкинуть сразу. В штатном режиме все задачи будут иметь финальный статус 2, их будет много, индекс не будет использоваться.
2.
Лучше так не делать в production'е.
Люди начинают писать в поле длинные стек-трейсы, таблица тостируется, вы теряете в перфомансе.
Ограничивайте длину поля явно.
3. Что касается очереди с повторами «упавших» задач, то не увидел самого главного: как отличить упавший воркер от долго работающего воркера?
Если воркер упал во время работы, кто перезапустит таску на выполнение? Кто проставит ошибку?
Везде предлагают один и тот же достаточно примитивный запрос на неиспользуемые индексы. В жизни его применять практически нереально.
Так, например, нет смысла удалять индексы на внешние ключи, даже если они не используются в данный момент.
Чтение данных может выполняться на репликах, тогда на мастере индекс использоваться не будет, то есть проверку нужно делать на всех хостах.
Если индекс использовался раньше, а сейчас перестал совсем, запросом его, увы, не найти.