Посмотрим на файлы, принадлежащие таблице.
=> CREATE DATABASE data_lowlevel;
CREATE DATABASE
=> \c data_lowlevel
You are now connected to database "data_lowlevel" as user "postgres".
=> CREATE TABLE t(id serial PRIMARY KEY, n numeric);
CREATE TABLE
=> INSERT INTO t(n) SELECT g.id FROM generate_series(1,10000) AS g(id);
INSERT 0 10000
Базовое имя файла относительно PGDATA можно получить функцией:
=> SELECT pg_relation_filepath('t');
pg_relation_filepath ---------------------- base/16499/16502 (1 row)
Поскольку таблица находится в табличном пространстве pg_default, имя начинается на base. Затем идет каталог для базы данных:
=> SELECT OID FROM pg_database WHERE datname = 'data_lowlevel';
oid ------- 16499 (1 row)
Затем - собственно имя файла. Его можно узнать следующим образом:
=> SELECT relfilenode FROM pg_class WHERE relname = 't';
relfilenode
-------------
16502
(1 row)
Тем и удобна функция, что выдает готовый путь без необходимости выполнять несколько запросов к системному каталогу.
Посмотрим на файлы:
postgres$ ls -l /usr/local/pgsql/data/base/16499/16502*
-rw------- 1 postgres postgres 409600 мар 31 15:25 /usr/local/pgsql/data/base/16499/16502 -rw------- 1 postgres postgres 24576 мар 31 15:25 /usr/local/pgsql/data/base/16499/16502_fsm
Сейчас мы видим только два слоя: основной и карта свободного пространства. Карта видимости появляется только после выполнения очистки:
=> VACUUM t;
VACUUM
postgres$ ls -l /usr/local/pgsql/data/base/16499/16502*
-rw------- 1 postgres postgres 409600 мар 31 15:25 /usr/local/pgsql/data/base/16499/16502 -rw------- 1 postgres postgres 24576 мар 31 15:25 /usr/local/pgsql/data/base/16499/16502_fsm -rw------- 1 postgres postgres 8192 мар 31 15:25 /usr/local/pgsql/data/base/16499/16502_vm
Аналогично можно посмотреть и на файлы индекса...
=> \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)
=> SELECT pg_relation_filepath('t_pkey');
pg_relation_filepath ---------------------- base/16499/16509 (1 row)
postgres$ ls -l /usr/local/pgsql/data/base/16499/16509*
-rw------- 1 postgres postgres 196608 мар 31 15:25 /usr/local/pgsql/data/base/16499/16509
...и на файлы последовательности:
=> SELECT pg_relation_filepath('t_id_seq');
pg_relation_filepath ---------------------- base/16499/16500 (1 row)
postgres$ ls -l /usr/local/pgsql/data/base/16499/16500*
-rw------- 1 postgres postgres 8192 мар 31 15:25 /usr/local/pgsql/data/base/16499/16500
Существует полезное расширение oid2name, входящее в стандартную поставку, с помощью которого можно легко связать объекты БД и файлы.
Можно посмотреть все базы данных:
postgres$ oid2name
All databases:
Oid Database Name Tablespace
----------------------------------
16499 data_lowlevel pg_default
12328 postgres pg_default
12327 template0 pg_default
1 template1 pg_default
Можно посмотреть все объекты в базе:
postgres$ oid2name -d data_lowlevel
From database "data_lowlevel":
Filenode Table Name
----------------------
16502 t
Или все табличные пространства в базе:
postgres$ oid2name -d data_lowlevel -s
All tablespaces: Oid Tablespace Name ----------------------- 1663 pg_default 1664 pg_global
Можно по имени таблицы узнать имя файла:
postgres$ oid2name -d data_lowlevel -t t
From database "data_lowlevel":
Filenode Table Name
----------------------
16502 t
Или наоборот, по номеру файла узнать таблицу:
postgres$ oid2name -d data_lowlevel -f 16502
From database "data_lowlevel":
Filenode Table Name
----------------------
16502 t
Размер каждого из файлов, входящих в слой, можно, конечно, посмотреть в файловой системе, но существуют и специальные функции.
Размер каждого слоя в отдельности:
=> SELECT pg_relation_size('t','main') main, pg_relation_size('t','fsm') fsm, pg_relation_size('t','vm') vm;
main | fsm | vm --------+-------+------ 409600 | 24576 | 8192 (1 row)
Размер таблицы без индексов:
=> SELECT pg_table_size('t');
pg_table_size
---------------
450560
(1 row)
Размер индексов таблицы:
=> SELECT pg_indexes_size('t');
pg_indexes_size
-----------------
196608
(1 row)
И размер всей таблицы, включая индексы:
=> SELECT pg_total_relation_size('t');
pg_total_relation_size
------------------------
647168
(1 row)
В таблице t есть столбец типа numeric. Этот тип может работать с очень большими числами. Например, с такими:
=> SELECT length( (123456789::numeric ^ 12345::numeric)::text );
length -------- 99907 (1 row)
При этом, если вставить такое значение в таблицу, размер файлов не изменится:
=> SELECT pg_relation_size('t','main');
pg_relation_size
------------------
409600
(1 row)
=> INSERT INTO t(n) SELECT 123456789::numeric ^ 12345::numeric;
INSERT 0 1
=> SELECT pg_relation_size('t','main');
pg_relation_size
------------------
409600
(1 row)
Поскольку версия строки не помещается на одну страницу, она хранится в отдельной TOAST-таблице. TOAST-таблица и индекс к ней создаются автоматически для каждой таблицы, в которой есть потенциально "длинный" тип данных, и используются по необходимости.
Имя и идентификатор такой таблицы можно найти следующим образом:
=> SELECT relname, relfilenode FROM pg_class WHERE OID = ( SELECT reltoastrelid FROM pg_class WHERE relname='t' );
relname | relfilenode
----------------+-------------
pg_toast_16502 | 16506
(1 row)
Вот и файлы TOAST-таблицы:
postgres$ ls -l /usr/local/pgsql/data/base/16499/16506*
-rw------- 1 postgres postgres 57344 мар 31 15:25 /usr/local/pgsql/data/base/16499/16506 -rw------- 1 postgres postgres 24576 мар 31 15:25 /usr/local/pgsql/data/base/16499/16506_fsm
Существуют несколько стратегий работы с длинными значениями. Название стратегии указывается в поле Storage:
=> \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)
Стратегию можно изменить, если это необходимо. Например, если известно, что в столбце хранятся уже сжатые данные, разумно поставить стратегию external.
Просто для примера:
=> ALTER TABLE t ALTER COLUMN n SET STORAGE extended;
ALTER TABLE
Эта операция не меняет существующие данные в таблице, но определяет стратегию работы с новыми данными.
Конец демонстрации.