Настройка памяти в PostgreSQL

Настройки в postgresql.conf

Для облегчения загрузки больших объемов данных в базу данных PostgreSQL, вам может понадобиться увеличить значения shared_buffers и max_locks_per_transaction в файле postgresql.conf.

shared_buffers

Параметр shared_buffers задает объем памяти, используемой для буферов разделяемой памяти. В документации PostgreSQL указано, что по причинам производительности вам возможно потребуется использовать большее значение параметра, чем минимальное значение в 128 КБ, либо значение в 16 KB раз больше, чем число установленное для параметра max_connections. Рекомендуется, чтобы shared_buffers был установлен на использование нескольких десятков MB для установленного экземпляра.

При загрузке больших объемов данных, вы часто нуждаетесь в более высоком значении shared_buffers, чем значение установленное по умолчанию в 32 МБ. После изменения этого параметра в файле postgresql.conf, вам необходимо перезапустить кластер базы данных.

max_locks_per_transaction

Значение max_locks_per_transaction указывает число объектов базы данных, которые могут быть заблокированы одновременно. В большинстве случаев, значения по умолчанию 64 бывает достаточно. Тем не менее, при загрузке большого количества данных (например, несколько тысяч) сразу, число одновременных заблокированных для транзакции объектов может превысить 64.

Это не взаимосвязь один-к-одному между конкурентными блокировками и количеством наборов данных; другими словами, если вы загружаете 3000 наборов данных, вам не нужно увеличивать max_locks_per_transaction до 3000. Начните с увеличения значения до 100, до массовой загрузки.

При изменении параметра max_locks_per_transaction, вы должны перезагрузить сервер.

Увеличение значения любого из данных параметров может привести к запросу базой данных большего объема разделяемой памяти, чем допускает ваша операционная система UNIX (OS). Для информации о том, как вы можете увеличить установку разделяемой памяти для вашей операционной системы, ознакомьтесь с разделом Управление ресурсами ядра (Managing Kernel Resources) в Документации PostgreSQL.

Повышение производительности пространственных запросов SQL

При выполнении SQL-запросов, которые возвращают пространственных столбцы ST_Geometry из таблицы бизнеса, можно повысить производительность запросов, если вы установите системную переменную среды, ST_GEOMETRY_OUTPUT_FORMAT, для вывода в тип ST_Geometry, а не в расширенное представление Well-Known Text (WKT).

По умолчанию, ST_GEOMETRY_OUTPUT_FORMAT установлен на TYPE, что означает, что возвращается hexabinary-представление. Это необходимо для создания работоспособной резервной копии базы геоданных. Вы должны только изменить эту переменную ST_GEOMETRY, если вы хотите улучшить производительность SQL-запросов. Если вы установите эту переменную, потому что вы планируете сделать ряд пространственных запросов SQL, убедитесь, что удалили её после того, как вы завершите ваши запросы, а затем перезапустите кластер базы данных PostgreSQL.

Переменная должна быть установлена на компьютере, где запущен PostgreSQL.

Для ОС Linux, установите переменную оболочки, из которой запускаете SQL-запросы. Для оболочки bash, синтаксис выглядит следующим образом:

ST_GEOMETRY_OUTPUT_FORMAT=ST_GEOMETRY

Для оболочки csh, синтаксис выглядит следующим образом:

setenv ST_GEOMETRY_OUTPUT_FORMAT ST_GEOMETRY

В Windows, создайте системную переменную среды в свойствах системы.

Имя переменной: ST_GEOMETRY_OUTPUT_FORMAT

Значение переменной: ST_GEOMETRY

После установки данной переменной, вам необходимо перезапустить кластер базы данных PostgreSQL.