PostgreSQL. Размер таблицы на диске

Чем больше изучаю PostgreSQL, тем больше понимаю, что кучу информации в этой теме ещё не знаю. Копнул одно, а из этого вылезаешь совсем в другое место. Поэтому долго получается.

Сегодня взял только одну тему и вроде-бы с ней разобрался – размер таблицы на диске.

CREATE TABLE bigtable (txt text);
Сразу же посмотрим, какой метод хранения данного столбца используется:
\d+ bigtable

Используется метод EXTENDED. Такой и оставим.
Добавим в таблицу сто тысяч записей следующим запросом:
INSERT INTO bigtable SELECT (999^999::numeric)::text FROM generate_series(1,100000);
Для ещё большей демонстрации, добавим индекс:
CREATE INDEX ON bigtable (md5(txt));
Не разбирался пока, почему именно md5 использую. Если без этой функции создавать индекс, тогда появляется вот такая ошибка:

Посмотрим объем таблицы bigtable:
\dt+ bigtable
SELECT pg_size_pretty(pg_total_relation_size('bigtable'));

Значения отличаются. По видимому, при использовании \dt+ нужно к размеру таблицы прибавлять еще размер её индексов:
\dt+ bigtable
\di+ bigtable_md5_idx

Полный размер таблицы bigtable ~405 MB.

[свернуть]

Проверяем размер файлов на диске

Как я выяснил, полный размер таблицы состоит из файлов основной таблицы (объем всех слоёв (форков) таблицы), индексов на основную таблицу и, если будут использованы toast-таблицы, файлы связанной toast-таблицы и индексов на неё.

А теперь «потрогаем» руками эти элементы:

1. Файлы основной таблицы bigtable

Узнаем, какие файлы на диске связаны с нашей таблицей (документация: «Размещение файлов базы данных», конкретно про функцию pg_relation_filepath в предпоследнем абзаце):

SELECT pg_relation_filepath('bigtable');

Переходим в каталог с PGDATA, там в каталог base/12328 и выполняем команду:

ls -al ––block-size=K | grep 2256865

(показать все файлы, размер выразить в мегабайтах, найти в списке те файлы, которые начинаются на «2256865»):

Итого: слои основной таблицы занимают 4736 КБ.

2. Файлы индексов основной таблицы

Сделаем то же самое, но для индекса основной таблицы.

Сначала узнаем название индекса (при его создании я не указывал имя, оно сформировалось автоматически):

SELECT * FROM pg_indexes WHERE tablename = 'bigtable'\gx

И это имя передадим в функцию pg_relation_filepath:

Снова смотрим размер файлов данного индекса на диске:

ls -al ––block-size=K | grep 2356872

Итого: файлы индекса основной таблицы занимают 5328 КБ.

3. Файлы связной toast-таблицы

Таблица у нас «весит» ~405 МБ, а файлы основной таблицы – всего ~6 МБ. Почему? Потому что используется технология TOAST.

С нашей основной таблице связана автоматически-созданная TOAST-таблица. Найдем её:

SELECT relnamespace::regnamespace, relname
FROM pg_class WHERE oid = (
SELECT reltoastrelid FROM pg_class WHERE relname = 'bigtable');

По умолчанию, объекты схемы pg_toast нам не видны, поэтому в функцию pg_relation_filepath нужно передать имя таблицы вместе с именем схемы:

Смотрим размер файлов:

Вот оно, пошло дело! Физически, данные таблицы (а точнее – столбца) хранятся именно тут.

Итого: файлы связной toast-таблицы занимают 400120 КБ.

4. Файлы индексов связной toast-таблицы

Теперь самое сложное (по крайней мере, для меня). Нужно найти файлы индексов, связной toast-таблицы. Они чуть-чуть спрятаны.

Для начала, найдем название индекса на эту таблицу. Опуская мои шаги, которые были предприняты при попытке найти название индекса, приведу сразу итоговый запрос (лучше почитать в документации про идентификаторы объектов OID и про приведение типов к regclass, в той же статье, описания pg_index и pg_class):

SELECT indexrelid::regclass FROM pg_index WHERE indrelid = (
SELECT oid FROM pg_class WHERE relname = 'pg_toast_2256865');

В качества таблицы, для которой ищем связный индекс (relname) передаем имя toast-таблицы, полученной на предыдущем шаге:

И передаем имя найденного индекса в функцию pg_relation_filepath:

Проверяем размер данного индекса на диске:

Итого: файлы индекса связной toast-таблицы занимают 4408 КБ.

Суммируем:

1. Файлы основной таблицы bigtable:

• 4712 КБ
• 24 КБ

2. Файлы индексов основной таблицы bigtable_md5_idx:

• 5328 КБ

3. Файлы связной toast-таблицы pg_toast.pg_toast_2256865:

• 400000 КБ
• 120 КБ

4. Файлы индексов связной toast-таблицы pg_toast.pg_toast_2256865_index:

• 4408 КБ

Суммарно 414592 КБ, или (немного округляя в большую сторону) 405 МБ.

А полный размер таблицы bigtable, напоминаю, и был 405 МБ:

Уфф! Вот из таких элементов состоит таблица PostgreSQL на диске. Но, возможно, она из чего-то еще состоит, с чем я пока не встретился.