Возможности MySQL и PostgreSQL: взгляд инфраструктурного провайдера27.12.2023 14:01

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

«Базы данных — это просто», говорили они. Кажется, чтобы развернуть любую базу данных, достаточно пяти секунд. Устанавливаем Docker, пишем команду: «docker run postgres». Готово, вы великолепны! На самом деле вокруг этой задачи у любого администратора еще много работы. Сначала нужно было подобрать железо, которое будет подходить СУБД. Настроить операционную систему и регулярные обновления. Организовать мониторинг и график бэкапов. Отладить репликацию и собрать кластер.

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

1. Команда обладает опытом в работе с конкретным решением и не видит смысла вступать в неизведанные воды.
2. Если это не первая БД в проекте, то новую выбирают с учетом развернутой инфраструктуры. В первую очередь учитываются настройки операционных систем и оборудования, с которого получают/отдают данные. Учитывают особенности сбора аналитики и особенностей других БД. Кроме этого, нужно учитывать, как выделяются ресурсы и как контролируется их расход.

Рассмотрим PostgreSQL и MySQL, которые работают самостоятельно или могут быть классными напарниками в реализации более сложных решений.

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

База данных относится к типу объектно-реляционных и часто используется как основная в проектах самых разных масштабов. Коротко работу с данными можно описать так: данные записываются в таблицы, индексируются для быстрого поиска и обрастают сложными зависимостями. Индексы помогают разметить данные в логике оглавления книги, чтобы не тратить время на полный просмотр базы данных. Зависимости нужны для реализации гибкой системы триггеров записи и чтения данных. Например, сотрудник банка с их помощью может выгрузить информацию по всем клиентам ипотекой, оформленной в понедельник.

Причин популярности PostgreSQL, кроме открытого исходного кода, много, но выделим две основные. Впрочем, это диалектический вопрос.

Надежность и репликация

PostgreSQL умеет работать с составными запросами, большими нагрузками и критическими данными, которые записываются на диск. Чтобы не потерять данные из-за сбоя сервера, запись сначала происходит в журнал транзакций (WAL). В аварийном случае из него можно будет восстановиться. WAL также участвует в обоих типах потоковой репликации:

• Асинхронная репликация. PostgreSQL сначала применит изменения на master-ноде, а потом отправит записи из WAL на реплики. Преимущество такого способа — быстрое подтверждение транзакции, поскольку не нужно ждать, пока все реплики применят изменения.
• Синхронная репликация. Изменения сначала записываются в WAL хотя бы одной реплики и только после этого фиксируются на основном сервере. Преимущество — более надежный способ, при котором сложнее потерять данные.

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

Еще есть логическая репликация. Этот вид оперирует записями в таблицах PostgreSQL. Этим она отличается от потоковой репликации, которая оперирует физическим уровнем данных: битами, байтами и адресами блоков на диске. Один сервер публикует изменения, другой подписывается на них. Подписываться на изменения можно выборочно. Например, на основном сервере 50 таблиц: 25 могут копироваться на одну реплику, а 25 — на другую.

Универсальность: модули и расширения

Решение подходит для работы в любой операционной системе: Linux, macOS, Windows. Большинство фреймворков (Ruby on Rails, Yii, Symfony, Django) поддерживают использование PostgreSQL в разработке.

За счет модулей и плагинов PostgreSQL адаптируется под самые разные задачи. Например, модуль pgcrypto предоставляет криптографические функции, а hstore — тип данных для хранения пар key-value, чтобы получить квази-Redis или MongoDB.

С помощью расширений PostgreSQL можно интегрировать в системы, которые ранее не понимали команды СУБД или работали медленно и некорректно. Так появились масштабные расширения для 1С, GIS и IoT.

При работе с PostgreSQL можно создавать пользовательские типы данных. Они нужны, чтобы упростить работу с базой или установить ограничения. Например, есть устройство, которое показывает только целые числа от 1 до 5. Данные с устройства необходимо писать в базу. Можно создать пользовательский тип данных, который состоит только из чисел 1, 2, 3, 4, 5. Тогда ввод других значений будет ошибкой, а значит, не нарушит данные.

Звучит как магия, но PostgreSQL может работать даже с NoSQL и JSON и слабоструктурированными данными. Еще с 2012 г. для этих целей есть тип данных JSON, который хранит в себе только корректный JSON. Перед преобразованием в этот тип происходит проверка на валидность, и любые ошибки будут заметны.

В версиях старше PostgreSQL 15 есть даже такие современные запросы как MERGE. Он позволяет создавать условные SQL-выражения, объединяющие в одном выражении операции INSERT, UPDATE и DELETE. Например, при помощи MERGE можно организовать слияние двух таблиц, вставляя недостающие записи и обновляя существующие.

Поддержка современных SQL-стандартов позволяет обеспечить максимальную совместимость с другими СУБД и переносить код между разными базами данных. PostgreSQL похож на молоток — при должном умении все вокруг становится гвоздями, поэтому другие инструменты кажутся не такими нужными. Тем не менее у БД есть ряд «классических» сценариев, в которых решение раскрывается в полную силу.

Для чего используют PostgreSQL в реальных условиях

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

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

Аналитика данных. Возможности СУБД используются для анализа больших объемов данных и генерации отчетов. Например, можно использовать OLAP-кубы для построения сводных таблиц. Это помогает использовать СУБД в задачах, связанных с обучением ML-моделей и machine learning.

Резюме

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

Особенности MySQL c синхронной и полусинхронной репликацией

MySQL принадлежит Oracle и является одним из самых популярных решений в области БД, хотя по количеству форков тот же PostgreSQL его опережает. Для некоммерческого использования MySQL распространяется бесплатно. На базах данных MySQL работают такие масштабные проекты, как Github, Wikipedia, Google, Booking.com, Yelp. Поэтому разговоры о том, что MySQL устарел — точка зрения евангелистов других СУБД.

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

• table_name — имя таблицы,
• column_name — имя столбца,
• column_type — тип данных столбца.

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

MySQL умеет хранить данные как key-value и позволяет работать с разными типами данных. В их числе:

• числовые,
• символьные,
• текстовые и бинарные,
• дата и время,
• JSON,
• составные типы.

Поле JSON лучше для хранения JSON, чем поле text, потому что:

• Предоставляет автоматическую валидацию документа. То есть если мы попытаемся туда записать что-то невалидное, выпадет ошибка.
• JSON хранится в бинарном формате, что позволяет переходить от одного документа JSON к другому (skip).

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

Как устроена репликация в MySQL

Репликация в MySQL состоит из трех основных шагов:

• Master-сервер записывает изменения данных (события) в двоичный журнал (binary log).
• Slave копирует изменения двоичного журнала в свой, который называется журналом ретрансляции (relay log).
• Slave воспроизводит изменения из журнала ретрансляции, применяя их к своим данным.

Существует два принципиально разных подхода к репликации: покомандная и построчная. В первом случае в журнал мастера протоколируются запросы изменения данных (INSERT, UPDATE, DELETE), а slave в точности воспроизводит команды у себя. При построчной же репликации в журнале окажутся непосредственно изменения строк в таблицах, и эти же фактические изменения применятся затем на слейве.

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

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

Отличия синхронной и полусинхронной репликации MySQL

Сферы использования и преимущества MySQL

Самая подходящая для MySQL сфера применения — это интернет-ресурсы и веб-приложения. В плане работы с большими нагрузками единого мнения, что выбрать — MySQL или PostgreSQL — пожалуй, нет, но среди преимуществ решения часто выделяют следующие моменты:

• Высокая скорость работы. Если ориентироваться на open source- инструменты тестирования, например Sysbench, то с определенными настройками СУБД показывает такую производительность.
• Надежная и простая система безопасности. MySQL использует безопасность, основанную на Access Control Lists для всех соединений, запросов и других операций, которые пользователи могут попытаться выполнить.
• Поддержка нескольких типов таблиц. Например, MyISAM, InnoDB.
• Корректная работа с нагрузками. MySQL используют как надежное и масштабируемое хранилище для OLTP нагрузок (с key-value как частный случай).
• Практичность и гибкость системы. Многолетний опыт использования и большое комьюнити позволяют находить ответы на самые нестандартные вопросы.
• Большой набор инструментов. Разные типы таблиц, индексы, транзакции, хранимые процедуры.
• Поддержка графических интерфейсов. WorkBench, SequelPro, DBVisualizer и Navicat DB.

Все функции проверки и восстановления базы данных MySQL сосредоточены в функциональности утилиты mysqlcheck. Это позволяет быстро и безопасно осуществлять проверку базы данных MySQL-сервера.

Резюме

MySQL — почти универсальное решение для E-commerce благодаря работе СУБД с транзакациями и быстрой скорости обработки запросов. MySQL — такой же стандарт индустрии, как PostgreSQL или MS SQL.

Источник: https://selectel.ru/blog/databases-for-everyone/

Полный текст статьи читайте на CNews