Российская субд postgres pro. Инфраструктура параллельных вычислений в PostgreSQL. Сравнение версий Postgres Pro

Скриптовые языки - PL/Lua, PL/LOLCODE, PL/Perl, plPHP, PL/Python, PL/Ruby, PL/sh, PL/Tcl и PL/Scheme;

Классические языки - C, C++, Java (через модуль PL/Java);

Статистический язык R (через модуль PL/R).

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 .

Перевод

Сегодня давайте поговорим о преимуществах Postgres перед другими системами с открытым кодом. Эту тему мы обязательно раскроем более подробно на PG Day"16 Russia, до которой осталось всего два месяца.

Возможно, вы спрашиваете себя: «Почему PostgreSQL?» Ведь есть и другие варианты реляционных баз данных с открытым исходным кодом (в рамках этой статьи мы рассматривали MySQL, MariaDB и Firebird), так что же Постгрес может предложить такого, чего нет у них? В слогане PostgreSQL заявляется, что это «Самая продвинутая база данных с открытым исходным кодом в мире». Мы приведем несколько причин, почему Постгрес делает такие заявления.

В первой части этой серии мы поговорим о хранении данных - модели, структуре, типах и ограничениях размера. А во второй части больше сфокусируемся на выборке и манипуляциях с данными.

Модель данных

PostgreSQL не просто реляционная, а объектно-реляционная СУБД. Это даёт ему некоторые преимущества над другими SQL базами данных с открытым исходным кодом, такими как MySQL, MariaDB и Firebird.

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

Структуры и типы данных

Существует обширный список типов данных, которые поддерживает Постгрес. Кроме числовых, с плавающей точкой, текстовых, булевых и других ожидаемых типов данных (а также множества их вариаций), PostgreSQL может похвастаться поддержкой uuid, денежного, перечисляемого, геометрического, бинарного типов, сетевых адресов, битовых строк, текстового поиска, xml, json, массивов, композитных типов и диапазонов, а также некоторых внутренних типов для идентификации объектов и местоположения логов. Справедливости ради стоит сказать, что MySQL, MariaDB и Firebird тоже имеют некоторые из этих типов данных, но только Постгрес поддерживает их все.

Давайте рассмотрим подробнее некоторые из них:

Сетевые адреса

PostgreSQL обеспечивает хранение разных типов сетевых адресов. Тип данных CIDR (бесклассовая маршрутизация интернет домена, Classless Internet Domain Routing) следует соглашению для сетевых адресов IPv4 и IPv6. Вот несколько примеров:

192.168.100.128/25
10.1.2.3/32
2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128
::ffff:1.2.3.0/128

Также для хранения сетевых адресов доступен тип данных INET, используемый для IPv4 и IPv6 хостов, где подсети являются необязательными. Тип данных MACADDR может использоваться для хранения MAC-адресов для идентификации оборудования, таких как 08-00-2b-01-02-03.

У MySQL и MariaDB тоже есть INET функции для конвертации сетевых адресов, но они не предоставляют типы данных для внутреннего хранения сетевых адресов. У Firebird тоже нет типов для хранения сетевых адресов.

Многомерные массивы

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

Создаем таблицу, у которой значения являются массивами CREATE TABLE holiday_picnic (holiday varchar(50) -- строковое значение sandwich text, -- массив side text , -- многомерный массив dessert text ARRAY, -- массив beverage text ARRAY -- массив из 4-х элементов); -- вставляем значения массивов в таблицу INSERT INTO holiday_picnic VALUES ("Labor Day", "{"roast beef","veggie","turkey"}", "{ {"potato salad","green salad","macaroni salad"}, {"chips","crackers"} }", "{"fruit cocktail","berry pie","ice cream"}", "{"soda","juice","beer","water"}");
MySQL, MariaDB, и Firebird так не умеют. Чтобы хранить такие массивы значений в традиционных реляционных базах данных, придется использовать обходной путь и создавать отдельную таблицу со строками для каждого из значений массива.

Геометрические данные

Геоданные быстро становятся основным требованием для многих приложений. PostgreSQL уже давно поддерживает множество геометрических типов данных, таких как точки, линии, круги и многоугольники. Один из этих типов – PATH, он состоит из множества последовательно расположенных точек и может быть открытым (начальная и конечная точки не связаны) или закрытым (начальная и конечная точки связаны). Давайте рассмотрим в качестве примера туристическую тропу. В данном случае туристическая тропа - это петля, поэтому начальная и конечная точки связаны, и, значит, мой путь является закрытым. Круглые скобки вокруг набора координат указывают на закрытый путь, а квадратные - на открытый.

Создаем таблицу для хранения троп CREATE TABLE trails (trail_name varchar(250), trail_path path); -- вставляем тропу в таблицу, -- для которой маршрут определяется координатами в формате широта-долгота INSERT INTO trails VALUES ("Dool Trail - Creeping Forest Trail Loop", ((37.172,-122.22261666667), (37.171616666667,-122.22385), (37.1735,-122.2236), (37.175416666667,-122.223), (37.1758,-122.22378333333), (37.179466666667,-122.22866666667), (37.18395,-122.22675), (37.180783333333,-122.22466666667), (37.176116666667,-122.2222), (37.1753,-122.22293333333), (37.173116666667,-122.22281666667)));
Расширение PostGIS для PostgreSQL дополняет существующие свойства геометрических данных вспомогательными пространственными типами, функциями, операторами и индексами. Оно обеспечивает поддержку местоположения и поддерживает как растровые, так и векторные данные. Оно также обеспечивает совместимость с множеством сторонних геопространственных инструментов (защищённых авторским правом и с открытым исходным кодом) для отображения, отрисовки и работы с данными.

Заметьте, что в MySQL 5.7.8 и в MariaDB, начиная с версии 5.3.3, были добавлены расширения типов данных для поддержки стандарта географической информации OpenGIS. Эта версия MySQL и последующие версии MariaDB предлагают хранение типов данных, аналогичное штатным геоданным Постгреса. Тем не менее, в MySQL и MariaDB значения данных сначала должны быть сконвертированы в геометрический формат простыми командами перед тем, как будут вставлены в таблицу. Firebird на данный момент не поддерживает геометрические типы данных.

Поддержка JSON

Поддержка JSON в PostgreSQL позволяет вам перейти к хранению schema-less данных в SQL базе данных. Это может быть полезно, когда структура данных требует определённой гибкости: например, если в процессе разработки структура всё ещё меняется или неизвестно, какие поля будет содержать объект данных.

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

В MySQL 5.7.8 и MariaDB 10.0.1 была добавлена поддержка встроенных объектов JSON. Но, хотя существует множество функций и операторов для JSON, которые теперь доступны в этих базах данных, они не индексируются так, как JSONB в PostgreSQL. Firebird пока что не присоединился к тренду и поддерживает объекты JSON только в виде текста.

Создание нового типа

Если вдруг так случится, что обширного списка типов данных Постгреса вам окажется недостаточно, вы можете использовать команду CREATE TYPE, чтобы создать новые типы данных, такие как составной, перечисляемый, диапазон и базовый. Рассмотрим пример создания и отправки запросов нового составного типа:

Создаем новый составной тип "wine" CREATE TYPE wine AS (wine_vineyard varchar(50), wine_type varchar(50), wine_year int); -- создаем таблицу, которая использует составной тип "wine" CREATE TABLE pairings (menu_entree varchar(50), wine_pairing wine); -- вставляем данные в таблицу при помощи выражения ROW INSERT INTO pairings VALUES ("Lobster Tail",ROW("Stag""s Leap","Chardonnay", 2012)), ("Elk Medallions",ROW("Rombauer","Cabernet Sauvignon",2012)); /* выборка из таблицы с использованием имени колонки (используйте скобки, отделяемые точкой от имени поля в составном типе) */ SELECT (wine_pairing).wine_vineyard, (wine_pairing).wine_type FROM pairings WHERE menu_entree = "Elk Medallions";
Поскольку они не являются объектно-реляционными, MySQL, MariaDB и Firebird не предоставляют такую мощную функциональность.

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

PostgreSQL может обрабатывать много данных. Текущие опубликованные ограничения перечислены ниже:

В Compose [прим. пер.: организация, в которой трудится автор оригинальной статьи] мы автоматически масштабируем вашу инсталляцию, чтобы вам не приходилось волноваться о росте количества данных. Но, как известно любому администратору баз данных, стоит с опаской относиться к слишком большим и неограниченным возможностям. Мы советуем руководствоваться здравым смыслом при создании таблиц и добавлении индексов.

Для сравнения, MySQL и MariaDB печально известны ограничением размера строк в 65 535 байт. Firebird также предлагает всего лишь 64Кб в качестве максимального размера строки. Обычно объём данных ограничивается максимальным размером файлов операционной системы. Поскольку PostgreSQL умеет хранить табличные данные в множестве файлов меньшего размера, он может обойти это ограничение. Но стоит отметить, что слишком большое количество файлов может негативно сказаться на производительности. MySQL и MariaDB поддерживают большее количество столбцов в таблице (до 4,096 в зависимости от типа данных) и большие индивидуальные размеры таблицы, чем PostgreSQL, но необходимость превысить существующие ограничения Постгреса возникает лишь в крайне редких случаях.

Целостность данных

Постгрес стремится соответствовать стандарту ANSI-SQL:2008, отвечает требованиям ACID (атомарность, согласованность, изолированность и надежность) и известен своей ссылочной и транзакционной целостностью. Первичные ключи, ограничивающие и каскадные внешние ключи, уникальные ограничения, ограничения NOT NULL, проверочные ограничения и другие функции обеспечения целостности данных дают уверенность, что только корректные данные будут сохранены.

MySQL и MariaDB больше работают на то, чтобы соответствовать стандарту SQL с движками таблиц InnoDB/XtraDB. Теперь они предлагают опцию STRICT с использованием режимов SQL, которая устанавливает проверки корректности используемых данных. Несмотря на это, в зависимости от того, какой режим вы используете, недостоверные и даже урезанные без вашего ведома данные могут быть вставлены или созданы при обновлении. Ни одна из этих баз данных сейчас не поддерживает CHECK ограничения. Кроме того, у них существует множество особенностей в отношении ограничений ссылочной целостности по внешним ключам. В дополнение к вышесказанному, целостность данных может существенно пострадать в зависимости от выбранного движка хранения. MySQL (и fork MariaDB) не делают секрета из того, что променяли целостность и соответствие стандартам на скорость и эффективность.

Подводя итоги

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

Если вам кажется, что PostgreSQL не соответствует вашим потребностям, или вы предпочитаете “стрелять от бедра”, тогда вам стоит обратить внимание на NoSQL базы данных, которые мы предлагаем в Compose, или подумать о других SQL базах данных, которые мы упоминали. У каждой из них есть свои преимущества. Compose твёрдо уверен, что очень важно выбрать правильную базу данных для конкретной задачи… иногда это означает, что нужно выбрать несколько баз данных!

Хотите больше Постгреса?

SQL ,

Разработка веб-сайтов

Перевод

В первой части этой серии мы поговорим о хранении данных - модели, структуре, типах и ограничениях размера. А во больше сфокусируемся на выборке и манипуляциях с данными.

Модель данных

Структуры и типы данных

Давайте рассмотрим подробнее некоторые из них:

Сетевые адреса

192.168.100.128/25
10.1.2.3/32
2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128
::ffff:1.2.3.0/128

Многомерные массивы

Геометрические данные

Поддержка JSON

Создание нового типа

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

PostgreSQL может обрабатывать много данных. Текущие опубликованные ограничения перечислены ниже:

Целостность данных

Подводя итоги

Хотите больше Постгреса?

PostgreSQL — это кроссплатформенная объектно-реляционная СУБД с открытым исходным кодом. Из этой статьи вы узнаете, как установить PostgreSQL в Ubuntu Linux, подключиться к нему и выполнить пару простых SQL-запросов, а также о том, как настроить резервное копирование.

Чтобы установить PostgreSQL 9.2 в Ubuntu 12.10, выполните следующие команды:

sudo apt-add-repository ppa:pitti/ postgresql
sudo apt-get update
sudo apt-get install postgresql-9.2

Попробуем поработать с СУБД через оболочку:

sudo -u postgres psql

Создадим тестовую базу данных и тестового пользователя:

CREATE DATABASE test_database;
CREATE USER test_user WITH password "qwerty" ;
GRANT ALL ON DATABASE test_database TO test_user;

Для выхода из оболочки введите команду \q .

Теперь попробуем поработать с созданной базой данных от имени test_user:

psql -h localhost test_database test_user

Создадим новую таблицу:

CREATE SEQUENCE user_ids;
CREATE TABLE users (
id INTEGER PRIMARY KEY DEFAULT NEXTVAL ("user_ids" ) ,
login CHAR (64 ) ,
password CHAR (64 ) ) ;

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

SELECT NEXTVAL ("user_ids" ) ;

Прописав в качестве значения по умолчанию для поля id таблицы users значение NEXTVAL ("user_ids" ) , мы добились того же эффекта, что дает auto_increment. При добавлении новых записей в таблицу мы можем не указывать id, потому что уникальный id будет сгенерирован автоматически. Несколько таблиц могут использовать одну и ту же последовательность. Таким образом мы сможем гарантировать, что значения некоторых полей у этих таблиц не пересекаются. В этом смысле последовательности более гибки, чем auto_increment.

Точно такую же таблицу можно создать и при помощи всего лишь одной команды:

CREATE TABLE users2 (
id SERIAL PRIMARY KEY ,
login CHAR (64 ) ,
password CHAR (64 ) ) ;

В этом случае последовательность для поля id создается автоматически.

Теперь с помощью команды \d можно ознакомиться со списком всех доступных таблиц, а с помощью \d users — увидеть описание таблицы users. Если вы не получили интересующую вас информацию, попробуйте \d+ вместо \d . Список баз данных можно получить командой \l , а переключиться на конкретную БД — командой \c dbname . Для отображения справки по командам скажите \? .

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

CREATE TABLE "anotherTable" ("someValue" VARCHAR (64 ) ) ;

Еще одна особенность PostgreSQL, с которой могут возникнуть сложности в начале работы с этой СУБД — так называемые «схемы». Схема представляет собой что-то вроде пространства имен для таблиц, как бы каталог с таблицами внутри базы данных.

Создание схемы:

CREATE SCHEMA bookings;

Переключение на схему:

SET search_path TO bookings;

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

В остальном работа с PostgreSQL мало чем отличается от работы с любой другой реляционной СУБД:

INSERT INTO users (login, password)
VALUES ("afiskon" , "123456" ) ;
SELECT * FROM users;

Если сейчас вы попытаетесь подключиться к постгресу с другой машины, то потерпите неудачу:

psql -h 192.168.0.1 test_database test_user

Psql: could not connect to server: Connection refused
Is the server running on host "192.168.0.1" and accepting
TCP/IP connections on port 5432?

Чтобы исправить это, добавьте строку:

listen_addresses = "localhost,192.168.0.1"

… в файл /etc/postgresql/9.2/main/postgresql.conf, а также.

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

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

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

СУБД	Postgres Pro Enterprise	Postgres Pro Standard	PostgreSQL
	Коммерческая СУБД, разработанная Postgres Professional для ответственных приложений и высоких нагрузок.	Российская СУБД с открытым кодом, разработанная компанией Postgres Professional на основе свободно-распространяемой СУБД PostgreSQL	Свободно-распространяемая СУБД, разработанная международным сообществом.

Единый Реестр российского ПО	✓	✓
64-разрядный счетчик транзакций	✓
Инкрементальный бэкап на уровне блоков	✓
Сертификат ФСТЭК СВТ 5, НДВ 4	✓	✓
Автономные транзакции	✓
Секционирование таблиц	✓	✓	✓
Компрессия данных	✓
Мультимастер	✓
Поддержка 1С	✓	✓	✓
Переносимые таблицы	✓
Хинты планировщика	✓

Версии 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 мы публикуем наши сборки в виде пакетов под различные платформы. Это следующие операционные системы и их версии:

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,
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 на текущий момент вошли следующие изменения:

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

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

Лицензия Postgres Pro Standard

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

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

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

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

Общество с ограниченной ответственностью "Постгрес Профессиональный" (Postgres Professional) не несет никакой ответственности за любые повреждения, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием данного программного обеспечения или его документации, даже если общество с ограниченной ответственностью "Постгрес Профессиональный" было извещено о возможности таких повреждений.

Общество с ограниченной ответственностью "Постгрес Профессиональный" (Postgres Professional) специально отказывается предоставлять любые гарантии, включая, но не ограничиваясь только этими гарантиями: неявные гарантии пригодности товара или пригодности для отдельной цели. Данное программное обеспечение предоставляется на основе принципа "как есть" и общество с ограниченной ответственностью "Постгрес Профессиональный" не обязано предоставлять сопровождение, поддержку, обновления, расширения или изменения.