Размеры объектов
При изучении структуры хранения данных мы анализировали размеры файлов, но не говорили подробно о способах оценки этого размера. В этом разделе будут показаны способы получения информации о размере основных объектов базы данных.
Определение размеров слоев таблиц
При работе с таблицей в реальной жизни данных может быть очень много, больше отведенного 1 ГБ на сегмент. Соответственно, появятся новые файлы. А в высоконагруженных системах таких файлов будет длинный список. В таких ситуациях выводить весь этот список и смотреть на размер файлов — не лучшая идея.
Поскольку все файлы удобно сгруппированы по слоям можно измерить размер целого слоя и получить таким образом нужную информацию. В PostgreSQL для этой задачи есть функция pg_relation_size(). В качестве первого аргумента она принимает название таблицы, слои которой нас интересуют, второй аргумент не является обязательным — вид слоя: main, fsm, vm и init.
Посмотрим на применение данной функции для таблицы tab_data.
postgres@sch_db=# SELECT pg_relation_size('tab_data');
pg_relation_size
-----------------
8192
(1 row)
Когда функция получает лишь один аргумент — название таблицы, она возвращает размер основного слоя — main. Проверим это, задав второй аргумент.
postgres@sch_db=# SELECT pg_relation_size('tab_data', 'main');
pg_relation_size
-----------------
8192
(1 row)
Таким же образом можно вывести размеры слоев fsm, vm и init.
В СУБД Pangolin есть собственные функции для определения размера каждого слоя: get_nblocks() и get_nblocks_all(). Первая функция выводит количество блоков слоев для одного объекта, а вторая — для всех объектов в базы данных.
Сейчас в таблице лежит одна строка данных, поэтому слой сегмента равен по размеру слою видимости. Добавим в таблицу 10000 строк, чтобы сымитировать работу с большим количеством данных.
postgres@sch_db=# INSERT INTO tab_data SELECT i,'Nr '||i::text FROM generate_series(1,100000) AS g(i);
Разбор запроса
Для добавления строк используется сложный запрос, разберем его с конца:
generate_series(1,100000)— функция generate_series() генерирует последовательность из чисел от 1 до 10000;AS g(i)— полученная последовательность сохраняется в псевдоним с именемgи обозначениемiдля элемента последовательности. Использование псевдонима помогает лаконично и понятно использовать результаты функций внутри запросов;'Nr '||i::text— создание строчки текста из двух частей: к кусочку строки'Nr 'присоединяется элемент последовательностиi, предварительно приводя число к строковому типу;SELECT i,'Nr '||i::text— на этом этапе формируется структура данных, соответствующая таблицеtab_data: в первый столбец будет записано числоi, а во второй — строка после объединения;INSERT INTO tab_data— непосредственная вставка данных в таблицу.
INSERT 0 100000
Выводить размер каждого слоя по отдельности может быть неудобно, поэтому воспользуемся следующим запросом.
postgres@sch_db=# SELECT pg_relation_size('tab_data','main') AS "MAIN",
pg_relation_size('tab_data','fsm') AS "FSM",
pg_relation_size('tab_data','vm') AS "VM";
В нем через запятую перечислены вызовы функции для каждого слоя по отдельности. Чаще всего результат выполнения функции носит название самой функции, поэтому в выводе получается две строки: первая — название функции, под ней — результат функции. Чтобы вывод был более понятным, используются псевдонимы для результатов, как в нашем случае. Тогда вывод будет выглядеть следующим образом.
MAIN | FSM | VM
---------+-------+------
4431872 | 24576 | 8192
(1 row)
После вставки 10000 строк размер основного слоя, одного сегмента в нашем случае, теперь значительно отличается от слоя видимости.
После добавления 10000 строк в таблицу, будет ли создан новый сегмент?
Нет, новый сегмент создан не будет, поскольку ограничение на его размер 1 ГБ, а это 1073741824 байт. В то время, как у нас сегмент равен 4431872 байт. Проверить количество сегментов можно, выведя все файлы таблицы.
postgres@sch_db=# \! ls -l $PGDATA/base/16389/16443*
-rw------- 1 postgres postgres 4431872 Oct 17 15:07 /pgdata/06/data/base/16389/16443
-rw------- 1 postgres postgres 24576 Oct 17 15:07 /pgdata/06/data/base/16389/16443_fsm
-rw------- 1 postgres postgres 8192 Oct 17 15:07 /pgdata/06/data/base/16389/16443_vm
Размер таблицы с TOAST
В случаях, когда в таблице хранятся длинные строки данных, появляется отдельная TOAST таблица со своими слоями. При использовании функции pg_relation_size() можно оценить размер слоев лишь основной таблицы. Для понимания размера с учетом TOAST слоев используется функция pg_table_size().
Создадим новую таблицу с типом данных text для одного из столбцов.
postgres@sch_db=# CREATE TABLE bigfield(tme timestamp DEFAULT now() PRIMARY KEY, big text);
CREATE TABLE
Добавим в таблицу длинную строку, чтобы была задействована TOAST таблица.
postgres@sch_db=# INSERT INTO bigfield(big) VALUES(repeat('ABCD',1000000));
INSERT 0 1
Для начала применим функцию pg_relation_size().
postgres@sch_db=# SELECT pg_relation_size('bigfield','main');
pg_relation_size
------------------
8192
(1 row)
Размер сегмента основной таблицы составляет 8 КБ, то есть одной страницы. В то время как добавленная строка данных весит примерно 4 МБ. Поскольку строка не поместилась на страницу, алгоритмы PostgreSQL перенесли данные в TOAST таблицу.
Подумайте, что же хранится на единственной странице в сегменте?
На странице в сегменте хранится указатель на строку в TOAST таблице с перемещенными данными.
Применим функцию pg_table_size() и посмотрим на размеры таблицы с учетом TOAST слоев.
postgres@sch_db=# SELECT pg_table_size('bigfield');
pg_table_size
---------------
4218880
(1 row)
Данная функция выводит лишь одно значение — суммарный размер основной и TOAST таблиц. Если же необходимо узнать размер только основного слоя (main) TOAST таблицы, нужно передать функции pg_relation_size() oid TOAST таблицы.
postgres@sch_db=# SELECT pg_relation_size((SELECT reltoastrelid FROM pg_class WHERE relname = 'bigfield')::regclass);
Разбор запроса
- pg_class — системный каталог PostgreSQL, в котором хранится информация обо всех объектах базы данных, имеющих отношения;
- reltoastrelid — столбец с
oidобъекта; - relname — это столбец в
pg_class, в котором хранятся названия отношений.
Во внутренней части запроса выбирается oid объекта с названием его отношения bigfield в системном каталоге pg_class. Последняя часть запроса:
::regclass— приводим полученныйoidк типу regclass, который гарантирует корректную работу функцииpg_relation_size().
Несмотря на получение из столбца reltoastrelid именно oid объекта, хорошей практикой считается явное приведение результата внутреннего запроса к необходимому типу аргумента внешней функции.
После выполнения запроса получим результат.
pg_relation_size
------------------
4112384
(1 row)
Зная oid TOAST таблицы, с помощью функции pg_relation_size() можно узнать размеры и других слоев.
Определение размеров индексов
Для индекса, как и любого объекта в PostgreSQL, можно посчитать размер. Это можно сделать с помощью двух функций: привычной нам pg_relation_size() и новой pg_indexes_size(). Функция pg_relation_size() требует в качестве аргумента название или oid объекта. Получить название индекса для таблицы можно за счет вывода информации о таблице.
Выведем информацию о таблице bigfield. Для получения детальной информации о bigfield в команде \d, выводящей краткую характеристику обо всех таблицах, нужно указать имя интересующей таблицы.
postgres@sch_db=# \d bigfield
Table "public.bigfield"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
tme | timestamp without time zone | | not null | now()
big | text | | |
Indexes:
"bigfield_pkey" PRIMARY KEY, btree (tme)
Откуда у таблицы bigfield появился индекс, если мы его не создавали?
Индекс появился автоматически при создании TOAST таблицы для основной таблицы bigfield. Он нужен для быстрого доступа к разным частям длинной строки, которая была перемещена в TOAST таблицу.
Под таблицей с перечислением столбцов расположена строчка с названием индекса, в нашем случае bigfield_pkey. Воспользуемся функцией pg_relation_size().
postgres@sch_db=# SELECT pg_relation_size('tab_data_msg_idx','main') AS "MAIN",
pg_relation_size('tab_data_msg_idx','fsm') AS "FSM";
MAIN | FSM
-------+-----
16384 | 0
(1 row)
Для индекса, как и таблицы, можно определить размер основного слоя (main) и карты свободного пространства (fsm).
В отличие от pg_relation_size() функции pg_indexes_size() требуется только название самой таблицы.
postgres@sch_db=# SELECT pg_indexes_size('bigfield');
pg_indexes_size
-----------------
16384
(1 row)
Стоит помнить, что функция pg_indexes_size() возвращает суммарный размер всех индексов. Поэтому если нужно найти размер конкретного индекса, лучше пользоваться функцией pg_relation_size().
Размер таблицы с индексами и TOAST
До этого были рассмотрены примеры определения размеров объектов по отдельности. Теперь посмотрим, как узнать размер всей таблицы, включая все объекты. Конечно, можно просто посчитать сумму размеров всех объектов, но в PostgreSQL для этого есть удобная функция — pg_total_relation_size().
Поскольку выше уже были подсчитаны размеры таблицы bigfield с учетом TOAST таблицы и ее индекс, проверим новую функцию на ее примере.
postgres@sch_db=# SELECT pg_total_relation_size('bigfield');
pg_total_relation_size
------------------------
4235264
(1 row)
Функция pg_total_relation_size() вернула размер 4235264 байт. Проверим его. Размер таблицы с учетом TOAST слоев — 4112384 байт, размер индекса — 16384 байт. Сложив эти значения как раз получаем 4235264 байт.
Таким образом, возвращаемый функцией pg_total_relation_size() результат равен сумме значений, возвращаемых функциями pg_table_size() и pg_indexes_size().
Размер базы данных
Разобравшись с размерами объектов, поднимемся на уровень выше — базы данных. В PostgreSQL есть возможность посчитать размер базы данных с учетом всех ее объектов — функция pg_database_size().
Применим новую функцию для текущей базы данных.
postgres@sch_db=# SELECT pg_database_size(current_database());
pg_database_size
------------------
14214028
(1 row)
В качестве аргумента функция pg_database_size() принимает название или oid базы данных, но если нужно узнать размер текущей базы данных, как в нашем случае, можно воспользоваться функцией current_database(), которая возвращает название текущей базы данных.
По умолчанию вывод всех размеров идет в байтах, но это можно изменить с помощью функции pg_size_pretty(). Она преобразует байты в более удобный для чтения формат.
postgres@sch_db=# SELECT pg_size_pretty(pg_database_size('sch_db'));
pg_size_pretty
----------------
9607 kB
(1 row)
В примере выше функция pg_size_pretty() преобразовала размер базы данных sch_db из байт в килобайты.
Итоги
pg_relation_size(relation [, fork])— размер конкретного слоя объекта; без второго аргумента возвращает размер слояmain;pg_table_size(relation)— суммарный размер таблицы с учетом TOAST таблицы и ее слоев;pg_indexes_size(relation)— суммарный размер всех индексов таблицы; для размера конкретного индекса использоватьpg_relation_size();pg_total_relation_size(relation)— полный размер объекта: таблица + TOAST + индексы; равен суммеpg_table_size()иpg_indexes_size();pg_database_size(name)— размер базы данных с учетом всех ее объектов;pg_size_pretty(size)— преобразует размер в байтах в читаемый формат (КБ, МБ, ГБ);- Размер слоя
fsmтаблицы — от 3 страниц (24 576 байт); размерvmзависит от количества страниц в сегменте; - Даже пустой индекс занимает минимум одну страницу (8 КБ) — первая страница содержит служебные структуры.