🐘 PostgreSQL – FAQ

Ответы на часто задаваемые вопросы начинающих DBA / FAQ for DBA beginners.

Введение

❗ В первую очередь, мы должны уметь думать самостоятельно. В особенности, это касается проблем, которые у вас возникают — сначала тщательно и усердно попытайтесь нагуглить решение, и только в случае неудачи задавайте вопросы (как это делать правильно — см. ниже).
Уважайте время и труд других участников сообщества, общайтесь исключительно в вежливой манере. Это первостепенные и необходимые условия для того, чтобы приумножать свои навыки и расти как профессионал.

Оглавление / Table of Contents

1. Сообщество

2. Вопросы
   2.1. Не могу подсоединиться к PostgreSQL. Что делать?
   2.2. Получаю ошибку что имя таблицы/колонки/etc неверное, хотя это не так. В чем дело?
   2.3. Как публиковать SQL запросы, определения функций, выводы команд вспомогательных утилит и прочую текстовую информацию при запросе помощи у сообщества?
   2.4. У меня медленно работает SQL запрос и я хочу попросить помощи у сообщества. Какую информацию мне необходимо предоставить?
   2.5. У меня не получается написать SQL запрос/etc и/или я получаю ошибки. Какую информацию мне необходимо предоставить для получения помощи от сообщества?
   2.6. В каком стиле писать код и идентификаторы в PostgreSQL?
   2.7. Секционирование (партиционирование)
   

   • 2.7.1. Какие убедительные причины (не)использования секционирования?
     
   • 2.7.2. Какие плюсы и минусы есть у секционирования?
     
   • 2.7.3. Какие нюансы есть при работе с секционированием?
     

   2.8. База/таблица весит больше чем я ожидаю, что делать?
   2.9. Как работает тип timestamp with time zone?
   

3. Книги/курсы

4. Полезные ссылки

5. Переводы / Translations
   5.1. English

1. Сообщество 👨‍👩‍👧‍👦

У PostgreSQL прекрасное вежливое сообщество, которое обладает заметным свойством толерантности к начинающим участникам. Среди основных точек входа стоит отметить следующее:

1. Чат русскоязычного сообщества PostgreSQL в Telegram;
2. PGDay.ru - Ежегодная конференция по PostgreSQL (Санкт-Петербург);
3. PGConf.ru - Ежегодная конференция по PostgreSQL (Москва).

2. Вопросы ⁉️

2.1. Не могу подсоединиться к PostgreSQL. Что делать?

Пожалуй, это наиболее частый вопрос у начинающих пользователей PostgreSQL, ежедневно звучащий в информационной среде сообщества. Как правило, они получают ошибки, содержащие ключевые слова Connection refused или Connection ... failed. Например:

error: connection to server at "localhost", port 5432 failed: Connection refused 

или (в случае попытки подключения через unix domain socket):

error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
    Is the server running locally and accepting connections on that socket?

Следует провести диагностику проблемы поэтапно (в настоящее время инструкция применительна только для Unix-based ОС):

2.1.1. Запущен ли основной процесс PostgreSQL?

Посмотреть, запущен ли основной процесс PostgreSQL, можно выполнив след. команду на сервере:

ps -ef | grep "postgresql.*config_file"

В случае, если PostgreSQL запущен, в выводе команды вы должны увидеть нечто вроде:

<PG_PATH> -D <DATA_DIR> -c config_file=<CONFIG_PATH>

Где <PG_PATH> — путь до исполняемого файла PostgreSQL, <DATA_DIR> — путь до директории с данными кластера, <CONFIG_PATH> — пусть до конфигурационного файла postgresql.conf (потребуется для дальнейшей диагностики).

В противном случае, проблема найдена: PostgreSQL не запущен. При этом, если попытка запуска СУБД окажется неудачной — в первую очередь обратите внимание на её логи.

2.1.2. Верен ли TCP-порт, открываемый сервером PostgreSQL?

Теперь необходимо убедиться, что PostgreSQL слушает нужный вам TCP-порт (если, конечно, вы не собираетесь работать только через Unix-сокеты). Откройте конфигурационный файл postgresql.conf (путь до него мы определили этапом выше) и найдите параметр port. Там должно быть значение необходимого вам порта, который по умолчанию равен 5432. В противном случае, исправьте номер порта на нужное вам значение. После применения изменений необходимо произвести рестарт PostgreSQL.

* В то же время, можно скорректировать значения порта непосредственно на клиенте (том самом, где возникла ошибка соединения) — всё зависит от конкретной цели, которую вы преследуете.

После рестарта PostgreSQL можно удостовериться, что нужный вам порт действительно прослушивается. Для этого можно выполнить след. команду на сервере (для порта 5432):

ss -ln | grep ":5432"

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

2.1.3. Верны ли адреса TCP/IP, по которым PostgreSQL принимает подключения?

Параметром в файле postgresql.conf, отвечающим за то, через какие сетевые интерфейсы PostgreSQL будет принимать соединения, является listen_addresses.

Если вы хотите подключиться к PostgreSQL локально (т.е. с того же сервера), подойдут значения 127.0.0.1 (для подключения по IPv4), ::1 (для подключения по IPv6) или localhost (в современных ОС данное доменное имя, как правило, транслируется в ::1. Подробнее см. тут). Стоит отметить, что в параметре listen_addresses, как следует из его названия, можно указать несколько значений через запятую. Учтите, что старые клиентские приложения, в подавляющем большинстве случаев, работают по IPv4.

Если вы хотите подключиться к PostgreSQL удаленно (т.е. с другого сервера), то необходимо принимать подключения с соотв. внешних интерфейсов. С помощью значения 0.0.0.0 можно принимать подключения со всех адресов IPv4, а :: — все адреса IPv6. В то же время, если указать значение *, то PostgreSQL будет принимать подключения со всех имеющихся сетевых интерфейсов.

Подробнее о подключениях и аутентификации к PostgreSQL см. тут. Напомним, что после применения изменений в файл postgresql.conf необходимо произвести рестарт PostgreSQL.

❗ В случае, если вы указали PostgreSQL прослушивать внешние интерфейсы (т.е., что-то, отличное от значений 127.0.0.1, ::1 или localhost параметра listen_addresses), то ваш сервер может быть доступне извне (т.е. из Интернета) и потенциально находится под угрозой. Чтобы избежать негативных последствий, необходимо корректно настроить ваш firewall и файл pg_hba.conf. Подробнее о них см. ниже.

2.1.4. Верна ли конфигурация аутентификации клиентов PostgreSQL?

Несмотря на то, что параметр listen_addresses в файле postgresql.conf настроен верно, PostgreSQL всё ещё может отвергать соединения. Причиной может быть то, что в конфигурационном файле pg_hba.conf нет соответствующего разрешения.

Подробнее о файле pg_hba.conf см. тут (включая примеры настройки).

2.1.5. Верна ли конфигурация firewall?

Даже если PostgreSQL настроен верно (с точки зрения подключения клиентов), вы всё ещё можете иметь неудачные попытки подключения. В этом случае вам необходимо обратить внимание на правильность настройки firewall (он может быть как на уровне ОС, так и на уровне ваших сетевых аппаратных/виртуальных устройств, напр., роутера). Конкретные шаги выходят за рамки данного FAQ.

---

2.2. Получаю ошибку что имя таблицы/колонки/etc неверное, хотя это не так. В чем дело?

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

Пример воспроизведения и решения проблемы:

SELECT * FROM TableName; -- error: relation "tablename" does not exist
SELECT * FROM "TableName"; -- OK

Обратите внимание, что идентификатор TableName без двойных кавычек был преобразован в tablename, что вызвало ошибку.

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

---

2.3. Как публиковать SQL запросы, определения функций, выводы команд вспомогательных утилит и прочую текстовую информацию при запросе помощи у сообщества?

Прежде всего, не используйте скриншоты для того, чтобы показать SQL запросы (и/или их результаты), определения функций, выводы команд вспомогательных утилит (таких, как psql и др.) и прочую текстовую информацию — это затрудняет их анализ, скорость/удобство воспроизведения проблемы и переиспользования вашего кода. Заметно эффективнее будет публикация оных, напр., на gist или pastebin.com.

При этом, если вы задаете вопрос в tg-чате и кол-во содержимого невелико, его следует опубликовать в режиме форматирования Monospace непосредственно в чат, используя ```language syntax, выбрав в кач-ве языка sql (таким образом будет достигнута подсветка синтаксиса SQL). Как правило, этот вопрос тесно связан с п. 2.4 и п. 2.5.

Многие участники сообщества принципиально не рассматривают скриншоты (и на это есть рациональные причины), поэтому постарайтесь оформить свой текст правильно.

---

2.4. У меня медленно работает SQL запрос и я хочу попросить помощи у сообщества. Какую информацию мне необходимо предоставить?

Чтобы получить адекватную помощь по оптимизации запроса, необходимо немного постараться и собрать некоторые данные.
Минимальная информация для получения помощи следующая:

1. Версия PostgreSQL на сервере, где запускается запрос — можно использовать вывод SELECT version();
2. Непосредственно SQL запрос;
3. Вывод метакоманды \d+ из утилиты psql для каждой используемой в запросе таблицы;
4. EXPLAIN (ANALYZE, VERBOSE, BUFFERS, COSTS, SETTINGS, TIMING, WAL) для запроса (подробнее об EXPLAIN см. тут). Если ваша версия PostgreSQL ещё не поддерживает какие-то из опций EXPLAIN выше, то уберите их. Когда нет возможности дождаться результата запроса, можно также убрать и опцию ANALYZE.

💡 Для автоматизации этого процесса можно воспользоваться данным .sql скриптом под утилиту psql.

❗ Внимательно отнеситесь к тому, как публиковать информацию, которая требуется для ответа на ваш вопрос.

---

2.5. У меня не получается написать SQL запрос/etc и/или я получаю ошибки. Какую информацию мне необходимо предоставить для получения помощи от сообщества?

1. Кратко опишите предметную область и то, что вы хотите сделать;
2. Продемонстрируйте то, что вы уже сделали. Это докажет то, что вы попытались решить проблему самостоятельно, а также даст начальную точку для участников сообщества, которые захотят вам помочь. Если вы получаете ошибки, то их также стоит приложить к своему вопросу;
3. Выполните хотя бы один из пунктов:
   3.1. Предоставьте вывод метакоманды \d из утилиты psql для каждой таблицы, которая будет участвовать в запросе;
   3.2. Создайте тестовое окружение, которое воспроизводит ваши таблицы/данные и непосредственно проблему. Это поможет другим участникам сообщества легко и быстро приступить к изучению вашей проблемы (и, как следствие, повысит желание помогать в её решении). Для этого следует использовать такие сервисы как sqlize.online или db-fiddle.com.
   

❗ Внимательно отнеситесь к тому, как публиковать информацию, которая требуется для ответа на ваш вопрос.

---

2.6. В каком стиле писать код и идентификаторы в PostgreSQL?

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

Если обратиться к примерам официальной документации PostgreSQL (которые также имеют некоторое разночтение), то, как правило, им свойственно следующее:

1. Все ключевые слова, включая слова из DML (SELECT/INSERT/UPDATE/DELETE), DDL (CREATE/ALTER/DROP) а также DCL (GRANT/REVOKE) пишутся в верхнем регистре. Например:

   SELECT * FROM table;

2. Идентификаторы (имена таблиц, столбцов, функций и т.д.) в большинстве случаев пишутся в нижнем регистре в формате snake_case. Например:

   SELECT floor(col_name) FROM table;

---

2.7. Секционирование (партиционирование)

2.7.1. Какие убедительные причины (не)использования секционирования?

✅ Когда это может быть необходимо и/или полезно:

• PostgreSQL имеет некоторые жёсткие ограничения, которые касаются, в частности, и максимального размера отношения (чем является таблица) — 32 TB. Таким образом, если объем ваших данных в таблице приближается к данному значению, то это веский повод задуматься о секционировании в самое ближайшее время;
• Если кол-во записей в таблице начинает превышать 5-10 миллиардов строк (и её размер составляет несколько TB без учета индексов — в этом смысле важна средняя ширина строк в таблице), то это повод неспеша задуматься о секционировании;
• Если появилась необходимость отправлять часть данных таблицы в "холодное" (более дешевое, но менее производительное) хранилище/архив — секционирование может помочь. Это разумно в случаях, когда эти данные требуются только в режиме read-only, и сравнительно редко. Как правило, к этому приходят по экономическим причинам (в то же время, производительность системы так или иначе падает).

❌ Когда в этом нет необходимости и/или может быть вредно:

• Не стоит применять секционирование только потому что это "модно", без оглядки на объективные технические причины. Иначе, вполне вероятно, вы получите больше вреда нежели пользы;
• Если кол-во записей в таблице не превышет 5-10 миллиардов строк (а её размер не превышает несколько TB без учета индексов), то, в среднем, задумываться о секционировании преждевременно — разумнее будет рассмотреть вертикальное масштабирование сервера СУБД, если необходимо. Однако, следует учитывать динамику роста кол-ва данных в таблице с течением времени (соотнося её с предполагаемым временем жизни системы);
• Если вы планируете применить секционирование, физически оставив/расположив секции на одном накопителе (диске) — в большинстве случаев это сомнительная затея. Бывают и исключения, которые частично описаны в данном FAQ.

2.7.2. Какие плюсы и минусы есть у секционирования?

✅ Плюсы:

• Массовое удаление данных можно осуществить удаляя конкретные секции — что значитильно эффективнее (в нескольких смыслах), чем аналогичный DELETE statement для обычной таблицы. Очевидно, это возможно лишь в случаях, когда в этих секциях не содержится ничего кроме запланированных к удалению данных;
• Секции можно расположить на различных tablespaces или даже экземплярах PostgreSQL — используя, напр., foreign data wrappers. Это открывает возможности к горизонтальному масштабированию СУБД (впрочем, это не единственный варант).

❌ Минусы:

• На каждую секцию создается отдельный индекс. Это, помимо прочего, может привести к замедлению запросов в N раз (где N - кол-во секций). Однако, в некоторых случаях может заметно помочь partition pruning;
• Поддерживать секционированную таблицу, так или иначе, сложнее, чем обычную.

2.7.3. Какие нюансы есть при работе с секционированием?

Представим, что вы всё-таки решили использовать секционирование. В таком случае, вам следует знать следующее:

• Не следует делать слишком большое кол-во секций (и/или которые содержат слишком мало данных). В первом приближении можно исходить из того, что 10 000 секций — это верхний предел, до которого секционирование работает условно-нормально. Это связано с тем, что планировщик для каждого запроса к секционированной таблице должен решить, какие секции потребуются для работы, что требует времени;
• Из коробки PostgreSQL поддерживает декларативное секционирование и с использованием наследования, первый вариант активно развивается и является хорошей отправной точкой для погружения в данную тему. Однако, существуют и сторонние решения, которые могут помочь в этом с уклоном в горизонтальное масштабирование/шардирование (напр., citus, spqr и timescaledb).

---

2.8. База/таблица весит больше чем я ожидаю, что делать?

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

• Вы что-то удалили из таблицы посредством DELETE и ожидаете что на диске появится дополнительное свободное место, либо были обновления через UPDATE и таблица "распухла"?
  

  • При удалении строк они физически не удаляются с диска, а лишь помечаются как удаленные;
  • При обновлении строк они физически не обновляются "на месте", вместо этого создаются новые строки (старые же помечаются как удаленные — в этом смысле UPDATE можно рассматривать как атомарный DELETE + INSERT). Если не менялся primary key (при его наличии), то, логически, это та же самая строка;
  • Нередко это штатная ситуация, когда свободное место не возвращается ОС — оно будет переиспользовано СУБД в дальнейшем;
  • Эти процессы могут порождать т.н. table bloat (похожим образом могут распухать и, напр., индексы). Решение этой "проблемы" можно начать со знакомства с командой VACUUM (в т.ч. опции FULL) и механизма autovacuum, но есть и более продвинутые способы.

• Когда-то был сбой в СУБД (в т.ч. неблагоприятный откат транзакции и/или аварийное завершение работы)?
  

  • В этом случае, вероятно, могут появиться т.н. "файлы-сироты" (probably orphaned files). Они физически есть на диске, однако, уже не связаны логически с БД (т.е., фактически, представляют собой мусор). Вы можете выявить их (и далее, возможно, вручную удалить) с помощью данного скрипта.
    ⚠️ Иногда вы можете быть и не в курсе, что это произошло. Таким образом, время от времени, отслеживать "сирот" не помешает.

2.9. Как работает тип timestamp with time zone?

Прежде всего, в PostgreSQL нет встроенного типа данных, который бы хранил временную метку вместе с часовым поясом. И даже timestamp with time zone (он же timestamptz), несмотря на его название, этой информации не хранит. Однако, это не является проблемой.
В среднем, при записи в БД значения от клиента, оно на уровне СУБД преобразуется к UTC, а при запросе клиентом значения из БД оно преобразуется в локальное время, также на уровне СУБД (в т.ч. к метке добавляется часовой пояс в формате +TZ в конце строки — в этом смысле название типа ... with time zone себя в некоторой степени оправдывает).

Более подробно это поведение описывается в документации.

💡 Приведение на уровне СУБД временной метки к UTC (и из него) для типа timestamp with time zone справедливо для случаев, когда клиент и СУБД обмениваются временными метками как строками (в подавляющем большинстве случаев это так, но иногда ответственность за приведение может взять на себя клиент). Как бы то ни было, в любом случае, вышуказанный тип не будет хранить часовой пояс (и СУБД будет полагать, что там физически хранится значение в формате UTC).

❗ Во избежание путаницы, с клиента к СУБД рекомендуется передавать временную метку в виде строки где явно указан часовой пояс (в формате YYYY-MM-DD HH:MI[:SS][.FFFFFF]+TZ, где [:SS] (кол-во секунд) и [.FFFFFF] ("дробная" часть секунды) являются опциональными частями; пример: 2025-01-20 17:14:16.71292+03). Это гарантирует, что приведение к UTC внутри СУБД будет выполнено корректно вне зависимости от текущей конфигурации локальной временной зоны как на сервере, так и на клиенте.

3. Книги/курсы 📘

1. Postgres: первое знакомство (.pdf);
2. PostgreSQL изнутри (.pdf);
3. Базовый курс DBA1 от компании Postgres Professional.

4. Полезные ссылки 🔗

1. Документация PostgreSQL на русском языке;
2. Вредные и/или опасные действия в PostgreSQL (eng);
3. SQLize — быстрый запуск, эксперименты и обмен кодом SQL;
4. SQLtest — упражнения по SQL;
5. Sql-ex.ru — упражнения по SQL;
6. Официальный FAQ на русском языке;
7. Туториалы по PostgreSQL, примеры использования (eng).