Sysadminium

Разберем что хранится в файлах данных. Каждая таблица состоит из нескольких слоёв. Слои данных в PostgreSQL это как минимум 1 файл. Подробнее про физическое хранение базы данных можете почитать тут.

Слои

Каждый файл занимает не больше 1 GB и кратен 8 KB. Поэтому если таблица больше 1 GB, то она хранится в нескольких файлах. Файлы состоят из 8 KB страниц, которые в случае необходимости помещаются в буферный кэш.

Существуют следующие слои:

• Основной слой (main) — сами данные. Этот слой существует у всех объектов;
• Слой инициализации (init) — существует только для нежурналируемых таблиц. Содержит пустую копию таблицы. В случае сбоя PostgreSQL не пытается восстановить нежурналируемую таблицу, а перезаписывает её пустой таблицей из этого слоя. Поэтому после сбоя нежурналируемые таблицы окажутся пустыми.
• Карта свободного пространства (fsm) — хранит информацию о том, где внутри файлов есть свободное пространство.
• Карта видимости (vm) — отмечает страницы, в которых все версии строк видны. Другими словами VACUUM уже их почистил от неактуальных версий строк. Такой слой существует только для таблиц. Он нужен для оптимизации, чтобы VACUUM знал, какие страницы чистить уже не нужно.

Работа с большими строками (TOAST)

В PostgreSQL одна строка должна помещаться в одну страницу, то есть не быть больше 8 КБ. Чтобы поместить большую строку у PostgreSQL есть следующие стратегии:

• сжать большие атрибуты;
• вынести большие атрибуты в отдельную служебную TOAST таблицу;
• можно объединить оба способа.

Механизм работы с большими строками называется — TOAST. Внешняя таблица в которую по кусочкам помещают длинную строку называют TOAST-таблица.

TOAST-таблица имеет собственную версионность. Например, хранится у вас в такой табличке фотография сотрудника. Вы изменяете сотруднику фамилию, появляется новая версия длинной строки, но фотография в новую версию не копируется. Фотография в TOAST табличке остаётся в той-же версии. Просто новая версия строки (из обычной таблички) ссылается на туже самую фотографию. Это экономит место на диске и увеличивает скорость работы.

Разделение и склеивание длинных строк PostgreSQL делает самостоятельно, то есть вам не нужно обо всем этом задумываться. Вы просто пишите запрос (SELECT), а PostgreSQL склеивает из нескольких частей длинную строку.

Но про них нужно знать. Так как длинные строки обрабатываются отдельно, то это замедляет работу базы данных, но только при запросах к длинному атрибуту, например к фотографии.

TOAST-таблица имеет свою схему pg_toast. А если это временная таблица, то pg_toast_temp_N.

Если в табличке есть поле с типом, куда может поместиться большое значение (numeric, text и т.д.), то TOAST-таблица создается сразу (как бы на всякий случай). Но до помещения больших атрибутов в TOAST-таблицу, она будет пустой.

Практика

Создадим базу данных и подключимся к ней. Затем сделаем там таблицу «t» и в неё вставим 10000 строк:

postgres@s-pg13:~$ psql
Timing is on.
psql (13.3)
Type "help" for help.

postgres@postgres=# CREATE DATABASE data_lowlevel;
CREATE DATABASE
Time: 66,523 ms

postgres@postgres=# \c data_lowlevel
You are now connected to database "data_lowlevel" as user "postgres".

postgres@data_lowlevel=# CREATE TABLE t(id serial PRIMARY KEY, n numeric);
CREATE TABLE
Time: 4,791 ms

postgres@data_lowlevel=# INSERT INTO t(n) SELECT g.id FROM generate_series(1,10000) AS g(id);
INSERT 0 10000
Time: 30,785 ms

Посмотрим на файлы таблицы

С помощью функции pg_relation_filepath(<объект>) можно узнать в каком файле находится объект. Узнаем в каком файле находится наша табличка (относительно каталога PGDATA):

postgres@data_lowlevel=# SELECT pg_relation_filepath('t');
 pg_relation_filepath
----------------------
 base/16494/16497
(1 row)
Time: 0,284 ms

Первое число (16494) — это идентификатор базы, второе (16497) — идентификатор таблички.

Этот путь можно было найти вручную. Так как таблица находится в табличном пространстве pg_default, то файл должен лежать в каталоге $PGDATA/base. Дальше нужно найти идентификаторы базы и таблицы следующими способами:

postgres@data_lowlevel=# SELECT OID FROM pg_database WHERE datname = 'data_lowlevel';
  oid
-------
 16494
(1 row)
Time: 0,904 ms

postgres@data_lowlevel=# SELECT relfilenode FROM pg_class WHERE relname = 't';
 relfilenode
-------------
       16497
(1 row)
Time: 0,350 ms

Идентификатор базы мы вытаскиваем из таблички pg_database, а идентификатор таблички из pg_class.

Теперь посмотрим на сами файлы, предварительно закрыв psql:

postgres@data_lowlevel=# \q

postgres@s-pg13:~$ ls -1 $PGDATA/base/16494/16497*
/usr/local/pgsql/data/base/16494/16497
/usr/local/pgsql/data/base/16494/16497_fsm
/usr/local/pgsql/data/base/16494/16497_vm

• Первый файл (16497) — это основной слой.
• Второй файл (16497_fsm) — карта свободного пространства.
• Третий файл (16497_vm) — карта видимости. Если этот файл появился, значит AUTOVACUUM уже отработал.

Каждый файл не может быть больше 1 GB и кратен 8 KB. Если таблица не помещается в 1 GB, создается дополнительный файл.

Посмотрим на файлы индексов

Теперь посмотрим на индексы. Так как создавая табличку «t» мы сделали поле «id» как публичный ключ, то под него создался индекс.

Подключимся снова к базе data_lowlevel и посмотрим на объект «t«:

postgres@s-pg13:~$ psql -d data_lowlevel
Timing is on.
psql (13.3)
Type "help" for help.

postgres@data_lowlevel=# \d t
                            Table "public.t"
 Column |  Type   | Collation | Nullable |            Default
--------+---------+-----------+----------+-------------------------------
 id     | integer |           | not null | nextval('t_id_seq'::regclass)
 n      | numeric |           |          |
Indexes:
    "t_pkey" PRIMARY KEY, btree (id)

Выше мы видим что индекс называется «t_pkey«.

Теперь с помощью уже знакомой функции pg_relation_filepath() определим путь к файлу этого объекта. И посмотрим на файлы:

postgres@data_lowlevel=# SELECT pg_relation_filepath('t_pkey');
 pg_relation_filepath
----------------------
 base/16494/16504
(1 row)
Time: 0,252 ms

postgres@data_lowlevel=# \q

postgres@s-pg13:~$ ls -1 $PGDATA/base/16494/16504*
/usr/local/pgsql/data/base/16494/16504

Как видим пока есть только 1 файл. fsm у индекса может быть, а vm — нет.

Про расширение oid2name

Существует расширение oid2name, которое позволяет сопоставить объекты и файлы.

Скомпилируем это расширение:

postgres@s-pg13:~$ exit
выход

root@s-pg13:~# cd pg/postgresql-13.3/contrib/oid2name/

root@s-pg13:~/pg/postgresql-13.3/contrib/oid2name# make
...

root@s-pg13:~/pg/postgresql-13.3/contrib/oid2name# make install
...

Без ключей выдаст список баз данных:

root@s-pg13:~/pg/postgresql-13.3/contrib/oid2name# su - postgres

postgres@s-pg13:~$ oid2name
All databases:
    Oid  Database Name  Tablespace
----------------------------------
  16465          appdb  pg_default
  16481       configdb  pg_default
  16448   data_catalog  pg_default
  16494  data_lowlevel  pg_default
  12664       postgres  pg_default
  12663      template0  pg_default
      1      template1  pg_default

Можно посмотреть на объекты в базе данных. Например на табличные пространства в базе data_lowlevel:

postgres@s-pg13:~$ oid2name -d data_lowlevel -s
All tablespaces:
   Oid  Tablespace Name
-----------------------
  1663       pg_default
  1664        pg_global

Можно по имени таблицы узнать её идентификатор, или по идентификатору узнать имя таблицы:

postgres@s-pg13:~$ oid2name -d data_lowlevel -t t
From database "data_lowlevel":
  Filenode  Table Name
----------------------
     16497           t

postgres@s-pg13:~$ oid2name -d data_lowlevel -f 16497
From database "data_lowlevel":
  Filenode  Table Name
----------------------
     16497           t

Узнаем размер объектов

С помощью функции pg_relation_size(‘<имя объекта>’, ‘<слой>’) можем узнать размер слоя:

postgres@s-pg13:~$ psql -d data_lowlevel
Timing is on.
psql (13.3)
Type "help" for help.

postgres@data_lowlevel=# SELECT pg_relation_size('t', 'main') main, pg_relation_size ('t', 'fsm') fsm, pg_relation_size ('t', 'vm') vm;
  main  |  fsm  |  vm
--------+-------+------
 450560 | 24576 | 8192
(1 row)

Time: 0,659 ms

Посмотреть размер таблицы без учета индексов можно с помощью функции pg_table_size():

postgres@data_lowlevel=# SELECT pg_table_size('t');
 pg_table_size
---------------
        491520
(1 row)

Time: 0,625 ms

А размер индексов можно посмотреть с помощью функции pg_indexes_size():

postgres@data_lowlevel=# SELECT pg_indexes_size('t');
 pg_indexes_size
-----------------
          245760
(1 row)

Time: 0,479 ms

И полный размер таблицы вместе с индексами можно посмотреть с помощью функции pg_total_relation_size():

postgres@data_lowlevel=# SELECT pg_total_relation_size('t');
 pg_total_relation_size
------------------------
                 737280
(1 row)

Time: 0,247 ms

Таблицы TOAST

Тип данных numeric может работать с очень большими числами. Например число 123456789 в степени 12345 будет содержать 99907 цифр:

postgres@data_lowlevel=# SELECT length((123456789::numeric ^ 12345::numeric)::text);
 length
--------
  99907
(1 row)

Time: 173,803 ms

Такое число в одну строку (8 KB) не поместится. Значит для этого поля PostgreSQL создаст служебную TOAST-таблицу.

Теперь поместим это число в базу data_lowlevel в табличку «t»:

postgres@data_lowlevel=# INSERT INTO t(n) SELECT 123456789::numeric ^ 12345::numeric;
INSERT 0 1
Time: 204,066 ms

Найдем эту TOAST-таблицу:

postgres@data_lowlevel=# SELECT reltoastrelid FROM pg_class WHERE relname='t';
 reltoastrelid
---------------
         16501
(1 row)
Time: 0,428 ms

postgres@data_lowlevel=# SELECT relname, relfilenode FROM pg_class WHERE OID = 16501;
    relname     | relfilenode
----------------+-------------
 pg_toast_16497 |       16501
(1 row)
Time: 0,277 ms

Выше мы из таблички pg_class вытащили все идентификаторы TOAST-таблиц, для таблицы «t». А дальше по идентификатору (16501) узнали имя таблички (pg_toast_16497).

Файл для TOAST таблицы можем тоже найти:

postgres@data_lowlevel=# SELECT pg_relation_filepath('t');
 pg_relation_filepath
----------------------
 base/16494/16497
(1 row)
Time: 0,206 ms

data_lowlevel=# \q

postgres@data_lowlevel=# \q

postgres@s-pg13:~$ du -sh $PGDATA/base/16494/16501*
57K     /usr/local/pgsql/data/base/16494/16501
4,5K    /usr/local/pgsql/data/base/16494/16501_fsm

Выше я узнал каталог нашей базы данных base/16494/. А затем используя идентификатор таблички (pg_toast_16497 | 16501) посмотрел на файлы. Как видим основной слой TOAST-таблицы весит 57 KB. У этого объекта может быть fsm и vm слои. В этой табличке пока только одно большое число, которое не поместилось в поле (8 KB) обычной таблички «t».

Стратегии работы с большими строками

В теоретической части этой статьи мы уже разобрали что большие строки либо сжимаются, либо помещаются в TOAST-таблицу. Какая текущая стратегия работы для таблицы «t«? Можем посмотреть так:

postgres@s-pg13:~$ psql -d data_lowlevel
Timing is on.
psql (13.3)
Type "help" for help

postgres@data_lowlevel=# \d+ t
                                                Table "public.t"
 Column |  Type   | Collation | Nullable |            Default            | Storage | Stats target | Description
--------+---------+-----------+----------+-------------------------------+---------+--------------+-------------
 id     | integer |           | not null | nextval('t_id_seq'::regclass) | plain   |              |
 n      | numeric |           |          |                               | main    |              |
Indexes:
    "t_pkey" PRIMARY KEY, btree (id)
Access method: heap

Стратегия указывается в поле Storage. Выше мы видим что поле id имеет стратегию «plain«. Так как это поле имеет тип integer, то оно не может иметь большое значение и TOAST таблицы отключены. А поле n имеет тип numeric, и стратегию main — приоритет сжатия.

Возможные типы стратегий:

• plain — TOAST не применяется;
• main — сжимает по максимуму и только если никак не может поместить, то создает TOAST таблицу;
• externed — сжимает кусочек данных, и если видит что не помещается, создаёт TOAST таблицу;
• external — не пытается сжать, сразу создает TOAST-таблицу.

Поменять стратегию для таблицы можно с помощью ALTER TABLE … ALTER COLUMN … SET STORAGE …:

postgres@data_lowlevel=# ALTER TABLE t ALTER COLUMN n SET STORAGE extended;
ALTER TABLE
Time: 0,856 ms

Но эта команда не изменит существующие данные, а повлияет на работу с новыми данными.

---