СУБД PostgreSQL: почему её стоит выбрать для работы с данными и как установить

Физически база данных — это файлы в определённом формате. Если базу нужно сохранить, например как резервную копию, используют дамп (от англ. dump — сбрасывать). Дамп — это файл с базой, в котором сохранены все данные, то есть просто хранилище, поэтому с таким файлом невозможно работать. Чтобы администрировать базу ― загружать, выгружать или изменять данные, нужна система управления базами данных (сокр. СУБД).

PostgreSQL — это бесплатная СУБД c открытым исходным кодом. С помощью PostgreSQL можно создавать, хранить базы данных и работать с данными с помощью запросов на языке SQL.

В бесплатной вводной части курса «SQL для работы с данными и аналитики» студенты узнают, что такое SQL, и учатся писать первые запросы в тренажёре. В основной части курса они осваивают PostgreSQL и другие инструменты для работы с базами данных и на реальных проектах тренируются переводить бизнес-задачи на язык SQL.

В 2022 году PostgreSQL была на четвёртом месте в общемировом рейтинге популярных СУБД. У системы много преимуществ, перечислим основные:

1. Свободный доступ
Любой специалист может бесплатно скачать, установить СУБД и сразу начать работу с базами данных. Если разместить базу и СУБД в облачном хранилище, придётся платить.

2. Можно установить на любую платформу
PostgreSQL подходит для работы в любой операционной системе: Linux, macOS, Windows. Пользователь получает систему «из коробки» — чтобы установить и использовать программу, не нужны дополнительные инструменты.

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

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

Запрос в PostgreSQL для создания такого типа данных выглядит так:

CREATE TYPE entity_source AS enum(1, 2, 3, 4, 5);

4. Позволяет работать с большими размерами данных
Размер базы данных в PostgreSQL не ограничен и зависит от того, сколько свободной памяти есть в месте хранения: на сервере, локальном компьютере или в облаке.

Максимальный размер таблицы — 32 терабайта. Этого более чем достаточно для хранения данных компаний типа Amazon. Одна строка в базе данных не может превышать 1,6 терабайт, а максимальный размер одной ячейки — 1 гигабайт. В такую ячейку можно добавить даже видео.

PostgreSQL позволяет работать с базами данных и элементами в них таких размеров, которые на практике в большинстве случаев не нужны. Поэтому эти ограничения можно назвать условными.

5. Соответствует требованиям ACID
Аббревиатура ACID расшифровывается так:
● атомарность (от англ. atomicity),
● согласованность (от англ. consistency),
● изолированность (от англ. isolation),
● устойчивость (от англ. durability).

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

Атомарность системы — возможность совершать транзакции. Транзакции — это операции или действия, которые происходят полностью или не происходят вообще.

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

Например, нужно транзакцией перевести деньги из одной таблицы в другую. В одной таблице деньги хранятся в виде числовых значений. В другой таблице есть поля с датами. Даты — это тоже числа. Во время транзакции числовые данные могут попасть в поле с датами. Это не противоречит атомарности, но согласованность будет нарушена: данные о деньгах не могут храниться в пространстве с датами. При согласованности системы такая транзакция выполняться не должна.

Изолированность системы означает, что параллельные действия не влияют друг на друга. Например, денежный перевод между двумя счетами не должен влиять на третий счёт.

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

PostgreSQL соответствует всем четырём требованиям ACID и обеспечивает сохранность данных при выполнении транзакций и других работ.

6. Поддерживает все функции, которые есть в современных базах
Например, в PostgreSQL есть оконные функции, вложенные транзакции и триггеры.

Оконные функции позволяют выбрать определённые записи в таблице и делать вычисления с ними в отдельном столбце. Например, можно добавить в таблицу с данными интернет-магазина столбец с датой первого посещения пользователем сайта. Этот столбец пригодится, если понадобится рассчитать LTV (от англ. customer lifetime value).

Вложенными называют транзакции внутри других. Например, выполнение серии переводов траншами в рамках одного договора. Допустим, были выполнены пять транзакций, а на шестой возникли проблемы. Откатиться должны все предыдущие транзакции, которые были внутри одной большой. Откат транзакции — это отмена всех изменений данных, вызванных этой транзакцией.

В PostgreSQL можно создавать триггеры — функции, которые автоматически запускаются при определённых условиях. Например, можно создать триггер, который запускается при удалении данных о закрытой компании из базы. Созданный триггер автоматически добавит в нужном поле другой таблицы запись: «данные о компании удалены, компания закрыта».

7. Есть свой диалект языка SQL
Примерно 80% операций в СУБД выполняется с помощью запросов на классическом языке SQL. Для некоторых действий в PostgreSQL есть свои запросы. Они значительно короче, поэтому работать с ними проще.

Например, нужно перевести строку '365' и целочисленный тип (int). Запрос на классическом SQL выглядит так:

CAST('365' AS int)

В PostgreSQL запрос можно сократить:

'365'::int

8. Можно настроить синхронное дублирование данных
Работа с PostgreSQL поддерживает логическую репликацию. Репликация — это сохранение копии базы данных. Копия может находиться на другом сервере. При логической репликации любые изменения синхронизируются во всех копиях базы данных вне зависимости от места их хранения. Это значит, что везде будет храниться одна версия базы данных.

9. Можно без потерь перенести данные из другой СУБД
Объём данных крупных компаний может быть размером 10 терабайт. Их перенос займёт время и приостановит работу. Небольшие компании или стартапы смогут «‎переехать» в PostgreSQL из другой СУБД быстро, не потеряв ничего в процессе. Перенести все данные можно с помощью специальных инструментов.

Для установки PostgreSQL нужна программа, которую можно скачать с сайта компании-поставщика. Для скачивания лучше выбрать последнюю версию установщика.

Дальнейший процесс состоит всего из пяти шагов. Рассмотрим на примере установки PostgreSQL для Windows.

1. Запустить установку. После загрузки файла программа-установщик запускается двойным кликом.

2. Выбрать компоненты установки. В появившемся окне нужно обязательно отметить базовые компоненты PostgreSQL Server и Command Line Tools. Дополнительные инструменты pgAdmin 4 и Stack Builder можно не устанавливать, если они не нужны для работы.

3. Выбрать папку, в которой будут храниться данные, и нажать кнопку Next. На этом этапе программа может запросить хост, пароль или порт. В этом случае можно указать настройки по умолчанию:
● хост: localhost,
● порт: 5432.

Если пользователь уже устанавливал PostgreSQL, а потом удалил или обновил до новой версии, система может предложить другой порт, например 5433. Лучше запомнить номер порта, потом он понадобится для работы.

4. Установить пароль. При установке система может запросить пароль. В этом случае обязательно назначить его и запомнить. Пароль будет нужен для соединения с сервером.

5. Завершить установку. Перед завершением установки программа предложит проверить настройки. Нужно дождаться окончания установки и завершить работу программы.

Установка PostgreSQL будет одинаковой для любой операционной системы, кроме Linux. Разработчики СУБД советуют устанавливать программу на Linux через командную строку — репозитарий.

Вместо pgAdmin можно установить другое приложение — DBeaver. DBeaver быстрее pgAdmin, и в нём можно работать не только с базами PostgreSQL, но и MySQL, Oracle, SQL Server и NoSQL. Приложение бесплатное и при этом регулярно обновляется и поддерживается.

Вот пошаговая инструкция для установки версии DBeaver 21.2.0:

1. Скачать программу-установщик с официального сайта.
2. После окончания загрузки запустить программу-установщик, выбрать язык и нажать кнопку OK.
3. Программа предложит выбрать два варианта установки: для всех пользователей или для текущего пользователя. Нужно выбрать вариант all users — «для всех пользователей».
4. Далее появится окно со списком компонентов программы для установки. Нужно выбрать все компоненты и нажать «Далее».
5. Выбрать папку, в которую будет установлена программа.
6. После окончания установки можно создать ярлык на рабочем столе, выбрав опцию Create Desktop Shortcut.
7. Для завершения установки нужно нажать кнопку «Готово».

DBeaver можно запускать через ярлык на рабочем столе или через меню «Пуск».

Для создания новых элементов в PostgreSQL используют команду CREATE, например:

● CREATE INDEX — создать индекс для столбца таблицы;
● CREATE SEQUENCE — создать последовательности в базе данных;
● CREATE TABLE — создать таблицу в базе данных;
● CREATE TRIGGER — создать триггер;
● CREATE FUNCTION — создать функцию SQL в базе данных;
● CREATE LANGUAGE — создать язык в базе данных;
● CREATE OPERATOR — создать оператора SQL в базе данных;
● CREATE TYPE — создать тип данных SQL в базе данных;
● CREATE USER — создать пользователя PostgreSQL.

Основные команды для работы с записями в таблицах баз данных:

● SELECT — выбрать записи в таблице;
● UPDATE — обновить данные в существующих записях таблицы;
● DELETE — удалить записи из таблицы.

Для удаления элементов используют команду DROP, например:

● DROP DATABASE — удалить базу данных;
● DROP INDEX — удалить индекс из таблицы;
● DROP SEQUENCE — удалить генератор последовательности;
● DROP TABLE — удалить таблицу;
● DROP TRIGGER — удалить триггер;
● DROP USER — удалить пользователя PostgreSQL.

Чтобы дать пользователю доступ к базе данных или отдельному объекту в базе, используют команду GRANT. А чтобы лишить доступа — команду REVOKE.