Физическое хранение

1. Подключитесь к БД ts_db.

   postgres@postgres=# \c ts_db

   You are now connected to database "ts_db" as user "postgres".

2. Создайте две таблицы: в одной столбцы типа int и float, в другой — int и text.

   postgres@ts_db=# CREATE TABLE i_f (id int, datum float);

   CREATE TABLE

   postgres@ts_db=# CREATE TABLE i_t (id int, mmessg text);

   CREATE TABLE

   Проверьте размеры таблиц.

   postgres@ts_db=# \dt+ i_*

                               List of relations
   Schema | Name | Type  |  Owner   | Persistence | Access method |    Size    | Description
   -------+------+-------+----------+-------------+---------------+------------+-------------
   public | i_f  | table | postgres | permanent   | heap          | 0 bytes    |
   public | i_t  | table | postgres | permanent   | heap          | 8192 bytes |
   (2 rows)

   Обратите внимание, исходные размеры таблиц различаются. Выполните следующие действия, чтобы определить причину.

3. Проверьте размеры таблиц, используя функцию pg_total_relation_size().

   postgres@ts_db=# SELECT pg_size_pretty(pg_total_relation_size('i_f'));

   pg_size_pretty
   ----------------
   0 bytes
   (1 row)

   postgres@ts_db=# SELECT pg_size_pretty(pg_total_relation_size('i_t'));

   pg_size_pretty
   ----------------
   8192 bytes
   (1 row)

   Типы данных int и float имеют фиксированный размер, и строки с двумя такими столбцами гарантированно поместятся на странице сегмента. А столбец типа text может хранить текстовое значение большой длины. Поэтому сразу создается TOAST таблица, под ссылку на которую выделяется страница (8 КБ) в таблице i_t.

4. Исследуйте тип хранилищ для столбцов таблиц.

   postgres@ts_db=# \d+ i_*

                                 Table "public.i_f"
   Column  |       Type       | Collation | Nullable | Default | Storage | Compression | Stats target |
   --------+------------------+-----------+----------+---------+---------+-------------+--------------+
   id      | integer          |           |          |         | plain   |             |              |
   datum   | double precision |           |          |         | plain   |             |              |
   Access method: heap
   
                                  Table "public.i_t"
   Column   |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target |
   ---------+---------+-----------+----------+---------+----------+-------------+--------------+
   id       | integer |           |          |         | plain    |             |              |
   mmessg   | text    |           |          |         | extended |             |              |
   Access method: heap

   Тип хранилища extended соответствует использованию технологии TOAST для хранения атрибутов (значений в столбцах) большого размера.

5. Изучите таблицу TOAST.

   postgres@ts_db=# SELECT relname, reltoastrelid as toast_oid, reltoastrelid::regclass as toast_name FROM pg_class WHERE relname ~ '^i_.$';

   Разбор команды

   • SELECT relname, reltoastrelid as toast_oid, reltoastrelid::regclass as toast_name — выборка имен отношений, oid таблиц TOAST и имен таблиц TOAST, полученных приведением типа regclass;
   • FROM pg_class — системный каталог, содержащий метаданные о всех отношениях (таблицах, индексах, представлениях и т.д.) в базе данных;
   • WHERE relname ~ '^i_.$' — фильтрация по имени отношения с помощью регулярного выражения: выбираются только имена, начинающиеся с i_ и заканчивающиеся одним символом (таблицы i_f и i_t).

    relname | toast_oid |       toast_name
   ---------+-----------+-------------------------
        i_f | 0         | -
        i_t | 16439     | pg_toast.pg_toast_16436
   (2 rows)

   Так как в столбцах таблицы i_f нет объемных данных, которые не помещались бы на одну страницу, она не использует таблицу TOAST. Это отражено в системном каталоге pg_class, который содержит метаданные о всех отношениях в БД. Поле reltoastrelid хранит oid таблицы TOAST или 0, если TOAST таблица не используется. Для таблицы i_t указан oid таблицы TOAST, имя которой можно узнать с помощью приведения типа.

   Проверьте размер таблицы TOAST.

   postgres@ts_db=# SELECT pg_size_pretty(pg_total_relation_size('pg_toast.pg_toast_16436'));

   pg_size_pretty
   ----------------
   8192 bytes
   (1 row)

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

6. Получите полные имена файлов, в которых будут размещаться данные таблиц i_f и i_t.

   postgres@ts_db=# SELECT pg_relation_filepath('i_f') as i_f, pg_relation_filepath('i_t') as i_t;

                      i_f                       |                   i_t
   ---------------------------------------------+---------------------------------------------
    pg_tblspc/16431/PG_15_202310091/16432/16433 | pg_tblspc/16431/PG_15_202310091/16432/16436
    (1 row)

7. Проверьте размеры файлов, полученных в предыдущем пункте.

   postgres@ts_db=# SELECT pg_relation_filepath(tablename::regclass) from pg_catalog.pg_tables where tablename ~ '^i_.$' \g (tuples_only=on format=unaligned) | xargs -i ls -l $PGDATA/{}

   Разбор команды

   • 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-запроса через конвейер в команду ОС, отключая заголовки и выравнивание;
   • xargs -i ls -l $PGDATA/{} — для каждой строки входного потока подставляет путь вместо {} и выполняет ls -l, выводя подробную информацию о файле.

   -rw------- 1 postgres postgres 0 Nov 10 09:06 /pgdata/06/data/pg_tblspc/16431/PG_15_202310091/16432/16433
   -rw------- 1 postgres postgres 0 Nov 10 09:07 /pgdata/06/data/pg_tblspc/16431/PG_15_202310091/16432/16436

   Сегменты обоих таблиц пусты, так как в них еще не были вставлены данные.

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

   postgres@ts_db=# INSERT INTO i_f VALUES(1,2.0);

   INSERT 0 1

   postgres@ts_db=# INSERT INTO i_t VALUES(1,'2');

   INSERT 0 1

   postgres@ts_db=# SELECT pg_relation_filepath(tablename::regclass) from pg_catalog.pg_tables where tablename ~ '^i_.$' \g (tuples_only=on format=unaligned) | xargs -i ls -l $PGDATA/{}

   -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

   В каждую таблицу была выполнена вставка по одной строке данных. Несмотря на небольшой размер данных, под каждую строку была выделена целая страница (8 КБ).

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

Вопрос 1

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

postgres@ts_db=# SELECT relname, reltoastrelid as toast_oid, reltoastrelid::regclass as toast_name FROM pg_class WHERE relname ~ '^i_.$';
 relname | toast_oid |       toast_name
---------+-----------+-------------------------
    i_f  | 0         | -
    i_t  | 16439     | pg_toast.pg_toast_16436
(2 rows)

Какой oid имеет таблица i_t?

Вопрос 2

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

postgres@ts_db=# CREATE TABLE i_f (id int, datum float);
postgres@ts_db=# CREATE TABLE i_t (id int, mmessg text);

postgres@ts_db=# \dt+ i_*
 Schema | Name | Type  |  Owner   |    Size    
--------+------+-------+----------+------------
 public | i_f  | table | postgres | 0 bytes    
 public | i_t  | table | postgres | 8192 bytes 
(2 rows)

Почему размер таблицы i_t равен 8192 байт, а i_f — 0 байт?

Таблица i_t содержит больше строкТип text имеет стратегию extended, и для таблицы создана TOAST таблица с указателем на страницуТип float не поддерживает физическое хранениеТаблица i_f создана без индексов

Вопрос 3

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

postgres@ts_db=# \d+ i_t
 Column | Type  | Storage  
--------+-------+----------
 id     | integer | plain   
 mmessg | text    | extended

Что означает значение extended в столбце Storage?

Данные хранятся в компрессированном виде без использования TOASTДанные сначала сжимаются, а если не помещаются на страницу — переносятся в TOAST-таблицуДанные всегда хранятся во внешней таблице без сжатияДанные не могут быть сохранены на диске

Вопрос 4

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

postgres@ts_db=# SELECT pg_relation_filepath('i_f');
           pg_relation_filepath
---------------------------------------------
 pg_tblspc/16431/PG_15_202310091/16432/16433
(1 row)

Какие элементы присутствуют в выведенном пути?

OID табличного пространства (16431)

Идентификатор версии PostgreSQL (PG_15_202310091)

OID базы данных (16432)

OID таблицы / filenode (16433)

Абсолютный путь к файлу в ОС