Шумиха вокруг PostgreSQL

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

Что такое PostgreSQL?

PostgreSQL – это объектно-реляционная СУБД, имеющая открытый исходный код, приспособленная для масштабирования, отвечающая стандартам ANSI/ISO. PostgreSQL конкурирует с такими высококлассными базами данных как Oracle и MySQL. В этой СУБД все таблицы имеют вид объектов с возможностью наследования, а выполнение операций с ними проходит как объектно-ориентированные функции. Хранимые в PostgreSQL файлы могут иметь различную структуры и содержимое записей.

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

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

Особенности PostgreSQL

Как и любая другая СУБД, PostgreSQL имеет свои особенности - преимущества и недостатки. К преимуществам PostgreSQL относятся:

• открытость исходного кода. Это означает, что программирование этой системы становится переходом между объектно-ориентированным и реляционным/процедурным программированием и позволяет создавать из данных сложные структуры с объектной идентификацией и наследованием таблиц. PostgreSQL поддерживает языки Delphi, C/C++, Java и JavaScript, Erlang, Lisp, Python, R, .Net;
• многоверсионность (Multiversion Concurrency Control, MVCC). Она применяется для снятия блокировок данных при транзакциях в конкурентных (многопользовательских) условиях. MVCC изолирует транзакции для каждой версии БД и защищает их от несогласованного изменения данных, то есть блокировка на чтение не блокирует запись, и наоборот;
• высокий уровень производительности, обеспеченный использованием индексов (стандартных, частичных и функциональных), интеллектуальным планированием запросов, системами управления буферами памяти и кэширования.База данных PostgreSQL отлично подходит для любых рабочих нагрузок: OLTP, OLAP, хранилища данных и многое другое. Она также полностью соответствует стандартам SQL, что позволяет писать эффективные запросы и программы PL/PGSQL. А переиндексация и реорганизация таблиц может быть выполнена в режиме онлайн;
• В PostgreSQL предусмотрены расширенные настройки для создания собственных плагинов и персонализации параметров, а также возможность индексирования и поддержки географических, растровых, векторных объектов с помощью расширения PostGIS. Эта СУБД совместима с ОС Windows, MacOS, Solaris, BSD и Linux в различных вариантах.

Однако, несмотря на такую продвинутость и гибкость PostgreSQL, у нее есть один недостаток - при выполнении более простых операций она может работать медленнее, чем ее «конкуренты» (MySQL, MSSQL и Oracle Database).

Отличие PostgreSQL от других СУБД

Как писали ранее,PostgreSQL поддерживает концепциюACID (Atomicity - атомарности, Consistency - согласованности, Isolation - изолированности, Durability - долговечности), что отличает ее от других СУБД. Но не только в этом заключается различия PostgreSQL от других систем. Более полный список представлен в таблице:

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

Среди СУБД с открытым кодом основным конкурентом PostgreSQL является СУБД MySQL. MySQL известна как одна из самых популярных баз данных в мире, в то время как PostgreSQL известна как передовая база данных RDBMS и его популярность растет по экспоненте с каждым днем.

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

Чтобы не ошибиться с выбором правильной системы, важно понимать задачи, которые стоят перед ней стоят.

Возможности PostgreSQL

• Функции в PostgreSQL являются блоками кодов, исполняемыми не на клиентском устройстве БД, а на самом сервере. Код может писаться и на чистом SQL, и с использованием иных языков программирования для создания дополнительных логик (например, условных переходов и циклов). В PostgreSQL допустимо использование функций для возврата набора записей, используемого в дальнейшем как результат выполнения обычных запросов. На функции распространяются права как их авторов, так и текущих пользователей.
• Типы данных. PostgreSQL поддерживает как традиционные типы данных SQL (например, числовые, строки, даты, десятичные и т.д.), так и неструктурированные типы данных (JSON, XML и HSTORE), а также сетевые типы данных, битовые строки и т.д. Отличительной особенностью PostgreSQL является поддержка таких данных как ARRAYs, NETWORK, Geometric data types (включая расширенные функции пространственных данных) для хранения и обработки пространственных данных.
• PostgreSQL имеет более продвинутую поддержку данных JSON (с версии 9.2), в частности, задействует широкий перечень специализированных операторов и функций. Например, функция JSONB из версии PostgreSQL-9.4, позволяет хранить данные JSON в двоичном формате и дает возможность осуществлять полнотекстовую индексацию, что существенно ускоряет поиск в документах JSON.
• Репликация PostgreSQL по сравнению с MySQL более надежна и поэтому более востребована. В отличие от MySQL, репликация PostgreSQL базируется на файлах WAL, что делает управление ею простым и быстрым. Postgres поддерживает первичную репликацию (в том числе первичную множественную и каскадную репликацию). Репликация в Postgres называется потоковой (физической) репликацией, и может проходить синхронно или асинхронно.
  По умолчанию репликация проходит асинхронно, и реплики могут обслуживать запросы на чтение. Если приложение (например, веб-приложение) требует, чтобы моментальный снимок данных на репликах был таким же, как основной, то в таких случаях пригождается синхронная репликация. Риск состоит в том, что первичная репликация будет зависать, если транзакции не будут зафиксированы в реплике.
• Репликация на уровне таблиц может производиться с применением внешних инструментов с открытым исходным кодом, таких как Slony, Bucardo, Londiste, RubyRep. Все они осуществляют репликацию на основе триггеров. PostgreSQL также поддерживает логическую репликацию, которая выполняет репликацию на уровне таблиц с использованием записей WAL и устраняет сложность, вызванную репликацией на основе триггеров. Первоначально логическая репликация поддерживалась расширением pglogical и была частью ядра PostgreSQL в версии 10.
  PostgreSQL является полностью SQL-совместимой базой данных и поддерживает все стандартные функции SQL. Приложения практически любого происхождения из любого домена могут использовать PostgreSQL в качестве своей базы данных, что делает его востребованным для сред OLTP, OLAP и DWH. PostgreSQL-лучший выбор для разработчиков, которым приходится писать сложные SQLS.
• Триггеры – это функции, инициируемые DML-операциями, то есть операциями, которые тестируют добавленные записи на соответствие тем или иным условиям. Функции для триггеров пишутся на разных языках программирования. Для множественных триггеров выполнение идет в алфавитном порядке.Триггеры postgresql более продвинутые. Поддерживаемые триггерные события AFTER, BEFORE и INSTEAD OF применяются для операций INSERT, UPDATE И DELETE. Если нужно выполнить сложный SQL при вызове триггера, это возможно сделать с помощью функций.

CREATE TRIGGER audit
AFTER INSERT OR UPDATE OR DELETE ON employee
FOR EACH ROW EXECUTE FUNCTION employee_audit_func();

• Механизмы правил – это механизмы создания операций выборки и DML-операций для обработки пользовательских запросов
• Под наследованием понимается возможность для таблиц наследовать родительские характеристики и наборы полей. При этом на данные, внесенные в новую таблицу, распространяется по умолчанию участие в запросах на родительские таблицы.
• Хранимые процедуры – являются важной частью процесса разработки баз данных. В PostgreSQL реализованы в виде функций с оговоркой RETURN VOID. которая поддерживает различные языки, например, Ruby, Perl (PlPerl), Python (PlPython), TCL, Pl/PgSQL, SQL и JavaScript.
• В PostgreSQL существуют индексы нескольких типов: B-дерево, хэш, R-дерево, GiST, GIN. Можно также создавать новые типы индексов самостоятельно.
• PostgreSQL не устанавливает лимитов на максимальные размеры баз данных, на число записей и индексов в таблицах; максимальные размеры: для таблицы - 32 Тбайт, для записи - 1,6 Тбайт, для поля - 1 Гбайт, в записи может быть 250-1600 полей (зависит от типа полей).
• MySQL и PostgreSQL поддерживают разделение таблиц для повышения производительности запросов, которые попадают в таблицы большого размера. Однако существуют определенные ограничения в обеих базах данных. MySQL поддерживает декларативное разбиение таблиц; поддерживаемые типы разделов - RANGE, LIST, HASH, KEY и COLUMNS (RANGE и LIST). Также поддерживается разбиение на разделы. Однако эта функция может быть не очень гибкой для баз данных из-за определенных ограничений. Начиная с версии MySQL 8.0, разбиение таблиц возможно только с системами хранения InnoDB и NDB, а не с любыми другими системами хранения, например, MyISAM.
  Если столбец ключа раздела не является частью всех ограничений первичного и уникального ключей таблицы, то разделить таблицу вообще невозможно. Единственный другой вариант-разбить таблицу без PRIMARY и UNIQUE KEY, что является редким явлением в мире СУБД.
  PostgreSQL дает возможность разбиения таблиц по двум способам: разбиение таблиц по наследованию и декларативное разбиение. Декларативное разбиение было внедрено в версии 10 и похоже на MySQL, тогда как разбиение по наследованию выполняется с использованием триггеров или правил. Преимущества производительности значительно выше, когда точная стратегия разбиения реализована в таблицах с большими объемами данных. Поддерживаются следующие типы разделов: RANGE, LIST и HASH.. С введением декларативного разбиения сложность и проблемы производительности из-за триггеров и правил устраняются.
• Масштабирование (увеличение размеров сегментов) таблиц может привести к проблемам с производительностью, а запросы, попадающие в эти таблицы, требуют больше ресурсов и времени для завершения. Таким образом, эффективный дизайн таблиц важен для хорошей производительности. Так MySQL и PostgreSQL имеют разные варианты для этого.
  MySQL поддерживает индексацию B-дерева и секционирование для повышения производительности запросов в больших таблицах. Однако отсутствие поддержки растровых, частичных и функциональных индексов в MySQL ограничивает возможности настройки базы данных.
  PostgreSQL имеет несколько вариантов индексации и два типа секционирования для улучшения операций с данными в масштабируемой таблице. Индексирование выражений, частичное индексирование, растровое индексирование и полнотекстовое индексирование могут помочь улучшить производительность запросов в таблицах большего размера, что, в свою очередь, может значительно повысить производительность запросов.
  В PostgreSQL разделы таблиц и индексы могут быть размещены в отдельных табличных пространствах в разных файловых системах дисков, что также может значительно улучшить масштабируемость таблиц.
• Поддерживаемые модели данных. Если MySQL имеет ограниченные возможности NoSQL, то PostgreSQL за последние три года стал очень популярным NoSQL среди разработчиков, так как NoSQL в базе данных RDBMS помогают справиться с неструктурированными данными, такими как JSON, XML и другими типами данных TEXT.
• Безопасность баз данных. PostgreSQL предоставляет доступ к объектам и данным базы данных через ROLES и PRIVILEGES с помощью команд GRANT. Аутентификация соединений осуществляется через файл аутентификации pg_hba.conf, который содержит список IP-адресов вместе с именем пользователя и типом доступа. Ниже приведен пример записи из файла pg_hba.conf:

host database user address auth-method [md5 or trust or reject]

Немного о важном - синтаксис PostgreSQL

PostgreSQL поддерживает большую часть стандарта SQL и чаще всего используется при необходимости

• поддержки триггеров, представлений и внешних ключей в случаях, когда требуется приложения со сложными БД, но без сложных команд SQL;
• обязательной и необходимой потребности создавать вложенные подзапросы (селекты);
• создания сложных команд SQL, полностью отвечающих условиям стандартизации ANSI;
• снижения риска утраты или повреждения данных;
• предоставления одновременного доступа к БД с возможностью работы с данными для большого числа пользователей;
• переноса БД на другое решение или расширения БД с осуществлением процедур повышенной сложности;
• поддержки нереляционных БД (NoSQL) и других типов данных.

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

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

Основы синтаксиса PostgreSQL

Почему Postgres подойдет для миграции из других СУБД

В последние годы все больше компаний принимают решение о переходе с зарубежных СУБД (в частности, с СУБД Oracle Database) на PostgreSQL и на производные этого решения от российской компании Postgres Professional - СУБД Postgres Pro Standard, Postgres Pro Certified, Postgres Pro Enterprise, Postgres Pro Enterprise Certified, PostgreSQL для Windows.

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

Многофункциональность Postgres и ее способность к обработке сложных запросов и поддержке массивных баз данных делают ее оптимальным решением по миграции больших БД с других СУБД без риска потерь информации и проблем с остановкой использующих их сервисов.

Например, при переходе с Oracle на PostgreSQL миграцию можно выполнить одним из способов, выбор которых зависит от критичности качества перехода и сложности таблиц:

• в первом случае таблицы переносятся полностью, а затем выполняется переключение. Данный вариант применяется в случаях, когда часть запросов может быть утеряна без ущерба БД;
• второй вариант предполагает перенос данных на уровне двух дата-центров. Критичные данные переносятся в переведенный на PostgreSQL первый ЦОД, а когда он включается, отключают второй ЦОД, работающий на Oracle. Механизмы синхронизации и переключения данных обеспечивают параллельность работы Oracle и PostgreSQL без простоев.l