Настройка PostgreSQL осуществляется с помощью редактирования файла
postgresql.conf и последующего перезапуска постмастера. Несмотря на
то, что некоторые параметры можно менять “на лету”, практика показала,
что полный перезапуск позволяет намного точнее и быстрее отследить
влияние тех или иных изменений конфигурации на поведение системы в
целом, так что имеет смысл использовать именно этот метод.

Настройка shared_buffers в Postgres’е очень важна. Чем больше памяти
ему выделено, тем больше данных он сможет использовать не обращаясь к
диску. Но если памяти выделить слишком много, то другим процессам ее
может не хватить и они будут использовать файл подкачки. Поэтому с
настройкой этого параметра придется поэкспериментировать - помимо
всего прочего, очень многое зависит от конкретики вашего сайта, в
частности от того, насколько часто запрашиваются одни и те же данные.
В качестве стартовой точки можно попробовать выделить Postgres’у 40%
памяти, если он работает на одной машине с веб-сервером, и 70%, если
это выделенный сервер баз данных. Только не забудьте, что до того, как
указывать количество памяти в файле postgresql.conf, вам надо
настроить операционную систему, чтобы она разрешила эту память
забрать, иначе PostgreSQL просто не запустится, написав в лог, что не
удалось получить требуемую память. О том как настроить выделение
необходимой памяти в разных ОС подробно написано в [142]документации
PostgreSQL. Эта память выделяется один раз при старте сервера.

Следующий полезный параметр - sort_mem. Эта память используется для
сортировки полученных наборов данных, и большое ее количество полезно,
если ваши запросы часто используют SELECT … ORDER BY… Но с этим
параметром надо быть очень осторожным - мало того, что указанное вами
количество памяти выделяется каждому процессу, так оно еще и может
выделяться несколько раз для сложных запросов! Так что с этим
параметром тоже стоит “поиграть” - попробуйте изменять его значения в
диапазоне, скажем, от 1 Мб до 128 Кб. Причем иногда результаты бывают
парадоксальными - уменьшение памяти ведет к повышению
производительности, по всей видимости, из-за создания множества
маленьких временных файлов, которые операционная система успешно
кеширует в свободной памяти.

Если задачи, выполняющиеся на сервере не являются критичными и
возможная потеря нескольких записей при аварии вас не пугает, то стоит
также отменить принудительную запись на диск результатов каждой
транзакции. Делается это указанием fsync = false. При этом результаты
ваших изменений будут хранится в памяти и записываться на диск целыми
блоками, что позволит достаточно заметно увеличить производительность.
Но, как было отмечено выше, если вдруг сервер “упадет”, то результаты
нескольких последних обновлений могут быть утеряны.

Очень сильно влияет на скорость работы грамотное индексирование
таблиц. Про индексы можно писать (и уже написано) много, но основной
принцип - индексировать надо те поля, которые используется для выборки
данных (проверяются в WHERE). Составные индексы (в которых
используется несколько полей) работают, если отбор происходит с
условием AND, если используется OR, то надо создавать несколько
отдельных индексов.

Однако для маленьких таблиц (скажем, до 500 строк) перебор почти
всегда оказывается быстрее, чем использование индексов. Тут можно
применить маленькую хитрость: в postgresql.conf указать enable_seqscan
= false (это запретит перебор для тех таблиц, у которых есть индексы)
и удалить все индексы в маленьких таблицах (индексы, автоматически
создаваемые для первичных ключей, можно удалить, используя DROP
CONSTRAINT).

Неплохой выигрыш в производительности может дать и оптимизация самих
SQL запросов, особенно тех, которые используются чаще всего. Для того,
чтобы их вычислить можно в скриптах перенумеровать все запросы и перед
каждым вызовом pg_query() записывать в лог (или в таблицу) номер
запроса. А потом просто проанализировать лог… Для того, чтобы
посмотреть как будет выполняться запрос можно (нужно!) использовать
команду EXPLAIN. Учтите, что в некоторых случаях даже простое
изменение порядка следования условий выборки в секции WHERE может
изменить план выполнения запроса!

В некоторых случаях может помочь и использование представлений
(VIEWS). Дело в том, что при выдаче “обычного” SQL запроса сервер его
анализирует, создает план выполнения и потом выполняет. А если
используется представление, то анализ и составление плана производится
только при его создании. Если запросы выполняются часто, то
сэкономленное время работы процессора может оказаться весьма заметным.
Не говоря уже о том, что запросы в скриптах станут намного короче и
нагляднее…

Практически во всех руководствах (и в документации PostgreSQL) можно
встретить рекомендации регулярно запускать VACUUM ANALYZE, для сжатия
таблиц. Рекомендация правильная и полезная, но недостаточная. Практика
показала, что без более-менее регулярных запусков VACUUM FULL ANALYZE
производительность системы постепенно падает, причем чем дальше, тем
больше. Разница между VACUUM и VACUUM FULL заключается в том, что FULL
физически переписывает на диске всю таблицу таким образом, чтобы в ней
не оставалось “дырок” от удаленных или обновленных записей. Но его
недостаток в том, что во время работы таблица полностью блокируется,
что может привести к проблемам на популярном сервере - начнет
скапливаться очередь запросов, ожидающих доступа к базе, каждый запрос
требует памяти, память кончается, начинается запись в swap, из-за
отсутствия памяти сам VACUUM тоже начинает использовать swap и все
начинает работать очень-очень медленно.

Тут можно использовать следующий трюк - во время работы VACUUM’а
перенаправлять посетителей на страничку с пояснениями, что идет
профилактика и сервер восстановит свою работу через несколько минут.
При использовании веб-сервера Apache это легко делается с помощью
mod_rewrite: ваш оптимизирующий скрипт при запуске создает, а при
окончании работы удаляет файл /home/site/optimizer.pid, а в Apache
включается mod_rewrite и указывается

RewriteCond /home/site/optimizer.pid -f
RewriteRule .* /optimization_message.html

Для того чтобы уменьшить время, в течение которого посетители не могут
добраться до вашего сайта, можно перенаправлять посетителей только в
то время, когда оптимизируются большие и частоиспользуемые таблицы, а
остальные “чистить” паралльно с работой сайта. Если данные в базе
обновляются очень часто, то можно, скажем, каждый час запускать VACUUM
ANALYZE, а раз в сутки - VACUUM FULL ANALYZE. Как правило, “время
недоступности” сервера при таком подходе можно сократить до одной-двух
минут даже на очень больших сайтах.

Кроме того, надо учесть, что VACUUM не оптимизирует индексы, поэтому
после отработки VACUUM FULL ANALYZE стоит запускать еще и REINDEX.

Разумеется, очень большое влияние на производительность оказывает и
структура вашей базы данных, но эта область очень сильно зависит от
конкретной задачи и в этой статье затрагиваться не будет.