Новые и важные изменения в СУБД PostgreSQL

Тезисы доклада на конференции "Корпоративные базы данных-2006" [CD06]. Полная версия доклада доступна на сайте авторов [BS06].

Приводится обзор наиболее важных изменений в новой версии СУБД PostgreSQL 8.1.X и примерный список новых возможностей, находящихся в разработке.

Введение

8 ноября 2005 года на конференции по открытым базам данных во Франкфурте, Германия, от имени PGDG было объявлено о выпуске версии 8.1 СУБД PostgreSQL. К настоящему времени было выпущено 3 релиза с исправлениями замеченных ошибок и скорее всего в ближайшее время ожидается выпуск версии 8.1.4. Желающие уже могут скачать ее из стабильной ветки CVS (-r REL8_1_STABLE).

Год назад, на 10-й юбилейной конференции [CD05], один из авторов анонсировал [B05] некоторые новинки, ожидаемые в 8.1, которые вызвали интерес к этой версии.

Здесь мы приведем обзор наиболее важных изменений, полный список которых доступен на странице проекта [R81].

Что нового в PostgreSQL 8.1 ?

Поддержка SQL:

• Двухфазная фиксация транзакций ( 2PC, двухфазный коммит) позволяет PostgreSQL участвовать в распределенных транзакциях. Двухфазный коммит имеет две четко разделенных фазы:
  • Фаза подготовки - координатор (обычно, инициируюшая база данных) посылает сообщение участникам распределенной транзакции о подготовке к транзакции (prepare message). Это сообщение также содержить уникальный номер транзакции TID. Когда участники получают это сообщение, они проверяют могут ли они зафиксировать транзакцию и отвечают координатору. При этом транзакция исполняется, но не фиксируется и ее состояние спасается на диск.
  • Фаза фиксации - после того, как координатор получил все ответы он решает зафиксировать или прервать начатую транзакцию в соответствии с правилом глобальной фиксации. Если он решает, что все хорошо, то он посылает участникам сообщение о фиксации транзакции, в противном случае все участники получают приказ прервать транзакцию.
  В результате,все участники либо фиксируют транзакцию, либо откатывают ее, независимо от сетевых проблем или краха какого либо узла. В PostgreSQL 2PC реализован так, что транзакции сначала подготавливаются (PREPARE TRANSACTION) и спасаются на диск и в последствии они могут быть зафиксированы (COMMIT PREPARED) или отменены (ROLLBACK PREPARED) даже после перезагрузки системы. JDBC драйвер теперь имеет поддержку XA протокола согласно JTA
• Использование ролей вместо пользователей и групп позволяет более гибко управлять доступом к объектам базы данных (database, table, function, language, schema, tablespace, role). При этом, пользователи и группы это просто роли, роли могут принадлежать другим ролям, роли могут быть владельцами объектов, роли могут наследоваться. Использование ролей описано в стандарте SQL.

  Роли очень удобно использовать для назначения конфигурационных параметров по умолчанию, например, запретить использование индексов для роли myname:

    ALTER ROLE myname SET enable_indexscan TO off;
  

  Отметим, что пользователи и группы для совместимости также можно использовать, однако внутри они реализованы через роли.

Поддержка очень больших баз данных:

• Использование промежуточных (in-memory) битовых карт (bitmap).
  • Это служебные индексы, которые строятся в памяти во время планирования запроса.
  • Не путать с bitmap индексами, которые создаются на диске и используются для работы с данными с малым количеством уникальных значений, например пол (муж./жен.)
  • Пользователь может отключать их использование (enable_bitmapscan)
  • Их основное использование - это оптимизация работы с несколькими индексами.

  В отличие от обычного сканирования индекса, в котором за один раз из индекса считывается только один указатель на запись, по которому потом поднимается сама запись из таблицы, bitmap scan считывает все указатели за один раз (Bitmap Index Scan), сортирует их в памяти и потом считывает записи уже в локализованном на диске порядке (Bitmap Heap Scan). Таким образом, увеличивается скорость чтения записей с диска, но ценою создания в памяти специальной структуры данных. Заметим, что производительность ORDER BY может пострадать, так как записи считываются не в том порядке, как они хранились в индексе.

  Если bitmap становится очень большим, то вместо записей хранятся ссылки на страницы, которые содержат записи, удовлетворяющие запросу. Поэтому, в таком случае, необходима дополнительная проверка (Recheck), чтобы получить только требуемые записи.

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

  Однако, bitmapscan может быть полезен и для работы с одним индексом, так как доступ к данным происходит последовательно, а не в произвольном порядке. При этом, cчитается, что обычный indexscan эффективнее bitmapscan-а для малого количества считываемых записей, в то время как seqscan эффективнее bitmapscan-а если приходится считывать с диска значительную долю таблицы.

• Table partitioning - разбиение таблицы на более мелкие части, при сохранении логической целостности. Для этого используется механизм наследования таблиц (table inheritance) в PostgreSQL и новое улучшение в планировщике, называемое CONSTRAINT EXCLUSION (в postgresql.conf требуется разрешить его использование, см. constraint_exclusion параметр), который позволяет использовать только релевантные части одной большой таблицы. При этом очень сильно ускоряются некоторые запросы, обновление данных облегчается и можно размещать малоиспользуемые данные на других носителях.

  Покажем как это работает на простом примере. Создаем таблицы с указанием проверок на данные, при этом следим, чтобы интервалы значений не пересекались. Также, создаем индексы.

  create table a ( i int primary key);
  create table a1( check (i >=0 and i<=2000) ) inherits(a);
  create table a2( check (i >=2001 and i<=4000) ) inherits(a);
  create table a3( check (i >=4001 and i<=6000) ) inherits(a);
  create index a1_idx on a1(i);
  create index a2_idx on a2(i);
  create index a3_idx on a3(i);
  

  Заполняем таблицы:

  for ((i=0;i<2000;i++)) do echo $i; done| psql test -c "copy a1 from stdin;"
  for ((i=2001;i<4000;i++)) do echo $i; done| psql test -c "copy a2 from stdin;"
  for ((i=4001;i<6000;i++)) do echo $i; done| psql test -c "copy a3 from stdin;"
  

  По умолчанию CONSTRAINT EXCLUSION выключен и простой запрос будет сканировать все таблицы:

  test=# explain select * from a where i = 10;
                                      QUERY PLAN                                  
   
  ----------------------------------------------------------------------------------
   Result  (cost=0.00..42.70 rows=34 width=4)
     ->  Append  (cost=0.00..42.70 rows=34 width=4)
           ->  Index Scan using a_pkey on a  (cost=0.00..4.82 rows=1 width=4)
                 Index Cond: (i = 10)
           ->  Bitmap Heap Scan on a1 a  (cost=2.04..12.63 rows=11 width=4)
                 Recheck Cond: (i = 10)
                 ->  Bitmap Index Scan on a1_idx  (cost=0.00..2.04 rows=11 width=0)
                       Index Cond: (i = 10)
           ->  Bitmap Heap Scan on a2 a  (cost=2.04..12.63 rows=11 width=4)
                 Recheck Cond: (i = 10)
                 ->  Bitmap Index Scan on a2_idx  (cost=0.00..2.04 rows=11 width=0)
                       Index Cond: (i = 10)
           ->  Bitmap Heap Scan on a3 a  (cost=2.04..12.63 rows=11 width=4)
                 Recheck Cond: (i = 10)
                 ->  Bitmap Index Scan on a3_idx  (cost=0.00..2.04 rows=11 width=0)
                       Index Cond: (i = 10)
  (16 rows)
  

  Этот же запрос при constraint_exclusion=on в postgresql.conf затронет только одну таблицу, что и требовалось показать.

  test=# explain select * from a where i = 10;
                                      QUERY PLAN                                  
    
  ----------------------------------------------------------------------------------
   Result  (cost=0.00..17.45 rows=12 width=4)
     ->  Append  (cost=0.00..17.45 rows=12 width=4)
           ->  Index Scan using a_pkey on a  (cost=0.00..4.82 rows=1 width=4)
                 Index Cond: (i = 10)
           ->  Bitmap Heap Scan on a1 a  (cost=2.04..12.63 rows=11 width=4)
                 Recheck Cond: (i = 10)
                 ->  Bitmap Index Scan on a1_idx  (cost=0.00..2.04 rows=11 width=0)
                       Index Cond: (i = 10)
  (8 rows)
  

  Заметим, что планировщик выполнения запросов использует ограничения, заданные только в виде CHECК. Кроме того, не поддерживаются UPDATE и DELETE. Несмотря на эти и некоторые другие ограничения, которые будут сняты в будущем, table partitioning является полезным для организации очень больших архивов.

Производительность:

• Улучшена производительность многопроцессорных серверов за счет повышения конкурентности доступа к разделяемой памяти и нового алгоритма управления буферами (clock-sweep).
• Поддержка конкурентности и восстанавливаемости GiST [GiST, BS05]. Обобщенное поисковое дерево (Generalised Search Tree, GiST) в PostgreSQL является одним из первых в технологии баз данных реализаций механизма разработки новых пользовательских типов и запросов. Более подробно про GiST можно прочитать в цитированных статьях, здесь отметим, что существующая реализация GiST не поддерживала конкурентного доступа к индексам, что существенно влияло на производительность и его используемость в реальных рабочих приложениях. При этом, новые расширения, такие как intarray, ltree, tsearch2, PosGIS, приобрели большую популярность и авторами были доработаны и реализованы для версии 8.1 алгоритмы, используемые в GiST, с поддержкой конкурентного доступа к индексам и возможности восстановления после краха системы. Отметим, что все приложения, которые использовали GiST, автоматически приобрели эти новые свойства ! Этот пункт интересен также новым подходом к поддержке разработчиков свободного ПО, полная история описана на сайте newsforge.com [GISTCR].

Разное:

• Расширенная поддержка функций - IN,OUT,INOUT параметры для pl/pgsql, расширенная диагностика и обработка ошибок. Теперь в определении функции можно задавать какие параметры ожидаются в качестве входных, а какие для вывода, что сильно облегчает написание функций, которые возвращают несколько колонок (раньше для этого надо было создавать фиктивный композитный тип). Кроме этого, облегчается портирование приложений с Оракле на PostgreSQL.

  CREATE FUNCTION foo(IN x integer, INOUT y integer, OUT z integer) AS $$
  BEGIN
      y := y + 5;
      z := x + 5;
  END;
  $$ LANGUAGE plpgsql IMMUTABLE STRICT;
  
  SELECT foo(10, 20);
     foo   
  ---------
   (25,15)
  (1 row)
  
  SELECT (foo(10, 20)).*;
   y  | z  
  ----+----
   25 | 15
  (1 row)
  

• Интегрированный autovacuum связан с понятием многоверсионности, используемой в PostgreSQL, работающий в фоновом режиме и является альтернативой отдельному процессу, называемого VACUUM. Преимуществом интегрированного сборщика мусора является его полная синхронизация с работой базы данных и возможность автоматически определять какие таблицы следует обрабатывать (настройки в postgresql.conf). Часто задают вопрос о необходимости сборки мусора. Кратко можно ответить, что это необходимо для обеспечения высокой производительности параллельно исполняемых запросов. Более подробное разъяснение следует ниже.

  Для обеспечения конкурентного доступа к данным в PostgreSQL используется версионная модель, при которой каждая транзакция видит свою версию данных. Транзакция идентифицируется числом xid (32-битное число без знака), которое монотонно возрастает, т.е., более поздняя транзакция имеет больший xid. Каждая запись (tuple) имеет два идентификатора транзакций: xmin - xid транзакции, добавившей запись, и xmax - xid транзакции, удалившей запись. При удалении записи, на самом деле физического удаления не происходит, а только устанавливается значение xmax. При обновлении записи, создается новая версия, а в старой версии ставится ссылка на новую и устанавливается значение xmax. Видимость записи определяется по xid текущей транзакции, xmin и xmax. Если поле xmin записи больше идентификатора текущей транзакции, значит запись находится для неё "в будущем" и невидима. Если же поле xmin записи меньше идентификатора транзакции, то эта версия находится для неё "в прошлом" --- либо она удалена, либо есть более новая версия, к которой можно перейти по ссылке. Эти идентификаторы обычно не видны, но их можно получить явно указав в запросе.

  # select xmin,xmax,i from a where i=5999; 
    xmin  | xmax |  i   
  --------+------+------
   185789 |    0 | 5999
  (1 rows)
  

  Откроем новую транзакцию и удалим одну запись, но не фиксируем транзакцию:

  # begin;
  BEGIN
  =# delete from a where i=5999;
  DELETE 1
  

  Тогла в другой сессии удаленная запись будет видна и можно увидеть номер удалившей транзакции (xmax) :

  # select xmin,xmax,i from a where i=5999;
    xmin  |  xmax  |  i   
  --------+--------+------
   185789 | 185809 | 5999
  (1 rows)
  

  После фиксации транзакции удаленная запись уже не будет видна.

  Как только заканчивает свою работу последняя транзакция, чей идентификатор меньше значения xmax записи, эта версия записи автоматически становится "мусором" и занятое ей место должно быть освобождено для последующего использования, в противном случае размер таблицы будет расти бесконечно. Для удаления такого мусора используется команда VACUUM. Кроме этого, VACUUM следит, чтобы xid транзакции не переполнялся (xid wraparound), иначе новая транзакция может получить идентификатор 0 и все данные, добавленные предыдущими транзакциями, окажутся для нее в будущем, т.е. невидимыми. Для этого в 7.2 были введены два специальных xid-а - BootstrapXID и FrozenXID, при этом "нормальные" идентификаторы транзакций всегда начинаются с xid=3. BootstrapXID и FrozenXID считаются зафиксированными и всегда старее, чем все "нормальные" идентификаторы, которые сравниваются между собой по модулю 2³¹ (2 миллиарда). Это означает, что для любого "нормального" xid есть по 2 миллиарда "более старых" и "более новых" транзакций. Если какая-либо версия записи в базе данных выживет 2 миллиарда транзакций после своего рождения, она неожиданно окажется в "будущем" и будет незафиксированной. Чтобы разрешить записи жить более этого предела, xmin этой записи заменяется на FrozenXID, т.е. станет в "прошлом" для всех транзакций и будет видимой. Вот это и делает команда VACUUM - назначает специальный идентификатор FrozenXID всем записям, которые старше 1 миллиарда транзакций. Это означает, что хотя бы один раз в миллиард транзакций надо запускать VACUUM, при этом PostgreSQL, для избежания потери данных, предупреждает о необходимости команды VACUUM и откажется работать, если она не будет запущена.

Что ожидается ?

Как всегда, точный список новинок в грядущей версии PostgreSQL трудно привести, что является обычным для свободного ПО, но примерный список запросов и замеченных ошибок обычно доступен на сервере проекта [TODO]. Отметим, некоторые запланированные возможности:

• Поддержка PITR частично записанных WAL журналов и встроенная репликация.
• Поддержка иерархических запросов
• Обновляемые представления (updateable view)
• Улучшенный алгоритм сортировки - выигрыш до 40%
• Bitmap индексы для очень больших таблиц с малым количеством уникальных значений
• Gin - обобщенный обратный индекс (inverted index)
• Индексная поддержка массивов на основе обратного индекса
• Полная поддержка UTF-8 в модуле полнотекстового поиска tsearch2 [TS2]
• Использование обратного индекса для tsearch2 для шкалирования полнотекстового поиска
• autovacuum будет работать на уровне отдельной таблицы
• Улучшенная поддержка наследования
• Оптимизация WAL
• Дальнейшая оптимизация table partitioning
• Поддержка параллельного выполнения запросов

Заключение

Версия 8.1 является новым шагом в сторону больших и нагруженных систем, предназначенных для непрерывной работы в режиме 24x7x365. Это подтверждается тем, что большие компании начинают использовать PostgreSQL в реальном бизнесе. Так, Sony Online Entertainment объявила [SOE05] об инвестировании 1.5 млн. USD в Enterprise DB для перехода с Oracle на PostgreSQL 8.1. В России крупнейший оператор сотовой связи компания Вымпелком (Beeline) тестирует ПО работающее с PostgreSQL и находится на стадии заключения контракта на поддержку кластера PostgreSQL. Компания Sun Microsystem объявила [SUN05] об официальной поддержке PostgreSQL (входит в Solaris 10), "beta" версия пакетов, оптимизированных для Solaris, уже доступна [SUN06]. Кроме этого, Sun поддерживает PostgreSQL в режиме 24x7.

Традиционно, PostgreSQL широко используется в научных проектах. Так, нами был запущен проект SAI CAS (Catalog Access Service), в рамках международной программы Virtual Observatory (Виртуальная Обсерватория), как часть проекта Астронет (wwww.astronet.ru), ориентированного на профессиональное астрономическое сообщество и где в качестве СУБД для работы с очень большими астрономическими каталогами (1Tb), используется PostgreSQL 8.1. Сервер БД HP rx1620 (Itanium2) был предоставлен HP Russia.

Благодарности

Авторы благодарят Российский Фонд Фундаментальных Исследований (РФФИ) за поддержку проектов Астронет (05-07-90225-в), Научная Сеть (06-07-89182-а) и компанию HP Russia за предоставленное оборудование.

Ссылки

• [CD05] Конференция Корпоративные базы данных-2005
• [CD06] Конференция Корпоративные базы данных-2006
• [R81] release-8-1.html
• www.postgresql.org - сервер проекта PostgreSQL
• [B05] Олег Бартунов, Что такое PostgreSQL? - обзорная статья (на русском) о PostgreSQL
• [BS05] Олег Бартунов, Федор Сигаев Написание расширений для PostgreSQL с использованием GiST
• [BS06] Олег Бартунов, Федор Сигаев Новые и важные изменения в СУБД PostgreSQL 8.1
• www.pgsql.ru - поиск по PostgreSQL ресурсам
• [GiST] PostgreSQL GiST development page
• [GISTCR] User companies pool on PostgreSQL enhancement
• [SOE05] EnterpriseDB press release
• [TODO] PostgreSQL TODO
• [TS2] Полнотекстовый поиск для PostgreSQL - tsearch2
• [SUN05] Sun Announces Support for Postgres Database on Solaris 10
• [SUN06] PostgreSQL - Optimized for Solaris in Solaris Packages format

Авторы

Олег Бартунов и Федор Сигаев являются членами PostgreSQL Global Development Group (поддержка и развитие GiST в PostgreSQL) и авторами информационно-поисковой системы по PostgreSQL ресурсам - www.pgsql.ru. Они являются авторами целого ряда популярных расширений PostgreSQL, в том числе, модуль полнотекстового поиска tsearch2, поддержка иерархических типов данных ltree, работа с целочисленными массивами intarray. Более подробная информация доступна на странице PostgreSQL GiST development.