Sql

Postgresql reindex: PostgreSQL: Documentation: 15: 25.2. Routine Reindexing

Мониторинг индексов в PostgreSQL и их перестроение

Мониторинг индексов в PostgreSQL очень важен, потому как индексы могут разрастаться или не использоваться.

Поиск неиспользуемых индексов

Иногда случается, что в базе данных некоторые индексы не нужны. То есть при создании базы сделали лишние или пересекающиеся индексы, а они на самом деле не используются. Лишние индексы занимают место, а также добавляют накладные расходы. Как только вы меняете таблицу, вам нужно обновить все индексы в ней. Поэтому мониторинг индексов необходим в PostgreSQL.

В представлении pg_stat_all_indexes в поле idx.scan вы можете увидеть количество обращений к индексу. Если там ноль, значит индекс не используется и возможно его следует удалить. Но следует точно понимать что вы делаете, и перепроверить все перед удалением таких индексов.

Посмотреть представление pg_stat_all_indexes для какой-нибудь таблицы можно так:

postgres@s-pg13:~$ psql -d data_lowlevel
Timing is on.
 
 
psql (13.3)
Type "help" for help.
postgres@data_lowlevel=# SELECT * FROM pg_stat_all_indexes WHERE relname='t';
 relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+---------+--------------+----------+--------------+---------------
 16497 |      16504 | public     | t       | t_pkey       |        0 |            0 |             0
(1 row)
Time: 10,941 ms

Перестроение индексов

Помимо лишних индексов существует другая проблема – разрастание индексов. Например у вас выросла таблица и индексы в ней тоже разрослись. Они перестали помещаться в одной странице и заняли ещё одну. Затем вы почистили табличку с помощью VACUUM, а страницы индексов всё равно останется две, просто в них образуются пустоты. Страницы могут только делиться, а склеиваться назад уже не могут (если не использовать VACUUM FULL).

Существует команда REINDEX которая занимается перестроением индексов. Можно перестроить как отдельный индекс, так и все индексы в таблице, в базе данных или во всей системе:

  • REINDEX INDEX индекс;
  • REINDEX TABLE таблица;
  • REINDEX DATABASE база;
  • REINDEX SYSTEM;

Команда VACUUM FULL перестраивает и таблицы и индексы, а команда REINDEX только индексы. REINDEX также устанавливает эксклюзивную блокировку на таблицу.

Пересоздание индексов

Можно пересоздать индексы без эксклюзивной блокировки с помощью ключевого слова CONCURRENTLY:

postgres=# CREATE INDEX новый ON . .. CONCURRENTLY;
postgres=# DROP INDEX старый;

С помощью такого перестроения индексов, новый индекс будет строится дольше, но это не приведёт к блокировке таблицы. Также такое создание нового индекса может завершиться с ошибкой, тогда этот новый индекс нужно удалить и попробовать сделать новый индекс ещё раз.

Индекс может служить для поддержки ограничения целостности, например для первичного ключа. В этом случае меняем индексы таким способом:

postgres=# CREATE UNIQUE INDEX новый ON ... CONCURRENTLY;
postgres=# ALTER TABLE ...
   DROP CONSTRAINT старое,
   ADD CONSTRAINT новое [UNIQUE|PRIMARI KEY] USING INDEX новый;

Сводка

By adminPostgreSQL3 комментария к записи Мониторинг индексов в PostgreSQL и их перестроение

Жизнь в консоли linux.

.. и в IDE

Скрипт автоматической переиндексации (reindex) и удаления мертвых данных (full vacuum) в базе postgresql.

REINDEX — операция переиндексации, исправление поврежденных индексов в базе.
VACUUM — сборка «мусора». VACUUM восстанавливает место занятое «мертвыми» данными. При выполнении обычных операций с данными, PostgreSQL не удаляет данные физически из таблиц, это происходит с операцией FULL VACUUM.
На работе имеется несколько серверов 1с+postgresql, некоторые с относительно не маленькими базами (около 80Гб), поэтому остро встает проблема свободного места на дисках и скорости работы этих баз. Со временем базы в постгрес имеют свойство разрастаться, и начинать медленней шевелиться из-за проблем с индексацией. Для решения этих проблем был написан простой скрипт, который раз в неделю ночью запускает reindex и full vacuum.
 
Создаем файл скрипта:
 
touch reindex.sh

 
Разрешаем его запуск:
 
chmod +x reindex. sh
 
Открываем и копируем туда собственно сам скрипт:
 
vim reindex.sh
 
#!/bin/bash

h=127.0.0.1
p=5432
u=postgres

xxx () {
/bin/echo $db >> /var/log/reindex.log
/bin/date >> /var/log/reindex.log
/bin/echo «begin reindex» >> /var/log/reindex.log
/usr/bin/psql —dbname $db —host $h —port $p —username $u -w —command «reindex DATABASE \»$db\»;» >> /var/log/reindex.log
/bin/date >> /var/log/reindex.log
/bin/echo «end reindex» >> /var/log/reindex.log
/bin/echo «begin vacuum full analyze» >> /var/log/reindex.log
/usr/bin/psql —dbname $db —host $h —port $p —username $u -w —command «vacuum full analyze;» >> /var/log/reindex.log
/bin/date >> /var/log/reindex.log

/bin/echo «end vacuum full analyze» >> /var/log/reindex.log
}

db=mhvl
xxx
 
где:
db — имя базы данных,
h — ip-адрес сервера postgresql (в моем случае скрипт лежит на нем),
p — порт, на котором постгрес работает,
u — имя пользователя в postgresql
 
Скрипт запускает reindex и full vacuum, внося записи в файл логов о времени начала и окончания операций с базой.
 
 Для автологина создаем на сервере с базой в корне домашней директории пользователя постгрес файлик:

touch /var/lib/pgsql/.pgpass

nano /var/lib/pgsql/.pgpass

И пишем в моем случае:

10.10.10.4:5432:*:postgres:pass

В общем случае формат его такой:

hostname:port:database:username:password

Т.е.  разрешен автологин с компьютера 10.10.10.4 на стандартный порт postgresql 5432 ко всем базе на сервере для пользователя postgres, и последним указан пароль.

Даем нужные права на файлик:

chmod 600 /var/lib/pgsql/.pgpass
chown postgres:postgres /var/lib/pgsql/.pgpass
Осталось добавить его в cron для автоматического запуска например час ночи воскресенья:
 
crontab -u root -e
 
добавляем строку:
 
0 1 * * 7 /root/reindex.sh
 
и сохраняем.

Sept. 28, 2012

PostgreSQL : Документация: 9.6: REINDEX : Postgres Professional

REINDEX

REINDEX — перестроить индексы

Краткий обзор 

ПЕРЕИНДЕКС [ ( ПОДГОЛ. ) ] { ИНДЕКС | ТАБЛИЦА | СХЕМА | БАЗА ДАННЫХ | СИСТЕМА }   имя

Описание

REINDEX перестраивает индекс, используя данные, хранящиеся в таблице индекса, заменяя старую копию индекса. Существует несколько сценариев использования REINDEX :

  • Индекс поврежден и больше не содержит достоверных данных. Хотя теоретически этого никогда не должно происходить, на практике индексы могут быть повреждены из-за программных ошибок или аппаратных сбоев. REINDEX предоставляет метод восстановления.

  • Индекс «раздулся», т.е. содержит много пустых или почти пустых страниц. Это может произойти с индексами B-дерева в PostgreSQL при некоторых необычных шаблонах доступа. REINDEX позволяет сократить занимаемое индексом пространство путем записи новой версии индекса без мертвых страниц. Дополнительную информацию см. в Разделе 24.2.

  • Вы изменили параметр хранения (например, коэффициент заполнения) для индекса и хотите убедиться, что изменение полностью вступило в силу.

  • Не удалось построить индекс с параметром CONCURRENTLY , оставив «недействительный» индекс. Такие индексы бесполезны, но может быть удобно использовать REINDEX для их перестроения. Обратите внимание, что REINDEX не будет выполнять параллельную сборку. Чтобы построить индекс, не мешая работе, вы должны удалить индекс и перевыпустить CREATE INDEX CONCURRENTLY команда.

Параметры

ИНДЕКС

Повторное создание указанного индекса.

ТАБЛИЦА

Воссоздать все индексы указанной таблицы. Если у таблицы есть вторичная таблица «TOAST», она также переиндексируется.

СХЕМА

Воссоздать все индексы указанной схемы. Если у таблицы этой схемы есть вторичная таблица «TOAST», она также переиндексируется. Также обрабатываются индексы общих системных каталогов. Эта форма REINDEX не может выполняться внутри блока транзакции.

БАЗА ДАННЫХ

Повторно создать все индексы в текущей базе данных. Также обрабатываются индексы общих системных каталогов. Эта форма REINDEX не может выполняться внутри блока транзакции.

СИСТЕМА

Воссоздать все индексы системных каталогов в текущей базе данных. Включены индексы общих системных каталогов. Индексы пользовательских таблиц не обрабатываются. Эта форма REINDEX не может выполняться внутри блока транзакции.

имя

Имя определенного индекса, таблицы или базы данных, которые необходимо переиндексировать. Имена индексов и таблиц могут быть дополнены схемой. В настоящее время REINDEX DATABASE и REINDEX SYSTEM могут переиндексировать только текущую базу данных, поэтому их параметр должен соответствовать имени текущей базы данных.

VERBOSE

Печатает отчет о ходе переиндексации каждого индекса.

Примечания

Если вы подозреваете повреждение индекса в пользовательской таблице, вы можете просто перестроить этот индекс или все индексы в таблице, используя REINDEX INDEX или REINDEX TABLE .

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

Один из способов сделать это — выключить сервер и запустить однопользовательский сервер PostgreSQL с параметром -P , включенным в его командную строку. Затем можно выдать REINDEX DATABASE , REINDEX SYSTEM , REINDEX TABLE или REINDEX INDEX , в зависимости от того, сколько вы хотите реконструировать. Если вы сомневаетесь, используйте REINDEX SYSTEM , чтобы выбрать реконструкцию всех системных индексов в базе данных. Затем завершите сеанс однопользовательского сервера и перезапустите обычный сервер. См. справочную страницу postgres для получения дополнительной информации о том, как взаимодействовать с однопользовательским серверным интерфейсом.

В качестве альтернативы можно запустить обычный сеанс сервера, указав -P в параметрах командной строки. Способ выполнения этого зависит от клиента, но во всех клиентах на базе libpq можно перед запуском клиента установить для переменной среды PGOPTIONS значение -P . Обратите внимание, что хотя этот метод не требует блокировки других клиентов, все же может быть разумным запретить другим пользователям подключаться к поврежденной базе данных до тех пор, пока ремонт не будет завершен.

REINDEX аналогичен удалению и воссозданию индекса в том смысле, что содержимое индекса перестраивается с нуля. Тем не менее, соображения блокировки довольно разные. REINDEX блокирует запись, но не чтение родительской таблицы индекса. Также требуется блокировка ACCESS EXCLUSIVE для определенного обрабатываемого индекса, которая блокирует операции чтения, пытающиеся использовать этот индекс. Напротив, DROP INDEX на мгновение принимает ACCESS EXCLUSIVE 9.0011 блокирует родительскую таблицу, блокируя как запись, так и чтение. Последующий CREATE INDEX блокирует запись, но не чтение; поскольку индекса нет, никакое чтение не попытается его использовать, а это означает, что не будет блокировки, но чтение может быть принудительно выполнено в виде дорогостоящих последовательных сканирований.

Для переиндексации отдельного индекса или таблицы необходимо быть владельцем этого индекса или таблицы. Для переиндексации базы данных необходимо быть владельцем базы данных (обратите внимание, что владелец может перестраивать индексы таблиц, принадлежащих другим пользователям). Конечно, суперпользователи всегда могут переиндексировать что угодно.

Примеры

Перестроить один индекс:

ПЕРЕИНДЕКС ИНДЕКС my_index;

Перестроить все индексы в таблице my_table :

ПЕРЕИНДЕКС ТАБЛИЦА my_table;

Перестроить все индексы в конкретной базе данных, не полагаясь на то, что системные индексы уже действительны:

$   экспорт PGOPTIONS="-P"  
$   psql сломанный_дб  
...
сломанный_db=> ПЕРЕИНДЕКС БАЗЫ ДАННЫХ сломанный_db;
сломанный_дб => \ q

Совместимость

В стандарте SQL нет команды REINDEX .

epub пдф

Переиндексация базы данных

В некоторых случаях индексы базы данных могут быть повреждены, и вам необходимо перестроить их с помощью команды REINDEX.

Как переиндексировать базу данных

Перестроить индексы очень просто:

  1. Подключиться к базе данных
  2. Выполните следующий запрос: ПЕРЕИНДЕКС БАЗЫ ДАННЫХ db_name;
  3. Если вы используете несколько баз данных, повторите шаги для каждой базы данных.
  4. Если Postgres.app показывает предупреждение о переиндексации, теперь вы можете скрыть его, нажав «Подробнее», а затем «Скрыть это предупреждение».

Зачем мне переиндексировать мои базы данных?

В некоторых случаях индексы могут быть повреждены. Это может произойти из-за ошибок в PostgreSQL или из-за изменений в macOS.

Ошибки в PostgreSQL, требующие REINDEX, обычно упоминаются в примечаниях к выпуску PostgreSQL.

Изменения в macOS, требующие REINDEX, к сожалению, нигде не задокументированы. Единственное проблемное изменение, о котором мы знаем в настоящее время, заключается в том, что в macOS 11 изменился порядок сортировки текста по умолчанию (сопоставление), что означает, что индексы, созданные или обновленные в более ранних версиях macOS, больше недействительны и должны быть перестроены.

Как узнать, затронута ли моя база данных?

Очень сложно сказать, повреждены индексы или нет. Postgres.app пытается обнаружить некоторые сценарии, в которых индекс мог быть поврежден, и показывает предупреждение, предлагающее переиндексировать вашу базу данных.

Если вы не уверены, мы рекомендуем выполнить операцию переиндексации, чтобы быть в безопасности.

Сколько времени занимает переиндексация?

Для небольших баз данных это займет всего несколько секунд, но если у вас много данных, это может занять несколько минут.

Вы можете использовать команду REINDEX (VERBOSE) DATABASE db_name; для просмотра сообщений о состоянии, если у вас большая база данных.

Обратите внимание, что для выполнения переиндексации необходимо выполнение любых параллельных записывающих транзакций. к концу, и новым транзакциям и сеансам может потребоваться дождаться завершения переиндексации. Если любой клиент держит транзакцию записи открытой, операция переиндексации будет заблокирована и будет ждать что без всякого предупреждения. При необходимости вы можете отменить и перезапустить операцию.

Что произойдет, если я не переиндексирую?

  • Запросы могут возвращать неправильные результаты (скорее всего, отсутствующие строки)
  • Столбцы UNIQUE могут содержать повторяющиеся значения

Устранение неполадок

  • Если вы видите «ОШИБКА: должен быть владельцем базы данных» , вы должны подключиться к базе данных либо как суперпользователь, либо как владелец базы данных
  • Если вы видите такие ошибки, как ОШИБКА: не удалось создать уникальный индекс , обратите внимание на сообщение и детали, подключитесь к соответствующей базе данных и вручную разрешить уникальный конфликт. При запросе данных старайтесь избегать использования индексов, например. выпустив SET enable_indexscan = выкл.; SET enable_indexonlyscan = выкл.; УСТАНОВИТЬ enable_bitmapscan = выкл.; в сеансе, который вы используете для этого. Затем повторите операцию переиндексации.

Если явно не запрошено иное (сопоставления ICU), PostgreSQL использует (правила сортировки текста, зависящие от языка), предоставляемые операционной системой. PostgresApp устанавливает локаль по умолчанию (и, следовательно, сопоставление) на «en_US.UTF-8» при инициализации нового кластер с PostgresQL 9.4.1 (конец 2014 г.). Однако сопоставления на основе UTF-8 не фактически реализовано в macOS (как и в большинстве систем BSD). Таким образом, эффективный порядок сортировки скорее следовал порядку байтов, см. Issue #216.

С обновлением до macOS 11 Apple начала использовать параметры сортировки ISO8859-1 примерно в половине случаев. доступных локалей, включая локаль Postgres.app по умолчанию, en_US. UTF-8. В качестве Индексы базы данных сохраняют порядок элементов во время их вставки, повреждения может произойти, если правила сортировки позже изменятся.

Влияют ли на мою базу данных изменения параметров сортировки macOS?

Postgres.app записывает версию macOS, где был вызван initdb , а также все версии macOS, на которой был запущен сервер. Поскольку эта информация недоступна для старых каталоги данных, Postgres.app угадывает версию macOS, используемую для вызова initdb для существующие каталоги данных на основе истории установки обновлений macOS. Если Postgres.app обнаруживает, что каталог данных использовался как до macOS 11, так и после macOS 11 или macOS версия, используемая для initdb каталога данных, неизвестна, она показывает предупреждение о переиндексации.

Таким образом, предупреждение является хорошим индикатором, но может быть не совсем точным. Если вы предпочитаете выполните ручную оценку, вот несколько рекомендаций:

Вас не затронет, если:

  • Кластер базы данных («Сервер» в PostgresApp) был инициализирован в macOS 11 или более поздней версии.
  • Вы все еще используете macOS 10.15 или более раннюю версию.
  • initdb был запущен вручную (не с помощью кнопки «Инициализировать» в PostgresApp) с --no-locale , --lc-collate или --locale установлен на «C» или «POSIX» или на незатронутый locale и никакие другие сопоставления на основе libc не используются для столбцов или индексов. База данных сортировку по умолчанию можно показать с помощью SELECT datname, datcollate FROM pg_database; , использование сопоставлений на уровне объекта можно определить, объединив pg_depend с pg_collation .
  • База данных восстановлена ​​из логического дампа ( pg_dump / pg_dumpall ) после обновления версия ОС (исключение: диапазон ключей разделов, см. ниже).

Вы, вероятно, пострадали, если:

  • ОС была обновлена ​​с macOS 10.15 или более ранней версии до macOS 11 или более поздней версии «на месте» с существующая база данных PostgreSQL.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *