Как хранится NULL. Таблица с NULL и без

На днях в разговоре зашла речь про null’ы в PostgreSQL (рекомендую посмотреть доклад Алексея Борщева NULLs в Postgres, там много интересного по теме того, как в PostgreSQL обрабатываются неопределенные значения).

И, в частности, обсуждался вопрос того, как именно Постгрес хранит null’ы. В докладе выше уже было об этом сказано немного, но порекомендую посмотреть вторую тему в курсе DBA2 Страницы и версии строк, там рассказано про организацию хранения значений и про неопределенные значения тоже немного есть.

Если кратко — null’s в Постгресе не хранятся. Если неопределенное значение появляется в строке — то создается (или дополняется) карта неопределенных значений. В этот раз я не буду рассматривать как это реализовано, а продолжу про разговор.

Помимо обсуждения как хранятся такие значения — хотелось практического понимания что будет с размером таблицы при наличии неопределенных значений и при их отсутствии. А лучше всего протестировать и получить это практическое понимание. Что ниже и проиллюстрировано:

Создаем таблицу, в которое будет два столбца:

pavel=# CREATE TABLE t_null (id integer, txt text);
CREATE TABLE

Один из них (id) будет всегда заполнятся значениями, а вот второе поле (txt) сначала будет без значений:

pavel=# INSERT INTO t_null SELECT (round(random()*10)) FROM generate_series(1,1000000);
INSERT 0 1000000

По умолчанию, утилита psql ничего не отображает в полях где встречаются неопределенные значения. Исправим это с помощью установки параметра null — установим строку, которая будет печататься вместо null’а:

pavel=# \pset null '(null)'
Null display is "(null)"

И проверим отображение:

pavel=# SELECT * FROM t_null LIMIT 10;
id | txt
----+--------
4 | (null)
5 | (null)
4 | (null)
10 | (null)
9 | (null)
6 | (null)
1 | (null)
9 | (null)
6 | (null)
9 | (null)
(10 rows)

А теперь проверим размер таблицы (на самом деле — Размер таблицы на диске определяется чуть сложнее, но сейчас вдаваться в такие детали не будем) с помощью метакоманды \dt+, посмотрите и запомните значение поля Size:

pavel=# \dt+ t_null 
                                  List of relations
 Schema |  Name  | Type  | Owner | Persistence | Access method | Size  | Description 
--------+--------+-------+-------+-------------+---------------+-------+-------------
 public | t_null | table | pavel | permanent   | heap          | 35 MB | 
(1 row)

Это размер таблицы, в которой есть текстовое поле (а это поле, напоминаю, может быть переменной длины), но все значения этого не определены.

Теперь запишем в половину таблицы, в поле txt, какие-то определенные значения. Для соблюдения формальностей — посмотрим сколько чего у нас есть в таблице:

pavel=# SELECT id, count(id) FROM t_null GROUP BY id;
id | count
----+--------
0 | 49647
1 | 99583
2 | 99925
3 | 100116
4 | 100223
5 | 100006
6 | 100310
7 | 100173
8 | 100176
9 | 99870
10 | 49971
(11 rows)

Для ровного счета — возьмем диапазон от 3 до 7:

pavel=# SELECT count(id) FROM t_null WHERE id > 2 AND id < 8;
count
--------
500828
(1 row)

Примерно половина таблицы. Эту половину и обновим — в текстовое поле запишем какой-то текст:

pavel=# UPDATE t_null SET txt='123456789123456789123456789123456789123456789123456789' WHERE id > 2 AND id < 8;
UPDATE 500828

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

pavel=# \dt+ t_null 
                                  List of relations
 Schema |  Name  | Type  | Owner | Persistence | Access method | Size  | Description 
--------+--------+-------+-------+-------------+---------------+-------+-------------
 public | t_null | table | pavel | permanent   | heap          | 79 MB | 
(1 row)

Однако, UPDATE в PostgreSQL — это две операции, удаление старой строки и вставка новой. Но старую строку еще нужно убрать — произвести очистку. Подробнее прочитать можно в том же курсе DBA2, в пятом занятии Очистка. Про свои опыты я тоже писал: SeqScan из не вакуумированной таблицы и Иллюстрация распухания в PostgreSQL. Дополнение. Здесь подробно про это не будем. Для чистоты получения точных данных — выполним полную очистку таблицы:

pavel=# VACUUM FULL t_null;
VACUUM

И опять посмотрим размер таблицы:

pavel=# \dt+ t_null 
                                  List of relations
 Schema |  Name  | Type  | Owner | Persistence | Access method | Size  | Description 
--------+--------+-------+-------+-------------+---------------+-------+-------------
 public | t_null | table | pavel | permanent   | heap          | 62 MB | 
(1 row)

Размер таблицы — 62 МБ. Тот же размер у нас бы получился, если создать новую таблицы и добавить в нее половину строк со значениями в поле txt.

А теперь посмотрим что было бы, если мы вообще не создавали бы это текстовое поле. Удалим его из таблицы:

pavel=# ALTER TABLE t_null DROP COLUMN txt;
ALTER TABLE

И снова выполним полную очистку таблицы:

pavel=# VACUUM FULL t_null;
VACUUM

И проверим ее размер:

pavel=# \dt+ t_null 
                                  List of relations
 Schema |  Name  | Type  | Owner | Persistence | Access method | Size  | Description 
--------+--------+-------+-------+-------------+---------------+-------+-------------
 public | t_null | table | pavel | permanent   | heap          | 35 MB | 
(1 row)

35 мегабайт — как раз столько же, сколько у нас получилось в случае если все значения в текстовом поле были неопределенными.

Кратко:

То есть, действительно, неопределенные значения в таблице не хранятся. Но помнить про помнить про необходимость (и особенности) очистки тоже нужно — в производственной системе не всегда может получиться выполнить полную очистку.

И интересно было бы посмотреть на накладные расходы при добавлении первого неопределенного значения — когда будет строится карта неопределенных значений. Но это уже в следующий раз.