Расположение файлов

Посмотрим на файлы, принадлежащие таблице.

=> 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 дек 24 17:29 /usr/local/pgsql/data/base/16499/16502
-rw------- 1 postgres postgres  24576 дек 24 17:29 /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 дек 24 17:29 /usr/local/pgsql/data/base/16499/16502
-rw------- 1 postgres postgres  24576 дек 24 17:29 /usr/local/pgsql/data/base/16499/16502_fsm
-rw------- 1 postgres postgres   8192 дек 24 17:29 /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 дек 24 17:29 /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 дек 24 17:29 /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)


TOAST

В таблице 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 дек 24 17:29 /usr/local/pgsql/data/base/16499/16506
-rw------- 1 postgres postgres 24576 дек 24 17:29 /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

Эта операция не меняет существующие данные в таблице, но определяет стратегию работы с новыми данными.


Конец демонстрации.