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 |
нет прямого эквивалента |
Сразу ответ на коммент:
В постгре мета-информация хранится в табличках, что очень гибко, ибо позволяет использовать всю мощь SQL для работы с этими данными, а не зазубривать ограниченные кастомные команды
В MySQL также давно появилась служебная базаinformation_schema, из которой также можно извлечь всё что нужно. При этом остаются удобные и легко-запоминающиеся шорткаты SHOW, для тех, кто хочет быстро что-то посмотреть.
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') |
Насколько упрощает жизнь 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|
Поправьте в статье, пожалуйста.
Если сначала сделать \pset format unaligned, а потом \x, то, конечно, будет невыровненный вывод. Но если вернуть \pset format aligned, то \x все корректно и красиво выравнивает.
Вот, хоть какая-то практическая польза появилась для меня от статьи помимио справочника эквивалентов SQL-команд. Спасибо! )
"Безопасность должна быть безопасной":
Почему-то в 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).
Может быть все вышеперечисленные возможности не нужны или реализуются каким-то иным более удобным способом?
Просветите, пожалуйста!
Комментарии (105)
BogdanPetrov
03.06.2024 14:47+5Спасибо за сравнение, увидел для себя отдельные интересные моменты. Но, честно говоря, немного поверхностно получилось, ничего критичного не увидел (а хотелось бы). Я MySQL не пользовался и все перечисленное не вызывает особых вопросов.
Pitkin_zadov
03.06.2024 14:47+11Вы просто увидели серьезную СУБД, а не это вот всё :).
Сравните MySQL например с MSSQL или Oracle. Будет тоже самое, если не хуже.
Kerman
03.06.2024 14:47О, как. MySql оказывается несерьёзная. И в каком месте она несерьёзная?
Если стоит задача хранить реляционные данные и получать к ним доступ, то чем плоха MySql? Я вот по наивности считал, что именно хранение данных - это первейшая задача СУБД.
Тот же оракл не имеет интовых типов данных. Да, там есть INTEGER, для совместимости с SQL'92, но это тот же самый number(0, 38). То есть число с переменной длиной, которое не запихнёшь с гарантией ни в один из типов современных бэкенд-языков. Это признак серьёзности?
Да, ни оракл, ни постгрес, ни mssql не умеют в add column after. У всех отмазка "ну это чтобы не пересоздавать таблицу". Мне пофиг на пересоздание, у меня там три записи (или вообще нет ни одной). Почему я не могу встроенным синтаксисом вставить столбец куда мне надо? Да, мне это важно, потому что я сортирую столбцы по их назначению. Мне потом их проще искать.
Почему у оракла 30к символов ограничение в nvarchar (при флаге extended, без него 12к)? Это из серии "640кб хватит всем"? Не, я понимаю, что можно пихать текст в блобы, никто вроде не запрещает. Но у того же мускуля 64к символов в TEXT и 4ккк символов в LONGTEXT. Неужели сложно?
MrNutz
03.06.2024 14:47+4Потому что инструменты созданы для работы. Иногда пересоздание таблицы в проде это куча времени, а бизнесу работать надо. Для удобной работы с полями есть другие инструменты. Так же как нет смысла закладываться на порядок записи данных в таблицу.
Kerman
03.06.2024 14:47Очень странный аргумент. Почему базу данных должен беспокоить простой бизнеса? Это вообще не её, базаданново дело. Она понятия не имеет, используются эти таблицы или нет. Насколько критичен простой именно сейчас, и вообще может быть сейчас выходной.
БД - инструмент разработчика. Разработчик делает инструмент для бизнеса. И позвольте разработчику самому решать, какая должна быть структура данных.
Я часто работаю с "сырыми" данными в sql, когда надо быстро посмотреть, в каком состоянии находятся объекты. Иногда таблицы довольно большие, 20, 30 и иногда 100+ столбцов. Мне УДОБНО, когда столбцы, относящиеся к одному функционалу находятся рядом, тогда не приходится скроллить таблицу или изобретать вьюшки на каждый случай. А на каждый случай вьюшек не напасёшься.
Zverienish
03.06.2024 14:47Если вам часто приходится работать с сырыми данными, значит что-то с работой у вас не так.
volos4
03.06.2024 14:47NS SQL management studio умеет в add column after. ваша правда - через пересоздание таблицы, индексов и копировании. данных. н для польщователя этот процесс прозрачный если количество строк невелико.
есть ли гарантия того что mysql не делает тоже самое - insert column через пересоздание таблицы?
Kerman
03.06.2024 14:47MySql делает это ТОЧНО через пересоздание таблицы. Что я прекрасно знаю и это меня ничуть не волнует.
ptr128
03.06.2024 14:47Через пересоздание любая СУБД умеет. Различия лишь в размерах синтаксического сахара для этих целей. Напишите себе процедуру, которая добавляет поле не в конец таблицы через её пересоздание и радуйтесь.
Kerman
03.06.2024 14:47Плюс к этой процедуре надо написать интерфейс для удобного редактирования структуры таблицы, чтобы можно было драгдропом поменять местами колонки. Потом механизм сопоставления версий структур данных для генерации миграций. Ну и сразу удобный data management, чтобы можно было в одном месте и данные в таблице поменять )
Хорошая идея. Я этим как раз и занимаюсь. Но это дорого и долго.
gudvinr
03.06.2024 14:47+2чтобы можно было драгдропом поменять местами колонки
Поставьте datagrip и меняйте драгдропом колонки без трогания структуры БД вообще.
Kerman
03.06.2024 14:47Да, классная фича, оценил. В своё время я отказался от DG из-за одной "фигни", которая критична для меня. Мне нужен список полей с комментариями внутри таблицы. Этого в DG нет или я не нашёл.
Мне понравилось, как именно перетаскиваются колонки. Прямо вместе с мясом. Я себе так же сделаю.
gudvinr
03.06.2024 14:47Почему я не могу встроенным синтаксисом вставить столбец куда мне надо? Да, мне это важно, потому что я сортирую столбцы по их назначению. Мне потом их проще искать.
Так сделайте view для таблицы и отдельно таблицу. Это будет работать вообще в любой БД.
Более того, в постгре и mysql можно ещё и update делать на таких view.Почему у оракла 30к символов ограничение в nvarchar (при флаге extended, без него 12к)? Это из серии "640кб хватит всем"?
Почему у мускуля 4ккк символов ограничение в TEXT (в LONGTEXT, без него 64к)? Это из серии "640кб хватит всем"?
NVARCHAR определяет кол-во символов - это 2x32k БАЙТ для UTF16 и 3x32к для UTF8, а TEXT в MySQL - это 64k БАЙТ. Причём, в случае с азиатскими символами, символов в TEXT влезет меньше, чем в NVARCHAR т.к. в UTF16 они занимают 2 байта, а в UTF8 - 3. А русскоязычными текстами разницы не будет никакой. Поэтому не всё так однозначно.
В CLOB (не BLOB) можно запихнуть 8+ терабайт текста, если очень надо
Kerman
03.06.2024 14:47Я упорядочиваю столбцы по их функционалу для того, чтобы снизить когнитивную нагрузку при работе с данными, а вы предлагаете насоздавать вьюшек на каждую большую таблицу, чтобы потом следить за соответствием этих вьюшек таблицам. Я не враг себе, мне такие костыли не нужны.
gudvinr
03.06.2024 14:47+3мне такие костыли не нужны
Если вас настолько волнует представление - это не проблема БД, а проблема инструментов, которые вы используете для просмотра БД.
Поэтому переупорядочивание колонок внутри БД - это костыль, который вы используете чтобы пофиксить проблему инструмента.Kerman
03.06.2024 14:47Это проблема БД, потому что она хранит структуру таблиц. Внешние инструменты будут вынуждены синхронизироваться с ней и хранить данные о порядке вне структуры.
В любом случае есть факт. MySql позволяет встроенным синтаксисом менять расположение колонок, а оракл/мс/постгрес - нет. Вот и всё. Не надо мне доказывать, что мне это не нужно. Не надо предлагать костылями подпереть. Не надо скидывать ответственность на другие инструменты.
ptr128
03.06.2024 14:47Это проблема БД, потому что она хранит структуру таблиц. Внешние инструменты будут вынуждены синхронизироваться с ней и хранить данные о порядке вне структуры.
Ну так храните пользовательский порядок полей в БД. Например, начинайте комментарий к полю с числа с ведущим нулем, определяющим порядок отображения поля.
gudvinr
03.06.2024 14:47+2А что делать, если вы не одинокий волк с уолл стрит, а работаете на работе? Ну знаете, это такое место где ещё другие люди есть.
И у других людей есть своё представление о прекрасном.Почему ваше представление должно быть единственно правильным и ему должны следовать другие люди? Причем речь именно о визуальном представлении на вашем мониторе, которое абсолютно ни на что не влияет, кроме ваших приколов.
Не надо скидывать ответственность на другие инструменты.
Так и не скидывайте ответственность за вывод на экран на базу данных :)
Kerman
03.06.2024 14:47Почему ваше представление должно быть единственно правильным
А и не должно быть. Это вообще-то подмена тезиса. Я говорю про ВОЗМОЖНОСТЬ упорядочивания колонок, а вы мне про эгоцентризм. Если работаешь с людьми - тогда учитывай интересы людей.
Я обычно работаю над проектом один, и в этом режиме мне абсолютно плевать на мнение коллег о структуре данных.
gudvinr
03.06.2024 14:47Так ВОЗМОЖНОСТЬ есть и в оракле и в постгресе. Ну да, допустим она реализуется не одной командой а тремя, но вам никто не запрещает.
Kerman
03.06.2024 14:47Не тремя. Даже не десятью. Нужно создать временную таблицу, перенести все данные, убрать индексы, убрать констрейнты, убрать внешние ключи, убрать триггеры. Дропнуть таблицу, переименовать временную, вернуть индексы, вернуть констрейнты, внешние ключи, пересоздать триггеры.
Вот чтобы всё это сделать нужно заморочиться. А чтобы это сделать без ошибок и для общего случая, нужно заморочиться сильно, прям очень сильно.
GlukKazan
03.06.2024 14:47При желании, всё это можно автоматизировать и выполнять одной строкой. Только зачем?
gudvinr
03.06.2024 14:47Не тремя. Даже не десятью.
Пожалуйста:
Создаём таблицу
create table X ( id bigint generated always as identity, given_name text not null, family_name text not null );Добавляем новые столбцы и копируем данные из старых
alter table X add column patronymic text, add column _family_name text not null generated always as (family_name) stored;Удаляем старый столбец
alter table X alter column _family_name drop expression, drop column family_name;Переименовываем временный столбец
alter table X rename column _family_name to family_name;
Zverienish
03.06.2024 14:47А зачем вы с данными работаете в таком количестве? Да мне приходится залазить в базу чтобы что-то проверить парни разработке, но это мизер времени занимает и посмотреть в сыром виде не проблема. А так делайте тесты на работу с бл и не придется проверять все.
galaxy
03.06.2024 14:47+2Да, ни оракл, ни постгрес, ни mssql не умеют в add column after. У всех отмазка "ну это чтобы не пересоздавать таблицу"
Ну у постгреса отмазка скорее "мы не против, просто геморроя оказывается многовато для такой малоактуальной фичи, и никто пока не заморочился аккуратно сделать". Вот, например, подход к снаряду 2014 года - почитайте простыню, если интересно, почему не шмогли.
Мне пофиг на пересоздание, у меня там три записи
Вот и делайте через пересоздание таблицы (или через добавление/удаление колонок, как в статье выше описано).
Нет и нет этой фишки, штука явно не ключевая для СУБД, чего срач разводить.
Zverienish
03.06.2024 14:47Сегодня 3 записи, а завтра миллион. И когда вы не единственный разработчик, то очень важен однотипный подход к миграциям, когда гранные одинаково надежно и максимально не блокирующе меняют бд. А если хочется понятней, то в ПГ есть комментарии к столбцам.
tuxi
03.06.2024 14:47+3Ну назвать мускуль несерьезной бд - это тоже не совсем верно. Просто считается, что раз ее пользуют в куче хоум-пет проектов "сайт 30 страничек на коленке", то это типа несерьезно. Но я видел проекты, где мускуль держал серьезные обьемы данных.
panzerfaust
03.06.2024 14:47+30Зашел почитать про сравнение производительности индексов, инсертов и апдейтов, а прочитал про то, что алиасов не завезли.
Zverienish
03.06.2024 14:47Да, а написана чистая вкусовщина человека который видимо весь день сидит в консольке и строчит запросы.
ky0
03.06.2024 14:47+5В чём вопрос? Почему СУБД, обладающая объективными преимуществами, но неудобная для вас по каким-то мелочам, популярнее?
Воспринимать серьёзно наезды на невозможность вставки колонки в середину таблицы (расскажите, зачем это может понадобиться, пожалуйста), вакуум, какое-то там форматирование консольного вывода и на неудобные настройки аутентификации по умолчанию - сложновато.
despair Автор
03.06.2024 14:47Ты хочешь добавить новое поле на этапе уже работающей системе, при этом поля сгруппировать по смыслу, а не добавлять всё в конец. А то получится хаотичная портянка полей, где трудно уловить смысл и логику данных.
gudvinr
03.06.2024 14:47+9Если очень хочется двигать поля зачем-то, то это сделать можно.
Если хочется чтоб красиво выглядело - можно просто view поверх таблицы сделать, и это будет работать в любой бд
ky0
03.06.2024 14:47+6Что такое "поля, сгруппированные по смыслу"? Какую прикладную пользу для работы СУБД эта группировка несёт?
Это уж безотносительно того, что менять состав полей на работающей продукционной системе - вообще так себе затея. А делать это настолько часто, чтобы получилась хаотичная портянка - так себе затея в квадрате.
imanushin
03.06.2024 14:47+3Возможно, это следствие какой-нибудь ошибки в дизайне приложения, в стиле «мы используем ‘select * from …’, а поля присваиваем по индексу ради производительности».
mentin
03.06.2024 14:47Я думаю автор хочет просто логически группировать по смыслу, и в процессе эволюции схемы добавлять рядом с "близкими" полями, чтобы в UI они рядышком были. Скажем, у пользователя есть поля регион, город, улица, номер_дома, мы вышли на международный рынок и хотим добавить страну перед регионом. Для СУБД никакой пользы, для человека - удобство.
Это уж безотносительно того, что менять состав полей на работающей продукционной системе - вообще так себе затея.
А что делать? :) Постоянно в софте только то, что он постоянно меняется. К счастью, разработчики баз данных это понимают, и добавление колонки нынче почти всегда мгновенно и не требует остановки базы данных.
Совместно с SELECT * добавлять колонки не в конец довольно опасно, в конец колонки добавлять гораздо безопаснее. Надо очень аккуратно писать чтобы добавление в середину ничего не поломало. Но в принципе, полезная операция для поддержания таблицы в аккуратном состоянии.
Antohin
03.06.2024 14:47+2Кажется я знаю ответ про прикладную пользу от порядка полей. Буду говорить про oracle, к postgres это скорее всего тоже применимо, но видимо со своими нюансами.
Выделение места в блоке для новой строки зависит от типа полей и их порядка: под null'овые значения типов date и number резервируется место если они "в середине" строки, т.е. за ними не-null-овое значение, и не резервируется если они в конце. Поэтому если у нас есть столбцы в которых мало значений, то сдвинув их в хвост мы сэкономим место. С другой стороны если поле изначально пустое, но рано или поздно будет заполнено, то возможно места в блоке уже не хватит и или хвост строки уедет в другой блок (chained rows) или вся строка. А это и накладные расходы в моменте и дальнейшее замедление доступа в случае chained rows.
В ситуации когда у нас таблица с кучей малозаполненных полей в "хвосте" и мы добавляем not null столбец в конец, длина строки может вырасти в разы. Если это какая-то огромная таблица, это может быть заметно и больно.
stvoid
03.06.2024 14:47Ну, вот помимо нелепого аргумента про "логическое упорядочивание" (хотя я понимаю смысл зачем это люди хотят, но это не имеет смысла по факту) вот это конечно... Вроде как и смысл есть, а вроде как самое яркое проявление ОКР в IT
ptr128
03.06.2024 14:47под null'овые значения типов date и number резервируется место
Не скажу про Oracle, но в PostgreSQL место не резервируется, так как значения полей с NULL значением в кортеже просто отсутствуют. А уж есть значение nullable полей или нет определяется битовой маской в заголовке кортежа.
Что касается изменения порядка полей в таблице, то тут следует понимать, что этот порядок прибит гвоздями в уже имеющихся кортежах в страницах данных. И если позволить менять этот порядок по желанию пользователя, то это потребует либо перестроения всех кортежей этой таблицы в БД, либо хранения в каждом кортеже того порядка полей, который был в нем использован. И то и другое - явно не плюс к производительности.
ky0
03.06.2024 14:47Да, вы всё правильно говорите - но такой глубокий анализ, помноженный на размеры БД, при которых эта экономия актуальна, подразумевает столь же скиллованных ДБА/ДБД, от которых странно ждать постоянного изменения набора полей :)
astentx
03.06.2024 14:47Так ровно по этой причине оракл не может вставить колонку в середину, иначе придется перелопатить все данные и устроить в них chained rows. Так как СУБД все же стала уделом программистов, и запросы типа select * не частые гости в UI (или как минимум являются бомбой замедленного действия), то порядок колонок после добавления чаще всего мало кого волнует. Если очень хочется навести порядок и использовать магию с наллами, то есть как альтернативные способы хранения разреженных данных, так и online redefinition. А вот как в MySQL реализована вставка колонки в середину при конкурентных апдейтах/инсертах и насколько оно действительно хорошо работает - интересный вопрос.
Akina
03.06.2024 14:47А вот как в MySQL реализована вставка колонки в середину при конкурентных апдейтах/инсертах и насколько оно действительно хорошо работает - интересный вопрос.
Начиная с версии 8.0.29, вставка дополнительного поля в любое место InnoDB-таблицы может использовать INSTANT алгоритм и не требует ребилда таблицы. В более старых версиях это верно только для случая добавления поля в конец структуры.
Что же до последующих DML, то местоположение поля в структуре не влияет - вообще.
ptr128
03.06.2024 14:47может использовать INSTANT алгоритм и не требует ребилда таблицы
А можно подробней? Метаданные что-ли в каждом кортеже сохраняет?
Akina
03.06.2024 14:47Эээ... поподробнее я даже в Internals не нашёл. Это надо смотреть исходники на предмет того, как точно выглядит структура записи в блоке диска.
Но я просто попробовал - создал таблицу на 2 varchar, положил пару записей, затем вставил между имеющихся третье поле, вставил ещё запись и обновил существующую. А потом посмотрел в гексах. И вижу, что хоть поле вставлено и между двумя существующими, но в тело данных пишется так, словно вставка выполнена в конец структуры. В обеих записях с не-NULL значением третьего поля.
То есть MySQL просто-напросто в метаданных хранит то, как порядок полей таблицы должен выглядеть для пользователя. А реально хранит по-своему.
Но если использовать INSTANT или COPY, то тело переписывается в соответствии с заданным порядком полей.
ptr128
03.06.2024 14:47Спасибо! Получается, MySQL отдельно ведет физический и логический порядок колонок. Вот только порядок полей определяет не только их сортировку в списке, но так же оптимизирует размер кортежа выравниванием. И вторая цель недостижима без полного перестроения всех кортежей при добавлении нового поля не в конец кортежа.
А первую цель можно достигнуть либо пользовательской таблицей, обновляемой в event trigger, либо сортируя по комментариям колонок, размещая в них логический порядок полей в качестве префикса к собственно комментарию.
Akina
03.06.2024 14:47Вот только порядок полей определяет не только их сортировку в списке, но так же оптимизирует размер кортежа выравниванием.
Вот чего ни разу не видел в MySQL - так это дырок в теле записи в файле ради выравнивания. И, поскольку в RM уделено достаточно немало вопросу расхода дисковой памяти, ограничениям и точным (!) расчётам размера записи, а также связанным ограничениям, но при этом нигде нет ни слова ни о порядке записей, ни о выравнивании, то я подозреваю, что выравнивание просто не используется. Всё пишется вплотную, без зазоров, минимизируя расход памяти (и дисковой, и оперативной). Впрочем, не скажу за блоки метаданных. А блоки переполнения по определению монопольны для записи.
ptr128
03.06.2024 14:47Вот чего ни разу не видел в MySQL - так это дырок в теле записи в файле ради выравнивания.
В PostgreSQL наоборот, этому уделяется большое внимание. Операции с невыровненными данными могут заметно терять в производительности.
точным (!) расчётам размера записи
Непонятно, как считать размер записи, если он зависит не только от полей переменной длины, но так же от того, NULL значение у поля или нет. В PostgreSQL так же добавляется TOAST. То есть в зависимости от данных поля они могут быть inline или в таблице TOAST, сжатыми или нет.
mentin
03.06.2024 14:47+2Во многих ситуациях в PostgreSql для этого можно использовать TYPE - логически сгруппированные поля засунуть в общий композиционный тип, и добавлять новые поля в него, а не в схему основной таблицы.
postgres=# create type my_type as (a int, b int); CREATE TYPE postgres=# create table my_table (x int, y my_type, z int); CREATE TABLE postgres=# alter type my_type add attribute c int; ALTER TYPE postgres=# insert into my_table values (1, (2, 3, 4), 5); INSERT 0 1 postgres=# select * from my_table; x | y | z ---+---------+--- 1 | (2,3,4) | 5 (1 row)
nin-jin
03.06.2024 14:47+5Ок, объясняю:
В постгре мета-информация хранится в табличках, что очень гибко, ибо позволяет использовать всю мощь SQL для работы с этими данными, а не зазубривать ограниченные кастомные команды.
Мутирующие операции через SELECT - это зашквар, конечно, но не критичный.
ON CONFLICT более гибок. А гибкость эта нужна. Например, поле created_time обновлять не надо, а updated_time - надо.
despair Автор
03.06.2024 14:47+1В MySQL также давно появилась служебная база`information_schema`, из которой также можно извлечь всё что нужно. При этом остаются удобные и легко-запоминающиеся шорткаты SHOW, для тех, кто хочет быстро что-то посмотреть.
baldr
03.06.2024 14:47+4удобные и легко-запоминающиеся шорткаты SHOW
Куда уж короче чем "\l", "\d", "\dn".. ?
SHOW нет в ANSI SQL - это такая же отсебятина движка, как и шорткаты в postgresql.
Akina
03.06.2024 14:47+1Запросы SHOW - это всего лишь набор предопределённых представлений. Так что нет тут никакой "отсебятины движка", есть просто дополнительные инструменты. Весьма удобные для администратора и разработчика.
Ведь те же дополнительные модули в составе Постгресса (геомодуль или там модуль линка на внешний сервер), отсутствующие в ANSI SQL, вам не приходит в голову называть отсебятиной?
panzerfaust
03.06.2024 14:47+9для тех, кто хочет быстро что-то посмотреть
Кто эти люди? ДБА? Мне, разработчику, эта кухня нужна была пару раз. Еще раз понадобится - один хрен буду гуглить "как сделать ХХХ в YYYSQL". От наличия алиасов вот ни холодно ни жарко.
Вы бы лучше вместо агрессивного дисклеймера описали вашу область деятельности и проблематику, и почему вас вообще волнуют вопросы быстрого доступа к DDL и порядка столбцов. Иначе это похожи на автообзоры, где машину "бракуют" за то, что в багажнике подсветки нет.
despair Автор
03.06.2024 14:47В MySQL также давно появилась служебная база
information_schema, из которой также можно извлечь всё что нужно. При этом остаются удобные и легко-запоминающиеся шорткаты SHOW, для тех, кто хочет быстро что-то посмотреть.
Akina
03.06.2024 14:47+3Ну с большинством того, что перечислено в статье, я в принципе даже готов мириться.
Но вот от чего я реально зверею - так это от отсутствия
SHOW CREATE {object}. Выгрузка через pg_dump или иные способы - это всё костыли, причём местами очень даже кривые. Попробуйте получить полный CREATE TABLE, не требующий последующих ALTER TABLE и CREATE INDEX!А ещё мне категорически не понравилась невозможность в ON CONFLICT обрабатывать событие дублирования при наличии нескольких ограничений уникальности. Сталкивался, правда, всего один раз, но видели бы вы, какого монстра пришлось рожать!
А вот в MySQL явно нехватает общепринятого понятия SCHEMA - тут оно просто алиас DATABASE. И очень неудобна литеральная, а не флаговая, обработка привилегий при работе с GRANT/REVOKE - и это при том, что в
mysql.userоно всё разобрано.Впрочем, на то, что есть в одной СУБД и отсутствует в другой, можно жаловаться долго.
yegreS
03.06.2024 14:47+1В Postgresql появилась новая операция MERGE, она гораздо более гибкая чем ON CONFLICT https://www.postgresql.org/docs/current/sql-merge.html
cadmi
03.06.2024 14:47+1Напомните, пожалуйста, как в mysql базу переименовать?
Akina
03.06.2024 14:47+2Нет такой фамилии...
cadmi
03.06.2024 14:47+1Я думаю, что все поняли, что это был легкий троллинг автора с его
> можно перемещать таблицы между базами данных с помощью RENAME TABLE
Но он предпочел проигнорировать вопрос, ведь в качестве ответа придется писать принятый у любителей mysql башизм "сделать запрос к information_schema, получить список всех таблиц, создать новую базу и прокрутить в цикле по именам таблиц RENAME TABLE из старой базы в новую".
А если серьезно, то эта "богатая возможность" (переносить таблицы между базами) - всего лишь следствие отсутствия namespace isolation у мурсиков. Это не фича, на самом деле, а проблема. Но кого это волнует, ведь зато есть удобные SHOW DATABASES;
posledam
03.06.2024 14:47+2Что же выбрать, качественную и надёжную подвеску, или бардачок для перчаток? Блин, какой сложный выбор.
tuxi
Не знаю как сейчас, а 20+ лет назад mysql был рекордсменом по минимальному времени выполнения insert, причем с большим отрывом от 2го места. Интересно как сейчас дела обстоят.
Kerman
20+ лет назад там был myisam, так что неудивительно. Да, был был быстрый инсерт, но в ущерб надёжности, транзакционности и прочим "ненужным" вещам. Сейчас с InnoDb всё примерно как у всех. Зависит от сценария, ключей, индексов, хранилища, памяти.
tuxi
Ну это понятно, что без транзакций. Но другие доступные бд сравнивались тоже с отключенными транзакциями. В 2002..2003 году делали свою ORM и там требовалось писать event log быстро, практически как пишет счетчик посещения страниц. Вот mysql победил просто с громадным отрывом. Помню, что даже какую то файловую бд тестили.
Politura
20 лет назад там там не было транзакций, оттого и была самая быстрая запись.
Думаю, теперь, как повзрослела, уже не самая быстрая.
Tzimie
Скажите у MSSQL delayed durability и он тоже так будет.
galaxy
С учетом потабличных блокировок при UPDATE (и некоторых INSERT) быстрая запись там специфическая (myisam).
При сколько-нибудь существенной concurrency от нее ничего не останется.