За что я не люблю PostgreSQL
DISCLAIMER: посыл этой статьи не в том, что
«PostgreSQL — гавно, не используйте PostgreSQL».
Посыл в следующем: «Может быть я чего-то не понимаю в этой жизни? Пожалуйста, объясните, может быть я изменю своё мнение!»
Когда-то в стародавние времена (лет 20+ назад), когда выбирал для себя базовую СУБД, выбор был между MySQL и PostgreSQL, я на них обоих посмотрел и, несмотря на то, что на тот момент PostgreSQL была существенно мощнее конкурента в плане возможностей (поддержка хранимых процедур и далее по списку), я сделал для себя выбор в пользу MySQL, поскольку его SQL-синтаксис мне понравился гораздо больше.
Среди того, что понравилось в MySQL:
Гораздо более развитый DDL (Data Definition Language) и DCL (data control language): SHOW DATABASES, SHOW TABLES, SHOW CREATE TABLE и т.п.
Также приятные плюшки в DML (data modification language) типа REPLACE INTO.
За 20+ лет, в течение которых пользовался MySQL, он существенно повзрослел и возмужал, добавились:
поддержка хранимых процедур и функций
поддержка общих табличных выражений (CTE)
поддержка оконных функций (Window Functions)
репликация / кластеризация
поддержка различных storage engines
поддержка полнотекстового поиска
расширенная статистика для оптимизации запросов
поддержка JSON
и т.д. и т.п.
Сейчас 20 лет спустя жизнь вновь столкнула с PostgreSQL (решил помочь товарищу с его проектом) и решил дать PostgreSQL «второй шанс».
Удивлению моему не было предела, когда 20 лет спустя я столкнулся с тем, что НИЧЕГО из того, что меня напрягало ещё тогда, не улучшено.
Итак, поехали…
Для сравнения, берём последнюю версию PostgreSQL 16.
Data Control Language
MySQL | PostgreSQL | psql shortcut |
|---|---|---|
SHOW DATABASES | SELECT datname FROM pg_database; | \l |
SHOW TABLES | SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' | \dt |
SHOW CREATE TABLE table_name | SELECT column_name, data_type, character_maximum_length, column_default, is_nullable FROM information_schema.columns WHERE table_name = 'table_name'; | \d+ table_name |
SHOW VARIABLES | SELECT name, setting FROM pg_settings; | \set |
SHOW PROCESSLIST | SELECT * FROM pg_stat_activity; | |
SHOW STATUS | SELECT * FROM pg_stat_database; | |
SHOW GRANTS | SELECT * FROM information_schema.enabled_roles; | \dp |
USE dbname | \c dbname | |
FLUSH PRIVILEGES | SELECT pg_reload_conf (); | |
KILL [CONNECTION | QUERY] thread_id | SELECT pg_terminate_backend (pid); | |
FLUSH TABLES | нет прямого эквивалента |
Data Definition Language
MySQL | PostgreSQL |
|---|---|
ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name new_data_type; | ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name; |
ALTER TABLE table_name ADD COLUMN col1 int AFTER col2 | CREATE TABLE foobar_new (…); |
можно перемещать таблицы между базами данных с помощью RENAME TABLE | Только переносить через дамп |
Офигеть, я не могу добавить новую колонку после другой конкретной колонки, а могу добавить только в конец…
Можно только процитировать Тинькова: «ну это п$%дец какой-то, ну как это может быть в 21-м веке…»
Data Manipulation Language
MySQL | PostgreSQL |
|---|---|
REPLACE INTO | INSERT INTO the_table (id, column_1, column_2) VALUES (1, 'A', 'X'), (2, 'B', 'Y'), (3, 'C', 'Z')ON CONFLICT (id) DO UPDATE SET column_1 = excluded.column_1, column_2 = excluded.column_2; |
Насколько упрощает жизнь REPLACE INTO, почему это нельзя добавить?
Поддержка JSON
В PostgreSQL два отдельных типа данных для JSON: JSON и JSONB.
При этом с типом JSON нельзя сделать практически ничего, всё вкусное и полезное только в JSONB.
При этом мы имеем чрезвычайно строгий и замороченный синтаксис для работы JSON, с постоянным приведением типов туда/сюда.
Пока писал нужные мне SQL-statements для работы с JSON, пришлось мучать ChatGPT несколько часов подряд, которого я довёл, наверное, до седины — постоянно сыпались те или иные ошибки, интуитивно-очевидные конструкции с JSON не работают.
Да и у меня добавилось седых волос.
Приятно-неприятные мелочи
Удобный вывод записей:
В MySQL можно сделать удобный вывод строк в случае длинных записей / значений:
mysql> SELECT * FROM geoPoints LIMIT 1\G
*************************** 1. row ***************************
id: 26
lat: 53.41119462
lon: 49.91625309
description: Родник № 2 в Каменной Чаше
ip_addr: 85.113.61.32
1 row in set (0.00 sec)
В PostgreSQL есть «типа аналог»: \pset format unaligned.
Однако вывод куда менее удобный для визуального анализа:
tgposts=> \pset format unaligned
Формат вывода: unaligned.
tgposts=> SELECT * FROM source_posts LIMIT 2;
post_id|channel_id|msg_id|post_date|grab_date|grouped_id|main_msg|post_text|post_media|media_type|media_saved
456|1124038902|51771|2024-05-28 09:29:16|2024-05-29 16:31:19||t|СДЭК намерен восстановить выдачу посылок не позднее 29 мая — заявление компании|||
457|1124038902|51772|2024-05-28 10:29:09|2024-05-29 16:31:19||t|Испания признала независимость Палестины — МИД Испании
upd: Аналогичные решения, согласно заявлениям местных властей, также официально утвердили Норвегия и Ирландия.|||
(2 строки)Есть ещё \x, но вывод всё равно менее удобный для визуального восприятия, поскольку данные не выровнены по горизонтали:
tgposts=> \x
Расширенный вывод включён.
tgposts=> SELECT * FROM source_posts LIMIT 2;
post_id|456
channel_id|1124038902
msg_id|51771
post_date|2024-05-28 09:29:16
grab_date|2024-05-29 16:31:19
grouped_id|
main_msg|t
post_text|СДЭК намерен восстановить выдачу посылок не позднее 29 мая — заявление компании
post_media|
media_type|
media_saved|
«Безопасность должна быть безопасной»:
Почему-то в PostgreSQL по умолчанию нельзя аутентифицироваться с паролем.
Вместо этого предлагается «аутентификация» по учетным данным операционной системы», которая почему-то считается более безопасной.
Пользователь базы данных должен совпадать с именем учетной записи операционной системы, с которой было установлено соединение. Ну коммон, это неудобно и негибко.
Чтобы входить с паролем, нужно прописывать отдельные разрешения для соответствующих пользователей в pg_hba.conf и перезагружать сервер (или делатьSELECT pg_reload_conf()).
Необходимость вакуумирования:
В PostgreSQL есть необходимость периодического вакуумирования.
Как нам объясняет ChatGPT, основные причины, по которым требуется вакуумирование в PostgreSQL:
Очистка старых версий строк
Обновление статистики таблиц
Защита от bloat (разбухания) индексов
Поддержка работы MVCC MySQL все основные storage engines как-то сами решают эти проблемы без необходимости об этом отдельно беспокоиться.
И это лишь «верхушка айсберга», со сколькими ещё сюрпризами мне предстоит столкнуться?…
Почему всё так грустно и неудобно?
Спросил об этом ChatGPT:
почему в Postgres отсутствует ряд возможностей (о которых я спрашивал ранее), которые есть при этом в MySQL?
вот что он / она / оно пишет:
Философия разработки:
PostgreSQL и MySQL имеют разные философии и подходы к разработке. PostgreSQL делает акцент на строгом следовании стандартам SQL и обеспечении высокой надежности и целостности данных, даже если это означает отказ от некоторых нестандартных или потенциально небезопасных возможностей.
MySQL, с другой стороны, больше ориентирован на производительность и гибкость, что иногда приводит к внедрению нестандартных языковых конструкций.
Ну хорошо, тезис со «строгой поддержкой стандартов» услышан.
Допустим, PostgreSQL так трепетно блюдёт стандарты и не добавляет ничего нестандартного…
Что же мы видим в реальности?
Видим, к примеру, ВСТРОЕННУЮ (не в виде отдельного модуля, а встроенную в core-функциональность) масштабную поддержку работы с геоданными, причём для неё определена куча отдельных пунктуационных операторов, к примеру:
SELECT geom1 && geom2; -- Возвращает true, если геометрии пересекаются
SELECT geom1 << geom2; -- Возвращает true, если geom1 полностью находится внутри geom2
SELECT geom1 >> geom2; -- Возвращает true, если geom2 полностью находится внутри geom1
SELECT geom1 &< geom2; -- Возвращает true, если геометрии пересекаются или одна находится внутри другой
SELECT geom1 &> geom2; -- Возвращает true, если геометрии пересекаются или одна содержит другую
SELECT geom1 <-> geom2; -- Возвращает расстояние между геометриями
SELECT geom1 @ geom2; -- Возвращает true, если geom1 находится внутри geom2
SELECT geom1 ~ geom2 @> distance; -- Возвращает true, если расстояние между геометриями меньше или равно distance
SELECT parallel(lseg '[(0,0),(2,2)]', lseg '[(1,1),(3,3)]'); -- Проверка параллельности отрезков, вывод: true
SELECT parallel(lseg '[(0,0),(2,2)]', lseg '[(1,0),(3,2)]'); -- Проверка параллельности отрезков, вывод: falseХм, где же тут поддержка стандартов?
И где целесообразность и логика: как, например, быть с допусками при проверки параллельности прямых? Ведь в реальности при дискретном представлении данных прямые практически никогда не бывают параллельными, если только они не параллельны сетке координат…
Да, в PostGIS есть для этого более вменяемая функция ST_ParallelWithTolerance, но тогда зачем вообще нужна эта функция parallel в core-функционале, если она по этой причине по определению мертворожденная?
Получается, сделать поддержку SHOW TABLES или SHOW DATABASES или ADD COLUMN AFTER — религия не позволяет, а запоминать всякие однобуквенно-двухбуквенные костыли вроде \l \dt \d+ \dp или каждый раз писать портянку вроде
SELECT column_name, data_type, character_maximum_length, column_default, is_nullable
FROM information_schema.columns
WHERE table_name = 'table_name';```
вместо SHOW CREATE TABLE или
INSERT INTO the_table (id, column_1, column_2)
VALUES (1, 'A', 'X'), (2, 'B', 'Y'), (3, 'C', 'Z')
ON CONFLICT (id) DO UPDATE
SET column_1 = excluded.column_1,
column_2 = excluded.column_2;
вместо REPLACE INTO — это более правильный и идеологически-выверенный подход?
Тогда как вы объясните целую россыпь нестандартных пунктуационных операторов (# :: ?@ ? ?. @> <@ && -> #> и т.п.), число которых приближается к сотне и поддержку геоданных (с кучей своих операторов и встроенных функций) в ядре СУБД общего назначения?
Возможно, я просто чего-то не понимаю или просто ChatGPT мне не может адекватно объяснить. Тогда объясните мне, пожалуйста, в комментах, уважаемые читатели, буду весьма благодарен…
А то дожил до седых волос и так и не понимаю причину того, что данная СУБД, является «выбором по умолчанию» для большинства (по моим ощущениям, обгоняя в этом отношении MySQL).
Может быть все вышеперечисленные возможности не нужны или реализуются каким-то иным более удобным способом?
Просветите, пожалуйста!
