Комментарии 22
А попробуйте, для интереса, char(1) вместо boolean ещё?
Будет хуже
boolean всегда занимает ровно 1 байт, а char(1) займёт 2 байта
https://www.postgresql.org/docs/current/datatype-character.html
The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string, which includes the space padding in the case of character.
Но есть ещё скрытый системный тип "char", который займёт ровно 1 байт (по той же ссылке ниже)
The type "char" (note the quotes) is different from char(1) in that it only uses one byte of storage, and therefore can store only a single ASCII character. It is used in the system catalogs as a simplistic enumeration type
Интересно, у меня в памяти осталось, что boolean больше, чем один байт, вероятно это в какой-то другой базе данных.
А что в этом чаре скрытого-то?# insert into test0 values ('a'),('b'),('c');
INSERT 0 3
# \d test0
Table "public.test0"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+---------
t | "char" | | |
После замены boolean на char(1) в моем примере таблица увеличилась на 8.5% (с 118 до 128Mb). Запрос значительно замедлился (среднее время выполнения 671 мс вместо 410).
Если же использовать "char" (чего делать не стоит, раз данный тип только для внутренних нужд СУБД), то размер таблицы не меняется (118 Mb), запрос несколько ускоряется (среднее время 370 мс).
"640 Кб будет достаточно, когда то говорил Билл Гейтс". Как он ошибался....
С одной стороны, да, нужно рассчитывать и планировать размерность типов данных. Но не нужно слишком сильно на этом зацикливаться. "У нас было все хорошо, пока не решили улучшить" (с) Я приведу пример. В соседнем королевстве 10млн жителей. Есть несколько операторов... Логично предположить, даже если все все жители решат перейти к одному оператору, у оператора станет 10млн клиентов, возьмем десятикратный запас и заложим ограничение что ну больше 100мнл клиентов в одной бд никогда не будет, это ошибка! 15 лет назад примерно такой метод использовался, везде установили подобные ограничения и чем это закончилось? А вот чем: через несколько лет отменили мобильное рабство, клиенты стали бегать от оператора к оператору, старые закрывались, заводились новые ID. Потом появился m2m и счетчик ID стал крутиться еще быстрее. В итоге при постоянном числе клиентов 9млн счетчик ID уже приближается к 80млн. А у нас ограничение 15 лет назад было заложено в 100М. Десятки тысяч строк, куча таблиц с ссылочной целостностью. Ну не знаю... Я уже 25 лет занимаюсь эксплуатацией, не раз видел аварии из-за переполнения и к подобным ограничениям отношусь не очень. Мир так быстро меняется, не известно что будет через 5 лет.... Сейчас 50% от таблицы сэкономили, через 3 года стоимость хранения упала в 4 раза.... Сомнительно это...
С языка сняли. Когда что-то проектируется для "сказочного королевства" - нужно быть очень осторожным. Вот сейчас нет дробей/корпусов/строений у дома - а завтра раз, вельможа какой "откусит" себе кусок от парка, построит там особняк себе, и придётся его дробный (или буквенный или литерный) номер втискивать как-то в базу.
Хотя - это будет проблема уже тех сотрудников, которые будут работать в то время.
Ну а в целом круто конечно! Большая работа и так подробно все изложено, разобрано на примерах. Читал с интересом)
Осталось еще загнать все boolean в bit(10), а затем выгрузить все данные через COPY TO PROGRAM на stdin 7zip, сохранив их на диске.
А если без шуток, то некоторые данные так и храню, создав таблицу архивов, журнал архивирования, процедуры архивирования и загрузки данных из архива во временную таблицу.
Тема замедления после замены первичного ключа с bigint на int не раскрыта.
Смотря на такие статьи про column tetris всегда возникает вопрос, а что мешает постгрес самому складывать данные самым оптимальным образом? Он ведь даже может запоминать порядок полей при котором была создана таблица чтобы скрывать это от пользователя, но хранить это в оптимальной последовательности. Даже boolean он мог бы складывать в smallint и проводить такие трансформации под капотом.
Тогда при каждом "неоптимальном" add column (например, добавление int-колонки в таблицу с text-колонками), PG пришлось бы автоматически перезаписывать всю таблицу - плохая идея для дефолтного поведения
Ну и в целом, PG среди СУБД - как Linux среди ОС: всегда делает ровно то, что запросил юзер (даже если это выстрел себе в ногу из дробовика)
Полагаю, что с одной стороны проблема некритичная - разработчик может и сам расположить поля в оптимальном порядке, а с другой стороны доработка СУБД, чтобы она сама упорядочивала столбцы гораздо сложнее, чем кажется на первый взгляд.
Например, написали мы команду create table t1( a, b, c), где-то у нас в системе есть запросы вида select * from t1 (да, это плохой стиль написания запросов, но никто же не запрещает так писать). Всегда их результаты были аналогичны результату запроса select a, b, c from t1.
Тут вдруг выходит новый релиз системы, который сам оптимизирует столбцы в таблице. И запрос select * from t1 начинает возвращать столбцы в другом порядке - так, как select c, a, b from t1. Обратная совместимость потеряна, приложение сломано. Значит придется хранить в словаре данных 2 версии описания таблицы - одну точно как было в команде create table, другую - в оптимизированном виде. Поддерживать оба варианта в актуальном состоянии, где-то использовать 1-ю, где-то 2-ю. Не забыв, что бывают приложения, которые тоже читают словарь данных.
Ну и как, тут уже упоминали - при добавлении в сущесвующую таблицу нового столбца системе либо придется перестраивать всю таблицу, что может требовать больших ресурсов, либо таблица будет уже не совсем оптимальна. В последнем варианте, разработчику придется помнить, что при создании таблицы система сама ее оптимальной сделает, а при добавлении новых столбцов - нет.
И запрос select * from t1 начинает возвращать столбцы в другом порядке - так, как select c, a, b from t1. Обратная совместимость потеряна, приложение сломано.
Ни одна нормальная РСУБД не гарантирует порядок полей и порядок строк при возвращении. Если программист надеется на это - то он сам себе злобный буратино, не знающий ничего про реляционную алгебру.
Спасибо за пост. Подскажите утилиты, которые можно запустить на PostgreSQL сервере, которые покажут рекомендации по смене типов колонок.
Менять значение только там, где его надо поменять это правильно, но делать это условием WHERE может оказать очень дорого (банально нет индекса по этому полю). Поэтому лучше пользоваться встроенным системным триггером, который такие обновления подавляет (suppress_redundant_updates_trigger https://www.postgresql.org/docs/16/functions-trigger.html). Из «недостатков» в ответ на попытку изменить значение на то же самое, в ответ придёт 0 rows affected. Это если принимать во внимание комментарий выше, где приложение ломается от перестановки полей.
Для красоты, писать условия для булевых полей можно явно:
WHERE
is_reg_town_verif
OR is_reg_street_verifСогласен, что
WHERE
is_reg_town_verif
OR is_reg_street_verifвыглядит короче, красивее и читается легче, чем
WHERE
is_reg_town_verif = true
OR is_reg_street_verif = trueНасчет триггера, подавляющего изменения не нужных строк - это хорошая возможность (прежде всего если есть подозрения, что в приложении есть и много апдейтов, в которых нет некоторых ограничений), с другой стороны использование триггеров усложняет логику приложения (вижу апдейт всего, по факту нужно помнить, что есть триггер и проапдейтится только то, что нужно). В плане эффективности - если update без where - это в любом случае просмотр всей таблицы, так что добавление ограничения вида
WHERE
is_reg_town_verif = true
OR is_reg_street_verif = trueего не замедлят никаким образом, если по колонкам нет индекса. А если есть, то может и ускорить его.
Логику приложения это точно не усложняет (это не триггеры, которые влияют на данные).
Это экономит ресурсы: размер таблицы на диске (за который тут в статье боролись), CPU, и самое главное io диска. Да ещё и вакуум, который тоже сожрёт процессор и io диска.
>по факту нужно помнить, что есть триггер и проапдейтится только то, что нужно
Зачем это помнить? Надуманные проблемы.
Оптимизация хранения данных в PostgreSQL