Крутой 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 лет назад, в 1917 году:
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 рекомендуется полистать мануал, почитать статьи и т.д.