Homepage: PostgreSQL

• Запросы для анализа PostgreSQL

• Tuning PostgreSQL postgresql.conf
• Резервное копирование и восстановление в PostgreSQL. WAL. Как бэкапить данные в PostgreSQL?
• Репликация PostgreSQL: Bucardo, SkyTools: Londiste и т.д.
• Индекс в базах данных: INDEX, REINDEX. HOT.
• Типы данных PostgreSQL. CONSTRAINT Ограничения целостности.
• Хранимые процедуры, функции и триггеры
• EXPLAIN
• Табличные пространства PostgreSQL

• pg_hba.conf - настройка политики доступа к базам данных и идентификации пользователей
• pg_config - эта утилита выводит параметры (например, с какими параметрами скомпилирован PostgreSQL) конфигурации текущей установленной версии PostgreSQL. Находится в пакете postgresql-devel (для ОС Fedora) postgresql-server-dev-8.3(для ОС Debian 5).
• pg_ctl является утилитой для запуска, остановки, перезапуска, перезагрузки конфигурационных файлов, информирования о состоянии сервера PostgreSQL, или отправки сигналов PostgreSQL- процессу. Показать статус севера:

  # sudo -u postgres pg_ctl status -D /var/lib/pgsql/data

• postgresql.conf - настройки сервера.

PostgreSQL (произносится «Постгре-Эс-Кю-Эль», коротко называется «Постгрес») - свободная объектно-реляционная система управления базами данных (СУБД). Использует порт 5432/tcp/udp. PostgreSQL использует только один механизм хранения данных под названием Postgres storage system (система хранения Postgres), в котором транзакции и внешние ключи полностью функциональны, в отличии от MySQL, в котором InnoDB и BDB являются единственными типами таблиц, которые поддерживают транзакции.

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

<note tip>Все объекты (таблицы, индексы …) базы данных в PostgreSQL хранятся в каталоге data/base/OID, т.е. названием каталога содержащего БД, будет не имя БД (как в MySQL), а номер (OID) БД.</note>

MVCC - одна из ключевых технологий доступа к данным, которая используется в PostgreSQL. Она позволяет осуществлять параллельное чтение и изменение записей (tuples) одних и тех же таблиц без блокировки этих таблиц. Чтобы иметь такую возможность, данные из таблицы сразу не удаляются, а лишь помечаются как удаленные. Изменение записей осуществляется путем маркировки этих записей как удаленных, и созданием новых записей с измененными полями. Таким образом, история изменений одной записи сохраняется в базе данных и доступна для чтения другими транзакциями. Этот способ хранения записей позволяет параллельным процессам иметь неблокирующий доступ к записям, которые были удалены или изменены в параллельных незакрытых транзакциях. Техника, используемая в этом подходе, относительно простая. У каждой записи в таблицы есть системные скрытые поля xmin, xmax.

• xmin - хранит номер транзакции, в которой запись была создана.
• xmax - хранит номер транзакции, в которой запись была удалена или изменена.

Перед началом выборки данных PostgreSQL сохраняет снапшот текущего состояния БД. На основании данных снапшота, полей xmin, xmax осуществляется фильтрация записей.

PostgreSQL поддерживает только общую для всех баз кластера кодировку, которая должна совпадать с локальной кодировкой (locale), иначе не будут работать строковые функции сортировки, upper/lower и т.п. Локаль общая для всех процессов сервера - соответственно он не может создать две базы в разных кодировках - кодировка всегда одна для всего сервера и всех его БД.

Посмотреть кодировку сервера (show server_encoding) и клиента(show client_encoding):

postgres=# show server_encoding;
 server_encoding 
-----------------
 UTF8
(1 row)

Т.е. создать базу в другой кодировке можно, но тогда в ней будут неправильно работать функции обработки строк и сортировка строк.

Указывать список кодировок нужно не для createdb (create database), а для подключения клиента к серверу (client_encoding), если кодировка символов которую ожидает программа-клиент не совпадает с её (программы-клиента) текущей системной локалью, с которой она была запущена.

• pgAdmin - GUI для PostgreSQL. Лучший графический клиент из существующих.
• Tora. Homepage: Tora - графический инструмент для проектировщиков баз данных и администраторов. Tora позволяет просматривать схемы баз данных, составлять и отлаживать PL /SQL-запросы, управлять дисками, следить за откатами и просматривать вывод SQL. Благодаря Qt4 может непосредственно работать с PostgreSQL и MySQL, а с другими СУБД — через ODBC.

  # aptitude install tora odbc-postgresql

• phpPgAdmin - веб- приложение с открытым кодом, написанное на языке PHP и представляющее собой веб- интерфейс для администрирования СУБД PostgreSQL.
• OpenOffice и SDBC. Для прямого доступа из OpenOffice к PostgreSQL без промежуточного уровня в виде ODBC/JDBC драйверов разрабатывается postgresql-sdbc драйвер.

  # aptitude install openoffice.org-sdbc-postgresql

• DbVisualizer

psql - PostgreSQL interactive terminal.

• Дополнительные возможности клиента psql
• Пример. Создать БД, пользователя, таблицу, установить права.
• PL/pgSQL ( (Procedural Language/PostGres Structured Query Language) - процедурное расширение языка SQL, используемое в СУБД PostgreSQL.
• Как в PostgreSQL сохранить данные в формате csv?

В директории /usr/share/doc/postgresql* можно найти дополнительную информацию по запуску.

• Подключиться к локальному серверу.

  $ sudo -u postgres psql
  psql (8.4.5)
  Type "help" for help.
  
  postgres=#

• Подключиться к удаленному серверу.

  $ psql -h 192.168.1.20 -U postgres

• В psql узнать подробности об определённом SQL команде можно при помощи \h (ключ \? выводит помощь по мета-командам), например для drop table:

  postgres=# \h drop table
  Command:     DROP TABLE
  Description: remove a table
  Syntax:
  DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

• Пример выполнения SQL запроса из командой строки

  # sudo -u postgres psql -d mybd --command="select * from pg_stat_user_indexes where schemaname='public';"

• Как в PostgreSQL посмотреть список пользователей?

     postgres=# select * from pg_shadow;
     postgres=# \du

• Вывести список баз данных:

     postgres=# select * from pg_database;
  или
     postgres=# \l
  Из командной строки:
     # psql -U postgres -A -q -t -c "select datname from pg_database"
  или:
     # psql -l

• Вывести название текущей базы данных:

  SELECT current_database();

• Вывести имя (идентификатор) текущего пользователя:

  SELECT current_user;

• Вывести список таблиц активной базы данных (если после ключа \d указать имя таблицы - будет выведена структура таблицы):

  postgres=# \dt
  или
  select * from pg_tables; # будут выведены все таблицы, в том числе и системные
  select tablename from pg_tables where schemaname='public';

• Удалить БД testbd777

  # sudo -u postgres dropdb testbd777

• Залить(восстановить) данные в БД c именем test777

  # sudo -u postgres psql test777 < lost_cdr.sql

• regexp_split_to_table. В текстовом поле ipaddr хранятся IP адреса разделенные ; т.е. 1 строка - много IP. Запрос выводит все IP в столбик т.н. 1 строка - 1 IP

  SELECT regexp_split_to_table(ipaddr, E';') as ip FROM peers;

• Вывести версию Postgresql:

  postgres=# SELECT version();
   PostgreSQL 8.4.8 on x86_64-redhat-linux-gnu, 
  compiled by GCC gcc (GCC) 4.4.5 20101112 (Red Hat 4.4.5-2), 64-bit

• Пример экранирования одинарной кавычки в слове Кот-д'Ивуар. В PHP для экранирования спецсимволов используется функция pg_escape_string().

  $ psql -U postgres -d mydb --command="INSERT INTO countries (english,russian) 
  VALUES ('Ivory Coast', 'Кот-д''Ивуар');"

• Для смены активной базы данных в psql, применяется ключ \с
• Вывести время запуска сервера и uptime

  SELECT pg_postmaster_start_time();
     pg_postmaster_start_time    
  -------------------------------
   2013-08-21 14:20:45.451467+00
  
  SELECT current_timestamp - pg_postmaster_start_time() AS uptime;

Если запрос запущен из интерфейса pgsql, то завершение работы сервера не поможет - запрос все равно продолжит свое выполнение, необходимо вызывать функцию pg_cancel_backend.

select * from pg_stat_activity; # посмотреть все запросы
select * from pg_stat_activity WHERE current_query like 'SELECT%'; # посмотреть все SELECT запросы
select * from pg_stat_activity WHERE current_query like 'INSERT%';

# снять все активные select запросы
SELECT pg_cancel_backend(procpid) as x FROM pg_stat_activity WHERE current_query like 'SELECT%';

# снять запрос VACUUM
SELECT pg_cancel_backend(procpid) as x FROM  pg_stat_activity WHERE current_query like 'VACUUM%';

SELECT запросы можно снимать из ОС командой kill

# ps auxww | grep ^postgres
...
postgres 15724 97.7 11.3 2332996 1871476 ?     Rs   07:50   1:53 postgres: postgres mybd 127.0.0.1(53624) SELECT
...
# kill 15724

<note important>procpid содержит PID процесса, которому можно сделать kill при необходимости. Например PID можно узнать запросом(отсортируем по длительности выполнения)

select datname,procpid,now()-query_start as duration,current_query from pg_stat_activity order by duration DESC;

</note>

В PostgreSQL транзакция - это список команд SQL, которые находятся внутри блока, начинающегося командой BEGIN и заканчивающегося командой COMMIT.

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

Пример запуска транзакции из файла delprices.sql, которая удаляет в БД test777 из таблиц prices и ratesheets строки с id=2

# nano delprices.sql
BEGIN;
DELETE FROM prices WHERE ratesheet_id=2;
DELETE FROM ratesheets WHERE id=2;
COMMIT;

Выполним транзакцию для test777:

# sudo -u postgres psql -l
# sudo -u postgres psql test777 < delprices.sql

• Размер базы данных PostgreSQL, таблицы, столбца, количество строк. Мониторинг использования диска.

Лог файлы PostgreSQL находятся в директории pg_log, для Fedora полный путь /var/lib/pgsql/data/pg_log. Детализация лог файлов настраивается в postgresql.conf.

• Zabbix и PostgreSql
• Monitoring Database Activity
• pgFouine - a PostgreSQL log analyzer
• bucardo.org check_postgres - Perl cкрипт для мониторинга более 20 параметров, определяющих состояние СУБД PostgreSQL.

Текущую активность базы данных легко оценить с помощью команды ps, для вывода в реальном времени (с задержкой 1 секунда) можно использовать утилиту watch:

# watch -n 1 'ps auxww | grep ^postgres'
postgres 14164  0.0  0.0 188492  5296 ?        S    Dec13   0:46 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data
postgres 14166  0.0  0.0 159904  1264 ?        Ss   Dec13   0:05 postgres: logger process                          
postgres 14168  0.0  0.1 188636 27208 ?        Ss   Dec13   0:49 postgres: writer process                          
postgres 14169  0.0  0.0 188492  1348 ?        Ss   Dec13   0:23 postgres: wal writer process                      
postgres 14170  0.0  0.0 188804  1752 ?        Ss   Dec13   0:17 postgres: autovacuum launcher process             
postgres 14171  0.0  0.0 160176  1468 ?        Ss   Dec13   0:45 postgres: stats collector process                 
postgres 21596  0.0  0.1 190228 30476 ?        Ss   Dec27   0:58 postgres: postgres mbill 127.0.0.1(37047) idle    
postgres 21597  0.0  0.0 189716  5672 ?        Ss   Dec27   0:00 postgres: postgres mbill 127.0.0.1(37048) idle

Так как для каждого клиента создаётся своя копия процесса postmaster, то это позволяет подсчитать число активных клиентов. Статусная строка даёт информацию о состоянии клиента. Фразы writer process, stats buffer process и stats collector process соответствуют системным процессам, запущенным самим PostgreSQL при старте. Пользовательские процессы имеют статусную строку вида:

postgres: «пользователь» «база» «хост» «статус»

«пользователь», «база» и «хост» соответствуют имени пользователя «пользователь» подсоединявшегося к базе «база» с компьютера «хост». «статус» может принимать следующие параметры:

• idle - ожидание команды от клиента,
• idle in transaction - ожидание команды от клиента внутри транзакции (между BEGIN и окончанием транзакции),
• SQL- команда - выполняется эта команда, например, SELECT,
• waiting - ждём когда разблокируется занятая другим процессом таблица. Для уточнения из-за чего возникла блокировка, нужно анализировать представление pg_locks.

Представления (Views) сборщика статистики.

• Statistics Collector (Standard Statistics Views): описание системных таблицах, собирающих информацию об активности базы данных.

Если в postgresql.conf разрешён сбор статистики (logging_collector = on), то информация об активности базы данных собирается в специальных системных таблицах.

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

• Для каких таблиц стоит создать новые индексы (индикатором служит большое количество полных просмотров и большое количество прочитанных блоков).
• Какие индексы вообще не используются в запросах. Их имеет смысл удалить, если, конечно, речь не идёт об индексах, обеспечивающих выполнение ограничений PRIMARY KEY и UNIQUE.
• Достаточен ли объём буфера сервера.
• Возможен «дедуктивный» подход, при котором сначала создаётся большое количество индексов, а затем неиспользуемые индексы удаляются.

<note warning>Для сброса системной статистики(файл pgstat.stat) в ноль применяется команда

select pg_stat_reset();

</note>

Стандартные Statistics Views. Вывести все представления каталога

select schemaname,viewname,viewowner from pg_views where schemaname='pg_catalog';

• pg_stat_activity - Каждая строка показывает: процесс сервера, OID базы данных, имя базы данных, ID процесса, OID пользователя, имя пользователя, имя приложения, адрес клиента и порт,время, текущею транзакцию, текущий запрос, статус процесса, текст запроса. Колонки показывающие данные текущего запроса доступны если параметр track_activities включен. Эти колонки доступны только для суперпользователя или пользователя владельца процесса.

  select * from pg_stat_activity;
  # Вывести запросы отсортированные по длительности выполнения
  select datname,client_addr,now()-query_start as duration,current_query from pg_stat_activity order by duration DESC;

• pg_stat_bgwriter
• pg_stat_database - Одна строка на кажду БД. Выводит OID базы данных, имя базы данных, количество процессов подключенных к базе(numbackends), кол-во транзакций примененных(xact_commit) и отмененных(xact_rollback), количество прочитанных блоков(blks_read), количество попаданий в буфер(blks_hit), количество выбранных(tup_returned), переданных(tup_fetched), добавленных(tup_inserted), обновленных(tup_updated) и удаленных строк(tup_deleted).

  select * from pg_stat_database;

  Вывести соотношение hit/read. При выполнении запроса PostgreSQL сначала смотрит, есть ли нужные в запросе данные в разделяемой памяти (shared buffers). Если они найдены, засчитывается hit, если нет - делается сравнительно медленный системный вызов fread для поднятия данных с диска или из дискового кеша операционной системы и засчитывается read. В среднем, верно правило: чем больше отношение hit/read, тем лучше настроен PostgreSQL, так как он очень мало читает с диска, в основном извлекая данные из разделяемой памяти. Для большинства не очень больших баз это отношение должно лежать в пределах 5000-10000. Не стремитесь, однако, искусственно завысить настройку shared_buffers, которая прямо определяет hit/read: слишком большие размеры разделяемой памяти ведут к потере производительности в базах с интенсивной записью. Также стоит помнить, что fread может быть довольно быстрым, если данные находятся в дисковом кеше ОС.

  select datname, case when blks_read = 0 then 0 else blks_hit/blks_read end as ratio from pg_stat_database;

• pg_stat_all_tables - Для каждой таблицы в текущей базе данных (включая TOAST таблицы): OID таблицы(relid), схема(schemaname) и имя таблицы(relname), количество последовательны просмотров(seq_scan), количество строк выбранных запросами(seq_tup_read), количество просмотров индексов (все индексы данной таблицы)(idx_scan), количество строк выбранных через сканирование индексов(idx_tup_fetch), количество: пересечений строк(n_tup_ins), обновленных(n_tup_upd), удаленных строк(n_tup_del), количество обновленных HOT строк(n_tup_hot_upd), количество живых(n_live_tup) и мертвых строк(n_dead_tup), время последнего ручного vacuum, время последнего автоматического vacuum, время последнего ручного analyze, время последнего автоматического analyze. Запросы для активной БД:

  select * from pg_stat_all_tables where schemaname='public';
  select relname,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze from pg_stat_all_tables where schemaname='public';

• pg_stat_sys_tables - То же что и pg_stat_all_tables, только системные таблицы.
• pg_stat_user_tables - То же что и pg_stat_all_tables, только пользовательские таблицы. Статистика seq scan/index scan. Список по таблицам: какое количество запросов к ним было выполнено посредством последовательного просмотра; какое количество запросов было выполнено с использованием индексов; а также отношение этих двух чисел. Позволяет оценить, все ли нужные индексы созданы в данной таблице. Если ваши таблицы содержат более нескольких тысяч рядов, последовательный просмотр будет выполняться медленнее просмотра индекса, поэтому в идеальном случае seqscan-ов в таких таблицах быть не должно. Если у вас они все же есть, анализируйте запросы к таким таблицам и создавайте соответствующие индексы. При этом важно не перестараться: чем больше индексов по колонкам таблицы, тем дороже становятся операции обновления данных. Также не забывайте, что после создания индекса таблице нужно делать ANALYZE, иначе планировщик запросов не заметит изменений в структуре таблицы.

  select relname,seq_scan,idx_scan, case when idx_scan = 0 then 100 else seq_scan/idx_scan end as ratio from pg_stat_user_tables order by ratio desc;
  select relname,seq_scan,idx_scan, case when idx_scan = 0 then 100 else seq_scan/idx_scan end as ratio,n_live_tup,n_dead_tup from pg_stat_user_tables order by ratio desc;

  Количество модификаций, произошедших в таблице. Список по таблицам: какое количество записей в них было добавлено, изменено и удалено с момента последнего сброса статистики. Администратор БД должен представлять, какие таблицы являются самыми нагруженными в текущей базе данных, а также каково соотношение между различными типами модифицирующих запросов к ним.

  select relname,n_tup_ins,n_tup_upd,n_tup_del from pg_stat_user_tables order by n_tup_ins desc;

• pg_stat_all_indexes - Поможет оценить эффективность и частоту использования индексов при реальной работе. Для каждого индекса текущей базы: OID таблицы и OID, схема, имя таблица и индекса, количество просмотров индекса(idx_scan), количество записей возвращенных при сканировании индекса(idx_tup_read), количество живых строк(idx_tup_fetch) таблицы полученных простым сканированием индексов используя этот индекс.

  select * from pg_stat_all_indexes;

• pg_stat_sys_indexes - То же что и pg_stat_all_indexes, только системные таблицы.
• pg_stat_user_indexes - То же что и pg_stat_all_indexes, только пользовательские таблицы.

  select * from pg_stat_user_indexes where schemaname='public';

  Статистика по индексам. Список по индексам: сколько записей из индекса были использованы в запросах по этому индексу; сколько рядов при этом получилось достать из родительской таблицы; разность этих двух чисел. Суть данной статистики проста: если у вас большая разница read-ов и fetch-ей, значит индекс устарел и ссылается на уже несуществующие данные, т.е. не всякий просмотр индекса и чтение из него соответствующего указателя на данные из таблицы (read) вызывает чтение самих данных из таблицы (fetch). В этом случае необходимо перестроить данный индекс, чтобы он соответствовал реальным данным в таблице.

  select relname,indexrelname,idx_tup_read,idx_tup_fetch,(idx_tup_read-idx_tup_fetch) as diff, CASE WHEN idx_tup_read=0 THEN 0 ELSE (idx_tup_read::float4-idx_tup_fetch)/idx_tup_read END as r FROM pg_stat_user_indexes ORDER BY r desc;

• pg_statio_all_tables - Для каждой таблицы текущей базы данных (включая TOAST таблицы), OID таблицы, схема и имя таблицы, количество блоков прочитанных с диска, количество попаданий в буфер, количество блоков прочитанных с диска и попавших в буфер для всех индексов таблицы, количество блоков прочитанных с диска и попавших в буфер that table’s auxiliary TOAST table (if any), количество блоков прочитанных с диска и попавших в буфер для индекса TOAST таблиц.
• pg_statio_sys_tables - То же что и pg_statio_all_tables, только системные таблицы.
• pg_statio_user_tables - То же что и pg_statio_all_tables, только пользовательские таблицы.
• pg_statio_all_indexes - Для каждого индекса текущей базы данных: OID таблицы и индекса, имя таблицы и индекса, количество блоков прочитанных с диска и попаданий в буфер.
• pg_statio_sys_indexes - То же что и pg_statio_all_indexes, только системные таблицы.
• pg_statio_user_indexes - То же что и pg_statio_all_indexes, только пользовательские таблицы
• pg_statio_all_sequences - Для каждой последовательности в текущей базе данных: OID последовательности, схема и имя последовательности, количество прочитанных блоков с диска и попаданий в буфер.
• pg_statio_sys_sequences - То же что и pg_statio_all_sequences, только системные таблицы.
• pg_statio_user_sequences - То же что и pg_statio_all_sequences, только пользовательские таблицы.
• pg_stat_user_functions - Значения времени указано в миллисекундах.

• pg_locks - информация о блокировках в базе данных.

• Explicit Locking - документация о типах блокировок в PostgreSQL.

Команда LOCK TABLE предназначена для блокировки таблиц на время транзакции. Блокировкой называется временное ограничение доступа к таблице (в зависимости от выбранного режима). Сеанс, заблокировавший таблицу, пользуется нормальным доступом; последствия блокировки распространяются только на других пользователей, пытающихся получить доступ к заблокированной таблице. <note tip>Блокировка не означает отказа в доступе. С точки зрения пользователя, подключенного к базе данных и пытающегося обратиться к заблокированному ресурсу, блокировка приводит к задержке, но не к отказу в предоставлении доступа. Пользователю приходится ожидать либо завершения заблокированной команды пользователем, либо снятия блокировки с таблицы.</note>

Некоторые команды SQL автоматически устанавливают блокировку для выполнения своих функций; в таких случаях PostgreSQL всегда выбирает минимально необходимый уровень блокировки. После завершения транзакции блокировка немедленно снимается.

Команда LOCK TABLE без параметра устанавливает максимально жесткий режим блокировки (ACCESS EXCLUSIVE). Чтобы ограничения были менее жесткими, следует явно задать нужный режим. <note tip>Блокировка таблиц возможна только в транзакциях. Выполнение команды LOCK TABLE вне транзакционного блока не приводит к ошибке, но установленная блокировка немедленно снимается. Транзакция создается командой BEGIN; команда COMMIT фиксирует изменения в базе данных и снимает блокировку.</note>

Ситуация взаимной блокировки (deadlock) возникает в там случае, когда каждая из двух транзакций ожидает снятия блокировки другой транзакцией. Хотя PostgreSQL распознает взаимные блокировки и завершает их командой ROLLBACK, это все равно причиняет определенные неудобства. Приложения не должны сталкиваться с проблемой взаимных блокировок, поэтому проектируйте их так, чтобы объекты всегда блокировались в одинаковом порядке.

• ACCESS SHARE MODE. Устанавливается автоматически командой SELECT для таблиц, из которых производится выборка данных. В заблокированных таблицах запрещается выполнение команд ALTER TABLE, DROP TABLE и VACUUM. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровня ACCESS EXCLUSIVE MODE.
• ROW SHARE MODE. Устанавливается автоматически командами SELECT, содержащими секцию FOR UPDATE или FOR SHARE. В заблокированных таблицах запрещается выполнение команд ALTER TABLE, DROP TABLE и VACUUM. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровней EXCLUSIVE MODE и ACCESS EXCLUSIVE MODE.
• ROW EXCLUSIVE MODE. Устанавливается автоматически командами UPDATE, INSERT и DELETE. В заблокированных таблицах запрещается выполнение команд ALTER TABLE, DROP TABLE и CREATE INDEX. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровней SHARE MODE, SHARE ROW EXCLUSIVE MODE, EXCLUSIVE MODE и ACCESS EXCLUSIVE MODE.
• SHARE UPDATE EXCLUSIVE MODE . Устанавливается автоматически командами VACUUM (без FULL), ANALYZE и CREATE INDEX CONCURRENTLY.
• SHARE MODE. Устанавливается автоматически командами CREATE INDEX (без CONCURRENTLY). В заблокированных таблицах запрещается выполнение команд INSERT, UPDATE, DELETE, ALTER TABLE, DROP TABLE и VACUUM. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровней ROW EXCLUSIVE MODE, SHARE ROW EXCLUSIVE MODE, EXCLUSIVE MODE и ACCESS EXCLUSIVE MODE.
• SHARE ROW EXCLUSIVE MOOE. Специальный режим блокировки, практически идентичный режиму EXCLUSIVE MODE, но допускающий установку параллельных блокировок уровня ROW SHARE MODE.
• EXCLUSIVE MODE. Запрещает выполнение команд INSERT, UPDATE, DELETE, CREATE INDEX, ALTER TABLE, DROP TABLE и VACUUM, а также команд SELECT с секцией FOR UPDATE. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровней ROW SHARE MODE, ROW EXCLUSIVE MODE, SHARE MODE, SHARE ROW EXCLUSIVE MODE и ACCESS EXCLUSIVE MODE.
• ACCESS EXCLUSIVE MODE. Устанавливается автоматически командами ALTER TABLE, DROP TABLE и VACUUM. В этом режиме для заблокированных таблиц запрещаются любые команды или параллельные блокировки любого уровня.

• VACUUM -- garbage-collect and optionally analyze a database
• vacuumdb -- garbage-collect and analyze a PostgreSQL database
• ANALYZE -- collect statistics about a database
• Vacuum Full без полного лока таблицы

Автоматическая сборка мусора (Automatic Vacuuming).

Синтаксис VACUUM:

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]

Синтаксис ANALYZE:

ANALYZE [ VERBOSE ] [ table [ ( column [, ...] ) ] ]

Кроме сборки мусора (VACUUM) производится ещё и анализ (ANALYZE). Периодическое выполнение команды ANALYZE необходимо для нормального функционирования планировщика. Собранная с помощью этой команды статистика позволяет значительно ускорить выполнение SQL- запросов. То есть, если не хочется настраивать автоматическую сборку мусора, то в любом случае её придётся делать только теперь в ручную. Процесс обычной сборки мусора в PostgreSQL (VACUUM без приставки FULL) не блокирует таблиц и может выполняться в фоне, не мешая выполнению запросов. Регулярное исполнение команд VACUUM и ANALYZE обязательно. Это необходимо по той причине, что иначе не получится заново использовать дисковое пространство, которое занимают ранее удалённые или изменённые строки и не удастся обновить статистику для планировщика запросов. И то и другое отрицательно сказывается на эффективности использования ресурсов и производительности запросов. Начиная с версии PostgreSQL 8.1 сервер может самостоятельно автоматически запускать ещё один системный процесс, который, соответственно, так и называется autovacuum daemon. Все настройки для этого процесса хранятся в postgresql.conf. К значениям этих параметров следует отнестись крайне внимательно. Если по каким-то причинам демон было решено не запускать, то в любом случае необходимо производить сборку мусора и набор статистики в ручную.

Основным средством физического и аналитического сопровождения баз данных в PostgreSQL является команда SQL VACUUM и ее аналог — сценарий vacuumdb. Оба средства выполняют две общие функции:

1. удаление всех данных, оставшихся в результате отмены транзакций и других операций, оставляющих временные данные;
2. анализ операций с базами данных, по результатам которого PostgreSQL конструирует более эффективные запросы.

Синтаксис VACUUM:

=# \h VACUUM
Command:     VACUUM
Description: garbage-collect and optionally analyze a database
Syntax:
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]

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

# sudo -u postgres psql mybd
mybd=# VACUUM ANALYZE VERBOSE;

<note>Команда ANALYZE служит для обновления информации о распределении данных в таблице. Эта информация используется оптимизатором для выбора наиболее быстрого плана выполнения запроса. Обычно команда используется в связке VACUUM ANALYZE. Если в базе есть таблицы, данные в которых не изменяются и не удаляются, а лишь добавляются, то для таких таблиц можно использовать отдельную команду ANALYZE. Также стоит использовать эту команду для отдельной таблицы после добавления в неё большого количества записей.</note>

Практика показала, что без более-менее регулярных запусков vacuum full analyze производительность системы постепенно падает, причем чем дальше, тем больше. Разница между vacuum и vacuum full заключается в том, что full физически переписывает на диске всю таблицу таким образом, чтобы в ней не оставалось «дырок» от удаленных или обновленных записей. Но его недостаток в том, что во время работы таблица полностью блокируется(включая и select запросы), что может привести к проблемам на популярном сервере - начнет скапливаться очередь запросов, ожидающих доступа к базе, каждый запрос требует памяти, память кончается, начинается запись в swap, из-за отсутствия памяти сам vacuum тоже начинает использовать swap и все начинает работать очень-очень медленно. Простой VACUUM (Без FULL) просто восстанавливает пространство и делает его доступным для повторного использования. Эта форма команды умеет работать параллельно с обычными чтение и запись таблицы, без монопольной блокировки.

Чтобы определить необходимость использования индекса для какой-либо таблицы, PostgreSQL должен иметь статистику по этой таблице. Эта статистика собирается при использовании VACUUM ANALYZE или просто ANALYZE. Используя статистику, оптимизатор узнает о том как много строк в таблице и если он должен использовать индексы, то он может принимать лучшие решения. Статистика также влияет на определение оптимального порядка соединений таблиц и метода соединения. При изменении содержимого таблицы должен периодически выполнятся сбор статистики.

# vacuumdb -U postgres --verbose --analyze --full --dbname=mydb
# crontab
# Запуск "vacuumdb" каждый день в 22:01
1 22 * * *  root vacuumdb -U postgres --quiet --analyze --dbname=mydb &

lcr_id - колонка автоинкрементная (lcr_id | integer | not null default nextval('df_lcr_list_lcr_id_seq'::regclass)). Запрос возвращает значение колонки lcr_id для вставленной записи, в этом случае id записи.

INSERT INTO df_lcr_list (datestart,dateend) VALUES (20120429,20120429) RETURNING lcr_id;

• PostgreSQL 8.4 System Catalogs
• Системные колонки: oid, tableoid, xmin, cmin, xmax, cmax, ctid

Системные таблицы(System Catalogs) PostgreSQL начинаются с префикса pg_.

• Партицирование (partitioning) больших таблиц PostgreSQL
• 8.4 Partitioning

Partitioning (партицирование, секционирование).

• PostgreSQL Documentation
• А. Ю. Васильев aka leopard "Работа с PostgreSQL: настройка и масштабирование"
• Краткий обзор возможностей PostgreSQL
• Иллюстрированный самоучитель по PostgreSQL: Дж. Уорсли, Дж. Дрейк «PostgreSQL. Для профессионалов.»
• Инструкция по оптимизации PostgreSQL 8.x
• Оптимизация работы Postgre SQL
• PostgreSQL: аналитика для DBA
• Библиотека полезных функций для PostgreSQL
• Иллюстрированный самоучитель по PostgreSQL: date_part()
• Отказоустойчивый кластер Master-Slave на PostgreSQL

• PostgreSQL FAQ на русском языке
  • postgresql.ru.net Как много дискового пространства в базе данных нужно для сохранения данных из обычного текстового файла?
  • postgresql.ru.net Почему мои запросы работают медлено? Почему они не используют мои индексы?
  • postgresql.ru.net Что такое OID?
  • Почему PostgreSQL использует так много памяти?

• Проекты-спутники PostgreSQL
• Regexps in PostgreSQL / Регулярные выражения в PostgreSQL
• in-memory table in PostgreSQL