PostgreSQL Frequently Asked Qestions

English version is here

Этот FAQ основан на вопросах, заданных в Telegram-канале https://t.me/pgsql и ответах на них.

Блокировки

Как разрулить deadlock?

Главное, что нужно понять, это то, что deadlock всегда разруливается на стороне приложения и есть только 3 способа борьбы с ним:

1. Приложение, чья транзакция завершилась аварийно должно уметь обрабатывать эту ситуацию и повторять запрос.
2. Разные транзакции должны обновлять одни и те же данные в одном и том же порядке. В таком случае deadlock невозможен в принципе.
3. Приложение блокирует все строки, которые оно собирается обновить, непосредственно перед обновлением.

Что такое virtualxid?

virtualxid это "виртуальный" идентификатор транзакции, который состоит из pid бэкэнда и значения некоторого счётчика, например "2/19". virtualxid назначается транзакции, которая не обновляла строки в базе данных а значит, ей не нужно сохранять свой идентификатор в системных столбцах xmin, xmax. Делается это для того, чтобы 32 битный счётчик "реальных" транзакций не закончился слишком быстро. Как только транзакция попытается обновить данные в базе данных (или вызовет функцию txid_current()) ей будет назначен реальный xid.

Работа с базой

Какие есть утилиты для работы с PostgreSQL?

1. Официальная консольная утилита psql
2. pgAdmin-III
3. pgAdmin 4

Чем мониторить PostgreSQL?

Онлайн сервисы для мониторинга

1. okmeter.io - предоставляет довольно подробную статистику, но платный и требует создания специального пользователя в БД и запуска демона, который отвечает за отправку статистики на сервис.
2. vividcortex.com - похожий по набору функций сервис, кроме PostgreSQL поддерживает Redis, MongoDB и MySQL.
3. OpsDash - ещё один онлайн сервис для мониторинга.
4. pgwatch2 - гибкое Open-Source решение для мониторинга PostgreSQL

Локальные утилиты

1. PASH-Viewer - аналог ораклового Top Activity, написан на Java, см. Мониторим активные сессии PostgreSQL 10, как в Oracle
2. pg_activity - консольное приложение (ncurses-based) для мониторинга текущих сессий в PostgreSQL
3. pg_top - 'top' для PostgreSQL

Как мне вставить в таблицу 1 миллиард записей? Быстро.

Вставка данных будет происходить быстрее, если на таблице отсутствуют индексы и внешние ключи. Для того, чтобы загрузить данные в таблицу из файла, можно воспользоваться командой COPY. Если данные нужно сгенерировать "на лету", можно использовать функцию generate_series. Например:

create table test(id bigint);
insert into test select n from generate_series(1, 1000000000) n;

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

do $$
declare
  i integer;
begin
  for i in 1 .. 1000 loop
    insert into test select n from generate_series(1, 1000000) n;
    raise warning '% total rows inserted', i*1000000;
  end loop;
end;
$$ language plpgsql;

Почему длинные транзакции это плохо?

1. Препятствуют удалению ненужных строк автовакуумом
2. Опасность переполнения счётчика транзакций.
3. Блокировки.

Почему OLAP запросы на OLTP базе это плохо?

1. Ресуры - любые запросы потребляют ресурсы - CPU, диск, коннект к базе. Поддержание транзакции, хоть и виртуальной в нашем случае, тоже небесплатно.
2. Длинные запросы на реплике могут уронить её - например, забить временными файлами диск.
3. OLAP запросы будут плохо работать на нормализованных OLTP данных - обычно требуется много вычислений внутри запроса (это CPU) и много соединений таблиц (это диск). Также OLAP запросы часто требуют создания дополнительных индексов, которые также поддерживаются небесплатно.
4. OLAP запросы поднимают мнгого данных в кеш и, соответственно, вымывают из кеша данные, нужные для выполнения OLTP запросов (не так критично на репликах, но нужно об этом помнить).
5. При большой нагрузке на реплику она начивает отставать от мастера (WAL сегменты на реплике применяются всегда один поток), а при синхронной репликации начинают появляться задержки при коммитах на мастере.

Подробнее см. Are Long Running Transactions Bad? и Why avoid long transactions?.

Администрирование

Почему не удаляются сегменты WAL из pg_xlog?

1. Есть слот репликации, который препятствует удалению WAL файлов, проверить это можно следующим запросом:

SELECT slot_name,
       lpad((pg_control_checkpoint()).timeline_id::text, 8, '0') ||
       lpad(split_part(restart_lsn::text, '/', 1), 8, '0') ||
       lpad(substr(split_part(restart_lsn::text, '/', 2), 1, 2), 8, '0')
       AS wal_file
FROM pg_replication_slots;

2. Включено архивирование WAL, но команда archive_command не отрабатывает корректно (см. логи)
3. Количество файлов в pg_xlog ещё не превысило значение параметра wal_keep_segments
4. По какой-либо причине долгое время не происходит chekpoint (например, из-за ошибки)

Как определить, к какому расширению относится процедура?

Проанализировать вывод:

\dx+ <extension-name>