
        psql 

При создании кластера создаются два табличных пространства:

        => select * from pg_tablespace;
          spcname   | spcowner | spcacl | spcoptions 
        ------------+----------+--------+------------
         pg_default |       10 |        | 
         pg_global  |       10 |        | 
        (2 rows)
        

* pg_global  - общие объекты кластера,
* pg_default - табличное пространство по умолчанию.

-----------------------------------------------------------------------

Для нового табличного пространства нужен пустой каталог,
владельцем которого является пользователь postgres.

cd /home/postgres 
mkdir ts1_dir 
ls -l ts1_dir 
total 0

-----------------------------------------------------------------------

Теперь можно создать табличное пространство:

        => create tablespace ts1 location '/home/postgres/ts1_dir';
        CREATE TABLESPACE

Список можно получить и командой psql:

        => \db
                      List of tablespaces
            Name    |  Owner   |        Location        
        ------------+----------+------------------------
         pg_default | postgres | 
         pg_global  | postgres | 
         ts1        | postgres | /home/postgres/ts1_dir
        (3 rows)
        

-----------------------------------------------------------------------

Табличное пространство может использоваться несколькими базами кластера.
При этом у каждой базы есть табличное пространство по умолчанию.

Создадим БД и назначим ей ts1 в качестве пространства по умолчанию:

        => create database test tablespace ts1;
        CREATE DATABASE

-----------------------------------------------------------------------

Теперь все создаваемые таблицы и индексы будут попадать в ts1,
если явно не указать другое.

Подключимся к базе:

        => \c test
        You are now connected to database "test" as user "postgres".

Создадим таблицу:

        => create table t1(id serial, name text);
        CREATE TABLE

        => select * from pg_tables where tablename='t1';
         schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers 
        ------------+-----------+------------+------------+------------+----------+-------------
         public     | t1        | postgres   |            | f          | f        | f
        (1 row)
        

Пустое поле tablespace указывает на табличное пространство по умолчанию.

-----------------------------------------------------------------------

Можно изменить табличное пространство по умолчанию, но при этом все таблицы
физически переносятся из одного пространства в другое.
Предварительно надо отключиться от базы.

        => \c postgres
        You are now connected to database "postgres" as user "postgres".

        => alter database test set tablespace pg_default;
        ALTER DATABASE

        => \c test
        You are now connected to database "test" as user "postgres".

-----------------------------------------------------------------------

При создании объекта можно явно указать табличное пространство:

        => create table t2(n numeric) tablespace ts1;
        CREATE TABLE

        => select * from pg_tables where tablename like 't_';
         schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers 
        ------------+-----------+------------+------------+------------+----------+-------------
         public     | t1        | postgres   |            | f          | f        | f
         public     | t2        | postgres   | ts1        | f          | f        | f
        (2 rows)
        

-----------------------------------------------------------------------

Каждая таблица (или индекс) хранится на диске в нескольких файлах.

Таблица t1 принадлежит табличному пространству pg_default, поэтому
ее следует искать в $PGDATA/base/
Имя подкаталога - oid (object identifier) базы данных:

        => select oid, datname from pg_database where datname='test';
          oid  | datname 
        -------+---------
         24723 | test
        (1 row)
        

        => select oid as dboid from pg_database where datname='test' \gset

        => \setenv DBOID :dboid

-----------------------------------------------------------------------

Имена файлов, относящиеся к таблице, будут начинаться на следующее число:

        => select relname, relfilenode from pg_class where relname='t1';
         relname | relfilenode 
        ---------+-------------
         t1      |       24726
        (1 row)
        

        => select relfilenode as relid from pg_class where relname='t1' \gset

        => \setenv RELID :relid

-----------------------------------------------------------------------

Теперь можно посмотреть сами файлы:

        => \! ls -l $PGDATA/base/$DBOID/$RELID*
        -rw------- 1 postgres postgres 0 июня  16 17:23 /usr/local/pgsql/data/base/24723/24726

Сейчас файл один и пустой, поскольку в таблице ничего нет. Добавим строк:

        => insert into t1(name) select s.a::text from generate_series(1,10000) as s(a);
        INSERT 0 10000

        => vacuum t1;
        VACUUM

-----------------------------------------------------------------------

Снова посмотрим:

        => \! ls -l $PGDATA/base/$DBOID/$RELID*
        -rw------- 1 postgres postgres 401408 июня  16 17:23 /usr/local/pgsql/data/base/24723/24726
        -rw------- 1 postgres postgres  24576 июня  16 17:23 /usr/local/pgsql/data/base/24723/24726_fsm
        -rw------- 1 postgres postgres   8192 июня  16 17:23 /usr/local/pgsql/data/base/24723/24726_vm

Теперь видим три файла:
* основной - собственно данные таблицы,
* fsm (free space map) - информация о свободном месте в страницах
* vm (visibility map) - информация о видимости версий строк для многоверсионности

Размер каждого из файлов можно узнать, не обращаясь к файловой системе, например:

        => select pg_relation_size('t1','fsm');
         pg_relation_size 
        ------------------
                    24576
        (1 row)
        

-----------------------------------------------------------------------

Добавим в таблицу очень длинную строку:

        => insert into t1(name) select string_agg(s.a::text,'.') from generate_series(1,10000) as s(a);
        INSERT 0 1

        => vacuum;
        VACUUM

Посмотрим на размер файлов:

        => \! ls -l $PGDATA/base/$DBOID/$RELID*
        -rw------- 1 postgres postgres 401408 июня  16 17:23 /usr/local/pgsql/data/base/24723/24726
        -rw------- 1 postgres postgres  24576 июня  16 17:23 /usr/local/pgsql/data/base/24723/24726_fsm
        -rw------- 1 postgres postgres   8192 июня  16 17:23 /usr/local/pgsql/data/base/24723/24726_vm

Размер не изменился. Где же строка?

-----------------------------------------------------------------------

Если строка не помещается в страницу, длинные значения будут храниться отдельно.
Это происходит прозрачно для приложения. Значение может сжиматься и распределяться
по нескольким страницами.
Эта технология называется TOAST (The Oversized-Attribute Storage Technique).

-----------------------------------------------------------------------

Найдем таблицу с данными TOAST:

        => select reltoastrelid as trelid from pg_class where relname='t1';
         trelid 
        --------
          24730
        (1 row)
        

        => select reltoastrelid as trelid from pg_class where relname='t1' \gset

        => \setenv TRELID :trelid

        => \! ls -l $PGDATA/base/$DBOID/$TRELID*
        -rw------- 1 postgres postgres 40960 июня  16 17:23 /usr/local/pgsql/data/base/24723/24730
        -rw------- 1 postgres postgres 24576 июня  16 17:23 /usr/local/pgsql/data/base/24723/24730_fsm
        -rw------- 1 postgres postgres  8192 июня  16 17:23 /usr/local/pgsql/data/base/24723/24730_vm

В этих файлах и хранится наша строка.

-----------------------------------------------------------------------

Посмотрим на индексы.

        => create index t1_id on t1(id);
        CREATE INDEX

        => vacuum;
        VACUUM

-----------------------------------------------------------------------

Другой способ найти путь к файлам - воспользоваться функцией:

        => select pg_relation_filepath('t1_id') as indexpath;
            indexpath     
        ------------------
         base/24723/24740
        (1 row)
        

        => select pg_relation_filepath('t1_id') as indexpath \gset

        => \setenv INDEXPATH :indexpath

        => \! ls -l $PGDATA/$INDEXPATH*
        -rw------- 1 postgres postgres 196608 июня  16 17:23 /usr/local/pgsql/data/base/24723/24740

Для индексов не бывает файла vm, так как индексы не хранят информации о многоверсионности.

-----------------------------------------------------------------------

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

-----------------------------------------------------------------------

Существует полезное расширение oid2name, входящее в стандартную поставку, с помощью которого
можно легко связать объекты БД и файлы.
Можно посмотреть все базы данных:

        => \! oid2name
        All databases:
            Oid  Database Name  Tablespace
        ----------------------------------
          12175       postgres  pg_default
          12170      template0  pg_default
              1      template1  pg_default
          24723           test  pg_default

-----------------------------------------------------------------------

Можно посмотреть все объекты в базе:

        => \! oid2name -d test
        From database "test":
          Filenode  Table Name
        ----------------------
             24726          t1
             24733          t2

Или все табличные пространства в базе:

        => \! oid2name -d test -s
        All tablespaces:
            Oid  Tablespace Name
        ------------------------
           1663       pg_default
           1664        pg_global
          24722              ts1

-----------------------------------------------------------------------

Можно по имени таблицы узнать имя файла:

        => \! oid2name -d test -t t1
        From database "test":
          Filenode  Table Name
        ----------------------
             24726          t1

Или наоборот, по номеру файла узнать таблицу:

        => \! oid2name -d test -f $RELID
        From database "test":
          Filenode  Table Name
        ----------------------
             24726          t1

-----------------------------------------------------------------------

Уже созданную таблицу (или индекс) можно переместить в другое табличное пространство.
Создадим еще одно табличное пространство ts2 и переместим в него таблицу t1.

mkdir ts2_dir 

        => create tablespace ts2 location '/home/postgres/ts2_dir';
        CREATE TABLESPACE

        => alter table t1 set tablespace ts2;
        ALTER TABLE

        => select * from pg_tables where tablename like 't_';
         schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers 
        ------------+-----------+------------+------------+------------+----------+-------------
         public     | t1        | postgres   | ts2        | t          | f        | f
         public     | t2        | postgres   | ts1        | f          | f        | f
        (2 rows)
        

-----------------------------------------------------------------------

Можно переместить и все таблицы сразу:

        => alter table all in tablespace ts1 set tablespace ts2;
        ALTER TABLE

        => select * from pg_tables where tablename like 't_';
         schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers 
        ------------+-----------+------------+------------+------------+----------+-------------
         public     | t1        | postgres   | ts2        | t          | f        | f
         public     | t2        | postgres   | ts2        | f          | f        | f
        (2 rows)
        

-----------------------------------------------------------------------

Табличное пространство можно переименовать:

        => alter tablespace ts1 rename to test_tablespace;
        ALTER TABLESPACE

А также удалить...

        => drop tablespace test_tablespace;
        DROP TABLESPACE

        => drop tablespace ts2;
        ERROR:  tablespace "ts2" is not empty

...но только, если оно пусто.

-----------------------------------------------------------------------

        => drop table t1;
        DROP TABLE

        => drop table t2;
        DROP TABLE

        => drop tablespace if exists ts2;
        DROP TABLESPACE

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

-----------------------------------------------------------------------

        => \q
