---
title: PostgreSQL. Часть 6. Регулярное обслуживание PostgreSQL
description: "# **PostgreSQL. Часть 6. Регулярное обслуживание PostgreSQL** Эта одна из публикаций цикла конспектов по PostgreSQL. Материал этих публикаций не особо структурирован и представляет собой просто заметки – конспектировалось что-то новое и то, что посчиталось интересным и важным. Эта публикация посвящена…"
url: "https://itoservice.ru/docs/postgresql-chast-6-regulyarnoe-obsluzhivanie-postgresql/"
updated: "2026-10-23"
category: PostgreSQL
---

# PostgreSQL. Часть 6. Регулярное обслуживание PostgreSQL

# **PostgreSQL. Часть 6. Регулярное обслуживание PostgreSQL**

Эта одна из публикаций цикла конспектов по PostgreSQL. Материал этих публикаций не особо структурирован и представляет собой просто заметки – конспектировалось что-то новое и то, что посчиталось интересным и важным. Эта публикация посвящена тому, как выполняется регулярное обслуживание PostgreSQL.

## **Список литературы**

При подготовке этого конспекта использовались следующие материалы:

1. Книга PostgreSQL 14 Administration Cookbook.

2. Курс DBA1: [Администрирование PostgreSQL 13. Базовый курс.](https://postgrespro.ru/education/courses/DBA1)

## **Регулярное обслуживание**

Как и любая другая ИТ-система (да и не только ИТ) PostgreSQL может требовать дополнительного обслуживания. Или, по крайней мере, требовать не отключения встроенных процессов самоочистки. Все зависит от объема данных.

Убедитесь, что функции самоочистки включены. Нужно проверить – включен ли autovacuum в конфигурационном файле postgresql.conf:

```
sudo nano /etc/postgresql/12/main/postgresql.conf
```

```
autovacuum = on
track_counts = on
```

  ![PostgreSQL. Часть 6. Регулярное обслуживание PostgreSQL - IT Аутсорсинг в Москве](https://itproblog.ru/wp-content/uploads/2024/02/image-67.png)

По умолчанию автоочистка включена.

Максимально количество процессов самоочистки работающих параллельно задается параметром autovacuum_max_workers в конфигурационном файле postgresql.conf. Каждый из этих процессов генерирует определенную нагрузку. Вся нагрузка всех процессов самоочистки суммируется и не может быть больше значения параметра autovacuum_vacuum_cost_limit в конфигурационном файле postgresql.conf. Это необходимо для того, чтобы процессы самоочистки не использовали абсолютно все ресурсы сервера. Увеличение значение параметра autovacuum_vacuum_cost_delay замедлит процесс самоочистки и немного высвободит ресурсы сервера. Однако, этого не рекомендуется делать.

Пример конфигурации более агрессивной автоматической очистки. Параметры в конфигурационном файле postgresql.conf:

```
autovacuum = on
autovacuum_max_workers = 3
autovacuum_analyze_scale_factor = 0.05
autovacuum_vacuum_cost_delay = 0
autovacuum_vacuum_scale_factor = 0.1
```

Пример конфигурации менее агрессивной автоматической очистки. Параметры в конфигурационном файле postgresql.conf:

```
autovacuum = on
autovacuum_max_workers = 3
autovacuum_analyze_scale_factor = 0.1
autovacuum_vacuum_cost_delay = 5
autovacuum_vacuum_scale_factor = 0.2
```

### **Устранение проблем с раздуванием**

Увеличение размеров таблицы, индекса или базы данных в большинстве случае вызвано долгим выполнением какого-либо запроса или долгой транзакцией.

Самый первый шаг в диагностике – посмотреть самый старый снимок данных:

```
SELECT now() -
CASE
WHEN backend_xid IS NOT NULL
THEN xact_start
ELSE query_start END
AS age
, pid
, backend_xid AS xid
, backend_xmin AS xmin
, state
FROM pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY 1 DESC;
```

  ![PostgreSQL. Часть 6. Регулярное обслуживание PostgreSQL - IT Аутсорсинг в Москве](https://itproblog.ru/wp-content/uploads/2024/02/image-68.png)

Если какие-то сессии находятся в состоянии “idle_in_transaction”, то есть смысл установить значение параметра “idle_in_transaction_session_timeout”, например, в минуту или пару минут (зависит от профиля нагрузки на сервер и приложение, которое использует БД). Штатное значение этого параметра равно нулю, т.е. сессии или транзакции никогда не завершаются и потенциально могут выполняться день, два или более.

Если использование параметра “idle_in_transaction_session_timeout”не представляется возможным, то можно рассмотреть параметр “old_snapshot_threshold”. Он определяет время, после которого неиспользуемые строки могут быть удалены. Если какой-то запрос попытается прочитать такие строки, то он будет отменен. Для всех других запросов, время выполнения которых меньше значения параметра “old_snapshot_threshold ” никаких негативных эффектов не будет.

### **Обслуживание индексов**

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

Например, можно переиндексировать одну конкретную базу данных:

```
reindexdb
```

  ![PostgreSQL. Часть 6. Регулярное обслуживание PostgreSQL - IT Аутсорсинг в Москве](https://itproblog.ru/wp-content/uploads/2024/02/image-69.png)

Переиндексировать все базы данных:

```
reindexdb -a
```

  ![PostgreSQL. Часть 6. Регулярное обслуживание PostgreSQL - IT Аутсорсинг в Москве](https://itproblog.ru/wp-content/uploads/2024/02/image-70.png)

Лучше всего индексировать отдельные таблицы, т.к. устанавливается AccessExclusiveLock в процессе перестроения индекса..

Можно запустить более “мягкое” перестроение индекса, но оно может завершиться с ошибкой. Пример запроса:

```
REINDEX INDEX CONCURRENTLY test_category_idx;
```

  ![PostgreSQL. Часть 6. Регулярное обслуживание PostgreSQL - IT Аутсорсинг в Москве](https://itproblog.ru/wp-content/uploads/2024/02/image-71.png)

Оптимальнее всего выполнять перестроение индекса для одной таблицы в один момент времени. Пример использования консольной утилиты:

```
sudo -u postgres reindexdb --dbname app --concurrently -t postgres.t --verbose
```

  ![PostgreSQL. Часть 6. Регулярное обслуживание PostgreSQL - IT Аутсорсинг в Москве](https://itproblog.ru/wp-content/uploads/2024/02/image-72-1024x126.png)

### **Индексы**

Запрос для определения частоты использования индексов:

```
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes ORDER BY idx_scan;
```

  ![PostgreSQL. Часть 6. Регулярное обслуживание PostgreSQL - IT Аутсорсинг в Москве](https://itproblog.ru/wp-content/uploads/2024/02/image-73.png)

### **Осторожное удаление индексов**

Не всегда безопасно удалять индекс сразу. Можно на время его “отключить”.

Запрос для отображения отключенных индексов:

```
SELECT ir.relname AS indexname
, it.relname AS tablename
, n.nspname AS schemaname
FROM pg_index i
JOIN pg_class ir ON ir.oid = i.indexrelid
JOIN pg_class it ON it.oid = i.indrelid
JOIN pg_namespace n ON n.oid = it.relnamespace
WHERE NOT i.indisvalid;
```

  ![PostgreSQL. Часть 6. Регулярное обслуживание PostgreSQL - IT Аутсорсинг в Москве](https://itproblog.ru/wp-content/uploads/2024/02/image-74.png)

Процедура:

1. Создать функцию для отключения индекса:

```
CREATE OR REPLACE FUNCTION trial_drop_index(iname
TEXT) RETURNS VOID
LANGUAGE SQL AS $$ UPDATE pg_index
SET indisvalid = false
WHERE indexrelid = $1::regclass;
$$;
```

  ![PostgreSQL. Часть 6. Регулярное обслуживание PostgreSQL - IT Аутсорсинг в Москве](https://itproblog.ru/wp-content/uploads/2024/02/image-75.png)

2. Применить её к потенциально неиспользуемому индексу.

```
select trial_drop_index('idx_t_s');
```

  ![PostgreSQL. Часть 6. Регулярное обслуживание PostgreSQL - IT Аутсорсинг в Москве](https://itproblog.ru/wp-content/uploads/2024/02/image-77.png)

3. Еще раз проверит список отключенных индексов:

```
SELECT ir.relname AS indexname
, it.relname AS tablename
, n.nspname AS schemaname
FROM pg_index i
JOIN pg_class ir ON ir.oid = i.indexrelid
JOIN pg_class it ON it.oid = i.indrelid
JOIN pg_namespace n ON n.oid = it.relnamespace
WHERE NOT i.indisvalid;
```

  ![PostgreSQL. Часть 6. Регулярное обслуживание PostgreSQL - IT Аутсорсинг в Москве](https://itproblog.ru/wp-content/uploads/2024/02/image-78.png)

4. Функция для отмены удаления:

```
CREATE OR REPLACE FUNCTION trial_undrop_index(iname
TEXT) RETURNS VOID
LANGUAGE SQL AS
$$ UPDATE pg_index
SET indisvalid = true
WHERE indexrelid = $1::regclass;
$$;
```

  ![PostgreSQL. Часть 6. Регулярное обслуживание PostgreSQL - IT Аутсорсинг в Москве](https://itproblog.ru/wp-content/uploads/2024/02/image-79.png)

5. Пример включения ранее отключенного индекса:

```
select trial_undrop_index('idx_t_s');
```

  ![PostgreSQL. Часть 6. Регулярное обслуживание PostgreSQL - IT Аутсорсинг в Москве](https://itproblog.ru/wp-content/uploads/2024/02/image-80.png)