Журнал транзакций PostgreSQL работает следующим образом: все изменения в файлах данных (в которых находятся таблицы и индексы) производятся только после того, как они были занесены в журнал транзакций, при этом записи в журнале должны быть гарантированно записаны на диск.
В этом случае нет необходимости сбрасывать на диск изменения данных при каждом успешном завершении транзакции: в случае сбоя БД может быть восстановлена по записям в журнале. Таким образом, данные из буферов сбрасываются на диск при проходе контрольной точки: либо при заполнении нескольких (параметр checkpoint_segments, по умолчанию 3) сегментов журнала транзакций, либо через определённый интервал времени (параметр checkpoint_timeout, измеряется в секундах, по умолчанию 300).
Изменение этих параметров прямо не повлияет на скорость чтения, но может принести большую пользу при активной модификации/добавлении данных в базе.
fsync
Данный параметр отвечает за сброс данных из кэша на диск при завершении транзакций.
Наиболее радикальное из возможных решений — выставить значение off параметру fsync. При этом записи в журнале транзакций не будут принудительно сбрасываться на диск, что даст большой прирост скорости записи. Учтите: вы жертвуете надёжностью, в случае сбоя целостность базы будет нарушена, и её придётся восстанавливать из резервной копии!
Использовать этот параметр рекомендуется лишь в том случае, если вы всецело доверяете своему серверу и своему источнику бесперебойного питания. Ну или если данные в базе не представляют для вас особой ценности. Для Windows — крайне не рекомендуется.
synchronous_commit
Параметр synchronous_commit определяет нужно ли ждать WAL записи на диск перед возвратом успешного завершения транзакции для подключенного клиента. По умолчанию и для безопасности данный параметр установлен в on (включен).
При выключении данного параметра (off) может существовать задержка между моментом, когда клиенту будет сообщенно об успехе транзакции и когда та самая транзакция действительно гарантированно и безопасно записана на диск (максимальная задержка — wal_writer_delay * 3).
В отличие от fsync, отключение этого параметра не создает риск краха базы данных: данные могут быть потеряны (последний набор транзакций), но базу данных не придется восстанавливать после сбоя из бэкапа. Так что synchronous_commit может быть полезной альтернативой, когда производительность важнее, чем точная уверенность в согласовании данных.
commit_delay и commit_siblings
commit_delay (в микросекундах, 0 по умолчанию) и commit_siblings (5 по умолчанию) определяют задержку между попаданием записи в буфер журнала транзакций и сбросом её на диск. Если при успешном завершении транзакции активно не менее commit_siblings транзакций, то запись будет задержана на время commit_delay. Если за это время завершится другая транзакция, то их изменения будут сброшены на диск вместе, при помощи одного системного вызова. Эти параметры позволят ускорить работу, если параллельно выполняется много мелких транзакций.
wal_sync_method
Метод, который используется для принудительной записи данных на диск. Если fsync=off, то этот параметр не используется. Возможные значения:
open_datasync— запись данных методом open() с параметромO_DSYNC;fdatasync— вызов метода fdatasync() после каждого commit;fsync_writethrough— вызов fsync() после каждого commit, игнорируя параллельные процессы;fsync— вызов fsync() после каждого commit;open_sync— запись данных методом open() с параметромO_SYNC;
Не все эти методы доступны на разных ОС. По умолчанию устанавливается первый, который доступен для системы.
full_page_writes
Установите данный параметр в off, если fsync=off. Иначе, когда этот параметр on, PostgreSQL записывает содержимое каждой записи в журнал транзакций при первой модификации таблицы. Это необходимо, поскольку данные могут записаться лишь частично, если в ходе процесса <<упала>> ОС. Это приведет к тому, что на диске окажутся новые данные смешанные со старыми.
Строкового уровня записи в журнал транзакций может быть недостаточно, чтобы полностью восстановить данные после падения системы. full_page_writes гарантирует корректное восстановление, ценой увеличения записываемых данных в журнал транзакций (Единственный способ снижения объема записи в журнал транзакций заключается в увеличении checkpoint_interval).
wal_buffers
Количество памяти используемое в SHARED MEMORY для ведения транзакционных логов. Стоит увеличить буфер до 256–512 кБ, что позволит лучше работать с большими транзакциями.
Например, при доступной памяти 1–4 ГБ рекомендуется устанавливать 256–1024 КБ.