Измените varchar на логическое значение в PostgreSQL.

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

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

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

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

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

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

Вот мой вопрос (ы).

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

postgresql storage query-optimization database-design postgresql-9.1
person David S    schedule 10.10.2012    source источник

Ответы (2)


arrow_upward
23
arrow_downward

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

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

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

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

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

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

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

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

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

В любом случае будет немного лучше с логическим значением. Помимо того, что boolean немного меньше, логика для 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 остается.

person Erwin Brandstetter    schedule 11.10.2012
comment
@DavidS Обратите внимание, что изменение типа с помощью ALTER TABLE приведет к исключительной блокировке всей таблицы во время ее перезаписи. Если вы не можете себе это позволить, вы можете сделать это поэтапно: ALTER TABLE чтобы добавить новое логическое поле, допускающее значение NULL; UPDATE для заполнения этого поля; ALTER TABLE, чтобы сделать его NOT NULL, ALTER TABLE, чтобы удалить старый столбец varchar, и ALTER TABLE, чтобы переименовать новый логический столбец в имя столбца varchar. Вы можете добавить триггер, который перехватывает новые INSERT, UPDATE и DELETE в процессе перезаписи и следит за тем, чтобы они также устанавливали новый логический столбец. - person Craig Ringer; 11.10.2012
comment
@КрейгРингер. Спасибо за комментарий/совет. - person David S; 11.10.2012

arrow_upward
2
arrow_downward

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

ALTER TABLE tablename ADD CONSTRAINT consistency CHECK (consistency IN ('Y', 'N'));
person Community    schedule 12.10.2012