Полезные советы по PostgreSQL

Полезные советы по PostgreSQL

Используйте родственные связи

PostgreSQL, будучи, в общем-то реляционной СУБД, поддерживает и некоторые «объектные» свойства. Например, наследование таблиц. То есть вы можете на базе существующей таблицы (которая в дальнейшем так и будет называться – базовой) создать производную (или даже несколько), дополнив её новыми полями:

CREATE TABLE mytable (id serial, ware varchar, price numeric(10,2));

CREATE TABLE withdesc (description text) INHERITS mytable;

Теперь в таблице withdesc, помимо собственного поля description, будут доступны и поля родительской таблицы mytable – id, ware и price. Причём данные, записанные, скажем, в поле ware таблицы withdesc, будут видны и в mytable (поскольку физически они там и хранятся). Естественно, и наоборот, изменения mytable будут находить отражение и в withdesc.

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

«Грузите» сервер с помощью курсоров

«Классическая» схема взаимодействия между клиентской программой и сервером баз данных заключается в том, что клиент отсылает запрос на сервер, получает ответ, размещает его в своей памяти и обрабатывает в соответствии со своими задачами. Очевидно, что если выборка получается достаточно объёмная, то клиенту приходится затрачивать значительные ресурсы на её временное хранение.

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

Как видите, это напоминает обычную работу из Perl-сценария, но на этот раз выборка хранится на стороне сервера. Запросом FETCH можно получить следующую, предыдущую, текущую запись (или несколько записей начиная с текущего положения указателя). Запрос MOVE служит для перемещения указателя в том или ином направлении. Безусловно, при использовании курсоров есть свои недостатки – частые запросы к СУБД заметно сказываются на быстродействии, да и на сеть создаётся дополнительная нагрузка... Поэтому в PostgreSQL курсоры используются сравнительно редко. Тем не менее, в ряде случаев они могут оказаться полезны, например, если приходится работать с большими объёмами данных на слабенькой клиентской машине.

Нужно заметить, что в PostgreSQL курсоры поддерживают только чтение. Кроме того, курсор может быть использован только внутри транзакционного блока (BEGIN – COMMIT/ROLLBACK).

Ищите информацию в первоисточниках

В PostgreSQL можно узнать много интересного непосредственно из системных таблиц. Получить их список в интерактивном терминале позволяет директива \dS. Например, таблица pg_authid содержит сведения о зарегистрированных PostgreSQL-пользователях (для работы можно использовать представления на её базе – pg_user, pg_shadow, pg_group); из pg_index вы получите данные по индексным файлам; и т. д.

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

Не забывайте об уборке

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

Поэтому не забывайте о периодической (в идеале – ежедневной, а также внеплановой после крупных изменений в базе) очистке, которая выполняется командой VACUUM (VACUUM ANALYZE – для анализа структуры данных). Параметр VERBOSE позволит вам узнать, чем же СУБД занимается в это время.

Нужно заметить, что зачастую при установке PostgreSQL утилита очистки базы (vacuumdb) автоматически «прописывается» в crontab или сценариях ежедневного обслуживания (periodic). Но поскольку очистка сильно замедляет работу СУБД, имеет смысл проконтролировать эти установки и привести их в соответствие с режимом работы вашего сервера.

Сергей Супрунов

Комментарии могут оставлять только зарегистрированные пользователи