Практикум
Табличное пространство
-
В сеансе postgres в оболочке создайте каталог для размещения табличного пространства:
[postgres@p620 ~]$ mkdir ts_data [postgres@p620 ~]$ ls
ts_data -
Подключитесь к БД postgres суперпользователем:
[postgres@p620 ~]$ psql psql (15.5)
Type "help" for help.
postgres=# -
Создайте табличное пространство
ts_lab:postgres=# CREATE TABLESPACE ts_lab LOCATION '/var/lib/postgres/ts_data';
CREATE TABLESPACE
postgres=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Descri
------------+----------+---------------------------+-------------------+---------+---------+-------
pg_default | postgres | | | | 63 MB |
pg_global | postgres | | | | 1002 MB |
ts_lab | postgres | /var/lib/postgres/ts_data | | | 0 bytes |
(3 rows) -
Создайте БД
ts_db, указав табличное пространствоts_labБД в качестве хранилища по умолчанию:postgres=# CREATE DATABASE ts_db TABLESPACE ts_lab; CREATE DATABASE -
Проверьте, увеличился ли размер занятого табличным пространством места на диске:
postgres=# SELECT pg_size_pretty(pg_tablespace_size('ts_lab'));
pg_size_pretty
----------------
9579 kB
(1 row)
postgres=# \x \db+ ts_lab \x
Expanded display is on.
List of tablespaces
-[ RECORD 1 ]-----+--------------------------
Name | ts_lab
Owner | postgres
Location | /var/lib/postgres/ts_data
Access privileges |
Options |
Size | 9579 kB
Description |
Expanded display is off. -
Сравните полученный размер с объемом БД
ts_db:postgres=# SELECT pg_size_pretty(pg_database_size('ts_db'));
pg_size_pretty
----------------
9567 kB
(1 row)Заметно, что есть небольшие накладные расходы.
Физическое хранение
-
Подключитесь к БД
ts_db:postgres=# \c ts_db
You are now connected to database "ts_db" as user "postgres". -
Создайте две таблицы: в одной столбцы типа int и float, в другой - int и text:
ts_db=# CREATE TABLE i_f (id int, datum float);
CREATE TABLE
ts_db=# CREATE TABLE i_t (id int, mmessg text);
CREATE TABLE
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)Обратите внимание на то, что исходные размеры таблиц отличаются. Необходимо определить причину.
-
Проверьте размеры таблиц, используя функцию
pg_total_relation_size:ts_db=# SELECT pg_size_pretty(pg_total_relation_size('i_f')); pg_size_pretty
----------------
0 bytes
(1 row)
ts_db=# SELECT pg_size_pretty(pg_total_relation_size('i_t')); pg_size_pretty
----------------
8192 bytes
(1 row)Причина в том, что типы данных int и float имеют фиксированный размер и строки с двумя такими столбцами гарантированно поместятся на странице. А столбец типа text может хранить текстовое значение неограниченной длины.
-
Исследуйте тип хранилищ для столбцов таблиц:
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.
-
Таблица TOAST:
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)Так как таблица
i_fне имеет столбцов, данные в которых могут не поместиться на одну страницу, она не использует таблицу TOAST, что можно проверить в системном каталоге pg_class, содержащем метаданные о всех отношениях в БД. Поле reltoastrelid содержит OID таблицы TOAST или 0. Для таблицы i_t с текстовым полем это поле не пустое - оно указывает OID таблицы TOAST, имя которой можно узнать с помощью reg-приведения типа.Проверьте размер таблицы TOAST (таблица имеет индекс):
ts_db=# SELECT pg_size_pretty(pg_total_relation_size('pg_toast.pg_toast_16436')); pg_size_pretty
----------------
8192 bytes
(1 row)Вывод: для таблиц со столбцами, могущими потребовать TOAST, даже при отсутствии строк в них, таблица TOAST со своим индексом создается сразу.
-
Получите полные имена файлов, в которых будут размещаться данные таблиц i_f и i_t:
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)Или, например, так:
ts_db=# select pg_relation_filepath(tablename::regclass) from pg_catalog.pg_tables where tablename ~ '^i_.$';
pg_relation_filepath
---------------------------------------------
pg_tblspc/16431/PG_15_202310091/16432/16433
pg_tblspc/16431/PG_15_202310091/16432/16436
(2 rows) -
Проверьте размеры файлов, полученных в предыдущем пункте лабораторной работы:
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 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Метакоманда
\gпозволяет записать вывод команд SQL в файл или передать их через конвейер для обработки командам ОС. Конструкция (tuples_only=on format=unaligned) отключает вывод заголовков и выравнивание. КомандаSELECTпередает через конвейер два относительных имени файла, находящихся вPGDATA- это имена файлов, физически содержащих данные таблиц. Командаxargsпринимает имена файлов для дальнейшей обработки - она конструирует командную строку дляls -l, предоставляющей подробную информацию о файлах. Пока файлы пусты. -
Вставьте в таблицы по одной произвольной строке и снова проверьте размеры файлов:
ts_db=# INSERT INTO i_f VALUES(1,2.0); INSERT 0 1
ts_db=# INSERT INTO i_t VALUES(1,'2');
INSERT 0 1
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Кб.
Слои
-
Проверьте на личие слоев FSM и VM для таблиц:
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 8192 Nov 10 10:28
/pgdata/06/data/pg_tblspc/16431/PG_15_202310091/16432/16436Пока имеются лишь основные слои таблиц -
main.В этом примере вместо
xargs- конструктора командной строки используется более потоковый редактор sed. Ему задан скрипт's/\(^.*$\)/ls -l \$PGDATA\/\1*/'- в нем выполняется замена в строках, так как указана командаs - substitute. Общий вид команды:s/<шаблон поиска>/<замена>/Здесь в
stdinкоманды sed поступают имена файлов от командыSELECT. Шаблон поиска дляsed ^.*$- это регулярное выражение, которому соответствуют любые строки (метасимвол.*) от начала (метасимвол^), до конца строки (метасимвол$). Шаблон для все строки^.*$помещен в круглые скобки, которые экранированы с помощью обратной косой черты\, для запоминания в буфере найденной по регулярному выражению строки. Этот буфер подставляется в измененную строку с помощью\1- это вставляет данные из первого буфера (у нас он единственный). Эта операция в регулярных выражениях называется back reference.Далее задача sed состоит в добавлении до имени файла строки
ls -lи пути к каталогуPGDATA, а после имени файла добавляется символ*- файловый шаблон для поиска. Так как на выходе sed просто строка, представляющая из себяls -l $PGDATA/<имя файла>*, эту строку надо выполнить, просто передав ее через конвейер оболочке bash. -
Выполните команду очистки и снова проверьте наличие слоев:
ts_db=# VACUUM;
VACUUM
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 -
Сравните полученные размеры с данными, возвращаемыми функцией
pg_relation_size: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)
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) -
Вставьте в таблицы по сто тысяч строк и снова повторите измерение:
ts_db=# INSERT INTO i_f SELECT g.i, random() FROM generate_series(1,100000) as g(i);
INSERT 0 100000
ts_db=# INSERT INTO i_t SELECT g.i, random()::text FROM generate_series(1,100000) as g(i);
INSERT 0 100000
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)
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)Объем, занимаемый слоем main значительно вырос, а слои FSM и VM не увеличились.
Программа oid2name
-
Выйдите из се анса psql и в оболочке Bash работайте пользователем
postgres:ts_db=# \q [postgres@p620 ~]$ -
Получите командой oid2name список БД кластера, их
OIDи табличных пространств:[postgres@p620 ~]$ oid2name All databases:
Oid Database Name Tablespace
----------------------------------
16390 koi8_db pg_default
5 postgres pg_default
16401 py_json_db pg_default
16389 student pg_default
4 template0 pg_default
1 template1 pg_default
16432 ts_db ts_lab -
Получите список всех имеющихся табличных пространств кластера:
[postgres@p620 ~]$ oid2name -s All tablespaces:
Oid Tablespace Name
------------------------
1663 pg_default
1664 pg_global
16431 ts_lab -
Определите названия файлов данных таблиц БД
ts_db:[postgres@p620 ~]$ oid2name -d ts_db From database "ts_db":
Filenode Table Name
----------------------
16433 i_f
16436 i_t -
В подробном виде получите данные о таблице
i_tбазы данныхts_db:[postgres@p620 ~]$ oid2name -d ts_db -t i_t -x From database "ts_db":
Filenode Table Name Oid Schema Tablespace
---------------------------------------------------------
16436 i_t 16436 public ts_lab -
Находясь в домашнем каталоге postgres командой
findОС найдите файл данных таблицыi_t(см. выше - он выделен):[postgres@p620 ~]$ find ~postgres/ -name 16436 /var/lib/postgres/ts_data/PG_15_202310091/16432/16436 -
Убедитесь, что каталог, в котором размещен этот файл соответствует OID БД
ts_db:[postgres@p620 ~]$ oid2name | grep ts_db 16432 ts_db ts_lab -
Обратная задача: известно имя файла данных в каталоге с OID БД, требуется найти имя таблицы:
[postgres@p620 ~]$ oid2name -f 16436 -d ts_db From database "ts_db":
Filenode Table Name
---------------------------
16436 i_t -
В каталоге с OID БД
ts_dbотфильтруйте все файлы с именами из цифр, отсортируйте полученный список по убыванию, выберите файл данный с максимальным OID и определите, какой это объект БД:[postgres@p620 ~]$ oid2name -d ts_db -f $(ls ts_data/PG_15_202310091/16432/ | egrep '^[0-9]+$' | sort -nr | head -1)
From database "ts_db":
Filenode Table Name
--------------------------------
16440 pg_toast_16436_indexЭто индекс для таблицы TOAST, для хранения длинных строк таблицы
i_t.В примере использована командная подстановка
$(). В ней полученный командойlsсписок файлов фильтруется командойegrep, которая отбрасывает все имена файлов, в которых есть любые нецифровые символы, далее командаsortсортирует список по убыванию в числовом порядке. Командаhead -1из отсортированного списка выбирает первый файл - у него самый большойOIDиз имеющихся в этом каталоге.