Измените varchar на boolean в PostgreSQL

Я начал работать над проектом, где есть довольно большая таблица (около 82 000 000 строк), которая, как мне кажется, очень раздута. Одно из полей определяется как:

consistency character varying NOT NULL DEFAULT 'Y'::character varying

Он используется как логическое значение, значения всегда должны быть ('Y' | 'N').

Примечание: нет проверочного ограничения и т.д.

Я пытаюсь найти причины для оправдания изменения этого поля. Вот что я имею:

Это используется как логическое, поэтому сделайте это. Явный лучше, чем неявный.
Он будет защищать от ошибок кодирования, потому что прямо сейчас там что-то, что можно преобразовать в текст, будет слепо.

Вот мои вопросы.

Как насчет размера/хранения? Db - UTF-8. Итак, я думаю, что в этом плане не так много экономии. Он должен быть 1 байт для boolean, но также 1 байт для 'Y' в UTF-8 (по крайней мере, то, что я получаю, когда проверяю длину в Python). Есть ли какие-либо другие накладные расходы на хранение, которые будут сохранены?
Производительность запроса? Будет ли Postgres получать прирост производительности для причины "=TRUE" и "='Y'"?

Ответ 1

PostgreSQL (в отличие от Oracle) имеет полноценный boolean type. Как правило, флаг "да/нет" должен быть boolean. Это правильный тип для использования!

Как насчет размера/хранения?

В принципе, столбец boolean занимает 1 байт на диске,
в то время как text или character varying (цитируя руководство здесь)...

требование хранения для короткой строки (до 126 байтов) - 1 байт плюс фактическая строка

Это 2 байта для простого символа. Таким образом, вы можете сократить хранение этого столбца пополам.

Фактическое хранение более сложное. Существует некоторая фиксированная накладная плата за стол, страница и строка, есть специальный NULL storage, а для некоторых типов требуется выравнивание данных. Общее воздействие будет очень ограниченным - если вообще это заметно.
Подробнее о том, как измерить фактическое пространство.

Кодирование UTF8 здесь не имеет никакого значения. Основные ASCII-символы совместимы с другими кодировками, такими как LATIN-1.

В вашем случае, согласно вашему описанию, вы должны сохранить NOT NULL constraint, который у вас уже есть, - независимо от базового типа.

Производительность запроса?

Будет немного лучше в любом случае с булевым. Кроме того, немного меньше, логика для boolean проще, а varchar или text также обычно обременены специальными правилами COLLATION. Но не ожидайте многого для чего-то простого.

Вместо

WHERE consistency = 'Y'

Вы можете написать:

WHERE consistency = TRUE

Но, действительно, вы можете упростить просто:

WHERE consistency

Никакой дальнейшей оценки не требуется.

Изменить тип

Преобразование таблицы просто:

ALTER TABLE tbl ALTER consistency TYPE boolean
USING CASE consistency WHEN 'Y' THEN TRUE ELSE FALSE END;

Это выражение CASE сбрасывает все, что не является TRUE ('Y'), на FALSE. Ограничение NOT NULL остается.

Ответ 2

Ни размер хранилища, ни производительность запросов не будут значительно лучше переключаться с одного VARCHAR на BOOLEAN. Хотя вы правы, что технически чище использовать логическое значение, когда вы говорите о двоичном значении, стоимость изменения, вероятно, значительно выше, чем польза. Если вы беспокоитесь о правильности, вы можете поместить чек в столбец, например

ALTER TABLE tablename ADD CONSTRAINT consistency CHECK (consistency IN ('Y', 'N'));