Что такое postgresql и для чего он нужен

Чем PostgreSQL лучше других SQL баз данных с открытым исходным кодом. Часть 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Расширение 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, чтобы создать новые типы данных, такие как составной, перечисляемый, диапазон и базовый. Рассмотрим пример создания и отправки запросов нового составного типа:

Поскольку они не являются объектно-реляционными, 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 твёрдо уверен, что очень важно выбрать правильную базу данных для конкретной задачи… иногда это означает, что нужно выбрать несколько баз данных!

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

Источник

Национальная библиотека им. Н. Э. Баумана
Bauman National Library

Персональные инструменты

PostgreSQL

Содержание

История

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

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

Разработка Postgres95 была выведена за пределы университета и передана команде энтузиастов. Новая СУБД получила имя, под которым она известна и развивается в текущий момент — PostgreSQL. [Источник 2]

О продукте

PostgreSQL поддерживается на всех современных Unix системах (34 платформы), включая наиболее распространенные, такие как Linux, FreeBSD, NetBSD, OpenBSD, SunOS, Solaris, DUX, а также под macOS. Начиная с версии 8.X PostgreSQL работает в «native» режиме под MS Windows NT, Win2000, WinXP, Win2003. Известно, что есть успешные попытки работать с PostgreSQL под Novell Netware 6 и OS2.

PostgreSQL неоднократно признавалась базой года, например, Linux New Media AWARD 2004, 2003 Editors’ Choice Awards, 2004 Editors’ Choice Awards. [Источник 3]

Основные возможности и функциональность

Надежность

Надежность PostgreSQL является проверенным и доказанным фактом и обеспечивается следующими возможностями:

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

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

Расширяемость

Расширяемость PostgreSQL означает, что пользователь может настраивать систему путем определения новых функций, агрегатов, типов,языков, индексов и операторов. Объектно-ориентированность PostgreSQL позволяет перенести логику приложения на уровень базы данных, что сильно упрощает разработку клиентов, так как вся бизнес логика находится в базе данных. Функции в PostgreSQL однозначно определяются названием, количеством и типами аргументов. [Источник 4]

Поддержка SQL

Кроме основных возможностей, присущих любой SQL базе данных, PostgreSQL поддерживает:

Типы данных

PostgreSQL поддерживает большой набор встроенных типов данных:

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

Начиная с PostgreSQL 9.2, может использоваться тип данных, представляющий диапазон данных, которые называются типами диапазонов. Это могут быть дискретные диапазоны (например, все целые значения от 1 до 10) или непрерывные диапазоны (например, любой момент времени между 10:00 и 11:00). Доступные типы доступных диапазонов включают диапазоны целых чисел, большие целые числа, десятичные числа, отметки времени (с часовым поясом и без него) и даты.

Пользовательские типы диапазонов могут быть созданы для обеспечения доступности новых типов диапазонов, таких как диапазоны IP-адресов, с использованием типа inet в качестве базы или диапазонов с плавающей точкой, используя тип данных float в качестве базы. Типы диапазонов поддерживают включенные и исключительные границы диапазона, используя символы и () соответственно. (например, представляет все целые числа, начиная с 4 включительно, но не включая 9.) Типы диапазонов также совместимы с существующими операторами, используемыми для проверки наложения, сдерживания, права и т. д. [Источник 4]

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

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

По состоянию на 2010, эта функция поддерживается не полностью, но, в частности, таблица ограничений в настоящее время не наследуемая. Все проверочные ограничения и ненулевые ограничения на родительской таблице автоматически наследуются его детьми. Другие типы ограничений (уникальные, первичный ключ, и иностранные ключевые ограничения) не наследуются.

Наследование обеспечивает способ отображения особенности иерархий обобщения, изображенных на сущность-связь диаграммах (ERD) непосредственно в базе данных PostgreSQL [Источник 4]

Функции запроса

Поддержка стандартов, возможности, особенности

PostgreSQL PostgreSQL поддерживает большинство возможностей стандарта SQL: 2011, ACID-совместимая и транзакционная (включая большинство DDL утверждения) избегает проблемы блокировки с помощью механизма Многоверсионное управление параллельным доступом (MVCC), обеспечивает иммунитет к «грязному» чтению и полую сериализационность; управляет комплексными SQL запросами используя множество индексированных методов, которые недоступны в других базах данных; имеет обновляемые представления и материализованные представления, триггеры, внешние ключи; поддерживает функции и хранимые процедуры, и другие возможности расширения, и имеет множество расширений, написанных третьими лицами. В дополнение к возможности работы с основными фирменными и с открытым исходным кодом базами данных, PostgreSQL поддерживает миграцию из них, путем своей обширной поддержки стандарта SQL и доступных инструментов миграции. Фирменные расширения в базах данных, таких как Oracle можно эмулировать с помощью встроенных и сторонних расширений совместимости с открытым исходным кодом. Последние версии также обеспечивают репликацию самой базы данных для доступности и масштабируемости.

PostgreSQL является кросплотформенной и работает на множестве операционных систем, включая Linux, FreeBSD, macOS, Solaris, и Microsoft Windows. Начиная с Mac OS X 10.7 Lion Server, PostgreSQL это стандартная база данных по умолчанию, и клиентские инструменты PostgreSQL идут в комплекте с настольной версией. Подавляющее большинство дистрибутивов Linux имеет PostgreSQL доступным в поддерживаемых пакетах.

PostgreSQL разработан PostgreSQL Global Development Group, разнообразной группой из многих компаний и отдельных вкладчиков. Это свободное и открытое программное обеспечение, распространяемое по условиям Лицензии PostgreSQL, разрешительной лицензии свободного программного обеспечения.

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

На данный момент (версия 9.4.5), в PostgreSQL имеются следующие ограничения: [Источник 5]

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

Разработка

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

Цикл работой над новой версией обычно длится 10-12 месяцев (сейчас ведется дискуссия о более коротком цикле 2-3 месяца) и состоит из нескольких этапов. [Источник 1]

Другие функции хранения

Ограничения ссылочной целостности, включая ограничения внешнего ключа, ограничения столбцов и проверки строк Двоичное и текстовое хранилище больших объектов Табличные Сравнение столбцов Онлайн-резервная Восстановление по времени, реализованное с использованием записи на основе записи Обновление на месте с помощью pg_upgrade за меньшее время простоя (поддерживает обновления с 8.3.x и выше) [Источник 1]

Установка и настройка

В данном разделе представлена инструкция по установки и настройке PostgreSQL для разных ОС [Источник 6]

Установка

Если установка происходит на macOS, то процесс установки можно запустить командой: [Источник 7]

На Linux СУБД устанавливается так:

После того, как все загружено и установлено, можно проверить, все ли в порядке, и какая стоит версия PostgreSQL. Для этого выполните следующую команду:

Инструкция по установке в цифровом формате

Настройка

Работа с PostgreSQL может быть произведена через командную строку (терминал) с использованием утилиты psql – инструмент командной строки PostgreSQL. [Источник 7]

Необходимо ввести следующую команду:

Этой командой запускается утилита psql. Хотя есть много сторонних инструментов для администрирования PostgreSQL, нет необходимости их устанавливать, т. к. psql удобен и отлично работает.

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

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

Основные операции с БД

Чтобы выполнять базовые действия в СУБД, нужно знать язык запросов к базе данных SQL.

Создание базы данных

Для создания базы данных используется команда: [Источник 7]

В приведенном ниже примере создается база данных с именем proglib_db.

Если забыть точку с запятой в конце запроса, знак «=» в приглашении postgres заменяется на «-». Это зачастую указывает на то, что необходимо завершить (дописать) запрос.

На рисунке 4 видно сообщение об ошибке из-за того, что в нашем случае база уже создана.

Создание нового юзера

Для создания пользователя существует команда:

В приведенном ниже примере создается пользователь с именем author.

При создании пользователя отобразится сообщение CREATE ROLE. Каждый пользователь имеет свои права (доступ к базам, редактирование, создание БД / пользователей и т. д.). Вы могли заметить, что столбец Attributes для пользователя author пуст. Это означает, что пользователь author не имеет прав администратора. Он может только читать данные и не может создать другого пользователя или базу.

Можно установить пароль для существующего пользователя. [Источник 7]

С этой задачей справится команда \password :

Чтобы задать пароль при создании пользователя, можно использовать следующую команду:

Удаление базы или пользователя

Для этой операции используется команда drop : она умеет удалять как пользователя, так и БД. [Источник 7]

Данную команду нужно использовать очень осторожно, иначе удаленные данные будут потеряны, а восстановить их можно только из бэкапа (если он был).

Если вы укажете psql postgres (без имени пользователя), то postgreSQL пустит вас под стандартным суперюзером (postgres). Чтобы войти в базу данных под определенным пользователем, можно использовать следующую команду:

Источник

Обзор СУБД PostgreSQL: в чем преимущества и секрет успеха?

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

История PostgreSQL

СУБД PostgreSQL начиналась как исследовательский проект в Калифорний­ском университете в Беркли. С 1996 года она разрабатывалась сообществом, и до сих пор в разработке PostgreSQL принимают активное участие сообщества и университеты. Перечислим основные исторические вехи:

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

Преимущества PostgreSQL

PostgreSQL обладает многими возможностями, которые привлекают разработ­чиков, администраторов, архитекторов и компании.

Преимущества PostgreSQL с точки зрения бизнеса

Преимущества PostgreSQL с точки зрения пользователя

PostgreSQL весьма привлекательна для разработчиков, администраторов и ар­хитекторов. Развитая функциональность позволяет гибко решать различные задачи. Перечислим некоторые ее преимущества с точки зрения разработчика: О новые версии выходят ежегодно; к настоящему моменту, начиная с вер­сии 6.0, было выпущено 24 основные версии;

Применения PostgreSQL

PostgreSQL применяется в различных ситуациях. Основные области примене­ния PostgreSQL можно отнести к двум категориям:

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

Истории успеха

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

Ответвления

Ответвлением называется независимый программный проект, основанный на каком-то другом проекте. От PostgreSQL существует более 20 ответвлений; расширяемый API этому весьма способствует. На протяжении многих лет раз­личные группы создавали ответвления и затем включали результаты своей работы в PostgreSQL.

Архитектура PostgreSQL

В PostgreSQL используется клиент-серверная архитектура, когда клиент и сер­вер могут находиться в различных узлах. Обмен данными между клиентом и сервером обычно ведется по протоколу TCP/IP или через Linux-сокеты. Сер­вер PostgreSQL может одновременно обрабатывать несколько подключений клиентов. Типичная программа, работающая с PostgreSQL, состоит из следую­щих процессов операционной системы:

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

Почти все компоненты PostgreSQL, включая регистратор, планировщик, анализатор статистики и диспетчер памяти, допускают конфигурирование. Конфигурация PostgreSQL выбирается в соответствии с характером приложе­ния: OLAP или OLTP.

Сообщество PostgreSQL

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

Сообщество PostgreSQL поддерживает службу агрегирования блогов Planet PostgreSQL. Некоторые разработчики и компании с ее помощью делятся своими знаниями и опытом.

Источник