PostgreSQL
свободная система управления базами данных Из Википедии, свободной энциклопедии
PostgreSQL (произносится «Пост-Грес-Кью-Эл»[6]) — свободная объектно-реляционная система управления базами данных (СУБД).
| PostgreSQL | |
|---|---|
 | |
 | |
| Тип | реляционная СУБД и SQL server[вд] |
| Автор | Майкл Стоунбрейкер[1] |
| Разработчик | сообщество PostgreSQL |
| Написана на | Си[2][3] |
| Операционные системы | FreeBSD[4], OpenBSD[4], Linux[4], macOS[4], Solaris[4], Windows[4] и Unix-подобная операционная система |
| Первый выпуск | 1996 |
| Последняя версия |
|
| Репозиторий | git.postgresql.org/gitwe… |
| Лицензия | PostgreSQL License (free and open-source) |
| Сайт | postgresql.org (англ.) |
| Медиафайлы на Викискладе | |
Существует в реализациях для множества UNIX-подобных платформ, включая различные BSD-системы, IRIX, Linux, macOS, Solaris/OpenSolaris, Tru64, QNX, а также для Microsoft Windows.
Поддержка стандартов, возможности, особенности
Суммиров вкратце
Перспектива
PostgreSQL базируется на языке SQL и поддерживает многие из возможностей стандарта SQL:2011[7][8] и ряд возможностей SQL:2016 в части работы с данными в формате JSON[9].
В PostgreSQL есть следующие ограничения[10]:
| Максимальный размер базы данных | Нет ограничений |
| Максимальный размер таблицы | 32 Тбайт |
| Максимальный размер поля | 1 Гбайт |
| Максимум записей в таблице | Ограничено размерами таблицы |
| Максимум полей в записи | 250—1600, в зависимости от типов полей |
| Максимум индексов в таблице | Нет ограничений |
Сильными сторонами PostgreSQL считаются:
- высокопроизводительные и надёжные механизмы транзакций и репликации;
- расширяемая система встроенных языков программирования: в стандартной поставке поддерживаются PL/pgSQL, PL/Perl, PL/Python и PL/Tcl; дополнительно можно использовать PL/Java, PL/PHP, PL/Py, PL/R, PL/Ruby, PL/Scheme, PL/sh и PL/V8, а также имеется поддержка загрузки модулей расширения на языке C[11];
- наследование;
- возможность индексирования геометрических (в частности, географических) объектов и наличие базирующегося на ней расширения PostGIS;
- встроенная поддержка слабоструктурированных данных в формате JSON с возможностью их индексации;
- расширяемость (возможность создавать новые типы данных, типы индексов, языки программирования, модули расширения, подключать любые внешние источники данных).
История
Суммиров вкратце
Перспектива
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].
| Версия | Дата первого релиза | Последняя минорная версия | Дата последнего релиза | Окончание поддержки[13] | Реализованные возможности |
|---|---|---|---|---|---|
| 6.0 | 1997-01-29 | н/д | н/д | н/д | Формально первый выпуск PostgreSQL. Уникальные индексы, утилита pg_dumpall, аутентификация с использованием ident |
| 6.1 | 1997-06-08 | 6.1.1 | 1997-07-22 | н/д | Многоколоночные индексы, последовательности, тип данных money, GEQO (GEnetic Query Optimizer) |
| 6.2 | 1997-10-02 | 6.2.1 | 1997-10-17 | н/д | Интерфейс JDBC, триггеры, интерфейс программирования сервера, ограничения |
| 6.3 | 1998-03-01 | 6.3.2 | 1998-04-07 | 2003-03-01 | Поддержка subselect из SQL-92, PL/pgTCL |
| 6.4 | 1998-10-30 | 6.4.2 | 1998-12-20 | 2003-10-30 | Поддержка VIEW (только в режиме read-only) и правил, PL/pgSQL |
| 6.5 | 1999-06-09 | 6.5.3 | 1999-10-13 | 2004-06-09 | MVCC, временные таблицы, поддержка новых операторов SQL (CASE, INTERSECT и EXCEPT) |
| 7.0 | 2000-05-08 | 7.0.3 | 2000-11-11 | 2004-05-08 | Внешние включи, синтаксис SQL-92 для соединений (JOIN) |
| 7.1 | 2001-04-13 | 7.1.3 | 2001-08-15 | 2006-04-13 | Упреждающая журнализация, OUTER JOIN |
| 7.2 | 2002-02-04 | 7.2.8 | 2005-05-09 | 2007-02-04 | PL/Python, поле OID становится необязательным в таблицах, интернационализация и локализация сообщений |
| 7.3 | 2002-11-27 | 7.3.21 | 2008-01-07 | 2007-11-27 | схемы, табличные функции, подготовленные запросы[14] |
| 7.4 | 2003-11-17 | 7.4.30 | 2010-10-04 | 2010-10-01 | Оптимизация в JOINах и функции data warehouse[15] |
| 8.0 | 2005-01-19 | 8.0.26 | 2010-10-04 | 2010-10-01 | Версия для Microsoft Windows, точки сохранения в транзакциях, табличные пространства, point-in-time recovery[16] |
| 8.1 | 2005-11-08 | 8.1.23 | 2010-12-16 | 2010-11-08 | Оптимизация производительности, двухфазный commit, секционирование таблиц, index bitmap scan в планировщике SQL-запросов, разделяемый режим блокировки на уровне строк, роли |
| 8.2 | 2006-12-05 | 8.2.23 | 2011-12-05 | 2011-12-05 | Оптимизация производительности, построение индексов без остановки работы, рекомендательные блокировки, режим «тёплого резерва» при восстановлении базы по записям WAL[17] |
| 8.3 | 2008-02-04 | 8.3.23 | 2013-02-07 | 2013-02-07 | Механизм Heap-only tuples, полнотекстовый поиск[18], SQL/XML, типы ENUM, типы UUID |
| 8.4 | 2009-07-01 | 8.4.22 | 2014-07-24 | 2014-07-24 | Оконные функции, права доступа на уровне полей (колонок) таблицы, параллельное восстановление базы, правила сортировки на уровне базы, общие табличные запросы и рекурсивные запросы[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] |
| 9.2 | 2012-09-10[22] | 9.2.24 | 2017-11-09 | 2017-11-09 | Каскадная потоковая репликация, сканирование только индекса, прямая поддержка 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 |
|
| 16 | 2023-09-14 | 16.4 | 2024-08-08 | 2028-11-09 |
|
| 17 | 2024-09-26 | 17.0 | 2024-09-26 | 2029-11-08 |
|
Основные возможности
Суммиров вкратце
Перспектива
Функции
Функции являются блоками кода, исполняемыми на сервере, а не на клиенте БД. Хотя они могут писаться на чистом SQL, реализация дополнительной логики, например, условных переходов и циклов, выходит за рамки SQL и требует использования некоторых языковых расширений. Функции могут писаться с использованием одного из следующих языков:
- Встроенный процедурный язык PL/pgSQL, во многом аналогичный языку PL/SQL, используемому в СУБД Oracle;
- Скриптовые языки — PL/Lua, PL/LOLCODE, PL/Perl, PL/PHP, PL/Python, PL/Ruby, PL/sh, PL/Tcl, PL/Scheme, PL/v8 (Javascript);
- Классические языки — C, C++, Java (через модуль PL/Java);
- Статистический язык R (через модуль PL/R).
PostgreSQL допускает использование функций, возвращающих набор записей, который далее можно использовать так же, как и результат выполнения обычного запроса.
Функции могут выполняться как с правами их создателя, так и с правами текущего пользователя.
Иногда функции отождествляются с хранимыми процедурами, однако между этими понятиями есть различие. С девятой версии возможно написание автономных блоков, которые позволяют выполнять код на процедурных языках без написания функций, непосредственно в клиенте.
Триггеры
Триггеры определяются как функции, инициируемые DML-операциями. Например, операция INSERT может запускать триггер, проверяющий добавленную запись на соответствия определённым условиям. При написании функций для триггеров могут использоваться различные языки программирования (см. выше).
Триггеры ассоциируются с таблицами. Множественные триггеры выполняются в алфавитном порядке.
Правила и представления
Механизм правил (англ. rules) представляет собой механизм создания пользовательских обработчиков не только DML-операций, но и операции выборки. Основное отличие от механизма триггеров заключается в том, что правила срабатывают на этапе разбора запроса, до выбора оптимального плана выполнения и самого процесса выполнения. Правила позволяют переопределять поведение системы при выполнении SQL-операции к таблице. Хорошим примером является реализация механизма представлений (англ. views): при создании представления создается правило, которое определяет, что вместо выполнения операции выборки к представлению система должна выполнять операцию выборки к базовой таблице/таблицам с учётом условий выборки, лежащих в основе определения представления. Для создания представлений, поддерживающих операции обновления, правила для операций вставки, изменения и удаления строк должны быть определены пользователем.
Индексы
В PostgreSQL имеется поддержка индексов следующих типов: B-дерево, хеш, GiST, GIN, BRIN, Bloom. При необходимости можно создавать новые типы индексов. Индексы в PostgreSQL обладают следующими свойствами:
- возможен просмотр индекса не только в прямом, но и в обратном порядке — создание отдельного индекса для работы конструкции
ORDER BY ... DESCне нужно; - возможно создание индекса над несколькими столбцами таблицы, в том числе над столбцами различных типов данных;
- индексы могут быть функциональными, то есть строиться не на базе набора значений некоего столбца/столбцов, а на базе набора значений функции от набора значений;
- индексы могут быть частичными, то есть строиться только по части таблицы (по некоторой её проекции); в некоторых случаях это помогает создавать намного более компактные индексы или достигать улучшения производительности за счёт использования разных типов индексов для разных (например, с точки зрения частоты обновления) частей таблицы;
- планировщик запросов может использовать несколько индексов одновременно для выполнения сложных запросов.
Многоверсионность (MVCC)
PostgreSQL поддерживает одновременную модификацию БД несколькими пользователями с помощью механизма Multiversion Concurrency Control (MVCC). Благодаря этому соблюдаются требования ACID и практически отпадает нужда в блокировках чтения.
Типы данных
PostgreSQL поддерживает большой набор встроенных типов данных:
- Численные типы
- Целые
- С фиксированной точкой
- С плавающей точкой
- Денежный тип (отличается специальным форматом вывода, а в остальном аналогичен числам с фиксированной точкой с двумя знаками после запятой)
- Символьные типы произвольной длины
- Двоичные типы (включая BLOB)
- Типы «дата/время» (полностью поддерживающие различные форматы, точность, форматы вывода, включая последние изменения в часовых поясах)
- Булев тип
- Перечисление
- Геометрические примитивы
- Интервалы (RANGE)
- Сетевые типы
- UUID-идентификатор
- XML-данные
- Массивы
- JSON
- Идентификаторы объектов БД
- Псевдотипы
Более того, пользователь может самостоятельно создавать новые требуемые ему типы и программировать для них механизмы индексирования с помощью GiST.
Пользовательские объекты
PostgreSQL может быть расширен пользователем для собственных нужд практически в любом аспекте. Есть возможность добавлять собственные:
- Преобразования типов
- Типы данных
- Домены (пользовательские типы с изначально наложенными ограничениями)
- Функции (включая агрегатные)
- Индексы
- Операторы (включая переопределение уже существующих)
- Процедурные языки
Наследование и секционирование
Таблицы могут наследовать характеристики и наборы полей от других таблиц (родительских). При этом данные, добавленные в порождённую таблицу, автоматически будут участвовать (если это не указано отдельно) в запросах к родительской таблице.
В PostgreSQL 10 был добавлен механизм секционирования таблиц. Секционирование предназначено для разделения одной таблицы на несколько так называемых секций (partitions). Секционирование схоже с наследованием, но имеет более дружелюбный к пользователю синтаксис и более строгие ограничения, что позволяет выполнять дополнительные оптимизации при планировании запросов.
Прочие возможности
- Соблюдение принципов ACID
- Соответствие стандартам ANSI, SQL-92, SQL-99, SQL:2003, SQL:2011
- Поддержка запросов с
OUTER JOIN,UNION,UNION ALL,EXCEPT,INTERSECTи подзапросов - Последовательности
- Контроль целостности
- Репликация
- Общие табличные выражения и рекурсивные запросы
- Аналитические функции
- Поддержка Юникода (UTF-8)
- Поддержка регулярных выражений в стиле Perl
- Встроенная поддержка SSL, SELinux и Kerberos
- Протокол разделяемых блокировок
- Подгружаемые расширения, поддерживающие 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]
- LibreOffice, OpenOffice.org
- LibreOffice и OpenOffice.org Base можно использовать в качестве интерфейса для PostgreSQL[43][44]
- pgBadger
- Анализатор логов 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]. В комплекте поставки данных продуктов содержится набор ПО для разработчиков и администраторов баз данных:
- Postgres Studio — аналог phpPgAdmin;
- Postgres Plus Debugger — отладчик для кода на PL/pgSQL, интегрированный с предыдущим пакетом;
- Migration Studio — инструмент для автоматического преобразования баз данных из MySQL/Oracle в PostgreSQL.
Существуют и другие коммерческие продукты, созданные на базе PostgreSQL и дополняющие её различными функциями:
- 2ndQPostgres — продукт компании 2nd Quadrant[53];
- Fujitsu Enterprise Postgres — продукт компании Fujitsu[54].
Postgres Pro
Postgres Pro компании Postgres Professional[55] — разрабатываемый в России дистрибутив, содержащий усовершенствования, впоследствии, как правило, поступающие в апстрим основного проекта. Данная версия PostgreSQL сертифицирована ФСТЭК[56] и рядом крупных российских организаций. В рамках российского импортозамещения рассматривается как замена Oracle[57][58][59].
Примечания
Ссылки
Wikiwand - on
Seamless Wikipedia browsing. On steroids.