Слои

Проверьте наличие слоев FSM и VM для таблиц.
```
postgres@ts_db=# SELECT pg_relation_filepath(tablename::regclass) from pg_catalog.pg_tables where tablename ~ '^i_.$' \g (tuples_only=on format=unaligned) | sed 's/$^.*$$/ls -l \$PGDATA\/\1*/' | bash
```
Разбор команды
- SELECT pg_relation_filepath(tablename::regclass) — получение относительного пути к файлу данных таблицы; приведение tablename::regclass преобразует имя таблицы в рег-тип, требуемый функцией pg_relation_filepath();
- from pg_catalog.pg_tables where tablename ~ '^i_.$' — выборка из каталога pg_tables таблиц с именами, соответствующими регулярному выражению ^i_.$ (начинаются с i_ и заканчиваются одним символом);
- \g (tuples_only=on format=unaligned) | — метакоманда, которая передает вывод SQL-запроса через конвейер в команду ОС, отключая заголовки и выравнивание;
- sed 's/$^.*$$/ls -l \$PGDATA\/\1*/' — потоковый редактор, который для каждой полученной строки (относительного пути) конструирует команду ls -l $PGDATA/<путь>/*, заменяя путь с помощью \1;
- | bash — выполняет сформированные sed команды в оболочке bash, выводя подробную информацию обо всех файлах (слоях) таблицы, включая main, fsm, vm и другие.
```
-rw------- 1 postgres postgres 8192 Nov 10 10:28 /pgdata/06/data/pg_tblspc/16431/PG_15_202310091/16432/16433
-rw------- 1 postgres postgres 8192 Nov 10 10:28 /pgdata/06/data/pg_tblspc/16431/PG_15_202310091/16432/16436
```
Пока имеются лишь основные слои таблиц — main.

Выполните команду очистки и снова проверьте наличие слоев.

postgres@ts_db=# VACUUM;

VACUUM

postgres@ts_db=# SELECT pg_relation_filepath(tablename::regclass) from pg_catalog.pg_tables where tablename ~ '^i_.$' \g (tuples_only=on format=unaligned) | sed 's/\(^.*$\)/ls -l \$PGDATA\/\1*/' | bash

-rw------- 1 postgres postgres  8192 Nov 10 10:28 /pgdata/06/data/pg_tblspc/16431/PG_15_202310091/16432/16433
-rw------- 1 postgres postgres 24576 Nov 10 11:05 /pgdata/06/data/pg_tblspc/16431/PG_15_202310091/16432/16433_fsm
-rw------- 1 postgres postgres  8192 Nov 10 11:05 /pgdata/06/data/pg_tblspc/16431/PG_15_202310091/16432/16433_vm
-rw------- 1 postgres postgres  8192 Nov 10 10:28 /pgdata/06/data/pg_tblspc/16431/PG_15_202310091/16432/16436
-rw------- 1 postgres postgres 24576 Nov 10 11:05 /pgdata/06/data/pg_tblspc/16431/PG_15_202310091/16432/16436_fsm
-rw------- 1 postgres postgres  8192 Nov 10 11:05 /pgdata/06/data/pg_tblspc/16431/PG_15_202310091/16432/16436_vm

После выполнения VACUUM у обеих таблиц (16433 и 16436) появились дополнительные слои: fsm (Free Space Map, 24 КБ) и vm (Visibility Map, 8 КБ). Слой fsm хранит информацию о свободном пространстве внутри страниц для ускорения вставки. Слой vm отслеживает видимость строк для всех активных транзакций.

Сравните полученные размеры с данными, возвращаемыми функцией pg_relation_size().

postgres@ts_db=# SELECT pg_relation_size(16433, 'main'), pg_relation_size(16433, 'fsm'), pg_relation_size(16433, 'vm');

 pg_relation_size | pg_relation_size | pg_relation_size
------------------+------------------+------------------
             8192 |            24576 |             8192
(1 row)

postgres@ts_db=# SELECT pg_relation_size(16436, 'main'), pg_relation_size(16436, 'fsm'), pg_relation_size(16436, 'vm');

 pg_relation_size | pg_relation_size | pg_relation_size
------------------+------------------+------------------
             8192 |            24576 |             8192
(1 row)

Вставьте в таблицы по 100000 строк и снова повторите измерение.

postgres@ts_db=# INSERT INTO i_f SELECT g.i, random() FROM generate_series(1,100000) as g(i);

INSERT 0 100000

postgres@ts_db=# INSERT INTO i_t SELECT g.i, random()::text FROM generate_series(1,100000) as g(i);

INSERT 0 100000

postgres@ts_db=# SELECT pg_relation_size(16433, 'main'), pg_relation_size(16433, 'fsm'), pg_relation_size(16433, 'vm');

 pg_relation_size | pg_relation_size | pg_relation_size
------------------+------------------+------------------
          4431872 |            24576 |             8192
(1 row)

postgres@ts_db=# SELECT pg_relation_size(16436, 'main'), pg_relation_size(16436, 'fsm'), pg_relation_size(16436, 'vm');

 pg_relation_size | pg_relation_size | pg_relation_size
------------------+------------------+------------------
          5275648 |            24576 |             8192
(1 row)

Для таблицы i_f (OID 16433) слой main занимает 4 431 872 байта (~4 МБ), fsm — 24 576 байт (24 КБ), vm — 8 192 байта (8 КБ). Для таблицы i_t (OID 16436) main — 5 275 648 байт (~5 МБ), fsm и vm — те же 24 КБ и 8 КБ. Размеры слоев fsm и vm совпали с физическими файлами на диске. Рост размера main у обеих таблиц обусловлен вставленными строками: таблица i_t занимает больше места из-за текстового столбца с большими значениями, хранимыми в TOAST.

Самопроверка

Вопрос 1

В сеансе psql выполнены команды:

postgres@ts_db=# VACUUM;

postgres@ts_db=# SELECT pg_relation_filepath(tablename::regclass) from pg_catalog.pg_tables where tablename ~ '^i_.$' //g (tuples_only=on format=unaligned) | sed 's/\(^.*$\)/ls -l \$PGDATA\/\1*/' | bash

-rw------- 1 postgres postgres  8192 Nov 10 10:28 /pgdata/06/data/pg_tblspc/16431/PG_15_202310091/16432/16433
-rw------- 1 postgres postgres 24576 Nov 10 11:05 /pgdata/06/data/pg_tblspc/16431/PG_15_202310091/16432/16433_fsm
-rw------- 1 postgres postgres  8192 Nov 10 11:05 /pgdata/06/data/pg_tblspc/16431/PG_15_202310091/16432/16433_vm
-rw------- 1 postgres postgres  8192 Nov 10 10:28 /pgdata/06/data/pg_tblspc/16431/PG_15_202310091/16432/16436
-rw------- 1 postgres postgres 24576 Nov 10 11:05 /pgdata/06/data/pg_tblspc/16431/PG_15_202310091/16432/16436_fsm
-rw------- 1 postgres postgres  8192 Nov 10 11:05 /pgdata/06/data/pg_tblspc/16431/PG_15_202310091/16432/16436_vm

Какие слои появились в файловой системе после выполнения VACUUM?

Основной слой (main) — сегмент с данными

Слой карты свободного пространства (fsm)

Слой карты видимости (vm)

Слой инициализации (init)

Вопрос 2

В сеансе psql выполнены команды:

postgres@ts_db=# VACUUM;

postgres@ts_db=# SELECT pg_relation_size(16433, 'vm');
 pg_relation_size
------------------
              8192
(1 row)

postgres@ts_db=# INSERT INTO i_f SELECT g.i, random() FROM generate_series(1,100000) as g(i);

postgres@ts_db=# SELECT pg_relation_size(16433, 'vm');
 pg_relation_size
------------------
              8192
(1 row)

Почему размер слоя vm остался 8192 байт после вставки 100000 строк?

vm растет только при выполнении VACUUMvm не нужен для таблиц с фиксированными типами данныхvm хранит всего 2 бита на страницу, поэтому его размер не меняетсяvm не может расти больше 1 страницы

Вопрос 3

В сеансе psql выполнены команды:

postgres@ts_db=# INSERT INTO i_f SELECT g.i, random() FROM generate_series(1,100000) as g(i);
postgres@ts_db=# INSERT INTO i_t SELECT g.i, random()::text FROM generate_series(1,100000) as g(i);

postgres@ts_db=# SELECT pg_relation_size(16433, 'main') AS i_f_main, pg_relation_size(16436, 'main') AS i_t_main;
 i_f_main  |  i_t_main
-----------+-----------
  4431872  |  5275648

Почему слой main таблицы i_t больше, чем у i_f, хотя в обе таблицы вставлено одинаковое количество строк?

Таблица i_t содержит больше индексовСтолбец типа text хранит большие значения random()::text, которые больше места даже с TOASTТип float имеет фиксированный размер, а text — переменный, и строки преобразованные из random()::text занимают больше местаТаблица i_t хранит данные без сжатия

Самопроверка​

Вопрос 1

Вопрос 2

Вопрос 3

Самопроверка