PostgreSQL базируется на языке SQL и поддерживает многие из возможностей стандарта SQL:2011[7][8] и ряд возможностей SQL:2016 в части работы с данными в формате JSON[9].
расширяемость (возможность создавать новые типы данных, типы индексов, языки программирования, модули расширения, подключать любые внешние источники данных).
История
PostgreSQL создана на основе некоммерческой СУБД Postgres, разработанной как open-source проект в Калифорнийском университете в Беркли. К разработке Postgres, начавшейся в 1986 году, имел непосредственное отношение Майкл Стоунбрейкер, руководитель более раннего проекта Ingres, на тот момент уже приобретённого компанией Computer Associates. Название расшифровывалось как «Post Ingres», и при создании Postgres были применены многие ранние наработки.
Стоунбрейкер и его студенты разрабатывали новую СУБД в течение восьми лет с 1986 по 1994 годы. За этот период в синтаксис были введены процедуры, правила, пользовательские типы и другие компоненты. В 1995 году разработка снова разделилась: Стоунбрейкер использовал полученный опыт в создании коммерческой СУБД Illustra, продвигаемой его собственной одноимённой компанией (приобретённой впоследствии компанией Informix), а его студенты разработали новую версию Postgres — Postgres95, в которой язык запросов POSTQUEL — наследие Ingres — был заменен на SQL.
Разработка Postgres95 была выведена за пределы университета и передана команде энтузиастов. Новая СУБД получила имя, под которым она известна и развивается в текущий момент — PostgreSQL. Старое название «Postgres» по-прежнему используется в сообществе наряду с «PostgreSQL», т.к. для многих оно удобнее в произношении[12].
Оптимизация производительности, двухфазный commit, секционирование таблиц, index bitmap scan в планировщике SQL-запросов, разделяемый режим блокировки на уровне строк, роли
8.2
2006-12-05
Старая версия, не поддерживается: 8.2.23
2011-12-05
2011-12-05
Оптимизация производительности, построение индексов без остановки работы, рекомендательные блокировки, режим «тёплого резерва» при восстановлении базы по записям WAL[17]
Оконные функции, права доступа на уровне полей (колонок) таблицы, параллельное восстановление базы, правила сортировки на уровне базы, общие табличные запросы и рекурсивные запросы[19]
9.0
2010-09-20
Старая версия, не поддерживается: 9.0.23
2015-10-08
2015-10-08
Встроенная потоковая двоичная репликация, режим горячего резерва, обновление без остановки сервера, поддержка 64-битных версий Windows[20]
9.1
2011-09-12
Старая версия, не поддерживается: 9.1.24
2016-10-27
2016-10-27
Синхронная репликация, правила сортировки, задаваемые для отдельных колонок, нелоггируемые таблицы, уровень изоляции транзакций «сериализуемая изоляция снимков», возможность записи в общих табличных запросах SQL, интеграция с SELinux, расширения, внешние таблицы[21]
Каскадная потоковая репликация, сканирование только индекса, прямая поддержка JSON, улучшенное управление блокировками, диапазонные типы, утилита pg_receivexlog, индексы space-partitioned GiST
9.3
2013-09-09
Старая версия, не поддерживается: 9.3.25
2018-11-08
2018-11-08
Настраиваемые фоновые рабочие процессы, контрольные суммы страниц для обнаружения повреждённых данные, операторы JSON, LATERAL JOIN, ускорена работа pg_dump, новая утилита pg_isready для мониторинга сервера, доработанные возможности триггеров и представлений базы (view), записываемые внешние таблицы, материализованные представления, улучшения репликации
9.4
2014-12-18
Старая версия, не поддерживается: 9.4.26
2020-02-13
2020-02-13
Тип данных JSONB, Оператор ALTER SYSTEM для изменения значений в конфигурации системы, возможность обновлять материализованные представления без блокировки чтения, динамическая регистрация/запуск/остановка фоновых рабочих процессов, API логического декодирования для подключения к БД, улучшение работы индексов GIN, поддержка страниц типа huge для Linux, перезагрузка кэша базы данных с использованием pg_prewarm, новая ускоренная версия Hstore позиционируемая как предпочтительный способ хранения колоночных данных[23]
9.5
2016-01-07
Старая версия, не поддерживается: 9.5.25
2021-02-11
2021-02-11
UPSERT, политики защиты строк, TABLESAMPLE, CUBE/ROLLUP, GROUPING SETS, и новый тип индекса BRIN[24]
9.6
2016-09-29
Старая версия, не поддерживается: 9.6.24
2021-11-11
2021-11-11
Поддержка параллельных запросов, усовершенствования функционала PostgreSQL для работы со сторонними данными (FDW) — добавлен параметр pushdown для sort/join, множественные синхронные сервера горячего резерва, ускорена операция vacuum на больших таблицах
10
2017-10-05
Старая версия, не поддерживается: 10.20
2022-02-10
2022-11-10
Логическая репликация[25], декларативное секционирование таблиц, улучшенное параллельное выполнение в запросах
11
2018-10-18
Старая версия, не поддерживается: 11.15
2022-02-10
2023-11-09
Улучшены устойчивость и производительность при секционировании, поддержка транзакций в хранимых процедурах, улучшенные возможности для параллельного выполнения в запросах, just-in-time (JIT) компиляция выражений[26][27]
12
2019-10-03
Старая поддерживаемая версия: 12.10
2022-02-10
2024-11-14
Ускорение запросов и более экономное использование дискового пространства; поддержка выражений языка путей SQL/JSON; генерируемые столбцы; улучшения в интернационализации и аутентификации; новый интерфейс для создания подключаемых движков работы с таблицами[28]
13
2020-09-24
Старая поддерживаемая версия: 13.6
2022-02-10
2025-11-13
Дедупликация узлов в индексах B-tree ускорена и требует меньше места, увеличена производительность запросов, которые используют агрегаты или секционированные таблицы, улучшено планирование запросов при использовании расширенной статистики, распараллелено вакуумирование индексов, инкрементальная сортировка[29][30]
14
2021-09-30
Старая поддерживаемая версия: 14.2
2022-02-10
2026-11-12
Разрешены определённые стандартом SQL опции SEARCH и CYCLE в общих табличных запросах, разрешено добавлять DISTINCT к GROUP BY[31][32]
15
2022-10-13
Старая поддерживаемая версия: 15.8
2024-08-08
2027-11-11
Поддержка команды SQL MERGE
Выборочная публикация содержимого таблиц в публикациях логической репликации благодаря возможности указывать списки столбцов и условия фильтрации строк
Дополнительные возможности сжатия, включая поддержку сжатия Zstandard (zstd). Сюда входит поддержка выполнения сжатия на стороне сервера во время pg_basebackup
Поддержка структурированного вывода журналов сервера в формате JSON
Улучшена производительность, особенно при сортировке в памяти и на диске[33]
16
2023-09-14
Старая поддерживаемая версия: 16.4
2024-08-08
2028-11-09
Возможность распараллеливания FULL и внутренних правых OUTER хэш-соединений
Возможность логической репликацию с резервных серверов
Возможность подписчикам логической репликации выполнять большие транзакции параллельно
Возможность мониторинга статистики ввода-вывода с использованием нового представления pg_stat_io
Добавление конструкторов SQL / JSON и функций идентификации
Добавлена поддержка сопоставления регулярных выражений имен пользователей и баз данных в pg_hba.conf и имен пользователей в pg_ident.conf[34]
17
2024-09-26
Текущая версия:17.0
2024-09-26
2029-11-08
Новая система управления памятью для VACUUM, которая снижает потребление памяти и может повысить общую производительность сборки мусора
Новые возможности SQL / JSON, включая конструкторы, функции идентификации и функцию JSON_TABLE(), которая преобразует данные JSON в табличное представление
Различные улучшения производительности запросов, в том числе для последовательного чтения с использованием потокового ввода-вывода, пропускной способности записи при высоком параллелизме и поиска по нескольким значениям в индексе btree
Улучшения логической репликации, включая:
Управление отказоустойчивостью
pg_createsubscriber, утилита, создающая логические копии из физических резервных копий
pg_upgrade теперь сохраняет слоты репликации как у издателей, так и у подписчиков
Новая опция подключения на стороне клиента, sslnegotiation = direct, которая выполняет прямое «рукопожатие» TLS, чтобы избежать повторного согласования
pg_basebackup теперь поддерживает инкрементное резервное копирование
COPY добавляет новую опцию ON_ERROR ignore, которая позволяет продолжить операцию копирования в случае ошибки[35]
Основные возможности
Функции
Функции являются блоками кода, исполняемыми на сервере, а не на клиенте БД. Хотя они могут писаться на чистом SQL, реализация дополнительной логики, например, условных переходов и циклов, выходит за рамки SQL и требует использования некоторых языковых расширений. Функции могут писаться с использованием одного из следующих языков:
Встроенный процедурный язык PL/pgSQL, во многом аналогичный языку PL/SQL, используемому в СУБД Oracle;
PostgreSQL допускает использование функций, возвращающих набор записей, который далее можно использовать так же, как и результат выполнения обычного запроса.
Функции могут выполняться как с правами их создателя, так и с правами текущего пользователя.
Иногда функции отождествляются с хранимыми процедурами, однако между этими понятиями есть различие.
С девятой версии возможно написание автономных блоков, которые позволяют выполнять код на процедурных языках без написания функций, непосредственно в клиенте.
Триггеры
Триггеры определяются как функции, инициируемые DML-операциями. Например, операция INSERT может запускать триггер, проверяющий добавленную запись на соответствия определённым условиям. При написании функций для триггеров могут использоваться различные языки программирования (см. выше).
Триггеры ассоциируются с таблицами. Множественные триггеры выполняются в алфавитном порядке.
Правила и представления
Механизм правил (англ.rules) представляет собой механизм создания пользовательских обработчиков не только DML-операций, но и операции выборки. Основное отличие от механизма триггеров заключается в том, что правила срабатывают на этапе разбора запроса, до выбора оптимального плана выполнения и самого процесса выполнения. Правила позволяют переопределять поведение системы при выполнении SQL-операции к таблице. Хорошим примером является реализация механизма представлений (англ.views): при создании представления создается правило, которое определяет, что вместо выполнения операции выборки к представлению система должна выполнять операцию выборки к базовой таблице/таблицам с учётом условий выборки, лежащих в основе определения представления. Для создания представлений, поддерживающих операции обновления, правила для операций вставки, изменения и удаления строк должны быть определены пользователем.
Индексы
В PostgreSQL имеется поддержка индексов следующих типов: B-дерево, хеш, GiST, GIN, BRIN, Bloom. При необходимости можно создавать новые типы индексов. Индексы в PostgreSQL обладают следующими свойствами:
возможен просмотр индекса не только в прямом, но и в обратном порядке — создание отдельного индекса для работы конструкции ORDER BY ... DESC не нужно;
возможно создание индекса над несколькими столбцами таблицы, в том числе над столбцами различных типов данных;
индексы могут быть функциональными, то есть строиться не на базе набора значений некоего столбца/столбцов, а на базе набора значений функции от набора значений;
индексы могут быть частичными, то есть строиться только по части таблицы (по некоторой её проекции); в некоторых случаях это помогает создавать намного более компактные индексы или достигать улучшения производительности за счёт использования разных типов индексов для разных (например, с точки зрения частоты обновления) частей таблицы;
планировщик запросов может использовать несколько индексов одновременно для выполнения сложных запросов.
PostgreSQL поддерживает одновременную модификацию БД несколькими пользователями с помощью механизма Multiversion Concurrency Control (MVCC). Благодаря этому соблюдаются требования ACID и практически отпадает нужда в блокировках чтения.
Типы данных
PostgreSQL поддерживает большой набор встроенных типов данных:
Численные типы
Целые
С фиксированной точкой
С плавающей точкой
Денежный тип (отличается специальным форматом вывода, а в остальном аналогичен числам с фиксированной точкой с двумя знаками после запятой)
Более того, пользователь может самостоятельно создавать новые требуемые ему типы и программировать для них механизмы индексирования с помощью GiST.
Пользовательские объекты
PostgreSQL может быть расширен пользователем для собственных нужд практически в любом аспекте. Есть возможность добавлять собственные:
Преобразования типов
Типы данных
Домены (пользовательские типы с изначально наложенными ограничениями)
Функции (включая агрегатные)
Индексы
Операторы (включая переопределение уже существующих)
Процедурные языки
Наследование и секционирование
Таблицы могут наследовать характеристики и наборы полей от других таблиц (родительских). При этом данные, добавленные в порождённую таблицу, автоматически будут участвовать (если это не указано отдельно) в запросах к родительской таблице.
В PostgreSQL 10 был добавлен механизм секционирования таблиц. Секционирование предназначено для разделения одной таблицы на несколько так называемых секций (partitions). Секционирование схоже с наследованием, но имеет более дружелюбный к пользователю синтаксис и более строгие ограничения, что позволяет выполнять дополнительные оптимизации при планировании запросов.
Подгружаемые расширения, поддерживающие SHA1, MD5, XML
Расширения для написания сложных выборок, отчётов и т. д. (API открыт)
Средства для генерации совместимого с другими системами SQL-кода и импорта из других систем
Автономные блоки на доступных языках, а не только SQL
Администрирование
Средства администрирования PostgreSQL:
psql
Основным интерфейсом для PostgreSQL является интерактивная консольная утилита[англ.]*psql, которую можно использовать для прямого ввода SQL-запросов или их выполнения из файла. Кроме того, psql предоставляет ряд метакоманд и различных функций оболочки для облегчения написания скриптов и автоматизации широкого спектра задач; например, автодополнение имён объектов и синтаксиса SQL.
pgAdmin
Пакет pgAdmin — это бесплатный и открытый графический пользовательский интерфейс для администрирования PostgreSQL, который поддерживается на многих компьютерных платформах[36]. Программа доступна более чем на дюжине языков. Первый прототип, названный pgManager, был написан для PostgreSQL 6.3.2 в 1998 году и переписан и выпущен как pgAdmin под лицензией GNU General Public License (GPL) в последующие месяцы. Вторая версия (названная pgAdmin II) была полностью переписана и впервые выпущена 16 января 2002 года. Третья версия, pgAdmin III, изначально была выпущена под лицензией Artistic License[англ.], а затем выпущена под той же лицензией, что и PostgreSQL. В отличие от предыдущих версий, написанных на Visual Basic, pgAdmin III написан на C++ с использованием фреймворка wxWidgets[37], что позволяет ему работать на большинстве распространённых операционных систем. Инструмент запросов включает в себя язык сценариев pgScript для поддержки задач администрирования и разработки. В декабре 2014 года Дэйв Пейдж, основатель и главный разработчик проекта pgAdmin[38], объявил, что с переходом на веб-модели началась работа над pgAdmin 4 с целью упрощения облачных развёртываний[39]. В 2016 году был выпущен pgAdmin 4. Он был написан на Python с использованием Flask и фреймворка Qt[40].
phpPgAdmin
phpPgAdmin — веб-инструмент администрирования для PostgreSQL, написанный на PHP и основанный на популярном интерфейсе phpMyAdmin, изначально созданном для администрирования MySQL[41].
PostgreSQL Studio
PostgreSQL Studio позволяет пользователям выполнять основные задачи разработки баз данных PostgreSQL из веб-консоли. PostgreSQL Studio позволяет пользователям работать с облачными базами данных без необходимости открывать межсетевые экраны[42]
Анализатор логов PostgreSQL pgBadger создаёт подробные отчёты из файла журналов PostgreSQL[45]
pgDevOps
pgDevOps — это набор веб-инструментов для установки и управления несколькими версиями PostgreSQL, расширениями и компонентами сообщества, разработки SQL-запросов, мониторинга работающих баз данных и поиска проблем производительности[46]
Adminer
Adminer — простой веб-инструмент администрирования для PostgreSQL и других СУБД, написанный на PHP.
pgBackRest
pgBackRest — инструмент резервного копирования и восстановления для PostgreSQL, который обеспечивает поддержку полного, дифференциального и инкрементного резервного копирования[47]
pgaudit
pgaudit — это расширение PostgreSQL, которое обеспечивает подробное ведение журнала аудита сеанса и/или объекта с помощью стандартного средства ведения журнала, предоставляемого PostgreSQL[48].
WAL-E
WAL-E — это инструмент резервного копирования и восстановления для PostgreSQL, который обеспечивает поддержку физических (на основе упреждающей журнализации) резервных копий, написанный на Python[49].
Ряд компаний предлагают собственные инструменты для PostgreSQL. Они часто состоят из универсального ядра, адаптированного для различных конкретных продуктов баз данных. Эти инструменты в основном используют функции администрирования инструментов с открытым исходным кодом, но предлагают улучшения в моделировании данных[англ.], импорте, экспорте или отчётности.
Качество исходного кода
Согласно результатам автоматизированного исследования различного ПО на предмет ошибок, проведённом в 2005 году, в исходном коде PostgreSQL было найдено 20 проблемных мест на 775 000 строк исходного кода (в среднем, одна ошибка на 39 000 строк кода)[50]. Для сравнения: MySQL — 97 проблем, одна ошибка на 8000 строк кода; FreeBSD (целиком) — 306 проблем, одна ошибка на 2500 строк кода; Linux (только ядро) — 950 проблем, одна ошибка на 800 строк кода.
Производные продукты
Лицензия PostgreSQL позволяет на его основе создавать различные, в том числе коммерческие, форки. Их известно несколько десятков[51].
На базе PostgreSQL компанией EnterpriseDB были разработаны другие варианты этой СУБД, являющиеся платными для коммерческого использования — Postgres Plus (состоит целиком только из продуктов с открытыми исходными кодами; плата требуется только при необходимости приобретения коммерческой поддержки продукта) и Postgres Plus Advanced Server (расширение PostgreSQL специальными возможностями для обеспечения совместимости с Oracle Database)[52]. В комплекте поставки данных продуктов содержится набор ПО для разработчиков и администраторов баз данных:
Fujitsu Enterprise Postgres — продукт компании Fujitsu[54].
Postgres Pro
Postgres Pro компании Postgres Professional[55] — разрабатываемый в России дистрибутив, содержащий усовершенствования, впоследствии, как правило, поступающие в апстрим основного проекта. Данная версия PostgreSQL сертифицирована ФСТЭК[56] и рядом крупных российских организаций. В рамках российского импортозамещения рассматривается как замена Oracle[57][58][59].
↑Dave, Page.The story of pgAdmin (неопр.). Dave's Postgres Blog. pgsnake.blogspot.co.uk (7 декабря 2014). Дата обращения: 7 декабря 2014. Архивировано 22 июня 2015 года.
↑phpPgAdmin Project.About phpPgAdmin (неопр.) (25 апреля 2008). Дата обращения: 25 апреля 2008. Архивировано 3 мая 2008 года.
↑PostgreSQL Studio.About PostgreSQL Studio (неопр.) (9 октября 2013). Дата обращения: 9 октября 2013. Архивировано из оригинала 7 октября 2013 года.
↑oooforum.org.Back Ends for OpenOffice (неопр.) (10 января 2010). Дата обращения: 5 января 2011. Архивировано из оригинала 28 сентября 2011 года.
↑libreoffice.org.Base features (неопр.) (14 октября 2012). Дата обращения: 14 октября 2012. Архивировано из оригинала 7 января 2012 года.
↑Greg Smith, Robert Treat, Christopher Browne.Tuning your PostgreSQL server (неопр.). Wiki. PostgreSQL.org. Дата обращения: 12 ноября 2011. Архивировано 15 ноября 2011 года.
↑pgDevOps (неопр.). BigSQL.org. Дата обращения: 4 мая 2017. Архивировано из оригинала 1 апреля 2017 года.
↑Подход к миграции баз данных с Oracle на EnterpriseDB(рус.). Бюро Соломатина (13 декабря 2010). — Подход к миграции баз данных с Oracle на EnterpriseDB. Дата обращения: 13 декабря 2010. Архивировано из оригинала 24 августа 2011 года. (недоступная ссылка с 07-03-2017 [3100 дней])
АлексеевАлександр. Серия статей о PostgreSQL — примеры использования физической и логической репликации, полнотекстового и пространственного поиска, автоматического восстановления после сбоев, написания расширений.
