Многие боятся переходить с «мускуля» на «посгрес» из-за того, что лишь смутно понимают, что это даст. Некоторых останавливает мысль, что наверно Postgres — это слишком сложная база и требует обучения. А также, что возможно чего-то придется лишиться в связи с переходом. Попробую немного прояснить ситуацию.
Вообще говоря, если кто-то боится сложности, то для начала можно сделать как все обычно делают: «втупую» перейти с MySQL на PostgreSQL, не используя новых возможностей. SQL — он и в Африке SQL, это не rocket science. При таком переходе ничего сложного (с т.з. программирования) для вас не будет. Ну кавычки другие, синтаксис чуть строже. Т.е. использовать pg как mysql с другими кавычками для начала, а дальше учиться по ходу пьесы.
Теперь по поводу того, что есть в одном, но нет в другом и наоборот. Чтобы понимать какие плюшки там есть. Вот список, конечно не полный и он явно не отсортирован по важности, но тем не менее.
Начнем с недостатков посгреса, которых наверно нет в мускуле.
- Чтобы работать в продакшене с посгресом, его нужно хорошенько профессионально настроить. Если вы не выставите правильно shared_buffers, настройки автовакуумов и т.д., то на серьёзных нагрузках всё будет медленно работать. Особо бесит то, что для highload-проектов практически всегда нужен еще и pgbouncer (это сторонняя разработка), который копит коннекты в очереди и следит, чтобы к самому посгресу было ограниченное число коннектов. Странно, что это не встроено в сам postgres
- Автовакуумы. Если говорить по простому, то чтобы достичь высоких показателей скорости записи/удаления, посгрес оставляет кучи мусора, которые потом чистят специально обученные демоны. Если неправильно настроить автовакуумы или с дуру вообще отключить, особенно на очень нагруженной базе, то место, занимаемое таблицами будет пухнуть, и рано или поздно или забьётся всё, что может забиться, или даже без опухания, база просто может встать колом и сказать, что кончились id транзакций. На каждой конференции есть 3-4 доклада о том, как кто-то героически бился с автовакуумом и победил.
- До недавнего времени не было INSERT IGNORE и INSERT ON DUPLICATE KEY UPDATE. Начиная с 9.5 появились аналоги. Очень странно, что так долго тянули с реализацией такого нужного всем функционала.
- В Mysql можно прямо в запросе оперировать переменными
SELECT @x:=0; SELECT @x:=@x+1 FROM table;
В посгресе такого нет, по крайней мере я не нашел (напишите, если ошибся). Ну, то есть, можно конечно сделать хранимку, где можно делать вообще всё что угодно, но вот чтобы прямо так в запросе — вроде как нет. - Нет нормального аналога phpmyadmin. По сути, большинство известных мне крутых посгресистов работают с SQL в командной строке, к чему сложно привыкнуть поначалу. Нет, есть всякие pgMyAdmin и т.д., но каждый из них имеет какую-то ущербность. Возможно платные есть хорошие, я не проверял.
- Mysql все знают, postgresql никто не знает. Поэтому новые проекты часто боятся начинать на postgresql, потому что надо будет поддерживать, да и вообще боязнь неизвестного. Есть целый класс php-программистов, для которых слово “база данных” и mysql — это одно и то же, слова-синонимы. Т.е. им как-то сложно вылезти из скорлупы, что ли.
- Говорят, дешевые хостинги не очень любят postgresql, потому что его сложнее администрировать. Например, чтобы создать пользователя, который может логиниться к postgres, надо делать это в двух местах: выполнить sql-запрос и прописать в pg_hba.conf
Из недостатков по сравнению с mysql пока всё. Если что-то еще знаете конкретное, что есть в mysql и чего нет в postgresql — пишите в комментариях. Теперь плюшки, которые есть у postgresql:
- CTE ( Common Table Expression)
Если объяснять по-простому, то подзапросы можно записывать отдельно, давая им имена, и все это в рамках одного запроса к БД. Например
WITH subquery1 AS ( SELECT ... JOIN... JOIN... GROUP BY.... ), subquery2 AS ( SELECT ... WHERE .... ) SELECT * FROM subquery1 JOIN subquery 2 ON ...
Крайне полезная вещь для сверхсложных запросов, где без именованных подзапросов можно сломать весь свой головной мозг, колдуя с join-ами и скобками подзапросов. Там конечно куча нюансов есть по производительности, которые надо знать, но всё равно невероятно полезная вещь. Которой нет в MySQL. Кстати, подзапросы в CTE можно использовать рекурсивно, например, чтобы получить всё поддерево в таблице вида “id, parent_id”. - Работа с ip-адресами. Например, надо быстро определить город/страну по ip-адресу.
Тут надо сказать, что в посгресе есть кастомные типы данных и даже операторы, которые с этими типами работают. Некоторые можно делать самому, некоторые можно получить, поставив расширение к посгресу. Например, есть расширение ip4r, позволяющее делать примерно так:
-- создадим таблицу с ip-диапазонами create table ip_ranges ( ip_range ip4r ); insert into ip_ranges values ('2.2.3.4-2.2.3.10'), ('1.2.0.0/16');
Теперь мы можем получить список диапазонов, которые пересекаются с заданным ip с помощью оператора &&:
test=> select * from ip_ranges where ip_range && '1.2.1.1'; ip_range ------------ 1.2.0.0/16 (1 row)
До кучи там есть и другие операторы: вхожение диапазонов один в другой и др. Чтобы поиск был очень быстрым, можно построить специальный индекс GIST:
CREATE INDEX ip_ranges_idx ON ip_ranges USING GIST (ip_range);
И всё будет просто “летать” даже на огромных объемах данных. Как такое сделать в mysql не представляю, может есть какой-то способ? - Разнообразные CONSTRAINTS, т.е. ограничения самой базы данных, обеспечивающие целостность. В MySQL также есть констрейнты UNIQUE, NOT NULL, FOREIGN KEY и и т.д. Но как насчет такого:
Модифицируем таблицу из предыдущего примера:
ALTER TABLE ip_ranges ADD CONSTRAINT ip_ranges_exclude EXCLUDE USING GIST(ip_range WITH &&);
Эта запись гарантирует, что в таблице только непересекающиеся друг с другом диапазоны ip. При попытке вставить диапазон, ip которого частично уже есть в таблице, будет ругань:
test=> insert into ip_ranges values ('1.2.3.4/32'); ERROR: conflicting key value violates exclusion constraint "ip_ranges_exclude" DETAIL: Key (ip_range)=(1.2.3.4) conflicts with existing key (ip_range)=(1.2.0.0/16).
Точно также можно использовать, например, тип данных circle и проверять, чтобы в таблице хранились непересекающиеся круги. Кстати, некоторые геометрические типы и операции с ними встроены прямо в стандартную поставку: circle, box, polygon и т.д.
Еще полезный constraint:
create table goods ( id bigint, price decimal(11,2), … check (price >= 0.01) )
И вы никогда не вставите туда случайно товар с нулевой ценой. Разумеется, условия внутри check могут быть любые. - Киллер-фича последних версий посгреса — тип jsonb, позволяющий очень быстро искать по джейсонам. Не буду подробно останавливаться, потому что в каждой второй статье про это все уши прожужжали.
- Так называемые “оконные функции”. Например, надо выдать для каждого сотрудника его зарплату, и среднюю зарплату по отделу в той же строке, без использования подзапросов и group by.
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary; depname | empno | salary | avg -----------+-------+--------+----------------------- develop | 11 | 5200 | 5020.0000000000000000 develop | 7 | 4200 | 5020.0000000000000000 develop | 9 | 4500 | 5020.0000000000000000 develop | 8 | 6000 | 5020.0000000000000000 develop | 10 | 5200 | 5020.0000000000000000 personnel | 5 | 3500 | 3700.0000000000000000 personnel | 2 | 3900 | 3700.0000000000000000 sales | 3 | 4800 | 4866.6666666666666667 sales | 1 | 5000 | 4866.6666666666666667 sales | 4 | 4800 | 4866.6666666666666667 (10 rows)
Через оконные фунции можно упрощать целый класс задач, например очень полезно для всякой аналитики и биллинга. - Хранимые процедуры можно писать на разных языках: чистом sql, pl/pgsql (это язык, удобный для работы с SQL-базой, но медленноват), на javascript (pl/v8), на перле и еще бог знает на чем. Вы можете даже приделать к посгресу свой любимый язык, если владеете си и достаточно усидчивы. Подробно об этом рассказывалось на pgday. На мой взгляд, в postgresql всё не так уж гладко с языками в хранимках, но всяко в 100 раз лучше, чем в mysql.
- Можно делать индексы не только по полям, но и по фунциям от них.
- Репликация (Hot Standby) сделана по уму. Работает быстро и консистентно.
- Скорость. По моим субъективным ощущениям, а я работал много лет с обеими базами, Postgresql в целом гораздо быстрее MySQL. В разы. Как на вставку, так и на чтение. Если правильно настроен, конечно.
Особенно это проявляется при выполнении сложных запросов, с которыми mysql просто не справляется, и надо городить временные таблицы. - строгость во всём. В mysql вроде бы только в 5.7 сделали строгий режим по умолчанию (я не проверял, это действительно так?). До этого можно было вставить в поле типа decimal(5,2) число больше положеннго, и в результате молча получить 999.99. Молчаливое обрезание строк и т.д. Таких приколов там тьмы. И это поведение по умолчанию. Postgresql костьми ляжет и будет ругаться, но не будет молча выполнять двусмысленный запрос.
- Транзакционность. CREATE TABLE, ALTER TABLE и т.д., как и простые запросы, можно выполнить в одной транзакции или откатить транзакцию на середине, если что-то не так. Со временем просто не понимаешь, как раньше выкручивался на mysql без этого.
- Полнотекстовый поиск из коробки. Там на мой взгляд немного непривычный для нормального человека синтаксис, но всё работает и не нужно подключать сбоку сторонние примочки типа sphinx.
- Последовательности (sequences). В mysql есть только AUTO_INCREMENT на поле таблицы, который тикает по одному. В postgresql этот механизм живет отдельно от таблицы, что можно использовать для самых разных потребностей, кроме того можно их создавать зацикленными
- Похоже, DBA считают главным преимуществом postgresql его транзакционную машину. Транзакции там встроены глубоко и хорошо, поэтому всё работает быстро и надежно, как на вставку, так и на чтение. В mysql система другая, там есть база, и есть отдельные движки (такие как: innodb, myisam и т.д.), причем движки не все транзакционные. Из-за этого разделения с транзакциями есть некоторые проблемы. Например, myisam не транзакционен вообще, innodb транзакционен, и обе таблицы можно использовать в одном запросе. Как при этом работает база я не берусь предсказать, наверно сложно и костыльно.
- Субъективно в postgresql меньше багов. Уж не знаю, как они этого добиваются, но для меня это факт — очень стабильная и надежная система, даже на больших нагрузках и объемах данных.
Это мой первый пост на хабр (песочница), так что прошу критиковать сильно, но конструктивно.
Какие еще есть конкретные преимущества и недостатки этих баз? Пишите в комментариях.