Краткий обзор возможностей СУБД Postgresql

Максимальный размер БД	Неограничен
Максимальный размер таблицы	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 к существующей резервной копии.

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

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

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

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

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

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

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

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

• NOT NULL – не NULL;
• UNIQUE – уникальность;
• PRIMARY KEY – первичный ключ;
• FOREIGN KEY/REFERENCES – внешний ключ, ссылки;
• CHECK – проверка.

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

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

Триггеры

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

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

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

Схемы

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

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

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

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

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

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

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

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