Возможности PostgreSQL для тех, кто перешел с MySQL

Крутой varanio буквально на прошлой неделе прочитал на DevConf забойный доклад для всех кто пересел на Посгрес с MySQL, но до сих пор не использует новую базу данных в полной мере. По мотивам выступления родилась эта публикация.

Мы рады сообщить, что подготовка к PG Day'17 Russia идет полным ходом! Мы опубликовали полное расписание предстоящего мероприятия. Приглашаем всех желающих прийти и похоливарить с Антоном лично :-)

Поскольку доклад на DevConf вызвал в целом положительные отзывы, я решил оформить его в виде статьи для тех, кто по каким-то причинам не смог присутствовать на конференции.

Почему вообще возникла идея такого доклада? Дело в том, что PostgreSQL сейчас явно хайповая технология, и многие переходят на эту СУБД. Иногда — по объективным причинам, иногда — просто потому что это модно.

Но сплошь и рядом складывается такая ситуация, когда какой-нибудь условный программист Вася вчера писал на MySQL, а сегодня вдруг начал писать на Посгресе. Как он будет писать? Да в целом также, как и раньше, используя лишь самый минимальный набор возможностей новой базы. Практика показывает, что проходят годы, прежде чем СУБД начинает использоваться более менее полноценно.

Не холивар

Сразу disclaimer: это не статья "мускуль vs посгрес". Переходить на посгрес или нет — ваше дело. Uber, к примеру, перешел обратно на MySQL по своим каким-то причинам.

Надо отдать должное Oracle, они явно двигают MySQL в правильном направлении. В 5.7 сделали strict mode по умолчанию. В восьмой версии обещают CTE и оконные функции, а также избавление от движка MyISAM в системных таблицах. Т.е. видно, что в базу вкладываются ресурсы, и хотелки юзеров исследуются очень серьёзно.

Однако в PostgreSQL по прежнему полным полно уникальных фич. В итоге я попытался сделать краткий обзор возможностей базы для разработчика.

Встроенные типы данных

В базу встроено множество типов данных, помимо обычных числовых и строковых. А также операторы для их взаимодействия.

Например, есть типы cidr, inet, macaddr для работы с ip адресами.

-- проверяем, входит ли ip адрес '128.0.0.1' в cidr '127.0.0.0/24' 
-- с помощью оператора &&
select '127.0.0.0/24'::cidr && '128.0.0.1';
-- вернет false

Или например, время с таймзоной (timestamptz), интервал времени и т.д.

-- Сколько сейчас времени в Нью-Йорке?
SELECT NOW() AT TIME ZONE 'America/New_York';

-- Сколько часов разницы сейчас между Москвой и Нью-Йорком?
SELECT NOW() AT TIME ZONE 'America/New_York' 
     - NOW() AT TIME ZONE 'Europe/Moscow';
-- результат: -07:00:00  

Когда я готовил этот слайд, я решил из любопытства посмотреть, а какое смещение времени относительно UTC было 100 лет назад, в 2017 году:

select '1917-06-17 00:00:00 UTC' at time zone 'Europe/Moscow';
-- результат: 1917-06-17 02:31:19

Т.е. москвичи жили по времени UTC+02:31:19.

Кроме перечисленных, есть и другие встроенные типы данных: UUID, JSONB, XML, битовые строки и т.д.

Тип array

Отдельно надо рассмотреть тип "array". Массивы давно и хорошо интегрированы в PostgreSQL. Многомерные массивы, слайсы, операторы пересечения, объединения и т.д. Существует множество функций для работы с массивами.

--- Пример проверки пересечения массивов
SELECT ARRAY [1, 2, 8, 10] && ARRAY [1, 2, 3, 4, 5];

--- Входит ли один массив в другой?
SELECT ARRAY [1, 2] <@ ARRAY [1, 2, 3, 4, 5]

Есть очень удобная функция, которая так и называется: array. В качестве аргумента подается некий SELECT-запрос, на выходе — результат запроса в виде массива.

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

INSERT INTO users
  (id, status, added_at)

  SELECT user_id, 5, '2010-03-03 10:56:40'
    FROM unnest(array[123, 1232, 534, 233, 100500]) as u(user_id)

Создаем собственные типы

Собственные типы можно создавать тремя способами. Во-первых, если вы знаете язык Си, то вы можете создать базовый тип, наравне с каким-нибудь int или varchar. Пример из мануала:

CREATE TYPE box (
  INTERNALLENGTH = 16,
  INPUT = my_box_in_function,
  OUTPUT = my_box_out_function
);

Т.е. создаете пару функций, которые умеют делать из cstring ваш тип и наоборот. После чего можно использовать этот тип, например, в объявлении таблицы:

CREATE TABLE myboxes (
  id integer,
  description box
);

Второй способ — это композитный тип. Например, для хранения комплексных чисел:

CREATE TYPE complex AS (
  r       double precision,
  i       double precision
);

И потом использовать это:

CREATE TABLE math (
  result complex
);

INSERT INTO math
  (result)

  VALUES
    ((0.5, -0.6)::complex);

SELECT (result).i FROM math;
-- результат: -0.6

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

CREATE DOMAIN us_postal_code AS TEXT;

us_postal_code — это более семантично, чем некий абстрактный text или varchar.

Создаем собственные операторы

Можно делать свои операторы. Например, сложение комплексных чисел (сам тип complex мы определили выше):

-- описываем функцию сложения, например, на языке SQL
CREATE OR REPLACE FUNCTION sum_complex(x COMPLEX, y COMPLEX) 
RETURNS COMPLEX AS $$
  SELECT x.r + y.r, x.i + y.i;
$$ language sql;

-- создаем оператор "плюс" для комплексных чисел
CREATE OPERATOR +
(
  PROCEDURE = sum_complex,
  LEFTARG = COMPLEX,
  RIGHTARG = COMPLEX
);

Создаем собственные правила для преобразования типов

Давайте сделаем какой-нибудь сферический в вакууме пример. Создадим типы RUR и USD, и правило для преобразования одного типа в другой. Так как я плохо знаю си, то для примера сделаем простой композитный тип:

CREATE TYPE USD AS (
  sum FLOAT
);

CREATE TYPE RUR AS (
  sum FLOAT
);

-- функция преобразования долларов в рубли (по курсу 60, это же сферический пример)
CREATE FUNCTION usd2rur(value USD)
RETURNS RUR AS $$
  SELECT value.sum * 60.0;
$$ LANGUAGE SQL;

-- описываем правило для посгреса, какой тип как "кастить".
CREATE CAST ( USD AS RUR )
  WITH FUNCTION usd2rur(USD) AS ASSIGNMENT;

Собственно, это всё, теперь можно использовать. Сколько там будет 100 баксов в рублях?

select '(100.0)'::usd::rur;

Результат будет таким:

rur
--------
(6000)
(1 row)

Типы в расширениях PostgreSQL

Существуют расширения, где описаны типы данных и все, что для них нужно. Например, расширение ip4r, описывающее типы для IP-адресов и их диапазонов.

Если вы посмотрите исходники https://github.com/RhodiumToad/ip4r/blob/master/ip4r--2.2.sql, то увидите, что расширение — это просто, по сути, набор иструкций CREATE TYPE, CREATE OPERATOR, CREATE CAST и т.д.

Описаны правила индексирования. Например, тип ip4r (диапазон IP-адресов) можно проиндексировать индексом GIST по оператору && (и другим). Таким образом, можно сделать таблицу для поиска городов по IP.

Или, например, есть расширение uri, которое делает тип, в котором вы сможете хранить вашу ссылку так, что из нее потом легко вытянуть схему или хост (в продакшене еще не пробовал, только планирую).

Индексы

Помимо стандартного btree есть и другие: GIN (можно использовать для некоторых операций с массивами, для jsonb, для полнотекстового поиска), GIST, brin и т.д.

Partial indexes

Бывают ситуации, когда у вас 10 миллионов строк в таблице, при чем из них только штук 100, допустим, в статусе "Платеж обрабатывается". И вы постоянно дергаете этот статус "обрабатывается" как-то так: select ... where status = 2.

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

В посгресе можно сделать индекс не по всей таблице, а по строкам, определенным по заданному условию:

CREATE INDEX my_money_status_idx on my_money(status) WHERE status = 2;

Этот индекс будет хорошо работать на запросах select * from my_money where status = 2 и при этом занимать мало места.

Индексы по выражению

В посгресе можно делать индексы не по одной колонке, а по любому выражению. Например, можно проиндексировать сразу имя с фамилией:

CREATE INDEX people_names
ON people ((first_name || ' ' || last_name));

И потом такой запрос будет быстро работать:

SELECT *
FROM people
WHERE
(first_name || ' ' || last_name) = 'John Smith';

Constraints

Помимо стандартных UNIQUE и NOT NULL, в базе можно делать еще и другие проверки целостности. В доменном типе можно прописать check:

CREATE DOMAIN us_postal_code AS TEXT
CHECK(
  VALUE ~ '^d{5}$'
  OR VALUE ~ '^d{5}-d{4}$'
);

который проверяет, что в колонку типа us_postal_code попадут только 5 цифр или 5 цифр, дефис и 4 цифры. Разумеется, сюда можно писать не только регулярки, но и любые другие условия.

Также check можно прописать в таблице:

CREATE TABLE users (
  id integer,
  name text,
  email text,
  CHECK (length(name) >= 1 AND length(name) <= 300)
);

Т.е. в имени должен быть хотя бы один символ, и не больше 300.

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

INSERT INTO math (result) VALUES ((0.5, 'привет')::complex);
ERROR: invalid input syntax for type double precision: "привет"

Таким образом, иногда композитный тип может быть предпочтительнее, чем jsonb, потому что в json вы можете напихать что угодно вообще.

Частичная уникальность и уникальность по выражению

В отличие от простой уникальности UNIQUE или PRIMARY KEY, в посгресе можно сделать уникальность среди определенного набора строк, заданного условием. Например, email должен быть уникальным среди неудаленных юзеров:

CREATE UNIQUE INDEX users_unique_idx
  ON users(email)
  WHERE deleted = false;

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

CREATE TABLE test_summ (
  a INT,
  b INT
);

CREATE UNIQUE INDEX test_summ_unique_idx
  ON test_summ ((a + b));

INSERT INTO test_summ VALUES (1, 2);

INSERT INTO test_summ VALUES (3, 0);
-- выдаст ошибку уникальности

Constraint Exclude

Ключевое слово EXCLUDE позволяет делать так, что при вставке/обновлении строки, эта строка будет сравниваться с другими по заданному оператору. Например, таблица, содержащая непересекающиеся диапазоны IP (проверяется оператором пересечения &&):

CREATE TABLE ip_ranges (
  ip_range ip4r,
  EXCLUDE USING gist (ip_range WITH &&)
);

Вообще, обычный UNIQUE — это, по сути, EXCLUDE с оператором =.

Хранимые процедуры

Хранимые процедуры можно писать на SQL, pl/pgsql, javascript, (pl/v8), python и т.д. Например, можно на языке R обсчитать какую-то статистику и вернуть из нее график с результатом.
Это отдельная большая тема, советую поискать доклад Ивана Панченко на этот счет.

CTE (Common Table Expressions)

Это будет и в MySQL 8, но всё равно давайте кратко остановимся на этом.

CTE — это просто. Вы берете какой-то кусок запроса и выносите его отдельно под каким-то именем.

WITH  subquery1 AS (
  SELECT ... -- тут куча всяких условий и тд.
),
subquery2 AS (
  SELECT ... -- тут тоже куча условий, группировок
)
SELECT *  -- начался основной запрос
  FROM subquery1
    JOIN subquery 2
  ON ...

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

Например, если у вас 20 джойнов с подзапросами и группировками, планировщик запросов может не понять ваших намерений и план запроса будет неоптимальным. Тогда можно вынести часть запроса в cte-подзапрос, а остальное уже дофильтровать в основном запросе.

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

В CTE можно использовать не только SELECT-запросы, но и UPDATE.

Пример: обновить юзеров с возрастом > 20 лет, и в том же запросе выдать имена обновленных вместе с какой-нибудь там страной.

with users_updated AS (
  UPDATE users
    SET status = 4
    WHERE age > 20
    RETURNING id
)

SELECT name, country
  FROM users
  JOIN countries
    ON users.country_id = countries.id
  WHERE id IN (
  SELECT id
    FROM users_updated
);

Но тут надо понимать, что иногда с помощью CTE можно хорошо выстрелить себе в ногу.
Такой запрос синтаксически верен, но по смыслу полный бред:

WITH
update1 AS (
  UPDATE test
  SET money = money + 1
),

update2 AS (
  UPDATE test
  SET money = money - 1
)

SELECT money FROM test;

Кажется, что мы прибавили рубль, потом отняли рубль, и должно остаться всё как есть.

Но дело в том, что update1 и update2 при своем выполнении будут брать начальную версию таблицы, т.е. по сути получится так, что один update затрет изменения другого. Поэтому с update внутри CTE надо точно знать, что ты делаешь и зачем.

Оконные функции

Про оконные функции я уже когда-то подробно писал здесь: https://habrahabr.ru/post/268983/. Оконные функции тоже обещают в MySQL 8.

Разное

FILTER

К агрегатным функциям (например, COUNT или SUM), можно дописывать условие FILTER, т.е. агрегировать не все строки, а только ограниченные неким выражением:

SELECT
  count(*) FILTER (WHERE age > 20) AS old,
  count(*) FILTER (WHERE age <= 20) AS young

FROM users;

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

watch

Все знают, что в psql есть команды для просмотра разных объектов, например d, dt+ и т.д.
Есть особая команда, называется watch. Т.е. вы выполняете запрос, потом пишете
watch 5 и ваш запрос будет выполняться каждые 5 секунд, пока не отмените.
Это работает не только с select, но и с любым другим, например с update (например, когда нужно большую таблицу медленно обновить по чуть-чуть).

Materialized View

Это как View, только закешированное (материализованное). Кеш можно обновлять с помощью команды REFRESH MATERIALIZED VIEW. Есть также ключевое слово CONCURRENTLY, чтобы Postgres не лочил при обновлении SELECT-запросы.

Listen / Notify

Я пока что не пробовал это в продакшене, поэтому не знаю, применимо ли это на практике (если кто использовал, поделитесь плиз опытом в комментариях). Суть в том, что можно подписаться на какое то событие, а также можно уведомить подписчиков, что событие произошло, передав при этом строку с доп. сведениями.

FDW

Механизм Foreign Data Wrappers позволяет использовать некоторые внешние данные, как простые таблицы. Т.е. к примеру, можно заджойнить постгресовую таблицу, мускульную таблицу, и csv файл.

Sequences

SEQUENCE — это посгресовый аналог MySQL-ного AUTO_INCREMENT. В отличие от MySQL, sequence может существовать отдельно от таблиц или наоборот, "тикать" сразу для нескольких таблиц. Можно задавать различные параметры, например, размер инкремента, зацикливание и проч.

Вместо выводов

Это верхушка айсберга, на самом деле. Есть еще куча нюансов, вообще никак не затронутых в статье, потому что на всё никакой статьи не хватит. По одним только хранимым процедурам можно написать книгу. Или посмотрите, к примеру, полный список sql-команд текущей версии: https://www.postgresql.org/docs/9.6/static/sql-commands.html

Главное, что я хотел показать в статье: несмотря на хайповость, PostgreSQL — очень старая СУБД, в которой очень много чего есть, и которая очень хорошо расширяется. Поэтому при переходе на нее с MySQL рекомендуется полистать мануал, почитать статьи и т.д.

Автор: rdruzyagin

Источник