Служебные табличные пространства

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

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


Пользовательские табличные пространства

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

postgres$ cd /home/postgres
postgres$ mkdir ts_dir

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

=> CREATE TABLESPACE ts LOCATION '/home/postgres/ts_dir';
CREATE TABLESPACE

Список табличных пространств можно получить и командой psql:

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


У каждой базы данных есть табличное пространство "по умолчанию".

Создадим БД и назначим ей ts в качестве такого пространства:

=> CREATE DATABASE appdb TABLESPACE ts;
CREATE DATABASE

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

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

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

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

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

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

=> CREATE TABLE t2(n numeric) TABLESPACE pg_default;
CREATE TABLE
=> SELECT tablename, tablespace FROM pg_tables WHERE schemaname = 'public';
 tablename | tablespace 
-----------+------------
 t1        | 
 t2        | pg_default
(2 rows)

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


Одно табличное пространство может использоваться для объектов нескольких баз данных.

=> CREATE DATABASE configdb;
CREATE DATABASE

У этой БД табличным пространством по умолчанию будет pg_default.

=> \c configdb
You are now connected to database "configdb" as user "postgres".
=> CREATE TABLE t(n integer) TABLESPACE ts;
CREATE TABLE

Управление объектами в табличных пространствах

Таблицы (и другой объекты, например, индексы), можно перемещать между табличными пространствами.

=> \c appdb
You are now connected to database "appdb" as user "postgres".
=> ALTER TABLE t1 SET TABLESPACE pg_default;
ALTER TABLE
=> SELECT tablename, tablespace FROM pg_tables WHERE schemaname = 'public';
 tablename | tablespace 
-----------+------------
 t2        | pg_default
 t1        | pg_default
(2 rows)


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

=> ALTER TABLE ALL IN TABLESPACE pg_default SET TABLESPACE ts;
ALTER TABLE
=> SELECT tablename, tablespace FROM pg_tables WHERE schemaname = 'public';
 tablename | tablespace 
-----------+------------
 t2        | 
 t1        | 
(2 rows)

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


Размер табличного пространства

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

=> SELECT pg_size_pretty( pg_tablespace_size('ts') );
 pg_size_pretty 
----------------
 7134 kB
(1 row)

Почему такой размер, если в табличном пространстве всего несколько пустых таблиц?


Поскольку ts является табличным пространством по умолчанию для базы appdb, в нем хранятся объекты системного каталога. Они и занимают место.


Удаление табличного пространства

Табличное пространство можно удалить, но только в том случае, если оно пусто.

=> DROP TABLESPACE ts;
ERROR:  tablespace "ts" is not empty

В отличие от удаления схемы, в команде DROP TABLESPACE нельзя использовать фразу CASCADE: объекты табличного пространства могут принадлежать разным базам данных, а подключены мы только к одной.


Но можно выяснить, в каких базах есть зависимые объекты. В этом нам поможет системный каталог.

Сначала узнаем и запомним OID табличного пространства:

=> SELECT OID FROM pg_tablespace WHERE spcname = 'ts';
  oid  
-------
 16469
(1 row)

=> SELECT OID AS tsoid FROM pg_tablespace WHERE spcname = 'ts' \gset

Затем получим список баз данных, в которых есть объекты из удаляемого пространства:

=> SELECT datname
FROM pg_database
WHERE OID IN (SELECT pg_tablespace_databases(:tsoid));
 datname  
----------
 configdb
 appdb
(2 rows)


Дальше подключаемся к каждой базе данных и получаем список объектов из pg_class:

=> \c configdb
You are now connected to database "configdb" as user "postgres".
=> SELECT relnamespace::regnamespace, relname, relkind
FROM pg_class
WHERE reltablespace = :tsoid;
 relnamespace | relname | relkind 
--------------+---------+---------
 public       | t       | r
(1 row)


Таблица больше не нужна, удалим ее.

=> DROP TABLE t;
DROP TABLE

И вторая база данных. Но, поскольку ts является табличным пространством по умолчанию, у объектов в pg_class поле содержит ноль:

=> \c appdb
You are now connected to database "appdb" as user "postgres".
=> SELECT count(*) FROM pg_class WHERE reltablespace = 0;
 count 
-------
   313
(1 row)

Это, как нам уже известно, объекты системного каталога.


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

=> \c postgres
You are now connected to database "postgres" as user "postgres".
=> ALTER DATABASE appdb SET TABLESPACE pg_default;
ALTER DATABASE

Вот теперь табличное пространство может быть удалено.

=> \c appdb
You are now connected to database "appdb" as user "postgres".
=> DROP TABLESPACE ts;
DROP TABLESPACE

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