Начало работы с PostgreSQL. Российская субд postgres pro

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

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

Триггеры

Триггеры определяются как функции, инициируемые DML-операциями. Например, операция INSERT может запускать триггер, проверяющий добавленную запись на соответствия определённым условиям. При написании функций для триггеров могут использоваться различные языки программирования.

Триггеры ассоциируются с таблицами. Множественные триггеры выполняются в алфавитном порядке.

Правила и представления

Механизм правил (англ. rules) представляет собой механизм создания пользовательских обработчиков не только DML-операций, но и операции выборки. Основное отличие от механизма триггеров заключается в том, что правила срабатывают на этапе разбора запроса, до выбора оптимального плана выполнения и самого процесса выполнения. Правила позволяют переопределять поведение системы при выполнении SQL-операции к таблице. Хорошим примером является реализация механизма представлений (англ. views): при создании представления создается правило, которое определяет, что вместо выполнения операции выборки к представлению система должна выполнять операцию выборки к базовой таблице/таблицам с учетом условий выборки, лежащих в основе определения представления. Для создания представлений, поддерживающих операции обновления, правила для операций вставки, изменения и удаления строк должны быть определены пользователем.

Индексы

В PostgreSQL имеется поддержка индексов следующих типов: B-дерево , хэш, R-дерево, GiST, GIN. При необходимости можно создавать новые типы индексов, хотя это далеко не тривиальный процесс. Индексы в PostgreSQL обладают следующими свойствами:

• возможен просмотр индекса не только в прямом, но и в обратном порядке - создание отдельного индекса для работы конструкции ORDER BY ... DESC не нужно;
• возможно создание индекса над несколькими столбцами таблицы, в том числе над столбцами различных типов данных;
• индексы могут быть функциональными, то есть строиться не на базе набора значений некоего столбца/столбцов, а на базе набора значений функции от набора значений;
• индексы могут быть частичными, то есть строиться только по части таблицы (по некоторой её проекции); в некоторых случаях это помогает создавать намного более компактные индексы или достигать улучшения производительности за счёт использования разных типов индексов для разных (например, с точки зрения частоты обновления) частей таблицы;
• планировщик запросов может использовать несколько индексов одновременно для выполнения сложных запросов.

Многоверсионность (MVCC)

PostgreSQL поддерживает одновременную модификацию БД несколькими пользователями с помощью механизма Multiversion Concurrency Control (MVCC). Благодаря этому соблюдаются требования ACID и практически отпадает нужда в блокировках чтения.

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

PostgreSQL обладает встроенной системой полнотекстового поиска, позволяющей искать внутри базы данных документы и сортировать их в заданном порядке. Основными преимуществами использования встроенного полнотекстового поиска являются: тесная интеграция с СУБД(транзакционность, одновременный доступ, восстановление после сбоев), масштабируемость, широкие возможности настройки (словари, парсеры, и т.д.).

Геоинформационные системы

PostGIS - расширение СУБД PostgreSQL предназначенное для хранения в базе географических данных. PostGIS включает поддержку пространственных индексов R-Tree/GiST и функции обработки геоданных.

2019: Совместимость с TerraLink xDE

2018

Включение в список коммитеров СУБД PostgreSQL сооснователя Postgres Professional Александра Короткова

В июне 2018 года список коммитеров (разработчиков, вносящих вклад в развитие кода) СУБД PostgreSQL пополнился третьим россиянином. В список основных коммитеров ядра PostgreSQL , сооснователь и руководитель разработки российской компании Postgres Professional .

2017

Документация версии 10 локализована для России

Основные нововведения:

• Логическая репликация : отдельные части этого механизма были добавлены в PostgreSQL уже довольно давно, а в этой версии логическая репликация стала полностью доступна для пользователей. С ее помощью можно выборочно реплицировать отдельные таблицы на другой сервер , который при этом может выполнять как читающие, так и пишущие запросы. Серверы, участвующие в репликации, могут работать под управлением разных версий PostgreSQL, что позволяет проводить обновление кластера с минимальным временем простоя.
• Декларативное секционирование избавляет администратора от необходимости вручную определять иерархию таблиц, создавать триггеры и ограничения целостности.
• Параллельное выполнение запросов стало возможным для сканирования битовых карт и индексов, для соединения слиянием и подзапросов в дополнение к тем возможностям, которые появились в предыдущей версии.
• Синхронная репликация с учетом кворума позволяет фиксировать изменения, если их подтвердило необходимое число произвольных реплик.
• SCRAM-аутентификация является более криптостойким вариантом используемой ранее MD5-аутентификации .

Всего, по словам разработчиков, в версию 10 вошло более 100 изменений и улучшений, часть из которых выполнена в компании Postgres Professional .

Интеграция с Ethereum

14 сентября 2017 года российская компания Postgres Professional объявила о создании прототипа расширения Posthereum для интеграции полнофункциональной СУБД PostgreSQL с блокчейн -платформой , предназначенной для регистрации сделок с любыми видами активов на основе системы «умных контрактов». По замыслу компании, крупные российские банки, корпорации и госструктуры, работающие с СУБД PostgreSQL, с помощью данной разработки смогут объединить базы данных с блокчейн-приложениями на основе Ethereum. Подробнее .

2016

PostgreSQL 9.6

29 сентября 2016 года сообщество разработчиков представило стабильную ветку СУБД PostgreSQL 9.6. Обновления для нее 9.6 будут выходить в течение пяти лет, до сентября 2021 года.

Основные дополнения

Сравнение Tibero и PostgreSQL

Корректирующий выпуск всех веток

11 февраля 2016 года сообщество разработчиков PostgreSQL сообщило о выпуске корректирующих обновлений для всех поддерживаемых веток PostgreSQL: 9.5.1, 9.4.6, 9.3.11, 9.2.15 и 9.1.20, в которых устранены две уязвимости, представлена порция исправлений ошибок, добавлена поддержка Python 3.5 в PL/Python и обеспечена возможность совместного использования Python2 и Python3 в одной БД .

Поддержка ветки 9.0.x прекращена. Выпуск обновлений для ветки:

• 9.1 продлен до сентября 2016 года.
• 9.2 продлен до сентября 2017 года,
• 9.3 продлен до сентября 2018 года,
• 9.4 продлен до декабря 2019 года,
• 9.5 продлен до января 2021 года.

Первая из уязвимостей (CVE-2016-0773) проявляется в движке обработки регулярных выражений и может привести к краху бэкенда при разборе регулярных выражений с символами вне диапазона Unicode (проблеме подвержены системы, в которых пользовательский ввод применяется для генерации регулярного выражения).

Вторая уязвимость (CVE-2016-0766) присутствует в движке PL/Java и позволяет повысить свои привилегии при работе с БД.

PostgreSQL 9.5

7 января 2016 года стало известно о выходе стабильной ветки СУБД PostgreSQL 9.5. Выпуск обновлений для ветки 9.5 будет поддерживаться до января 2021 года .

Изменения

• Функциональность "UPSERT" (добавить-или-модифицировать), реализованная через новое выражение "INSERT ... ON CONFLICT DO NOTHING/UPDATE", позволяющая обработать ситуацию невозможности добавления данных через "INSERT", например, из-за нарушения условий уникальности или недопустимости значения одного из полей. Вместо вывода ошибки теперь можно игнорировать выполнение оператора или изменить связанные с ключевым полем данные (т.е. если запись уже существует, вместо INSERT выполнить UPDATE);
• Ограничение доступа на уровне строк (Row-Level Security, RLS). Доступ пользователей к данным в таблице теперь можно разграничивать на уровне отдельных строк, например, можно запретить определённой категории пользователей просмотр строк, в которых хранятся данные, добавленные другим пользователем. Для активации RLS следует использовать директиву "ALTER TABLE tablename ENABLE ROW LEVEL SECURITY", после чего следует задать правила доступа при помощи выражения "CREATE POLICY";
• BRIN-индексы ("индексы блоковых зон", Block Range Index), позволяющие сверхкомпактно индексировать очень большие таблицы, без применения традиционных B-деревьев. Суть BRIN-индексов сводится к разбиению общего индекса на блоки, каждый из которых содержит данные индекса только для определённого диапазона значений. В тесте подобный метод оказался примерно в два раза медленнее b-деревьев при осуществлении операций выборки данных, но в 3-4 раза быстрее при создании и обновлении индекса, а также занял значительно меньше места на диске (64 Кб против 28 Мб);
• Новые функции и операторы для типа данных JSONB. Для изменения значений в документе JSONB теперь можно обойтись без извлечения и переопределения всего документа, благодаря появлению функции jsonb_set(). Также добавлены функции json_strip_nulls (удаление атрибутов, содержащих значения NULL) и jsonb_pretty (вывод в отформатированном JSON). Добавлен оператор "||" для соединения двух значений JSONB;
• Инструмент pg_rewind, позволяющий существенно упростить процесс восстановления отказоустойчивых конфигураций после переключения на резервный сервер. После возвращения в строй основного сервера возникает задача синхронизации его состояния с продолжившим работу запасным сервером, который успел накопить свою порцию изменений. Утилита pg_rewind пытается восстановить состояние первичного сервера по WAL-логу транзакций, перебирая их начиная с момента незадолго до сбоя, определяя изменённые данные и перенося только изменившиеся блоки, что позволяет обойтись без восстановления полной копии с работающего резервного сервера.
• Значительно оптимизированы скорости сортировки и хэширования в памяти. Благодаря применению нового метода сортировки строковых значений и чисел, удалось до 20 раз увеличить скорость создания индексов, а время выполнения запросов требующих сортировки больших объёмов данных, сократить в 2-12 раз;
• Добавлена поддержка выражения TABLESAMPLE, позволяющего сформировать выборку над неполным объёмом данных из больших таблиц, без выполнения ресурсоёмких операций сортировки над всей таблицей. Например, запрос "SELECT * FROM test TABLESAMPLE SYSTEM(10)" сформирует вывод, охватив только 10% от таблицы test. Доступно несколько алгоритмов отсеивания значений в процессе неполной выборки;
• Улучшено масштабирование на системах с большим количеством процессорных ядер и оперативной памяти. Например, на системе с 24 ядрами CPU и 496 Гб ОЗУ в тесте EnterpriseDB при нагрузке в 64 одновременных соединения PostgreSQL 9.5 показал прирост производительности в 96% по сравнению с PostgreSQL 9.4;
• Автоматизировано управление размером лога транзакций. Возможность исключения отражения таблиц в логе транзакций (ALTER TABLE ... SET LOGGED / UNLOGGED);
• Аналитические возможности "GROUPING SETS", "CUBE" и "ROLLUP", позволяющие формировать вывод с группировкой по набору полей и рассчитывать число комбинаций различных категорий;
• Улучшена репликация и средства повышения отказоустойчивости. Добавлен механизм отслеживания состояния выполнения репликации, в том числе реализованы методы для определения причины возникновения отдельных изменений в процессе выполнения логической репликации;
• Произведены множественные улучшения в механизме Foreign Data Wrappers, включая выражение "IMPORT FOREIGN SCHEMA", которое позволяет автоматизировать импорт всех связанных внешних таблиц для существующих таблиц с выбранной меткой сервера. Кроме того, обеспечена возможность наследования внешних таблиц в локальных таблицах и наоборот, например, "CREATE local_customers () inherits (remote.customers);"
• В утилиту vacuumdb добавлена опция "-j", позволяющая запускать VACUUM в несколько одновременно выполняемых потоков.

2015

Инфраструктура параллельных вычислений в PostgreSQL

4 мая 2015 года стало известно о принятии изменений в дерево исходных текстов СУБД PostgreSQL с реализацией инфраструктуры для параллельных вычислений .

Она предоставляет:

• Удобные процедуры для координирования запуска и завершения работы параллельно выполняемых рабочих процессов;
• Синхронизация различных внутренних состояний (GUCs, комбинированный маппинг CID, снапшоты транзакций) между лидером группы параллельных работ и непосредственно распараллеленными рабочими процессами;
• Ограничение вызова различных операций, которые могут привести к внесению некорректных изменений в условиях активного распараллеливания;
• Доставка уведомлений клиенту через сообщения ErrorResponse, NoticeResponse и NotifyResponse от работающих в параллельном режиме обработчиков.

Postgres-XL на EcoServer - альтернатива для ЦОД

13 августа 2015 года стало известно о завершении испытаний системы управления базами данных Postgres-XL на серверах линейки EcoServer .

Тестирование проводилось с целью мониторинга новых технологий и реализации плана технологического развития на 2015 год.

Андрей Черногоров , генеральный директор «Индиго ИТ », отметил: «Сегодня на рынке ИТ наиболее востребованными являются СУБД MS SQL и Oracle DataBase . Вместе с тем, по ряду ключевых возможностей им ни чем не уступает, а кое-где и превосходит, СУБД с открытыми кодами PostgreSQL , что открывает перед ней широкие перспективы для использования в рамках программы импортозамещения».

Для тестирования специалисты компании подготовили идентичные для всех СУБД тестовые наборы данных. Объектом испытаний стала база данных объемом 1 ТБ, состоящая из 1 млн. бизнес-объектов. Продолжительность тестирования для каждой СУБД - 10 часов.

В нем участвовали последние версии наиболее востребованных заказчиками «Индиго ИТ » СУБД :

• открытая СУБД PostgreSQL 9.4 .

Всего проведено 5 наборов тестов:

• создание сложно структурированных документов,
• обновление сложно структурированных документов,
• поиск документов,
• запись файла в базу данных,
• получение файла из базы данных.

Результаты тестирования, 2015

Под временем, затраченным в каждом из наборов тестов указанных в таблице, имеется ввиду усредненное по всем наборам значение (мс). Тестирование проводилось на серверах с процессорами Intel Xeon Е5 v3 с 128 Гб ОЗУ.

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

Поддержка данной версией СУБД с открытым кодом PostgreSQL широко распространенного формата обмена данными JSON нацелена на растущий рынок нереляционных хранилищ данных NoSQL и особенно на популярную СУБД MongoDB .

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

Версия PostgreSQL 9.4 поддерживает формат JSON (JavaScript Simple Object Notation), который быстро завоевал популярность при организации обмена данными между различными системами, в том числе и с применением протокола REST (Representational State Transfer). Успех документальной СУБД MongoDB во многом обусловлен как раз растущей популярностью JSON .

Структурированный формат PostgreSQL для сохранения данных в соответствии со спецификациями JSON (JSONB) исключает необходимость реструктуризации документа перед его занесением в базу данных. В результате PostgreSQL проглатывает документы так же быстро, как и MongoDB , продолжая при этом удовлетворять требованиям ACID (atomicity, consistency, isolation, durability - атомарность, согласованность, изолированность и надежность), которые предъявляются к хранению информации в базах данных. Кроме того, PostgreSQL поддерживает полный набор индексных сервисов, функций и операторов для эффективного манипулирования данными JSON.

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

PostgreSQL получила ряд новых функций:

• Новый интерфейс API для декодирования данных из потока репликации открывает независимым разработчикам программного обеспечения путь к созданию более быстрых реплицирующих систем.
• Новая функция Materialized Views, называемая «одновременным обновлением», позволяет на лету обновлять итоговые отчеты.
• Функция Alter System Set поможет администраторам изменять файл конфигурации PostgreSQL непосредственно из командной строки SQL.

Добавлен ряд функций и возможностей, среди которых динамические фоновые исполнители (Dynamic Background Workers), манипуляции с массивами и табличные функции, увеличена общая производительность.

PostgreSQL 9.3

В PostgreSQL 9.3 реализован ряд механизмов, позволяющих обмениваться информацией с другими базами и хранилищами данных. Модули Foreign Data Wrapper, которые появились еще в версии 9.1 и раньше позволяли только считывать данные из других систем, теперь предоставляют и возможность записи. Поддерживается работа как с реляционными таблицами, так и с полуструктурированной информацией из систем NoSQL. Для СУБД также создан драйвер, который позволяет связать с друг другом две разных копии самой PostgreSQL и обеспечивает ускоренное выполнение транзакций между ними.

Среди других особенностей - расширенная поддержка JSON и возможность создания произвольных фоновых серверных модулей с неограниченным доступом к данным PostgreSQL. Пример - модуль Mongres, автоматически переводящий запросы MongoDB в формат PostgreSQL.

Реализовано автоматическое обновление представлений и добавлена утилита, позволяющая в параллельном режиме выполнять резервное копирование больших баз. Приняты меры по повышению надежности СУБД. Функция Fast Failover позволяет переключить работу с мастер-базы на копию меньше чем за секунду. Появилась возможность проверки контрольных сумм страниц, помогающая диагностировать сбои жестких дисков.

PostgreSQL 9.2

PostgreSQL 9.0

Разработчики открытой системы управления базами данных PostgreSQL выпустили в сентябре 2010 года первый релиз-кандидат системы Postrgesql 9.0, в котором реализованы все функции, заготовленные к выходу в девятой версии этой популярной СУБД. В свободном доступе на данный момент доступна бинарная версия предварительной сборки Postgresql 9.0 и все желающие могут протестировать новые возможности этой разработки перед тем, как переводить на нее производственные серверы, работающие с реальной информацией.

Также в девятой версии появилась возможность репликации информации из бинарных логов, соответствующая механизму Hot Stanby Databases в Oracle Database. Не обошли вниманием разработчики и набирающие популярность облачные или SaaS -системы. Теперь СУБД оптимизирована для работы в среде виртуальных машин, поддерживает механизм быстрого клонирования данных, а также возможность репликации информации с единого мастер-сервера на большое количество (более сотни) подчиненных серверов. Также новый релиз полностью поддерживает возможности адресации памяти в 64-битных вариантах Windows .

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

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

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

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

Локализация

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

Языки

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

• Python
• 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 также предоставляет возможность резервного копирования и за счёт этого, восстановление БД на конкретный момент времени, а также инкрементальное резервное копирование.

В профессиональной среде коротко называется «постгрес») - свободная объектно-реляционная система управления базами данных (СУБД).

Сильными сторонами PostgreSQL считаются:

Исторический очерк

PostgreSQL ведет свою «родословную» от некоммерческой СУБД Postgres, разработанной, как и многие проекты, в Калифорнийском университете в Беркли . К разработке Postgres, начавшейся в 1986 году, имел непосредственное отношение Майкл Стоунбрейкер , руководитель более раннего проекта Ingres , на тот момент уже приобретённого компанией Computer Associates. Само название «Postgres » расшифровывалось как «Post Ingres », соответственно, при создании Postgres были применены многие уже ранее сделанные наработки.

Стоунбрейкер и его студенты разрабатывали новую СУБД в течение восьми лет, с по 1994 год . За этот период в синтаксис были введены процедуры, правила, пользовательские типы и многие другие компоненты. Работа не прошла даром - в 1995 году разработка снова разделилась: Стоунбрейкер использовал полученный опыт в создании коммерческой СУБД Illustra, продвигаемой его собственной одноимённой компанией (приобретённой впоследствии компанией Informix), а его студенты разработали новую версию Postgres - Postgres95, в которой язык запросов POSTQUEL - наследие Ingres - был заменен на SQL.

В этот момент разработка Postgres95 была выведена за пределы университета и передана команде энтузиастов. С этого момента СУБД получила имя, под которым она известна и развивается в текущий момент - PostgreSQL.

Основные возможности

Функции

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

• Встроенный процедурный язык PL/pgSQL , во многом аналогичный языку PL/SQL , используемому в СУБД Oracle;
• Скриптовые языки - PL/Lua, PL/LOLCODE, PL/Perl , plPHP, PL/Python , PL/Ruby, PL/sh, PL/Tcl и PL/Scheme;
• Классические языки - , C++ , Java (через модуль PL/Java);
• Статистический язык (через модуль PL/R).

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

Функции могут выполняться как с правами их создателя, так и с правами текущего пользователя.

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

Триггеры

Пользовательские объекты

PostgreSQL может быть расширен пользователем для собственных нужд практически в любом аспекте. Есть возможность добавлять собственные:

• Преобразования типов
• Типы данных
• Домены (пользовательские типы с изначально наложенными ограничениями)
• Функции (включая агрегатные)
• Индексы
• Операторы (включая переопределение уже существующих)
• Процедурные языки

Наследование

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

Данная функциональность в текущее время не является полностью завершённой. Однако она достаточна для практического использования.

Прочие возможности

• Соблюдение принципов ACID .
• Соответствие стандартам ANSI SQL-92 и SQL-99.
• Поддержка запросов с OUTER JOIN , UNION , UNION ALL , EXCEPT , INTERSECT и подзапросов.
• Последовательности.
• Поддержка Юникода (UTF-8).
• Поддержка регулярных выражений в стиле Perl .
• Встроенная поддержка SSL ,SELinux и Kerberos .
• Протокол разделяемых блокировок.
• Подгружаемые расширения, поддерживающие SHA1 , MD5 , XML ,

расширения для написания сложных выборок, отчётов и другую функциональность (API открыт).

• Средства для генерации совместимого с другими системами SQL-кода и импорта из других систем.
• Автономные блоки на доступных языках, а не только SQL

Надёжность

Согласно результатам автоматизированного исследования различного ПО на предмет ошибок, в исходном коде PostgreSQL было найдено 20 проблемных мест на 775 000 строк исходного кода (в среднем, одна ошибка на 39 000 строк кода) . Для сравнения: MySQL - 97 проблем, одна ошибка на 4 000 строк кода; FreeBSD (целиком) - 306 проблем, одна ошибка на 4 000 строк кода; Linux (только ядро) - 950 проблем, одна ошибка на 10 000 строк кода.

Коммерческие расширения

На базе PostgreSQL компанией EnterpriseDB созданы более мощные варианты этой СУБД, являющиеся платными для коммерческого использования - Postgres Plus (состоит целиком только из продуктов с открытыми исходными кодами; плата требуется только при необходимости приобретения коммерческой поддержки продукта) и Postgres Plus Advanced Server (расширение PostgreSQL специальными возможностями для обеспечения совместимости с Oracle Database) . В комплекте поставки данных продуктов содержится большой набор ПО для разработчиков и DBA:

• Postgres Studio - более мощный аналог pgAdmin;
• Postgres Plus Debugger - отладчик для кода на PL/pgSQL, интегрированный с предыдущим пакетом;
• Migration Studio - инструмент для автоматического преобразования баз данных из MySQL/Oracle в PostgreSQL

История развития

Основные возможности СУБД по мере выхода новых версий

8.3

• Интегрированный полнотекстовый поиск
• Базовая поддержка для XML
• Новый тип - перечисление
• Массивы составных типов(структур в понимании С)
• Новый тип - UUID(уникальный глобальный идентификатор)

8.4

• Аналитические функции
• Общие табличные выражения и рекурсивные запросы
• Сопоставление локали(COLLATION) отдельно для каждой базы данных
• Права доступа на колонку

9.0

• Автономные блоки(использование любых разрешённых процедурных языков без создания самих процедур)
• Асинхронная встроенная репликация с «тёплым резервом» посредством журнала транзакций
• Поддержка 64 разрядных версий ОС WINDOWS
• Облегчено администрирование прав на множества объектов
• Триггеры могут быть ориентированы на отдельные столбцы и их комбинации
• Отложенные до конца транзакции проверки уникальности
• Ограничения типа Exclusion
• Сверх-быстрый механизм извещений между сессиями(LISTEN/NOTIFY)

9.1

• Синхронная репликация
• Очень быстрые нежурналируемые таблицы
• Сопоставление локали(COLLATION) вплоть до каждого столбца таблицы отдельно
• Расширение возможностей общетабличных выражений для INSERT/UPDATE/DELETE
• Доступ на чтение ко внешним источникам данных как к таблицам
• Введён механизм пакетов расширений(extension)
• Введён каноничный уровень изоляции serializable
• Расширение функционала SELinux

9.2

• Введён механизм index-only scan для планировщика
• Новый тип данных - диапазоны(range data types)
• Новый тип данных - JSON
• Многоуровневая потоковая репликация

Дальнейшие планы

Дальнейшие планы по развитию СУБД находятся по адресу http://wiki.postgresql.org/wiki/Todo

Примечания

Ссылки

• Официальный сайт PostgreSQL (англ.)
• Search PostgreSQL sites (англ.) . Специализированный поиск по ресурсам PostgreSQL
• PostgreSQL . Серия из шести статей, опубликованных в журнале Linux Format (2006-2007)

Я достаточно долго думал над вопросом, какую систему управления базами данных (СУБД) выбрать для своих статей и решил остановиться на PostgreSQL.
Выбор обусловлен несколькими причинам:

1. Бесплатная СУБД
2. Простота установки.
3. Поддержка основных операционных систем
4. Удобная програма pgAdmin для работы с базами
5. Это современная СУБД с хорошими возможностями

В принципе в JDK есть встроенная база данных — Derby. Но пользоваться ей, на мой взгляд, очень неудобно. MySQL в общем тоже неплохо, но в нем достаточно неудобная утилита для ввода команд. Остальные базы либо платные, либо малоизвестные. Само собой, после прочтения моих статей вам никто не мешает попробовать поработать с этими базами данных самостоятельно.

Загрузить нужную версию PostgreSQL можно с этой страницы: Download PostgreSQL .
На данный момент я использовал версию 9.5.2. Какая версия будет на момент чтения статьи вами — не знаю. Но надеюсь, что в ближайшие годы что-то кардинально не поменяется.
Будьте внимательны — загружайте версию для вашей операционной системы. Дальше запускаете установку. По экранам она выглядит вот так.

На втором экране вам надо выбрать директорию для установки. Я не рекомендую устанавливать в каталог “Program Files” по умолчанию, т.к. на Windows серверных платформ это бывает чревато. На домашних системах скорее всего проблем не будет, но как говорится, “обэегшись на молоке, дуешь на воду”. Посему я обычно ставлю директорию “C:\PostgreSQL\”

На следующем экране вам надо ввести парль для пользователя “postgres”. Для разработки я выбираю такой же пароль: “postgres”.

На следующем экране запрашивается порт, на котором будет “висеть” PostgreSQL. Если у вас не установлен PostgreSQL, то можно оставить по умолчанию “5432”.

Локализацию можно оставить как есть. Кому интересно, может выбрать что-нибудь конкретное. Я в принципе проблем не имел при выборе по-умолчанию.

Запускаем установку …

и ждем, пока она закончится.

По окончанию вам предложат установить дополнительную утилиты Stack Builder — я ее обычно не ставлю, так что “галочку” можно снять и нажать “Finish”.

В общем установка закончилась. Теперь в списке сервисов Windows можно увидеть PostgreSQL

Я нередко устанавливаю ручной запуск сервиса, но это уже как вам будет удобно.

Осталось только узнать, что в комплекте PostgreSQL устанавливается весьма удобная и легко понятная программа для управления СУБД — pgAdmin III. Ее можно найти в стартовом меню Windows (если вы работаете под другой ОС — поищите, наверняка найдете).
Запускайте и дальше все достаточно просто.

Кликаем дважды на PostgreSQL 9.5 слева — вас могу попросить ввести пароль.
Дальше вы увидите слева струткуру вашей СУБД.

Открываем слева раздел “Базы данных” и видим уже заранее созданную базу “postgres”.
Щелкните правой кнопкой мыши на пункте “Базы данных” и в выпадающем меню выберите “Новая база данных…”. Появится форма для ввода — для начала достаточно ввести имя базы данных — я назвал ее “contactdb”

Выделите мышкой вновь созданную базу данных и получите возможность делать с ней, что хотите.

Для запуска команд вам надо открыть SQL-редактор. Проще всего — нажать кнопку на верхней панели.

В открытом окне можно набирать команды SQL.

В общем все готово. Мы можем запускать SQL-скрипт, который создаст нужную нам таблицу для контактов и вставит туда тестовые данные.
Для создания необходимых нам данных SQL-скрипт выглядит вот так:

DROP TABLE IF EXISTS JC_CONTACT; CREATE TABLE JC_CONTACT (CONTACT_ID SERIAL, FIRST_NAME VARCHAR(50) NOT NULL, LAST_NAME VARCHAR(50) NOT NULL, PHONE VARCHAR(50) NOT NULL, EMAIL VARCHAR(50) NOT NULL, PRIMARY KEY (CONTACT_ID)); INSERT INTO JC_CONTACT (FIRST_NAME, LAST_NAME, PHONE, EMAIL) VALUES ("Peter","Belgy","+79112345678","[email protected]"); INSERT INTO JC_CONTACT (FIRST_NAME, LAST_NAME, PHONE, EMAIL) VALUES ("Helga","Forte","+79118765432","[email protected]"); SELECT * from JC_CONTACT;

Postgres Pro - Российская СУБД, разработанная компанией Postgres Professional на основе свободно-распространяемой СУБД PostgreSQL. Postgres Pro входит в реестр российского ПО (см https://reestr.minsvyaz.ru/reestr/65273/)

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

Сравнение версий Postgres Pro

Версии Postgres Pro

Номер версии Postgres Pro строится из номера версии PostgreSQL с дополнением одного разряда, обозначающего номер текущего выпуска. При выходе новой минорной версии PostgreSQL (обычно это происходит при появлении патчей, связанных с безопасностью и исправлением серьезных ошибок), нумерация Postgres Pro сбрасывается на единицу. Например, при выходе PostgreSQL 9.5.1 выходит PostgresPro 9.5.1.1, затем до появления PostgreSQL 9.5.2 может выйти Postgres Pro 9.5.1.2, 9.5.1.3 и т.д. При выходе PostgreSQL 9.5.2 Postgres Pro обновится до версии 9.5.2.1 и т.д.

Одновременно с выпуском исходного кода Postgres Pro мы публикуем наши сборки в виде пакетов под различные платформы. Это следующие операционные системы и их версии:

1. Linux
   • CentOS 6/7,
   • Debian 7/8,
   • Ubuntu 12.04/14.04/16.04/16.10,
   • Oracle Linux,
   • Rosa Enterprise Linux server,
   • ROSA SX Cobalt Server,
   • ROSA DX Cobalt Server,
   • ROSA Marathon LTS 2012,
   • Alt Linux Centaur 8,
   • Alt Linux СПТ 6,
   • Alt Linux СПТ 7,
   • SUSE Linux Enterprise Server,
2. Microsoft ® Windows ® 2012 или 2016 .

Базы данных Postgres Pro 9.5.*.* совместимы с PostgreSQL 9.5.* При переходе с 9.5 dump/restore не требуется. При переходе с более ранних версий PostgreSQL необходимо использование dump/restore или pg_upgrade.

Текущая версия Postgres Pro Standard - 11.2.1 . Дата выпуска - 28 марта 2019. .

Отличия Postgres Pro Standard от PostgreSQL

В Postgres Pro Standard по сравнению с актуальной версией PostgreSQL на текущий момент вошли следующие изменения:

1. Улучшения производительности на многоядерных системах:
   • оптимизации выделения хэш-таблиц в shared memory, исключающая lock contention при большом количестве процессов.
   • оптимизации Resource Owner. Улучшает производительность сложных запросов и запросом к таблицам с большим количеством партиций.
   • Оптимизации buffer manager
   • Оптимизация LWLock для архитектуры Power8
   • Оптимизации двухфазного коммита
2. Усовершенствования полнотекстового поиска:
   • поддержка поиска фраз
   • поддержка словарей hunspell для работы со словоформами
   • некоторые словари, в том числе русский и английский, включены в состав дистрибутива и их подключение требует одной команды SQL
   • модуль shared_ispell , оптимизирующий производительность полнотекстового поиска, путем загрузки словарей в память при старте сервера, а не при начале сессии.
3. Покрывающие индексы. Поддержка конструкции INCLUDING в CREATE INDEX.
4. Переносимость: поддержка библиотеки libicu на всех платформах, что обеспечивает однозначную обработку порядка сортировки и прочих операций с юникодными символами. На ряде платформ эта библиотека улучшает производительность сортировки, и, что немаловажно, позволяет в Postgres Pro пользоваться abbreviated keys, которые были отключены в основной версии PostgreSQL.
5. Модуль pg_trgm поддерживает не только нечеткое сравнение строк, но и нечеткий поиск подстроки.
6. Модуль pageinspect поддерживает доступ не только к метаинформации, но и к внутреннему представлению данных таблиц.
7. Добавлен новый модуль sr_plan , позволяющий сохранять планы выполнения запросов и использовать сохраненные планы вместо того, чтобы формировать план запроса заново, при каждом выполнении.
8. Добавлен модуль dump_stat , позволяющий при дампе базы данных сохранять информацию о статистике и восстанавливать её. Это позволяет ускорить процедуру восстановления, избавляя от необходимости вычисления статистики командой VACUUM ANALYZE после восстановления.
9. Добавлен модуль JSQuery , позволяющий на специальном языке формулировать запросы к полям типа JSONB с поддержкой GIN-индексов.
10. Модуль предоставляет дополнительный тип данных для совместимости с Microsoft SQL Server.
11. Модуль предоставляет дополнительный оператор равенства для совместимости с Microsoft SQL Server.
12. Модуль предоставляет транзакционно-небезопасную функцию для усечения временных таблиц, предотвращающую разрастание каталога pg_class.
13. Модуль предоставляет набор функций, которые немедленно обновляют статистику в целевых таблицах после операций INSERT, UPDATE, DELETE и SELECT INTO в них.
14. Модуль добавляет поддержку указаний для планировщика, позволяющих отключать или подключать определённые индексы при выполнении запроса.

Более подробно с различиями PostgreSQL и Postgres Pro можно познакомиться в Таблице сопоставления продуктов.

Лицензия Postgres Pro Standard

Postgres Pro Standard распространяется по лицензии PostgreSQL с дополнениями Postgres Professional: