Виктор Вислобоков

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

За дополнительной информацией обращайтесь на сайт PostgreSQL

Содержание

---

Размер базы данных
Поддерживаемые встроенные типы данных
Типы данных, создаваемые пользователем
Локализация
Языки, которые могут использоваться для написания хранимых процедур (функций)
Функции и операторы
Индексы
Полнотекстовый поиск
Многоверсионный контроль конкурентых транзакций и изоляция транзакций
Журналы (логи) опережающей записи (WAL)
Репликация и технология Hot Standby
Табличные пространства (tablespaces)
Гибкая настройка сервера
Ограничения целостности
Хранимые процедуры
Триггеры
Система правил
Схемы
Роли и привелегии
Сбор статистики
Резервное копирование и восстановление

---

Размер базы данных

---

Максимальный размер БД	Неограничен
Максимальный размер таблицы	32 TB
Максимальный размер записи (строки) в таблице	1.6 TB
Максимальный размер поля в записи (строке)	1 GB
Максимальное количество записей (строк) в таблице	не ограничено
Максимальное количество полей (колонок) в таблице	250 - 1600 в зависимости от типа данных в колонке
Максимальное количество индексов на таблицу	не ограничено

Поддерживаемые встроенные типы данных

---

Числовые типы
smallint	короткое 2-х байтовое целое
integer	обычное 4-х байтовое целое
bigint	большое 8-байтовое целое
decimal	дробное с фиксированной точкой
numeric	дробное с фиксированной точкой
real	дробное с плавающей точкой
double precision	дробное с плавающей точкой двойной точности
serial	целое с автоувеличением
bigserial	большое целое с автоувеличением

Денежные типы
money	для хранения денежных значений

Символьные типы
character varying(n), varchar(n)	строка переменной длины с ограничением
character(n), char(n)	строка фиксированной длины
text	строка переменной неограниченной длины

Бинарные (двоичные) типы
bytea	бинарная строка переменной длины

Дата и время
timestamp [ (p) ] [ без часового пояса ]	дата и время
timestamp [ (p) ] с часовым поясом	дата и время с часовым поясом
interval [ (p) ]	интервал времени
date	только дата
time [ (p) ] [ без часового пояса ]	только время
time [ (p) ] с часовым поясом	только время с часовым поясом

Логические типы
boolean	TRUE или FALSE

Геометрические типы
point	Точка на плоскости (x,y)
line	Невидимая линия (не полностью реализовано)
lseg	Видимый отрезок ((x1,y1),(x2,y2))
box	Четырёхугольник ((x1,y1),(x2,y2))
path	Замкнутый многоугольник (похож на полигон) ((x1,y1),...)
path	Ломаная линия [(x1,y1),...]
polygon	Полигон (похож на замкнутый многоугольник) ((x1,y1),...)
circle	Круг (x,y),r (центр и радиус)

Типы для адресов компьютерных сетей
cidr	IPv4 или IPv6 сеть
inet	IPv4 или IPv6 хост и сеть
macaddr	MAC адрес

Битовые строки
bit [ (n) ]	битовая строка фиксированной длины
bit varying [ (n) ]	битовая строка переменной длины

Типы для поиска текста
tsquery	запрос на поиск текста
tsvector	список для поиска текста

UUID тип
uuid	универсальный уникальный идентификатор

XML типы
xml	данные XML

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

Типы данных, создаваемые пользователем

---

С помощью команды CREATE TYPE пользователи могут создавать новые типы данных для своих нужд.

Локализация

---

PostgreSQL работает с локализацией, установленной в операционной системе и отвечающей стандарту POSIX. На практике это означает возможность работы с несколькими десятками языков, в том числе и с русским языком во всех возможных кодировках: koi8-r, cp1251, iso8859-5 и UTF-8. Возможность корректной работы PostgreSQL с конкретной кодировкой зависит от корректной поддержки этой кодировки средствами самой операционной системы.

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

Языки, которые могут использоваться для написания хранимых процедур (функций)

---

• Java
• Perl
• Python
• Ruby
• Tcl
• C/C++
• PL/pgSQL

Функции и операторы

---

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

Индексы

---

PostgreSQL предлагает 4-ре типа индексов: B-tree, Hash, GiST и GIN. Каждый тип индекса имеет свой алгоритм реализации, что позволяет существенно увеличить быстродействие, если для определённого вида данных выборать определённый типа индекса.

PostgreSQL позволяет создавать индексы с использованием выражений, например: CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

PostgreSQL позволяет создавать частичные (partial) индексы, используя выражение WHERE, например: CREATE INDEX orders_unbilled_index ON orders (order_nr)   WHERE billed IS NOT true;.

Полнотекстовый поиск

---

Начиная с версии 8.3 в ядро PostgreSQL включен функционал полнотекстового поиска (который раньше поставлялся в виде отдельного модуля-расширения).

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

Многоверсионный контроль конкурентых транзакций и изоляция транзакций

---

В PostgreSQL реализован (Multiversion Concurrency Control, MVCC) - многоверсионный контроль конкурентных транзакций, который управляет конкурентным доступом к данным на многоверсионной основе. На практике это означает, что при запросе к БД каждая транзакция видит как бы снимок данных (версию) на момент этого снимка, а не текущее состояние данных. Таким образом транзакции защищаются от просмотра нецелостных данных, которые могут ещё только формироваться другими конкурентными транзакциями в тех же самых строках таблицы. Этим же достигается изоляция транзакций для каждой сессии к БД. MMVC позволяет избегать методов явной блокировки, которые применяются в традиционных СУБД и таким образом, минимизирует блокировки данных и позволяет увеличить производительность в многопользовательской работе. Основное преимущество MMVC состоит в том, что чтение данных никогда не блокирует запись, а запись никогда не блокирует чтение.

Также в PostgreSQL реализованы традиционные схемы явных блокировок данных, применяющихся для изоляции транзаций, такие как:

• блокировка на уровне таблицы
• блокировка на уровне записи в таблице (строки)
• advisory блокировки (для собственных блокировок на уровне приложений)

Также реализовано отслеживание deadlocks (взаимных блокировок)

Журналы (логи) опережающей записи (WAL)

---

PostgreSQL реализует механизм WAL (журналов опережающей записи), что даёт такие преимущества как:

• Повышение производительности работы СУБД за счёт того, что записываются только сделанные изменения без переписывания всех данных в таблицах.
• Повышение надёжности хранения данных за счёт предварительного сохранения буферизируемых данных в WAL
• Возможность отката состояния БД на любой момент времени, путём применения WAL к существующей резервной копии

Репликация и технология Hot Standby

---

Начиная с 9.0, на основе развития WAL заработала репликация по технологии Hot Standby. Технология позволяет получить на сервере вторую базу данных, которая является актуальной копией оригинальной базы данных, доступной только на чтение. Технология может быть использована также и на удалённом сервере, который подключается к primary или master серверу и загружает с него WAL логи, предоставляя онлайновую репликацию базы данных и поддерживая копию базы данных на удалённом сервере в актуальном состоянии, а также делая эту копию доступной для запросов на чтение. Это некий аналог технологии Active DataGuard в СУБД Oracle.

Табличные пространства (tablespaces)

---

Табличные пространства в PostgreSQL позволяют задать место хранения объектов БД в файловой системе. Сперва создаётся табличное пространство с определённым именем. Далее, это имя может быть использовано при создании таблиц, чтобы разместить эти таблицы именно в данном табличном пространстве

Гибкая настройка сервера

---

Основной конфигурационный файл postgresql.conf включает более настраиваемых 150 параметров по разделам:

• Файлы и пути к ним
• Сетевые соединения
• Авторизация и безопасность
• Выделение ресурсов
• WAL - логи обратной записи
• Планирование запросов
• Ошибки и протоколирование
• Статистика запросов
• Оптимизация данных через VACUUM
• Управление блокировками
• Совместимость версий и платформ
• Настройки клиента по умолчанию

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

Ограничения целостности

---

Поддерживаются следующие ограничения целостности:

• NOT NULL - не NULL
• UNIQUE - уникальность (начиная с 9.0 введёно понятие DEFFERABLE UNIQUE)
• PRIMARY KEY - первичный ключ
• FOREIGN KEY/REFERENCES - внешний ключ, ссылки
• CHECK - проверка
• EXCLUDE - проверка уникальности по сложному условию (начиная с 9.0)

Хранимые процедуры

---

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

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

Начиная с 9.0, можно создавать функции без объявления имени (Анонимные блоки) для выполнения блока операторов на любом встроенном языке, который поддерживает PostgreSQL прямо в командной строке.

Триггеры

---

Триггеры предназначены для автоматического выполнения отдельных процедур в зависимости от операции, для которой они были назначены. Триггеры могут быть назначены до или после операций INSERT, UPDATE или DELETE как для случаев изменения записи в таблице так и для случая выполнения оператора SQL. Если произошло событие, на которое был назначен триггер, то вызывается закреплённая за этим триггером процедура.

Начиная с 9.0.x есть триггеры на колонки (столбцы) и кроме того, при объявлении триггера можно использовать ключевое слово WHEN, добавляющее дополнительное условие для срабатывания триггера.

Система правил

---

Система правил (более правильно говорить: система правил изменения запросов) позволяет изменять запросы согласно заданным правилам и затем передаёт изменённый запрос планировщику запросов для планирования и выполнения. Система правил является очень мощным инструментом и может быть использована во многих случаях таких как хранимые процедуры, представления (views) и версии.

Схемы

---

PostgreSQL поддерживает схемы. Схемы являются как бы дополнительными областями видимости внутри базы данных. Также схему можно сравнить и c дополнительным путём (название схемы должно указываться перед названием таблицы) и с каталогом, внутри которого можно разместить таблицы. В любой базе данных по умолчанию существует схема public, в которой по умолчанию создаются все таблицы и которую не нужно указывать специально, но администратор БД может создавать другие схемы (и разграничивать доступ к ним), что обеспечивает ещё один уровень распределения прав доступа для пользователей, позволяет выделить каждому пользователю как бы персональный раздел внутри БД с теми же самыми названиями таблиц, что и у других пользователей.

Роли и привелегии

---

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

Начиная с 9.0 поддерживаются права на схемы, а также права по умолчанию.

Сбор статистики

---

Чтобы построить производительный план запроса, планировщик запросов в PostgreSQL использует так называемую статистику или статистическую информацию, собранную на основе анализа данных в таблицах, которая собирается с помощью команды ANALYZE, в свою очередь являющейся частью процесса обслуживания БД VACUUM. Начиная с версии 8.1, в PostgreSQL появилась возможность вместо ручного вызова команд сбора статистики, работать с новым инструментом, который назвали autovacuum. С помощью autovacuum весь необходимый сбор статистики и процесс обслуживания БД происходит в фоновом режиме автоматически. Исходя из настроек, PostgreSQL сам определяет таблицы, для которых необходимо провести сбор статистики и выполнить обслуживание VACUUM.

Резервное копирование и восстановление

---

PostgreSQL предлагает несколько режимов резервного копирования и восстановления БД. Поскольку БД располагаются в файловой системе, вполне нормальным методом является резервное копирование на уровне файлов, т.е. самого каталога где размещаются файлы БД. Единственное условие такого режима - полный останов сервера PostgreSQL. Однако, для систем высокой готовности такой режим резервного копирования недопустим, поэтому PostgreSQL позволяет выполнять резервное копирование при запущенном сервере, не прерывая его обычной работы. Наиболее простой режим - это получение дампа БД в текстовом виде (в форме операторов SQL) на стандартный вывод. Для экономии дискового пространства можно сразу же перенаправлять такой дамп на стандартный ввод утилите сжатия (например gzip). Также существует возможность создания дампа БД в двоичной форме, а также возможность задавать специальные параметры для большего удобства в получении резервной копии и её последующего восстановления.

PostgreSQL также предоставляет возможность резервного копирования WAL и за счёт этого, восстановление БД на конкретный момент времени, а также инкрементальное резервное копирование.