Крутой 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 рекомендуется полистать мануал, почитать статьи и т.д.
Комментарии (137)
unabl4
23.06.2017 10:37+5Очень важная, на мой взгляд, вещь, которая есть в постгресе и нет в мускуле — транзакционный DDL. Экономит просто нереальное кол-во нервных клеток. Constraints тоже классная и нужная вещь, которую в мускуле имплементировали только на уровне интерфейса (сюрприз новичкам) — вызывает лишь недоумение. Ну и конечно умение генерировать series. Ну и в целом постгрес рулит — уж очень он функциональный и продуманный.
symbix
23.06.2017 11:07+1В MySQL 8 уже перешли на транзакционный DDL.
С трудом представляю себе, как они это сделали: в исходниках MySQL системные словари прибиты к MyISAM гвоздями с незапамятных времен. Явно пришлось переработать огромные залежи очень старого кода.
Varim
23.06.2017 11:37+1Constraints которую в мускуле имплементировали только на уровне интерфейса (сюрприз новичкам) — вызывает лишь недоумение.
скажите, кратко, что за проблемы с Constraints в MySQL? что значит на уровне интерфейса?
kshvakov
23.06.2017 11:42Я так понимаю это неработающие CHECK
Varim
23.06.2017 11:50+1The CHECK clause is parsed but ignored by all storage engines
да это кончено жестьsymbix
23.06.2017 12:02Имеет место такая жесть, да.
Но это относится только к check, foreign keys итд работают.
Melkij
23.06.2017 14:27foreign keys итд работают.
Да и те как-то хитро сделаны
MySQL parses but ignores “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification.
create table testfk ( parent_id int not null references testbase(id) ) engine=innodb;
Вот так сделать синтаксически верно, а FK просто тихо создано не будетsymbix
23.06.2017 14:33+1Ага, надо в constraint писать. Там какая-то проблема "курицы и яйца" у них в этом месте.
ErshoFF
23.06.2017 10:38-1В каком контексте в статье употребляется слово «хайповость», «хайповая технология»?
Что оно отражает?
varanio
23.06.2017 10:46+1Ну, это что-то модное. То, о чем все говорят на конференциях. То, что все используют в новых проектах и т.д. Нелегаси
ErshoFF
23.06.2017 12:08-2Спасибо за ответ.
Гугление выдало в том числе значение — пирамида (с англ. HYIP — High Yield Investment Program) имеющая не самый положительный смысл.
Также трудно PG назвать новым проектом.
На конференции по PG конечно будут говорить о PG. Но это одна конференция.
Хотелось бы комментарий автора статьи услышать.
japan007
23.06.2017 11:38-10все идет к тому, что мускул и постгрес станут похожими на mssql аж 2005 (!) года выпуска
TheDeadOne
23.06.2017 11:58+6У них, кроме всего прочего, есть одно огромное преимущество перед MS SQL Server'ом — не надо платить 800 килорублей за лицензию.
japan007
23.06.2017 12:04-2первое и главное, на мой взгляд, преимущество — низкий порог вхождения. а килорубли проблема бизнеса, не моя
TheDeadOne
23.06.2017 12:20+2Правильно ли я понял, что ваше нежелание/неумение разбираться должно стоить бизнесу 800k? Кроме того, PL/pgSQL дался мне немного проще, чем T-SQL, как и нюансы администрирования СУБД. Или вы вхождением называете умение запускать Management Studio и жать в ней кнопочки в правильном порядке?
Alexeyco
23.06.2017 20:14+1Ну я какое-то время прогал в т.ч. на Oracle. А рядышком сидели ребята с проектом на MS SQL. Периодически им приходилось менять стулья — прогорали до пола. С другой стороны, иногда читал какие-нибудь вдохновенные статьи про «в MySQL запланировано то-то и то-то» и удивлялся — в Oracle я это использовал испокон века, гайдам по такому функционалу 10-летней давности вагон. Так что, думается мне, если уж бабло на что и тратить — так это на Oracle.
А бизнес сейчас деньги ой, как считает. Поэтому в данный момент мы используем MySQL. Я не могу сказать, что истекаю кровью, т.к. со сменой СУБД изменился и стек, и работодатель, и подход (теперь логика отсутствует в БД). И как-то иногда читаю про какие-то фичи PgSQL и для себя отмечаю, что потребность в подобном ну ой, какая редкая. Из всех возможностей для себя я вижу, пожалуй, что только простую реализацию Materialized Path средствами БД. С деревьями приходится сталкиваться частенько, поэтому мне бы такое в MySQL очень пригодилось.
Envek
24.06.2017 00:28+1Oracle после PostgreSQL — боль. Транзакционного DDLя нет, sqlplus — жалкое подобие консольного клиента, вообще не чета psql'ю (а есть куда более продвинутые, pgcli, например), типов данных из коробки — раз два и обчёлся (CLOB'ы — это вообще тихий ужас). Многие запросы пишутся через жопу (уже спустя год с содроганием вспоминаю вездесущие SELECT FROM DUAL).
Возможно, это дело привычки. С Постгресом я уже много лет работаю, а с Oracle год повеселился (и, надеюсь, больше ни-ни).
Но есть и плюсы: многие вещи в Оракле есть из коробки. Полнотекстовый поиск? Пожалуйста (+$100500), геометрия/география? Вот тебе бесплатный Oracle Locator и платный Oracle Spatial (нам, кстати, хватило бесплатного).
Но берёшь PostgreSQL, добавляешь в него по вкусу PostGIS, pg_pathman, мониторинг хороший (тут можно попиарить okmeter), приправляешь каким-нибудь barman'ом — и становится даже лучше.
VovanZ
23.06.2017 18:15+2Вы забыли про возможность запускать под нормальными системами, а не под вендой.
Eldhenn
23.06.2017 12:21+2Не надо грязи. Я сейчас живу на MS SQL 2005. Это боль. Не такая большая, как 2000, но ощутимая.
Varim
23.06.2017 12:45-1а вы с чем сравниваете, боль по сравнению с чем?
Если можно, несколько пунктов которые в другой СУБД лучше.Eldhenn
23.06.2017 12:52+5Например — нет кастомных типов, нет enum, нет логического типа. Мне уже по ночам снятся CASE WHEN expr = 1 THEN.
Синтаксис T-SQL… не так очевиден, как PL/pgSQL. Триггеры в постгресе пишутся намного проще.
Нет юникода, как я забыл! Основная причина того, что мы до сих пор на 1251 — невозможность utf8 в субд.Varim
23.06.2017 13:02+1sql 2005 снят с поддержки.
пользовательские типы были в 2008, я с ними работал, функциональные возможности типов не помню.
тип bit есть, не знаю с какой версии.
юникодные NCHAR NVARCHAR был, но на данный момент я не помню какая связь с collationEldhenn
23.06.2017 14:21> sql 2005 снят с поддержки.
Прочтите исходный комментарий ветки.
> тип bit есть
Я не хочу bit. Я хочу булевый. Вы знаете разницу между ними?
> юникодные NCHAR NVARCHAR
Это не тот юникод. Совсем не тот, поверьте.Varim
23.06.2017 14:30Я не хочу bit. Я хочу булевый. Вы знаете разницу между ними?
я не уверен, хотелось бы узнать, наверно какое то удобство в чем тоmayorovp
23.06.2017 14:44+2Булев (логический) тип — это то, что возвращают операторы сравнения, также его принимают и возвращают логические операторы.
bit — это целочисленный тип с двумя возможными значениями.
В SQL нельзя написать
WHERE foo, если foo имеет тип bit. Приходится писатьWHERE foo=1. В обратную же сторону получается еще хуже — вместоSELECT a<b as cmpнужно писатьSELECT case when a<b then 1 else 0 end as cmp.
Varim
28.06.2017 09:23Это не тот юникод. Совсем не тот, поверьте.
Можете сказать что не так с юникодом?Eldhenn
28.06.2017 09:35UTF16 не так.
Varim
28.06.2017 10:35+1UTF16 это проблема для какого то клиента? Для PHP?
Eldhenn
28.06.2017 14:13Если вам доставляет извращённое удовольствие регулярно перекодировать в utf8 и обратно — я рад за вас. Мне не доставляет ни малейшего. Очень весело, например, собирать json. Да и разбирать часто не менее весело.
Varim
28.06.2017 14:17Вам видимо кажется что я прикалываюсь, но нет, мне действительно интересно какие у кого проблемы с MS SQL. Я MS SQL с PHP не использовал, потому о проблемах с кодировкой юникода слышу в первые. Я предполагал что можно задавать кодировку, с которой подключаешься к MS SQL.
AlexLeonov
29.06.2017 01:13-1Причем тут PHP? Этот язык, к вашему сведению, вообще понятием «кодировка» не оперирует. Для него что request, что response — просто набор байт. И не более того.
Вам прямо говорят, что JSON, например, это UTF-8. Независимо от языка.mayorovp
29.06.2017 08:57Ничего подобного, JSON может быть в любой кодировке. Стандарт вообще ничего не говорит про бинарное представление JSON, только про текстовое.
A JSON text is a sequence of tokens formed from Unicode code points that conforms to the JSON value grammar.
— Final draft of the TC39 "The JSON Data Interchange Format" standart, пункт четвертый.
win32nipuh
24.06.2017 10:05В чем именно похожими?
Слишком общая фраза, так же общо отвечу: у SQL Server даже 2017 нет многих возможностей, которые в PostgreSQL были уже давно.
symbix
23.06.2017 12:10+1Самый распространенный вариант частичного индекса (email + not deleted) в mysql можно изобразить через виртуальные столбцы. Синтаксис виртуальных столбцов несколько отличается в mariadb и oracle mysql (где они появились позднее), но суть та же. Покажу на примере mariadb.
CREATE TABLE users ( ... email varchar(255), is_deleted boolean NOT NULL DEFAULT FALSE, _null_if_deleted char(0) AS (IF(is_deleted, NULL, '')) PERSISTENT, ... ); CREATE UNIQUE INDEX uniq_users_email ON users(email, _null_if_deleted);
Трюк в виртуальном char(0) поле, которое может иметь всего 2 значения — пустая строка и null. Если пользователь удален, то там будет null, и в итоге строка в uniq_users_email участвовать не будет.
Не подумайте, я вовсе не хочу превратить топик в оправдания mysql-я, я люблю постгрес и использую его практически во всех проектах, где я принимаю решение о выборе базы данных с самого начала. Но очень часто приходится работать с проектами, которые уже сделаны на mysql и выбора нет.
Varim
23.06.2017 12:50то там будет null, и в итоге строка в uniq_users_email участвовать не будет
а почему? из за слова PERSISTENT?
непонятно как char(0) при null должен исключаться от попадания в индексsymbix
23.06.2017 13:04Из-за слова NULL. NULL-значения не участвуют в unique index. В случае индекса по нескольким полям — если хотя бы одно из них NULL. char(0) — это просто для экономии места, чтобы не хранить никакое значение, которое тут все равно служебное и смысла не несет.
Varim
23.06.2017 13:30+2для меня это неожиданное поведение, в ms sql 2016 null колонки попадают в индекс и значения двух колонок (null, null) заблокируют вставку следующей пары (null, null), так же как и например
(null, 1) заблокируют вставку следующей пары (null, 1)
вот можно поиграться
USE [test] GO /****** Object: Table [dbo].[users] Script Date: 6/23/2017 1:31:19 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[users]( [id] [int] NOT NULL, [email] [varchar](255) NULL, [is_deleted] [bit] NULL, CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO USE [test] GO SET ANSI_PADDING ON GO /****** Object: Index [uniq_users_email] Script Date: 6/23/2017 1:31:32 PM ******/ CREATE UNIQUE NONCLUSTERED INDEX [uniq_users_email] ON [dbo].[users] ( [email] ASC, [is_deleted] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GOsymbix
23.06.2017 13:33+1Это какое-то очень странное поведение, NULL не является уникальным значением, один NULL не равен другому.
В ANSI SQL это четко прописано. В этом весь смысл NULL-а.
А как тогда в MS SQL делать уникальность опционального email-а (пусть даже безо всяких deleted)?
mayorovp
23.06.2017 13:42CREATE UNIQUE INDEX IX_users_email ON users(email) WHERE email IS NOT NULLsymbix
23.06.2017 13:45+2Я так и думал. :)
С точки зрения ANSI SQL это полная жесть, конечно. "Уникальность" NULL-а ломает кучу классических SQL-паттернов, ради которых NULL и задуман таким, какой он в ANSI SQL.
Varim
23.06.2017 14:18+1«Уникальность» NULL-а ломает кучу классических SQL-паттернов
а можно огласить весь список? я предпалагаю что ms sql как то должен их обойти без особых проблемsymbix
23.06.2017 14:39Полагаю, что для обхода везде придется дописывать where field is not null.
Скажем, поиск дубликатов:
> create table a (id serial, v int); > insert into a (v) values (1), (null), (2), (null), (1); > select * from a a1, a a2 where a1.v = a2.v and a2.id > a1.id; +----+------+----+------+ | id | v | id | v | +----+------+----+------+ | 1 | 1 | 5 | 1 | +----+------+----+------+ 1 row in set (0.00 sec)mayorovp
23.06.2017 14:48В запросах это решается настройкой ANSI NULLS:
SET ANSI_NULLS ON
Причем установить ее можно глобально для базы, тогда не нужно будет писать эту строку каждый раз (хотя все равно пишут, для независимости от настроек базы).
Неустранимыми "особенностями" обладают лишь те объекты, которые хранятся в базе — индексы и, может быть, ограничения. Индексированные виды при этом внезапно работают в режиме ANSI_NULLS.
Varim
23.06.2017 14:50в ms sql 2016 так
select * from a a1, a a2 where a1.v = a2.v and a2.id > a1.id; id v id v 1 1 5 1
SET ANSI_NULLS on/off ничего не меняетmayorovp
23.06.2017 14:54А, точно. ANSI_NULLS влияет только на интерпретацию сравнений с константой NULL.
symbix
23.06.2017 14:56А вот выше говорят про настройку ANSI_NULLS. Я верно понимаю, что такой результат выборки получается при ANSI_NULLS ON, а если сделать OFF, то вылезут еще строчки с NULL-ами?
mayorovp
23.06.2017 14:58Нет, я там ошибся. Жаль, нельзя себе минус поставить...
symbix
23.06.2017 18:24+4То есть получается, что в MSSQL:
1) при сравнении с константой NULL результат зависит от ANSI_NULLS,
2) при сравнении значений полей NULL-ы всегда не равны,
3) но в UNIQUE INDEX NULL-ы всегда равны.
Я все верно усвоил?
Мой мозг. :) Зачем, зачем они так сделали?
Varim
23.06.2017 14:42А как тогда в MS SQL делать уникальность опционального email-а (пусть даже безо всяких deleted)?
а мне вот наоборот интересно, как в PG или MySql делать индекс по комбинации где одна колонка заполнена, а другая nullsymbix
23.06.2017 14:45Вообще, как правило, вы не должны этого хотеть, ведь NULL — это не значение, а отсутствие значения.
А если очень хочется, можно, конечно, сделать индекс вида (col1, is_null(col2)). В постгресе напрямую, в mysql — тем же трюком с virtual columns, только наоборот.
mayorovp
23.06.2017 13:42Хм, проверил — вы правы, в уникальном индексе в MS SQL может быть только 1 NULL. Осталось понять, каким образом я запомнил противоположное?..
Varim
23.06.2017 14:10в 2000 вроде было другое поведение, я уже не помню точно, возможно зависит от SET ANSI_NULLS ON или еще от чего нибудь
Varim
23.06.2017 13:36а как вы узнали что оно действительно не хранит значений?
symbix
23.06.2017 13:52Насчет хранения не уверен, но, как минимум, для выборки по индексу не надо читать ничего из данных:
MariaDB [test]> create table foo (c0 char(0), index (c0)); Query OK, 0 rows affected (0.08 sec) MariaDB [test]> insert into foo values (''); Query OK, 1 row affected (0.01 sec) MariaDB [test]> explain select c0 from foo; +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | foo | index | NULL | c0 | 1 | NULL | 1 | Using index | +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.01 sec)
"If the index is a covering index for the queries and can be used to satisfy all data required from the table, only the index tree is scanned. In this case, the Extra column says Using index."
Как точно узнать, что совсем не хранится, я не знаю. :) Но в случае char(0) выглядит логичным ничего не хранить. Это скорее уже предположение.
VolCh
24.06.2017 01:27Для nullable char(0) логичным выглядит хранить один бит вне зависимости от наличия индекса, но есть подозрение, что в большинстве случаев это экономия на спичках., даже с индексом.
symbix
24.06.2017 13:39Я выбирал из char(0) и enum-а с одним возможным значением. char(0) показался эстетичнее, плюс дополнительное соображение о логичности хранения только одного бита (enum-то наверняка всегда хранится как integer).
mayorovp
23.06.2017 13:09+1Согласно стандарту ANSI SQL, NULL-значение не может быть равно никакому другому, даже другому NULL (похожим образом ведет себя NaN в IEEE 754). Поэтому ключи, содержащие хотя бы 1 NULL, всегда считаются уникальными.
Так было сделано для того, чтобы можно было накладывать ограничение уникальности на опциональные поля, иначе в таких ограничениях было бы мало смысла.
AlexLeonov
23.06.2017 12:42Про enum в кастомных типах забыли: https://www.postgresql.org/docs/current/static/datatype-enum.html
Это один из самых частых вопросов от свитчеров.
Varim
23.06.2017 13:09+1а есть какая нормальная IDE к PgSql?
pgAdmin что то не впечатлил.
Есть что то бесплатное с аналогом Database Diagram в Management studio?symbix
23.06.2017 13:39+1Меня вполне устраивает Database Tools, который поставляется с практически всеми JetBrains-овскими IDE.
DistortNeo
23.06.2017 19:48+1Попробуйте поставить pgAdmin 3, потому что pgAdmin 4 — какое-то невнятное убожество, которое умеет только тормозить и жрать память, потому что написано "на вебе".
Caravus
23.06.2017 13:24Listen / Notify
Я пока что не пробовал это в продакшене, поэтому не знаю, применимо ли это на практике (если кто использовал, поделитесь плиз опытом в комментариях).
Мы используем. Связали так php и golang, отправляется уведомление при изменении данных, чтоб бэкенд подхватил. Работает как часы, очень удобно.varanio
23.06.2017 13:31golang подписан, а php отправляет уведомления?
Caravus
23.06.2017 13:32Да. PHP меняет данные, сохраняет, отправляет в golang «эй, смотри тут данные обновились».
varanio
23.06.2017 13:36и сколько соединений с pg держит голанг?
Caravus
23.06.2017 13:38Два. Одно для общения с самой базой, одно для Listen.
varanio
23.06.2017 16:35Если соединение прервалось и восстановилось, в этот период может что-то продолбаться, так ведь?
А в схеме, когда много-много подписчиков, каждый из них должен будет постоянно держать соединение с базой, чтобы не пропустить сообщение. Но соединение с базой в посгресе — это дорого. Вот это меня беспокоитCaravus
23.06.2017 16:46По поводу соединения — не задумывался пока.
Если не соединяться с базой — как с ней общаться? :) Как и с любым сервисом.varanio
23.06.2017 16:51Ну в случае с классической очередью, можно хоть раз в час подсоединяться, проверять, нет ли чего нового, и отсоединяться. А тут прям привязан к соединению
Caravus
23.06.2017 17:11Ну мы тут говорим про очереди или про pub/sub? :) Если всё что нужно — передать сообщение чтоб клиент его рано или поздно получил (например раз в час проверяет) — почему бы просто не записать в табличку, а потом оттуда прочитать?
Мы же используем этот механизм для реалтайма, то есть оповестить клиента настолько быстро насколько возможно. Это удобней чем городить огороды с rpc/http апи или ещё чем… с базой и так соединены оба.VolCh
24.06.2017 01:31Записать в табличку, а потом прочитать, плохо, как минимум, работает, когда читателей много, писатель о них не знает.
andriyan0v
24.06.2017 11:59pg_notify можно сразу из тригерной процедуры делать для реалтайма, однако есть свои минусы
Caravus
24.06.2017 14:46Яизначально хотел делать нотифи по сохранению строки в таблицу, но потом решил что будет проще просто делать ещё один SQL запрос из PHP, больше контроля и проще отладка для меня как для программиста (а совсем не DBA).
symbix
23.06.2017 18:31Не, проблема в том, что если, скажем, отрестартить гошный сервис, то часть уведомлений потеряется.
В последних постгресах есть API для декодирования WAL, и на гитхабе можно найти декодеры в json, protobufs и все такое. Возможно, это было бы более надежным решением, да и notify никакие не нужны — все само прилетит. Насколько я понимаю, отфильтровать только нужное можно тоже на уровне декодера.
Envek
24.06.2017 00:33+1Тогда вам нужны очереди. Если хочется
странногооставаться в рамках Постгреса, то посмотрите в сторону PgQ, возможно, что это оно (сам не работал). А вообще, лучше не мучит мозг, а взять что-нибудьMQ: RabbitMQ, там, тысячи их.VolCh
24.06.2017 01:42-1MQ все разные, но не знаю ни одной, которая позволяла бы воркеру может полность нового, может год назад поратовшего( штатно и быстро получить все пропущенные события с конкретного… момента
symbix
24.06.2017 16:20Идея с WAL decoder мне нравится тем, что можно получить функциональность RethinkDB, оставаясь в рамках постгреса.
Еще не пробовал на практике, но скоро собираюсь — на горизонте маячит подходящая задачка.
edo1h
24.06.2017 01:47+1Дорого устанавливать соединение. Держать установленное соединение разве дорого?
barkalov
23.06.2017 13:46Оффтопик: Есть информация о том, что самая распространенная ORM для node.js (Sequelize.js, 400k загрузок в месяц) некорректно работает с postgres. Писать статью?
Rastler
23.06.2017 14:20+1Конечно! А если кратко, то в чем суть проблемы?
barkalov
23.06.2017 15:06+2В postgres есть hardcoded переменная, NAMEDATALEN = 64. Которая, среди прочего накладывает ограничение на длину идентификатора (названия таблиц, алиасов, полей) 64-1=63 символа. Если писать запросы руками, этого более чем достаточно. Однако ORM, при запросах с JOIN, для вложенных таблиц создает алиас вида ParenttablenameChildtablename, склеивая названия таблиц, запросто превышая этот предел длины.
Ни sequelize, ни postgres ошибок и предупреждений не выдает, и на сложных запросах спокойно и валидно возвращает не фронтенд некорректные данные. При этом в тестах (на более менее простых запросах) всё ок.
У меня проблема обнаружилась на втором месяце разработки, когда уже была написана жирная бизнес-логика и в ход пошли трехэтажные запросы. Стоило нескольких бессонных ночей.
Ишью открыта с 2014 года и выглядит так, будто это не критическая проблема, а мелкая недоработка.
Простого решения нет. Мне пришлось пересобрать postgres из исходников, установив NAMEDATALEN = 1024. Но это плохое решение: во-первых, со слов разрабов, уже с NAMEDATALEN = 128 есть падение производительности postgres; во-вторых умеючи можно и 1024 превысить и не заметить.
vgoloviznin
23.06.2017 14:22+1Напишите, а ишью создали?
barkalov
23.06.2017 15:08Ишью открыто с 2014 года. Мило же?
varanio
23.06.2017 16:49+2Это ишью надо в посгресе открывать имхо. Ну что такое 63 символа — смех один. Я тоже напарывался на это ограничение, и не в orm (генерили автоматом то ли хранимки, то ли юзеров, я забыл)
barkalov
23.06.2017 17:00В маиллистах постреса поднимали эту тему некоторое время назад. Тогда увеличили с 32 до 64, отметив что дальнейшее увеличение дает performance degrade.
Я был бы рад даже не увеличению параметра, а хотя бы нормальной ошибке при превышении.
sl_bug
23.06.2017 17:26+2я вот не понимаю причины использование в данном orm таких имён, можно ведь как в рельсах t1, t2, t3… алиасы использовать.
barkalov
24.06.2017 00:31+2Изначальных мотиваций две: вроде как проще дебажить сгенерированный SQL и проще парсить ответ (напрямую) в дерево. Я, было, хотел написать патч, но, честно говоря, просто зарылся коде и, за выделенное себе на это время — не осилил. Как-то там тяжело всё, целая экосистема и надо с умом подойти, чтобы сделать красиво.
Вообще, интересная ситуация. Я всё понимаю, open-source, никто ничего никому не должен, возьми и сделай, или хотя бы заранее читай все issue. Всё так. Но я всё равно как-то офигел, что такое возможно в 10k звездном проекте с 400 тысячами загрузок в месяц. Представляете, это была моя инициатива строить проект на node стеке, и тут, спустя два месяца работы, я понимаю, что в решении есть один «несущественный недостаток»: из базы приходят не все данные. Три дня и ночи искал у себя в коде баг. Потом нашел открытую ишью от 2014 года. Ещё три дня просто молчал и познавал дзен.
jankovsky
23.06.2017 14:50-10В 2013 postgres был еще нормальной СУБД. Сейчас же надобавляли всякой ерунды ненужной. Пользоваться стало неудобно. Ресурсов стал есть мама не горюй. Стабильно раз в сутки приходится по cron его перегружать. Про репликации я вообще молчу. Как обычно, хорошее долго на рынке не держится. Даже подозрение закрадывается, а не конкурентов ли с проприетарной стороны баррикад это проделки. С нынешним впечатлением и речи не может быть о переходе с MySQL. Однозначное «нет».
EvilFox
23.06.2017 19:12+2Ресурсов стал есть мама не горюй
Как настроете так и будет жрать. На мой взгляд он экономнее MySQL.
Envek
24.06.2017 00:12+1Вот не знаю даже, что вы с Постгресом такое сделали. Он из коробки настроен на очень экономное использование памяти, так, чтобы нормально работать на машинках класса Raspberry Pi и хуже. Ему специально надо «гайки ослаблять», чтобы разрешить использовать все ресурсы и раскрыть производительность по полной. Для старта возьмите PgTune, а уже потом есть мануалы по том, как его тюнить.
DistortNeo
23.06.2017 21:02Да, в PostgreSQL мне понравились индексы по выражению.
Но есть и небольшая ложка дёгтя: если взять два поля, объединить их в range и построить индекс, то он будет срабатывать только когда в запросе тоже используются range.
То есть
WHERE int8range(col1, col2) @> $valueбудет работать эффективно, аWHERE col1 <= $value AND $value < col2— нет. Это не очень удобно при использовании ORM.sl_bug
23.06.2017 21:44Так индекс по range, почему он должен использоваться для операций типа "<"?
DistortNeo
23.06.2017 21:54+1Потому что мне так хочется. Написано-то одно и то же. Вот пусть база сама догадается, что можно использовать индекс на range.
VolCh
24.06.2017 01:55Насколько я понимаю — не одно и то же. Вы уверены, что результаты будут одинаковы для разных типов col*?
Envek
24.06.2017 00:08+1А это со всеми функциональными индексами так: ускоряются только запросы с условием с точно таким же выражением (и одним из поддерживаемых данным индексом операторов в условии ещё, сверяйтесь в документации — btree и gist ускоряют разные запросы!).
Fortop
24.06.2017 10:21-2SELECT count(*) FILTER (WHERE age > 20) AS old, count(*) FILTER (WHERE age <= 20) AS young FROM users;
И чем это принципиально лучше такого?
SELECT SUM(IF(age>20,1,0)) AS old, SUM(IF(age<=20,1,0)) AS young FROM users;
varanio
24.06.2017 10:59+3Читабельностью
Fortop
24.06.2017 13:37-2Не смешно.
Брюнетка/блондинка…
Ещё есть плюсы?
Ну там используются partial индексы, например?Envek
24.06.2017 22:30+1К сожалению, нет, ни в том, ни в другом варианте:
- count и filter
EXPLAIN ANALYZE SELECT count(*) FILTER (WHERE age < 18) AS young FROM people; -> Seq Scan on people (cost=0.00..212803.95 rows=10999995 width=4) (actual time=0.060..646.134 rows=11000000 loops=1) sum с условием. В PostgreSQL нет
IF(function if(boolean, integer, integer) does not exist), поэтому вместо этого используетсяCASE:
EXPLAIN ANALYZE SELECT (SUM(CASE WHEN age < 18 THEN 1 ELSE 0 END)) AS young FROM people; -> Seq Scan on people (cost=0.00..212803.95 rows=10999995 width=4) (actual time=0.042..674.906 rows=11000000 loops=1)
- Однако если
WHEREприсобачить уже в конце выражения, послеFROM, только тогда используются:
EXPLAIN ANALYZE SELECT count(*) FROM people WHERE age < 18; -> Index Only Scan using young_people on people (cost=0.43..55783.71 rows=1952507 width=0) (actual time=0.043..153.447 rows=1925242 loops=1)
Таблица для проверки:
CREATE TABLE people (id serial, name varchar, age int, primary key (id)); INSERT INTO people (name, age) SELECT md5(random()::text)::varchar AS name, (random() * 100)::int AS age FROM generate_series(1, 1000000); -- достаточно большая таблица, чтобы планировщик захотел заморочиться с индексами CREATE INDEX young_people ON people (age) WHERE age < 18; VACUUM ANALYZE people;Envek
24.06.2017 22:48На самом деле неправильно вас понял (перепутал покрывающие с частичными индексами), но ответа это не меняет.
Используйте вот такую конструкцию, если хотите оба значения и использовать индекс (и то, только при условии, что оба фильтра отсекают большое количество записей):
SELECT (SELECT count(*) FROM people WHERE age < 18) AS young, (SELECT count(*) FROM people WHERE age >= 18) AS too_old;
QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------- Result (cost=29282.67..29282.68 rows=1 width=16) (actual time=160.145..160.145 rows=1 loops=1) InitPlan 1 (returns $0) -> Aggregate (cost=5369.82..5369.84 rows=1 width=8) (actual time=23.040..23.040 rows=1 loops=1) -> Index Only Scan using people_age on people (cost=0.42..4936.65 rows=173270 width=0) (actual time=0.016..15.298 rows=175201 loops=1) Index Cond: (age < 18) Heap Fetches: 0 InitPlan 2 (returns $1) -> Aggregate (cost=23912.83..23912.83 rows=1 width=8) (actual time=137.100..137.100 rows=1 loops=1) -> Seq Scan on people people_1 (cost=0.00..21846.00 rows=826730 width=0) (actual time=0.010..100.024 rows=824799 loops=1) Filter: (age >= 18) Rows Removed by Filter: 175201 Planning time: 0.065 ms Execution time: 160.170 ms
Обратите внимание, что во втором случае планировщик всё равно предпочёл полное сканирование, потому что колонок в таблице мало, а второе условие отбирает бо?льшую часть записей в таблице и профита лезть в индекс нет.
- count и filter
Legion21
обычно ORM поддерживают только смежные фичи… возможно из-за этого uber перешел обратно на MySQL)
Jabberwok
Убер перешел на MySQL потому что им нужна была не RDBMS, а платформа для реализации своей key-value базы: https://eng.uber.com/schemaless-part-one/