🐘 PostgreSQL – FAQ

Ответы на часто задаваемые вопросы / FAQ.

Введение

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

Оглавление / 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?
   2.10. Что не так со звездочками (*)?
   2.11. Утилита pg_dump создает бэкапы?
   

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?

Стоит отметить, что у сообщества нет единого мнения по данному вопросу. Однако, настоятельно не рекомендуется использовать в идентификаторах (именах объектов) что-то, отличное от символов латинского алфавита из нижнего регистра (a-z), арабских цифр (0-9) и символа нижнего подчеркивания (_), т.к. это потенциально может привести к техническим проблемам. В том числе использование символов из верхнего регистра (напр., A-Z) имеет нюансы, описанные ранее. Как следует из вышесказанного, символы русского алфавита также не рекомендуются.

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

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

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

   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).

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

---

2.10. Что не так со звездочками (*)?

Не используйте SELECT/RETURNING * кроме как для отладки. Где бы то ни было. Сразу по нескольким причинам, в т.ч.:

• Производительность — чем больше столбцов возвращает запрос, тем больше нагрузка на сеть, сериализацию данных и т.д. В некоторых случаях может и вовсе быть "лишний поход" в TOAST за теми данными, которые на самом деле не нужны, что является достаточно дорогой операцией;
• Безопасность — при добавлении новых, в т.ч. системных/конфедициальных столбцов, в используемые в запросе таблицы, они случайно могут "протечь" туда, куда не следует (вплоть до конечного клиента в запущенных случаях);
• Предсказуемость и стабильность поведения — в общем случае непонятно, что такое "вернуть всё", особенно с течением времени (и результаты могут быть довольно неожиданными).

Например:

SELECT * FROM users; -- Плохо
SELECT id, name FROM users; -- Хорошо

2.11. Утилита pg_dump создает бэкапы?

Ознакомившись с документацией (и не только) можно предположить, что pg_dump является утилитой для создания бэкапов. Однако, в действительности, она создает не полноценный бэкап, а лишь логический дамп содержимого базы данных, на основании которого в дальнейшем можно попытаться "восстановить" состояние БД. Среди прочего:

• Восстановленные (через pg_restore) объекты получают другие OID;
• Время восстановления труднопрогнозируемо (в т.ч. при построении индексов) и в среднем заметно дольше, чем у подходящих инструментов;
• В ряде случаев pg_dump может аварийно завершиться или выдать некорректные результаты (пример);
• Интерфейс утилиты перегружен исторически устаревшими и запутанными параметрами — это может привести к неожиданным результатам.

Для создания надежных бэкапов (бинарных копий) следует использовать, напр., pg_basebackup или иные соотв. утилиты.

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

1. Образовательные материалы от компании Postgres Professional, в т.ч. (в порядке увеличения сложности):
   1.1. Postgres: первое знакомство (книга, .pdf);
   1.2. PostgreSQL. Основы языка SQL (книга, .pdf);
   1.3. PostgreSQL. Профессиональный SQL (книга, .pdf);
   1.4. Основы технологий баз данных;
   1.5. PostgreSQL изнутри (книга, .pdf).

* Стоит заметить, что материалы выше не являются рекламой — они действительно крайне достойного качества, при этом распространяются свободно.

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

1. Документация PostgreSQL на русском языке;
2. Вредные и/или опасные действия в PostgreSQL (eng);
3. SQLize — быстрый запуск, эксперименты и обмен кодом SQL;
4. SQLtest — упражнения по SQL;
5. LeetCode Database Problems — упражнения по SQL: базовые задачи, все задачи (eng);
6. Sql-ex.ru — упражнения по SQL;
7. Официальный FAQ на русском языке;
8. Neon — туториалы по PostgreSQL, примеры использования (eng);
9. pgPedia — энциклопедия PostgreSQL (eng);
10. postgresqlco.nf — описание и управление конфигурацией (eng).