Работаем в штатном режиме

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

Шумиха вокруг PostgreSQL 20 Апреля 2022

В современном мире для полноценного использования баз данных (БД) необходимы системы управления базами данных (СУБД). На сегодняшний день существует очень много таких систем, но наибольшими популярностью и востребованностью пользуется реляционные базы данных с открытым исходным кодом. В данной статье мы предлагаем рассмотреть с разных ракурсов наиболее востребованную, на наш взгляд, СУБД 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 от других систем. Более полный список представлен в таблице:

Особенность MySQL PostgreSQL MSSQL Oracle Database
Open Source GNU GPL с открытым исходным кодом Открытый исходный код Коммерческая система управления реляционными базами данных. Требуется приобретение лицензии Коммерческая система управления реляционными базами данных. Требуется приобретение лицензии
Соответствие требованиям ACID Частичное соответствие Полное соответствие Полное соответствие Полное соответствие
Поддержка NoSQL/JSON Поддержка некоторых функций JSON,NoSQL JSON,NoSQL JSON,NoSQL
Поддержка MERGE Да Нет Да Да
Логическая репликация Поддерживается Поддерживается Поддерживается Поддерживается
Оконные функции Поддерживаются Поддерживаются Поддерживаются Поддерживаются
Вложенные селекты Да Да Да Да
Транзакции Да Да Да Да
Триггеры Да Да Да Да

Важно отметить, что к отличиям 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

Основы синтаксиса 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