[Из песочницы] Возможности PostgreSQL, которых нет в MySQL, и наоборот12.10.2015 11:03

сегодня в 10:25

Многие боятся переходить с «мускуля» на «посгрес» из-за того, что лишь смутно понимают, что это даст. Некоторых останавливает мысль, что наверно 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 меньше багов. Уж не знаю, как они этого добиваются, но для меня это факт — очень стабильная и надежная система, даже на больших нагрузках и объемах данных.

Это мой первый пост на хабр (песочница), так что прошу критиковать сильно, но конструктивно.

Какие еще есть конкретные преимущества и недостатки этих баз? Пишите в комментариях.

↑
—
↓
887
Добавить в избранное 17

@varanio

карма 1,0

рейтинг 6,4