Comments 108
Не знаю как сейчас, а 20+ лет назад mysql был рекордсменом по минимальному времени выполнения insert, причем с большим отрывом от 2го места. Интересно как сейчас дела обстоят.
20+ лет назад там был myisam, так что неудивительно. Да, был был быстрый инсерт, но в ущерб надёжности, транзакционности и прочим "ненужным" вещам. Сейчас с InnoDb всё примерно как у всех. Зависит от сценария, ключей, индексов, хранилища, памяти.
Ну это понятно, что без транзакций. Но другие доступные бд сравнивались тоже с отключенными транзакциями. В 2002..2003 году делали свою ORM и там требовалось писать event log быстро, практически как пишет счетчик посещения страниц. Вот mysql победил просто с громадным отрывом. Помню, что даже какую то файловую бд тестили.
20 лет назад там там не было транзакций, оттого и была самая быстрая запись.
Думаю, теперь, как повзрослела, уже не самая быстрая.
Спасибо за сравнение, увидел для себя отдельные интересные моменты. Но, честно говоря, немного поверхностно получилось, ничего критичного не увидел (а хотелось бы). Я MySQL не пользовался и все перечисленное не вызывает особых вопросов.
О, как. MySql оказывается несерьёзная. И в каком месте она несерьёзная?
Если стоит задача хранить реляционные данные и получать к ним доступ, то чем плоха MySql? Я вот по наивности считал, что именно хранение данных - это первейшая задача СУБД.
Тот же оракл не имеет интовых типов данных. Да, там есть INTEGER, для совместимости с SQL'92, но это тот же самый number(0, 38). То есть число с переменной длиной, которое не запихнёшь с гарантией ни в один из типов современных бэкенд-языков. Это признак серьёзности?
Да, ни оракл, ни постгрес, ни mssql не умеют в add column after. У всех отмазка "ну это чтобы не пересоздавать таблицу". Мне пофиг на пересоздание, у меня там три записи (или вообще нет ни одной). Почему я не могу встроенным синтаксисом вставить столбец куда мне надо? Да, мне это важно, потому что я сортирую столбцы по их назначению. Мне потом их проще искать.
Почему у оракла 30к символов ограничение в nvarchar (при флаге extended, без него 12к)? Это из серии "640кб хватит всем"? Не, я понимаю, что можно пихать текст в блобы, никто вроде не запрещает. Но у того же мускуля 64к символов в TEXT и 4ккк символов в LONGTEXT. Неужели сложно?
Потому что инструменты созданы для работы. Иногда пересоздание таблицы в проде это куча времени, а бизнесу работать надо. Для удобной работы с полями есть другие инструменты. Так же как нет смысла закладываться на порядок записи данных в таблицу.
Очень странный аргумент. Почему базу данных должен беспокоить простой бизнеса? Это вообще не её, базаданново дело. Она понятия не имеет, используются эти таблицы или нет. Насколько критичен простой именно сейчас, и вообще может быть сейчас выходной.
БД - инструмент разработчика. Разработчик делает инструмент для бизнеса. И позвольте разработчику самому решать, какая должна быть структура данных.
Я часто работаю с "сырыми" данными в sql, когда надо быстро посмотреть, в каком состоянии находятся объекты. Иногда таблицы довольно большие, 20, 30 и иногда 100+ столбцов. Мне УДОБНО, когда столбцы, относящиеся к одному функционалу находятся рядом, тогда не приходится скроллить таблицу или изобретать вьюшки на каждый случай. А на каждый случай вьюшек не напасёшься.
NS SQL management studio умеет в add column after. ваша правда - через пересоздание таблицы, индексов и копировании. данных. н для польщователя этот процесс прозрачный если количество строк невелико.
есть ли гарантия того что mysql не делает тоже самое - insert column через пересоздание таблицы?
MySql делает это ТОЧНО через пересоздание таблицы. Что я прекрасно знаю и это меня ничуть не волнует.
Через пересоздание любая СУБД умеет. Различия лишь в размерах синтаксического сахара для этих целей. Напишите себе процедуру, которая добавляет поле не в конец таблицы через её пересоздание и радуйтесь.
Плюс к этой процедуре надо написать интерфейс для удобного редактирования структуры таблицы, чтобы можно было драгдропом поменять местами колонки. Потом механизм сопоставления версий структур данных для генерации миграций. Ну и сразу удобный data management, чтобы можно было в одном месте и данные в таблице поменять )
Хорошая идея. Я этим как раз и занимаюсь. Но это дорого и долго.
Почему я не могу встроенным синтаксисом вставить столбец куда мне надо? Да, мне это важно, потому что я сортирую столбцы по их назначению. Мне потом их проще искать.
Так сделайте 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+ терабайт текста, если очень надо
Я упорядочиваю столбцы по их функционалу для того, чтобы снизить когнитивную нагрузку при работе с данными, а вы предлагаете насоздавать вьюшек на каждую большую таблицу, чтобы потом следить за соответствием этих вьюшек таблицам. Я не враг себе, мне такие костыли не нужны.
мне такие костыли не нужны
Если вас настолько волнует представление - это не проблема БД, а проблема инструментов, которые вы используете для просмотра БД.
Поэтому переупорядочивание колонок внутри БД - это костыль, который вы используете чтобы пофиксить проблему инструмента.
Это проблема БД, потому что она хранит структуру таблиц. Внешние инструменты будут вынуждены синхронизироваться с ней и хранить данные о порядке вне структуры.
В любом случае есть факт. MySql позволяет встроенным синтаксисом менять расположение колонок, а оракл/мс/постгрес - нет. Вот и всё. Не надо мне доказывать, что мне это не нужно. Не надо предлагать костылями подпереть. Не надо скидывать ответственность на другие инструменты.
Это проблема БД, потому что она хранит структуру таблиц. Внешние инструменты будут вынуждены синхронизироваться с ней и хранить данные о порядке вне структуры.
Ну так храните пользовательский порядок полей в БД. Например, начинайте комментарий к полю с числа с ведущим нулем, определяющим порядок отображения поля.
А что делать, если вы не одинокий волк с уолл стрит, а работаете на работе? Ну знаете, это такое место где ещё другие люди есть.
И у других людей есть своё представление о прекрасном.
Почему ваше представление должно быть единственно правильным и ему должны следовать другие люди? Причем речь именно о визуальном представлении на вашем мониторе, которое абсолютно ни на что не влияет, кроме ваших приколов.
Не надо скидывать ответственность на другие инструменты.
Так и не скидывайте ответственность за вывод на экран на базу данных :)
Почему ваше представление должно быть единственно правильным
А и не должно быть. Это вообще-то подмена тезиса. Я говорю про ВОЗМОЖНОСТЬ упорядочивания колонок, а вы мне про эгоцентризм. Если работаешь с людьми - тогда учитывай интересы людей.
Я обычно работаю над проектом один, и в этом режиме мне абсолютно плевать на мнение коллег о структуре данных.
Так ВОЗМОЖНОСТЬ есть и в оракле и в постгресе. Ну да, допустим она реализуется не одной командой а тремя, но вам никто не запрещает.
Не тремя. Даже не десятью. Нужно создать временную таблицу, перенести все данные, убрать индексы, убрать констрейнты, убрать внешние ключи, убрать триггеры. Дропнуть таблицу, переименовать временную, вернуть индексы, вернуть констрейнты, внешние ключи, пересоздать триггеры.
Вот чтобы всё это сделать нужно заморочиться. А чтобы это сделать без ошибок и для общего случая, нужно заморочиться сильно, прям очень сильно.
При желании, всё это можно автоматизировать и выполнять одной строкой. Только зачем?
Не тремя. Даже не десятью.
Пожалуйста:
Создаём таблицу
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;
А зачем вы с данными работаете в таком количестве? Да мне приходится залазить в базу чтобы что-то проверить парни разработке, но это мизер времени занимает и посмотреть в сыром виде не проблема. А так делайте тесты на работу с бл и не придется проверять все.
Да, ни оракл, ни постгрес, ни mssql не умеют в add column after. У всех отмазка "ну это чтобы не пересоздавать таблицу"
Ну у постгреса отмазка скорее "мы не против, просто геморроя оказывается многовато для такой малоактуальной фичи, и никто пока не заморочился аккуратно сделать". Вот, например, подход к снаряду 2014 года - почитайте простыню, если интересно, почему не шмогли.
Мне пофиг на пересоздание, у меня там три записи
Вот и делайте через пересоздание таблицы (или через добавление/удаление колонок, как в статье выше описано).
Нет и нет этой фишки, штука явно не ключевая для СУБД, чего срач разводить.
Сегодня 3 записи, а завтра миллион. И когда вы не единственный разработчик, то очень важен однотипный подход к миграциям, когда гранные одинаково надежно и максимально не блокирующе меняют бд. А если хочется понятней, то в ПГ есть комментарии к столбцам.
Ну назвать мускуль несерьезной бд - это тоже не совсем верно. Просто считается, что раз ее пользуют в куче хоум-пет проектов "сайт 30 страничек на коленке", то это типа несерьезно. Но я видел проекты, где мускуль держал серьезные обьемы данных.
Зашел почитать про сравнение производительности индексов, инсертов и апдейтов, а прочитал про то, что алиасов не завезли.
В чём вопрос? Почему СУБД, обладающая объективными преимуществами, но неудобная для вас по каким-то мелочам, популярнее?
Воспринимать серьёзно наезды на невозможность вставки колонки в середину таблицы (расскажите, зачем это может понадобиться, пожалуйста), вакуум, какое-то там форматирование консольного вывода и на неудобные настройки аутентификации по умолчанию - сложновато.
Ты хочешь добавить новое поле на этапе уже работающей системе, при этом поля сгруппировать по смыслу, а не добавлять всё в конец. А то получится хаотичная портянка полей, где трудно уловить смысл и логику данных.
Если очень хочется двигать поля зачем-то, то это сделать можно.
Если хочется чтоб красиво выглядело - можно просто view поверх таблицы сделать, и это будет работать в любой бд
Что такое "поля, сгруппированные по смыслу"? Какую прикладную пользу для работы СУБД эта группировка несёт?
Это уж безотносительно того, что менять состав полей на работающей продукционной системе - вообще так себе затея. А делать это настолько часто, чтобы получилась хаотичная портянка - так себе затея в квадрате.
Возможно, это следствие какой-нибудь ошибки в дизайне приложения, в стиле «мы используем ‘select * from …’, а поля присваиваем по индексу ради производительности».
Я думаю автор хочет просто логически группировать по смыслу, и в процессе эволюции схемы добавлять рядом с "близкими" полями, чтобы в UI они рядышком были. Скажем, у пользователя есть поля регион, город, улица, номер_дома, мы вышли на международный рынок и хотим добавить страну перед регионом. Для СУБД никакой пользы, для человека - удобство.
Это уж безотносительно того, что менять состав полей на работающей продукционной системе - вообще так себе затея.
А что делать? :) Постоянно в софте только то, что он постоянно меняется. К счастью, разработчики баз данных это понимают, и добавление колонки нынче почти всегда мгновенно и не требует остановки базы данных.
Совместно с SELECT * добавлять колонки не в конец довольно опасно, в конец колонки добавлять гораздо безопаснее. Надо очень аккуратно писать чтобы добавление в середину ничего не поломало. Но в принципе, полезная операция для поддержания таблицы в аккуратном состоянии.
Кажется я знаю ответ про прикладную пользу от порядка полей. Буду говорить про oracle, к postgres это скорее всего тоже применимо, но видимо со своими нюансами.
Выделение места в блоке для новой строки зависит от типа полей и их порядка: под null'овые значения типов date и number резервируется место если они "в середине" строки, т.е. за ними не-null-овое значение, и не резервируется если они в конце. Поэтому если у нас есть столбцы в которых мало значений, то сдвинув их в хвост мы сэкономим место. С другой стороны если поле изначально пустое, но рано или поздно будет заполнено, то возможно места в блоке уже не хватит и или хвост строки уедет в другой блок (chained rows) или вся строка. А это и накладные расходы в моменте и дальнейшее замедление доступа в случае chained rows.
В ситуации когда у нас таблица с кучей малозаполненных полей в "хвосте" и мы добавляем not null столбец в конец, длина строки может вырасти в разы. Если это какая-то огромная таблица, это может быть заметно и больно.
экономия на спичках
Ну, вот помимо нелепого аргумента про "логическое упорядочивание" (хотя я понимаю смысл зачем это люди хотят, но это не имеет смысла по факту) вот это конечно... Вроде как и смысл есть, а вроде как самое яркое проявление ОКР в IT
под null'овые значения типов date и number резервируется место
Не скажу про Oracle, но в PostgreSQL место не резервируется, так как значения полей с NULL значением в кортеже просто отсутствуют. А уж есть значение nullable полей или нет определяется битовой маской в заголовке кортежа.
Что касается изменения порядка полей в таблице, то тут следует понимать, что этот порядок прибит гвоздями в уже имеющихся кортежах в страницах данных. И если позволить менять этот порядок по желанию пользователя, то это потребует либо перестроения всех кортежей этой таблицы в БД, либо хранения в каждом кортеже того порядка полей, который был в нем использован. И то и другое - явно не плюс к производительности.
Да, вы всё правильно говорите - но такой глубокий анализ, помноженный на размеры БД, при которых эта экономия актуальна, подразумевает столь же скиллованных ДБА/ДБД, от которых странно ждать постоянного изменения набора полей :)
Так ровно по этой причине оракл не может вставить колонку в середину, иначе придется перелопатить все данные и устроить в них chained rows. Так как СУБД все же стала уделом программистов, и запросы типа select * не частые гости в UI (или как минимум являются бомбой замедленного действия), то порядок колонок после добавления чаще всего мало кого волнует. Если очень хочется навести порядок и использовать магию с наллами, то есть как альтернативные способы хранения разреженных данных, так и online redefinition. А вот как в MySQL реализована вставка колонки в середину при конкурентных апдейтах/инсертах и насколько оно действительно хорошо работает - интересный вопрос.
А вот как в MySQL реализована вставка колонки в середину при конкурентных апдейтах/инсертах и насколько оно действительно хорошо работает - интересный вопрос.
Начиная с версии 8.0.29, вставка дополнительного поля в любое место InnoDB-таблицы может использовать INSTANT алгоритм и не требует ребилда таблицы. В более старых версиях это верно только для случая добавления поля в конец структуры.
Что же до последующих DML, то местоположение поля в структуре не влияет - вообще.
может использовать INSTANT алгоритм и не требует ребилда таблицы
А можно подробней? Метаданные что-ли в каждом кортеже сохраняет?
Эээ... поподробнее я даже в Internals не нашёл. Это надо смотреть исходники на предмет того, как точно выглядит структура записи в блоке диска.
Но я просто попробовал - создал таблицу на 2 varchar, положил пару записей, затем вставил между имеющихся третье поле, вставил ещё запись и обновил существующую. А потом посмотрел в гексах. И вижу, что хоть поле вставлено и между двумя существующими, но в тело данных пишется так, словно вставка выполнена в конец структуры. В обеих записях с не-NULL значением третьего поля.
То есть MySQL просто-напросто в метаданных хранит то, как порядок полей таблицы должен выглядеть для пользователя. А реально хранит по-своему.
Но если использовать INSTANT или COPY, то тело переписывается в соответствии с заданным порядком полей.
Спасибо! Получается, MySQL отдельно ведет физический и логический порядок колонок. Вот только порядок полей определяет не только их сортировку в списке, но так же оптимизирует размер кортежа выравниванием. И вторая цель недостижима без полного перестроения всех кортежей при добавлении нового поля не в конец кортежа.
А первую цель можно достигнуть либо пользовательской таблицей, обновляемой в event trigger, либо сортируя по комментариям колонок, размещая в них логический порядок полей в качестве префикса к собственно комментарию.
Вот только порядок полей определяет не только их сортировку в списке, но так же оптимизирует размер кортежа выравниванием.
Вот чего ни разу не видел в MySQL - так это дырок в теле записи в файле ради выравнивания. И, поскольку в RM уделено достаточно немало вопросу расхода дисковой памяти, ограничениям и точным (!) расчётам размера записи, а также связанным ограничениям, но при этом нигде нет ни слова ни о порядке записей, ни о выравнивании, то я подозреваю, что выравнивание просто не используется. Всё пишется вплотную, без зазоров, минимизируя расход памяти (и дисковой, и оперативной). Впрочем, не скажу за блоки метаданных. А блоки переполнения по определению монопольны для записи.
Вот чего ни разу не видел в MySQL - так это дырок в теле записи в файле ради выравнивания.
В PostgreSQL наоборот, этому уделяется большое внимание. Операции с невыровненными данными могут заметно терять в производительности.
точным (!) расчётам размера записи
Непонятно, как считать размер записи, если он зависит не только от полей переменной длины, но так же от того, NULL значение у поля или нет. В PostgreSQL так же добавляется TOAST. То есть в зависимости от данных поля они могут быть inline или в таблице TOAST, сжатыми или нет.
Во многих ситуациях в 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)
Ок, объясняю:
В постгре мета-информация хранится в табличках, что очень гибко, ибо позволяет использовать всю мощь SQL для работы с этими данными, а не зазубривать ограниченные кастомные команды.
Мутирующие операции через SELECT - это зашквар, конечно, но не критичный.
ON CONFLICT более гибок. А гибкость эта нужна. Например, поле created_time обновлять не надо, а updated_time - надо.
В MySQL также давно появилась служебная база`information_schema`, из которой также можно извлечь всё что нужно. При этом остаются удобные и легко-запоминающиеся шорткаты SHOW, для тех, кто хочет быстро что-то посмотреть.
удобные и легко-запоминающиеся шорткаты SHOW
Куда уж короче чем "\l", "\d", "\dn".. ?
SHOW нет в ANSI SQL - это такая же отсебятина движка, как и шорткаты в postgresql.
Запросы SHOW - это всего лишь набор предопределённых представлений. Так что нет тут никакой "отсебятины движка", есть просто дополнительные инструменты. Весьма удобные для администратора и разработчика.
Ведь те же дополнительные модули в составе Постгресса (геомодуль или там модуль линка на внешний сервер), отсутствующие в ANSI SQL, вам не приходит в голову называть отсебятиной?
для тех, кто хочет быстро что-то посмотреть
Кто эти люди? ДБА? Мне, разработчику, эта кухня нужна была пару раз. Еще раз понадобится - один хрен буду гуглить "как сделать ХХХ в YYYSQL". От наличия алиасов вот ни холодно ни жарко.
Вы бы лучше вместо агрессивного дисклеймера описали вашу область деятельности и проблематику, и почему вас вообще волнуют вопросы быстрого доступа к DDL и порядка столбцов. Иначе это похожи на автообзоры, где машину "бракуют" за то, что в багажнике подсветки нет.
В MySQL также давно появилась служебная базаinformation_schema, из которой также можно извлечь всё что нужно. При этом остаются удобные и легко-запоминающиеся шорткаты SHOW, для тех, кто хочет быстро что-то посмотреть.
Ну с большинством того, что перечислено в статье, я в принципе даже готов мириться.
Но вот от чего я реально зверею - так это от отсутствия SHOW CREATE {object}. Выгрузка через pg_dump или иные способы - это всё костыли, причём местами очень даже кривые. Попробуйте получить полный CREATE TABLE, не требующий последующих ALTER TABLE и CREATE INDEX!
А ещё мне категорически не понравилась невозможность в ON CONFLICT обрабатывать событие дублирования при наличии нескольких ограничений уникальности. Сталкивался, правда, всего один раз, но видели бы вы, какого монстра пришлось рожать!
А вот в MySQL явно нехватает общепринятого понятия SCHEMA - тут оно просто алиас DATABASE. И очень неудобна литеральная, а не флаговая, обработка привилегий при работе с GRANT/REVOKE - и это при том, что в mysql.user оно всё разобрано.
Впрочем, на то, что есть в одной СУБД и отсутствует в другой, можно жаловаться долго.
В Postgresql появилась новая операция MERGE, она гораздо более гибкая чем ON CONFLICT https://www.postgresql.org/docs/current/sql-merge.html
Напомните, пожалуйста, как в mysql базу переименовать?
Нет такой фамилии...
Я думаю, что все поняли, что это был легкий троллинг автора с его
> можно перемещать таблицы между базами данных с помощью RENAME TABLE
Но он предпочел проигнорировать вопрос, ведь в качестве ответа придется писать принятый у любителей mysql башизм "сделать запрос к information_schema, получить список всех таблиц, создать новую базу и прокрутить в цикле по именам таблиц RENAME TABLE из старой базы в новую".
А если серьезно, то эта "богатая возможность" (переносить таблицы между базами) - всего лишь следствие отсутствия namespace isolation у мурсиков. Это не фича, на самом деле, а проблема. Но кого это волнует, ведь зато есть удобные SHOW DATABASES;
Что же выбрать, качественную и надёжную подвеску, или бардачок для перчаток? Блин, какой сложный выбор.
Условные 20 лет назад уже небыло смысла выбирать mysql поскольку был релизнут постгрес 8.0 в котором были кардинальные изменения скорости и стабильности. А вот условные лет 25 назад во времена 7.х тогда он был несколько падуч и тормозен и тогда я тогда тоже пользовал это поделие под названием mysql ... вместе с php. Но время идет прогресс не стоит на месте и при релизе пг 8.0 тормознутость пропала. А когда вошел во вкус in db business logic так вообще mysql бы считать анахронизмом вроде пхп. Но что делать есть вполне уважаемые люди что до сих пор на перле пишут ?. Правда последнее время количество комитов в постгрес от индусов, не сказать чтобы не напрягало...
Много лет (10+) используем обе БД. Триггеры и хранимые процедуры практически не используем, т.к. вся логика на бекэнде. Единственная причина, по которой не перешли полностью на Postgres - наше нищебродство :) У MySQL есть возможность делать MyISAM таблицы, которые не поддерживают транзакции, констрейнты, ломаются, блокируются только целиком, но занимают в два раза меньше места. А еще их можно сжать и тогда вообще класс. Основную же боль MySQL нам причиняет двумя способами:
DDL не поддерживают транзакции. В результате падение апдейт скрипта превращает рутину в длительное приключение с возвратом к предыдущей версии.
субъективно планировщик запросов работает заметно хуже и на селекте с 4-5 джойнами можно внезапно получить тейблскан вместо поиска по индексам. Обычно лечится анализом таблиц.
Вероятно и бакапы не используете для экономии места? Это подход. Если данные не очень нужны.
У MySQL есть возможность делать MyISAM таблицы, которые не поддерживают транзакции, констрейнты, ломаются, ...
У постгри есть unlogged таблицы, которые примерно так же себя ведут
А еще их можно сжать и тогда вообще класс
Если в бд хранится что-то очень жирное, PG сжимает страницы в TOAST на лету по умолчанию, при этом не теряется транзакционность и все остальное
Если в бд хранится что-то очень жирное, PG сжимает страницы в TOAST на лету по умолчанию, при этом не теряется транзакционность и все остальное
Это другое (с) В MySQL можно с помощью myisampack сжимать MyISAM таблицы. Они становятся ридонли и при запросе по ним в память загружаются только запрашиваемые столбцы. Иногда это дает впечатляющий рост производительности.
Напомни, как в мурсиках выгрузить схему таблицы в анси-режиме? Я напомню: это такой режим, в котором целочисленные типы не сопровождаются уродскими скобками с числами внутри, и которые, естественно, не поддерживаются ни одним другим движком (ну, возможно, почти ни одним)?
Что там в мурсиках с COPY TO/FROM? А, всё так же портянки VALUES() в дампах.
FLUSH PRIVILEGES не имеет аналога в PostgreSQL, потому что только в мурсиках догадались изменения доступа делать максимально тупым образом: они вне транзакции, то есть, откатить нельзя, но при этом недоступны другим сессиям. pg_reload_conf(), естественно, никоим образом не является даже отдаленно аналогом.
Порядок столбцов: SELECT к1, к2, к3, …, где к1 — колонка, которую я хочу видеть первой, к2 — второй, к3 — третьей, и так далее, причем, порядок этот я выбираю произвольно во время запроса, могу менять от запроса к запросу. А «программисты мурсиков на HeisiSQL», конечно же, не нужны, если только компании не наплевать на качество.
И да, постгресс — говно, но надо хотя бы знать, где именно, а не алиасы с недосубд для personal home page сравнивать.
Почему-то никто в комментах не обозначил, что проблема с bloat-ом таблиц...раздута (простите).
Это вот все будет проявляться на каких-то довольно нагруженных и специфических сценариях. В 99% случаях auto-vacuum (который по умолчанию включен) сделает за вас всю работу и вы даже не узнаете что там да как.
Да нет, вполне может стать проблемой. Постгрес из коробки, доку не читаем...
Ну хз. Можете привести обычный кейс когда это всплывет?
Cоздаём табличку, не трогаем fillfactor, начинаем апдейтить. Можно ещё автовакуум отключить, чтобы побыстрее было.
Как я и написал, если у вас не дикий хайлоад (а судя по посту, до этого далеко) - то с апдейтами стандартные настройки справятся. У меня лично примерно 200-500 rps в одну таблицу круглосуточно(в зависимости от пользователей). Ниче не настраивал, все ок уже…ну долго в общем.
Если сам отключаешь авто вакуум, вероятно знаешь что делаешь, тут уж бд ни при чем.
Опять же, не один только авто вакуум исправляет ситуацию. Если у вас нет долгих висящих транзакций для которых требуется держать старые версии строк, оно же переиспользует место в страницах таблицы автоматически. Забыл как это у них называется, но вы поняли.
Справятся конечно, просто табличка вырастет в размерах вдвое, со всеми вытекающими.
оно же переиспользует место в страницах таблицы автоматически
Пока какой-нибудь вакуум не пройдёт - не получится. Кто-то должен пометить строки как свободное место.
Я ж написал, не только автовакуум: https://habr.com/ru/companies/postgrespro/articles/449704/
Как по мне, все описанные удобства MySQL - мелочи.
Важнее масштабируемость, надежность и ANSI SQL, вместо fancy shortcuts.
(еще не на пенсии, но с базками 30+ лет)
Кстати, "говно" а не "гавно". :)
Спросил об этом ChatGPT:
Дальше , не читал
А где у MySQL массивы? )
"Движок" для хранения таблиц у PostgreSQL действительно не самый современный, есть и другие проблемы. Но они либо решаемые, либо несущественные.
PostgreSQL ценят прежде всего за предсказуемость, надёжность, огромное кол-во возможностей, расширяемость.
Psql можно очень сильно адаптировать под себя через psqlrc: сделать цветное приглашение командной строки, прикрутить pspg для отображения и навигации (!) в табличных данных, можно "налепить" читабельных команд типа :databases, :tables, если короткие эквиваленты не нравятся.
Выравнивание в расширенном выводе почему-то не работает только у Вас )
А где у MySQL массивы?
А зачем? Это ж нереляционная конструкция. Сродни весьма обоснованно заплёванным multivalued-полям в MS Access. То, что в одном диалекте бред и нафиг не нужно, в другом - огромное количество возможностей? ну как-то не очень. Тут просто повезло, что в одном диалекте в дополнение создана куча инструментов для использования, почему удобно и вообще признаётся, тогда как в другом они так и остались бедным родственником.
Странное сравнение, если честно. Взяты довольно таки "узкие" области, которые не то чтобы совсем на фиг никому не нужны, но попахивают аргументами для срача в курилке, не более того
Что важно в реале:
Стоимость решения как в моменте, так в TCO на Х лет. Сорян, но никто не будет выбирать базу, только потому что кому-то не нравится, как задачу можно решить. Можно? Да! Выполнять, за это эксперту и деньги платят. А вот чистые деньги за "ядра чистый изумруд" будут очевидно присутствовать и будут фактором для выбора
Возможность покрыть нефункциональные требования. Проектов много, требований много. Все сводить к сферическому коню в вакууме наверное не стоит, но это может быть show stopper, либо опять же, решение есть, но стоит денег
Все остальное реально неважно. девелоперы часто базу видят через призму какого-то ORM высокоуровневого фрйемворка, большинство задач в целом не эпичные от слова совсем и отлично будут жить на любой базе
Поправьте в статье, пожалуйста.
Если сначала сделать \pset format unaligned, а потом \x, то, конечно, будет невыровненный вывод. Но если вернуть \pset format aligned, то \x все корректно и красиво выравнивает.
А то получится хаотичная портянка полей, где трудно уловить смысл и логику данных.
COMMENT: https://www.postgresql.org/docs/16/sql-comment.html
Тут уже много сказано, поэтому маленькое замечание.
Использовал и то, и другое в нагруженных проектах и с большими и очень большими БД. Если кратно: MySQL придуман, чтобы развлекаться с ним самим, постгрес придуман для того, чтобы развлекаться с данными.
Разбираться, почему запрос тормозит в мыскле - это отдельное развлечение, в постгресе же выхлоп EXPLAIN ANALYZE в подавляющем большинстве случаев будет более чем достаточен.
А для тех, кто не умеет читать этот выхлоп с листа, есть еще и https://explain.tensor.ru/
Хм, а почему никто еще не возразил, что replace into != on conflict update:
REPLACEworks exactly likeINSERT, except that if an old row in the table has the same value as a new row for aPRIMARY KEYor aUNIQUEindex, the old row is deleted before the new row is inserted
Не вздумайте делать replace в таблице с foreign key on delete cascade.
На самом деле REPLACE INTO в MySQL используется весьма нечасто.
Гораздо чаще применяется INSERT .. ON DUPLICATE KEY UPDATE. Он даже лучше и гибче, чем постгрессовский ON CONFLICT, ибо отслеживает все имеющиеся на таблице ограничения уникальности.
Ну и ничем не отличается от Postgres
Ну как же не отличается? Вот обычная таблица - но в ней два ограничения уникальности. В таблицу выполняется вставка, причём дублирование может возникнуть по любому из двух ограничений.
Если в ON CONFLICT указать только одно ограничение - то обнаружение второго (до или после UPDATE) вызовет ошибку, и операция не будет выполнена. Указать два ограничения - нельзя, синтаксисом не предусмотрено (получим вульгарный syntax error). Вообще не указать тоже нельзя, и по той же причине (получим ON CONFLICT DO UPDATE requires inference specification or constraint name, хотя в описании синтаксиса объект_конфликта указан как необязательный).
Или я неправильно знаю, и есть способ? если так - поделитесь, пожалуйста. Например, вот тут: https://dbfiddle.uk/gCTNSGBS
INSERT .. ODKU это вполне способен переварить: https://dbfiddle.uk/1Ztr669E
Это какой-то позор... (с)
Гораздо более развитый DDL (Data Definition Language) и DCL (data control language): SHOW DATABASES, SHOW TABLES, SHOW CREATE TABLE и т.п.
это не DDL и не DCL. И нигде ниже не была продемонстрирована "гораздо большая развитость".
Data Control Language
...
помимо того, что это не DCL, а чем, собственно, не устроили эквиваленты psql (\l, \d и иже с ними)?
Эти вещи, скорее, забота клиента, и вставлять их в язык - решение неоднозначное (при этом, конструкции вроде SHOW CREATE TABLE, выдающие в виде DML запроса определение объекта, действительно полезны и в PG их подчас не хватает).
FLUSH TABLES
FLUSH HOSTS
...
а объясните людям без 20+ лет опыта в MySQL, на кой хрен вообще эти команды нужны?
ALTER TABLE table_name CHANGE COLUMN
Круто, CHANGE COLUMN умеет переименовывать и сразу тип менять. Развитость налицо. А ежели изменение типа чуть сложнее, чем INT -> BIGINT, по какие правилам преобразование идет? В недоразвитом постгресе можно указать явно через USING.
Офигеть, я не могу добавить новую колонку после другой конкретной колонки, а могу добавить только в конец...
Офигеть, а еще INSERT AFTER row нельзя сделать, 19 век прямо. Порядок колонок в таблице тоже такое себе явление (кто в курсе, что стандарт по этому поводу говорит?).
Если сильно приперло, поменять можно через пересоздание таблицы (CREATE TABLE AS SELECT, например).
В InnoDB (сюрприз!) перестановка колонок вызовет перезапись всей таблицы.
REPLACE INTO
А INSERT ... ON CONFLICT типа только многословностью не угодил?
Угу, в частном (и довольно глупом) случае, который покрывает REPLACE, он неприятно многословен. При этом такой же синтаксис есть и в mysql, только более недоразвитый
А есть в mysql аналог MERGE? (врочем, в PG ее ждали минимум лет 5... олды вспомнят еще как в 8.4 обсуждалось)
Пока писал нужные мне SQL-statements для работы с JSON, пришлось мучать ChatGPT несколько часов подряд, которого я довёл, наверное, до седины — постоянно сыпались те или иные ошибки, интуитивно-очевидные конструкции с JSON не работают.
"Все хреново, но что, не скажу." Можно конкретики? Ибо синтаксис JSONB у них сильно шагнул вперед за последние годы (я сам давно отстал от жизни уже). Можно делать такое:
-- Where jsonb_field was {}, it is now {"a": [{"b": 1}]}
UPDATE table_name SET jsonb_field['a'][0]['b'] = '1';А еще есть jsonpath...
Почему-то в PostgreSQL по умолчанию нельзя аутентифицироваться с паролем.
зато как прекрасны эти GRANT ... TO user@127.0.0.1 в мускуле. Только бы не запутаться, с какого ипшника ходишь...
Необходимость вакуумирования
Единственная адекватная претензия. Время показывает, что архитектура MVCC в постгресе не самая удачная. Все zheap не дождемся...
INSERT INTO the_table ... ON CONFLICTвместоREPLACE INTO
жесть... Ни то, ни другое не стандарт, и внезапно в мускуле есть свой INSERT ON CONFLICT ! А вот MERGE, между прочим, стандарт.
Чатгпт сказало, видите ли, что "PostgreSQL делает акцент на строгом следовании стандартам SQL и обеспечении высокой надежности и целостности данных, даже если это означает отказ от некоторых нестандартных или потенциально небезопасных возможностей", хотя прямо на сайте у них написано: "PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features".
Ну и жаловались бы с таким уровнем аргументации чатгпт, он бы вам посочувствовал.
спасибо за развёрнутый ответ)
А есть в mysql аналог MERGE?
Да, всегда жалел, что нет. А ещё - очень нехватает INSERT/UPDATE/DELETE в CTE и RETURNING.
Когда-то в стародавние времена (лет 20+ назад), когда выбирал для себя базовую СУБД, выбор был между MySQL и PostgreSQL, я на них обоих посмотрел и, несмотря на то, что на тот момент PostgreSQL была существенно мощнее конкурента в плане возможностей
PostgreSQL и сейчас несоизмеримо мощнее конкурента. А вот боль с VACCUUM FULL тех времен я помню очень хорошо.
Ух ты, кто-то ещё использует MySQL в 2024 году
Ладно б ещё хотя б с MariaDB сравнивали, она и то живее и активнее поддерживает фичи из новых редакций стандарта SQL (system-versioned, bitemporal tables и т. д.)
Сейчас MySQL по функционалу уже скорее с SQLite соперничает, чем с PG
Получился НЕСКУЧНЫЙ краткий справочник. Спасибо прочитал с удовольствием.
Нытик. И в статье больше про предпочтения, а не серьезные преимущества.
Почему все лёгкие в использовании системы теряют популярность? Из-за олдов, которые считают, что всё лёгкое это типо не стандарт? Или чтобы у джунов и миддлов отобрать хлеб? Чтобы работать айтишниками могли только накачанные дяди с бородой.
Где-то в качестве РСУБД SQLite достаточно, где-то MySQL, а где-то нужна мощная и расширяемая РСУБД - MS SQL, Oracle или PostgreSQL.
Что касается выбора РСУБД, то ведущую роль играют два соображения. Во-первых, компетенции. Чем меньше разных РСУБД в инфраструктуре, тем проще и дешевле эту инфраструктуру поддерживать и развивать. Во-вторых, так как конечной цели часто не знает даже заказчик, не то что разработчики, то при прогнозируемой вероятности расширения и развития разрабатываемой системы, хочется выбрать РСУБД, которую потом не потребуется менять на более развитую.
Разговоры про "стандарт" мне лично непонятны. Есть стандарт SQL и можно говорить о том, насколько та или иная СУБД соответствует этому стандарту. Если же сравнивать MySQL и PostgreSQL, то это все же идеологически разные СУБД. Причем PostgreSQL может решать любые задачи, которые решает MySQL, пусть порой и с некоторым проигрышем в производительности, а MySQL не всегда способен решить задачи, которые решает PostgreSQL, без катастрофического провала в производительности.
Почему после MySQL мне неудобен PostgreSQL