tablichnye_prostranstva_v

Табличные пространства в Postgresql

Табличные пространства в Postgresql служат для организации физического хранения данных и определяют расположение данных в файловой системе

Табличные пространства

Табличные пространства служат для организации физического хранения данных и определяют расположение данных в файловой системе
Например, можно создать одно ТП на медленных дисках для архивных данных, а другое — на быстрых дисках для данных, с которыми идет активная работа
При инициализации кластера создаются два ТП: pg_default и pg_global


testdb=# SELECT * FROM pg_tablespace;
 1663 | pg_default |       10 |        |
 1664 | pg_global  |       10 |        |

testdb=#

Одно и то же ТП может использоваться разными базами данных, а одна база данных может хранить данные в нескольких ТП
При этом у каждой БД есть так называемое «ТП по умолчанию»,в котором создаются все объекты, если не указано иное. В этом жеТП хранятся и объекты системного каталога. Изначально в качестве «ТП по умолчанию» используется ТП pg_default, но можно установитьи другое.ТП pg_global особенное: в нем хранятся те объекты системного каталога, которые являются общими для кластера
https://postgrespro.ru/docs/postgresql/16/manage-ag-tablespaces
pg_global - PGDATA/global
pg_default - PGDATA/base/dboid/
ts - PGDATA/pg_tblspc/tsoid
Проще говоря, табличное пространство - это указание на каталог, в котором хранятся данные.
При создании пользовательского ТП указывается произвольный каталог. Postgresql создает на него симлинк в каталоге PGDATA/pg_tblspc/

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


sudo -u postgres mkdir /var/lib/postgresql/ts_dir


--Создать табличное пространство
CREATE TABLESPACE ts LOCATION '/var/lib/postgresql/ts_dir';

--Список табличных пространств
\db

У каждой базы данных есть табличное пространство «по умолчанию». Создадим базу и назначим ей ts в качестве такого пространства, чтобы все создаваемые таблицы и индексы попадали в ts, если явно не указать другое


--Создать базу данных и табличное пространсто для базы по умолчанию
CREATE DATABASE appdb TABLESPACE ts;

\c appdb

--Создать таблицу 1
CREATE TABLE t1(
  id integer GENERATED ALWAYS AS IDENTITY,
  name text
);

--Создать таблицу 2 и явно указать табличное пространство
CREATE TABLE t2(
  n numeric
) TABLESPACE pg_default;

appdb=# SELECT tablename, tablespace FROM pg_tables WHERE schemaname = 'public';
 t1        |
 t2        | pg_default

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


appdb=# CREATE INDEX ON t1(id) TABLESPACE pg_default;
CREATE INDEX
appdb=#
appdb=# SELECT indexname,tablespace FROM pg_indexes WHERE tablename='t1';
 t1_id_idx | pg_default

Еще один способ задать табличное пространство без явного указания при создании объекта — предварительно установить значение параметра default_tablespace


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

configdb=#
configdb=# -- Для временных таблиц и их индексов можно указать отдельное табличное пространство по умолчанию
configdb=#
configdb=# SET temp_tablespaces = 'ts';
SET
configdb=# CREATE TEMP TABLE temp(s text);
CREATE TABLE
configdb=#
configdb=# \d temp
 s      | text |           |          |

configdb=#

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


appdb=# --Переместить таблицу в другое пространство
appdb=# ALTER TABLE t1 SET TABLESPACE pg_default;
ALTER TABLE
appdb=#

appdb=#
appdb=# SELECT tablename, tablespace FROM pg_tables WHERE schemaname = 'public';
 t2        | pg_default
 t1        | pg_default

appdb=#
appdb=# --Перенести индексы можно и при перестроении:
appdb=# REINDEX (TABLESPACE ts) TABLE t1;
REINDEX
appdb=#
appdb=#
appdb=#
appdb=# --Можно переместить и все объекты из одного табличного пространства в другое:
appdb=# ALTER TABLE ALL IN TABLESPACE pg_default SET TABLESPACE ts;
ALTER TABLE
appdb=#
appdb=# SELECT tablename, tablespace FROM pg_tables WHERE schemaname = 'public';
 t2        |
 t1        |

appdb=#

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

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

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


appdb=#
appdb=# SELECT pg_size_pretty( pg_tablespace_size('ts') );
 8657 kB

appdb=#
appdb=# --В psql получить размер табличных пространств можно командой:
appdb=# \db+
 pg_default | postgres |                            |                   |         | 42 MB   |
 pg_global  | postgres |                            |                   |         | 560 kB  |
 ts         | postgres | /var/lib/postgresql/ts_dir |                   |         | 8657 kB |

appdb=#

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

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


DROP TABLESPACE ts;

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


-- Сначала узнаем и запомним идентификатор табличного пространства:
appdb=# SELECT oid FROM pg_tablespace WHERE spcname = 'ts';
 16412

appdb=# --Затем получим список баз данных, в которых есть объекты из удаляемого пространства:
appdb=#
appdb=# SELECT datname
FROM pg_database
WHERE oid IN (SELECT pg_tablespace_databases(16412));
 configdb
 appdb

appdb=#
appdb=# --Дальше подключаемся к каждой базе данных и получаем список объектов из pg_class:
appdb=# \c configdb
You are now connected to database "configdb" as user "postgres".
configdb=#
configdb=# SELECT relnamespace::regnamespace, relname, relkind
FROM pg_class
WHERE reltablespace = 16412;
 public       | t       | r

configdb=#
configdb=# DROP TABLE t;
DROP TABLE
configdb=#

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


=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# ALTER DATABASE appdb SET TABLESPACE pg_default;
ALTER DATABASE
postgres=#
postgres=# --Вот теперь табличное пространство может быть удалено.
postgres=# DROP TABLESPACE ts;
DROP TABLESPACE
postgres=#
postgres=# --От имени пользователя postgres удалим каталог, созданный для табличного пространства.
postgres=#


sudo -u postgres rm -rf /var/lib/postgresql/ts_dir

Табличные пространства — средство для организации физического хранения данных
Логическое (базы данных, схемы) и физическое (табличные пространства) разделения данных независимы

Источник: https://edu.postgrespro.ru/16/dba1-16/dba1_10_data_tablespaces.html

https://postgrespro.ru/docs/postgresql/16/manage-ag-tablespaces