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

Дарья Барышева

Обзор

Табличное пространство (tablespace) — это системный объект, позволяющий администраторам размещать объекты баз данных (таблицы, индексы, spill-файлы и другое) в различных директориях и даже на различных дисках. Другими словами, табличное пространство определяет, где хранить физические файлы для логических объектов БД. Благодаря наличию табличных пространств возможно иметь несколько файловых систем на одной машине и выбирать наиболее оптимальный способ хранения для различных данных. Например, часто используемые таблицы можно хранить на высокопроизводительных SSD-дисках, а прочие таблицы (в первую очередь, с архивными данными) — на стандартных HDD-дисках.

Табличное пространство — это глобальный объект, к которому можно обращаться из любой базы данных при наличии соответствующих прав.

Существует несколько табличных пространств, используемых по умолчанию в каждом ADB-кластере. Они добавляются автоматически на этапе инициализации СУБД. Их описание приведено в таблице ниже.

Используемые по умолчанию табличные пространства
Название Описание

pg_global

Используется для хранения общих системных каталогов

pg_default

Используется другими объектами баз данных. Например, шаблонные БД template0 и template1 используют это табличное пространство. Поэтому по умолчанию оно также применяется ко всем пользовательским таблицам, если при вызове команды CREATE DATABASE не указывается явно параметр TABLESPACE
ВНИМАНИЕ
Не рекомендуется удалять либо изменять табличные пространства, приведенные выше.

Просмотр существующих табличных пространств

Для просмотра существующих табличных пространств в текущем ADB-кластере выполните следующие шаги:

  1. Подключитесь к мастер-хосту ADB (например, через SSH) под пользователем gpadmin, который создается по умолчанию.

    $ sudo su - gpadmin

  2. Подключитесь к БД через psql (или любую иную клиентскую программу).

    $ psql adb

    Результат команды:

    psql (9.4.24)
Type "help" for help.

  3. Выполните одну из следующих команд:

    • Сделайте выборку из таблицы pg_catalog.pg_tablespace.

      SELECT oid, * FROM pg_catalog.pg_tablespace;

      Возможный результат приведен ниже. Поле oid соответствует уникальному идентификатору табличного пространства.

       oid  |  spcname   | spcowner | spcacl | spcoptions
------+------------+----------+--------+------------
 1663 | pg_default |       10 |        |
 1664 | pg_global  |       10 |        |
(2 rows)

    • При работе с терминальным клиентом psql для вывода списка всех табличных пространств можно воспользоваться метакомандой \db+.

      \db+

      Результат:

                                   List of tablespaces
    Name    |  Owner  | Location  | Access privileges | Options | Description
------------+---------+-----------+-------------------+---------+-------------
 pg_default | gpadmin |           |                   |         |
 pg_global  | gpadmin |           |                   |         |
(2 rows)

      Обратите внимание на столбец Location в выводе предыдущей команды:

      • Для созданных пользователем табличных пространств это поле будет содержать физическое расположение табличного пространства.

      • Для созданных по умолчанию табличных пространств это поле будет пустым. Размещение таких табличных пространств совпадает с основной директорией данных (data directory), созданной на этапе инициализации кластера. Чтобы найти местоположение этой директории, можно вывести значение конфигурационного параметра data_directory.

        SHOW data_directory;

        Вывод команды:

            data_directory
-----------------------
 /data1/master/gpseg-1
(1 row)

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

Чтобы создать новое табличное пространство, необходимо использовать команду CREATE TABLESPACE, указать имя табличного пространства и затем определить место его хранения в файловой системе — после ключевого слова LOCATION.

ВАЖНО

Существует несколько требований к директории, выбираемой для нового табличного пространства:

  • Она должна существовать на всех хостах кластера — включая мастер, резервный мастер (standby) и каждый сегмент-хост.

  • Она должна быть пустой.

  • Ее владельцем должен являться системный пользователь gpadmin.

  • Она не должна находиться на съемном или временном носителе. В случае потери табличного пространства (из-за удаления каталога, сбоя дисков и так далее) весь кластер может стать неработоспособным.

Следующая команда создает новое табличное пространство newspace в директории /newspace.

CREATE TABLESPACE newspace LOCATION '/newspace';

Вывод команды выглядит следующим образом:

CREATE TABLESPACE

Теперь при запуске ранее приведенных команд для вывода табличных пространств вы получите следующее:

  • SQL-запрос:

    SELECT oid, * FROM pg_tablespace;
      oid  |  spcname   | spcowner | spcacl | spcoptions
-------+------------+----------+--------+------------
  1663 | pg_default |       10 |        |
  1664 | pg_global  |       10 |        |
 28508 | newspace   |       10 |        |
(3 rows)

  • psql-команда:

    \db+
                                 List of tablespaces
    Name    |  Owner  | Location  | Access privileges | Options | Description
------------+---------+-----------+-------------------+---------+-------------
 newspace   | gpadmin | /newspace |                   |         |
 pg_default | gpadmin |           |                   |         |
 pg_global  | gpadmin |           |                   |         |
(3 rows)

На уровне системы ADB хранит все пользовательские табличные пространства как символические ссылки (symlink) в каталоге pg_tblspc основной директории данных (data directory). Чтобы убедиться в этом, выйдите из psql и запустите следующую команду. В команде необходимо прописать собственную директорию данных вместо /data1/master/gpseg-1/:

$ ls -l /data1/master/gpseg-1/pg_tblspc

Результат приведен ниже. 28508 — OID созданного табличного пространства.

total 0
lrwxrwxrwx 1 gpadmin gpadmin 11 Sep  7 08:43 28508 -> /newspace/1
ПРИМЕЧАНИЕ
Создание табличных пространств доступно только суперпользователю. Но в дальнейшем вы можете предоставить доступ на использование нового табличного пространства и обычным пользователям. Для этого предоставьте им привилегию CREATE для выбранного табличного пространства.

Использование табличного пространства для работы с объектами БД

Создание объектов

ADB позволяет определять табличное пространство для баз данных, таблиц и индексов. Для этого необходимо использовать параметр TABLESPACE при запуске команды CREATE для перечисленных типов объектов. Ниже приведены несколько примеров:

  • Базы данных. Следующая команда создает новую БД test_space_db в табличном пространстве newspace. Это табличное пространство будет использоваться по умолчанию для всех системных каталогов БД, а также всех таблиц и индексов, создаваемых внутри БД (кроме случаев явного указания TABLESPACE при их создании). Если параметр TABLESPACE не указан при вызове команды CREATE DATABASE, новая база данных будет использовать табличное пространство своей шаблонной БД (по умолчанию — pg_default).

    CREATE DATABASE test_space_db TABLESPACE newspace;

    Результат:

    CREATE DATABASE

    Подключитесь к созданной БД, используя psql:

    \c test_space_db

    Результат:

    You are now connected to database "test_space_db" as user "gpadmin".

  • Таблицы:

    • Если при создании таблицы не указывать явно табличное пространство, для нее будет использоваться табличное пространство, назначенное по умолчанию для текущей базы данных. Например, следующая команда создает таблицу test_space_t1 в табличном пространстве newspace.

      CREATE TABLE test_space_t1(a INT, b TEXT) DISTRIBUTED BY(a);

      Результат:

      CREATE TABLE

    • Допускается также выбирать табличное пространство, отличное от заданного по умолчанию для БД. Следующая команда создает таблицу test_space_t2 в пространстве pg_default:

      CREATE TABLE test_space_t2(a INT, b TEXT) TABLESPACE pg_default DISTRIBUTED BY(a);

      Результат:

      CREATE TABLE

  • Индексы. Как и в случае с таблицами, при создании индексов можно использовать табличное пространство текущей БД или выбирать любое иное, указывая его явно в команде.

    • Использование табличного пространства, назначенного по умолчанию для текущей БД:

      CREATE INDEX test_space_idx2 ON test_space_t1 (b);

      Результат:

      CREATE INDEX

    • Использование явно определенного табличного пространства:

      CREATE INDEX test_space_idx ON test_space_t2 (b) TABLESPACE pg_default;

      Результат приведен ниже:

      CREATE INDEX

Вы также можете использовать конфигурационный параметр default_tablespace — для определения табличного пространства, которое следует выбирать по умолчанию для всех последующих команд CREATE TABLE и CREATE INDEX (в которых явно не определено требуемое табличное пространство). Этот параметр не оказывает влияния на команду CREATE DATABASE, так как для базы данных табличное пространство по умолчанию берется из шаблонной БД, на основе которой создается текущая (если не указана явно опция TABLESPACE). В следующем примере показано, как установить параметр "на лету" для текущего сеанса работы с БД, но, как и для всех session-параметров, вы также можете определить его значение на уровне роли, базы данных и системы в целом.

SET default_tablespace = newspace;

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

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

  • Базы данных. Чтобы увидеть список БД с назначенными для них табличными пространствами, можно запустить метакоманду \l+ в psql:

    \l+

    Результат запуска команды приведен ниже. Обратите внимание на столбец tablespace.

                                                                       List of databases
     Name      |  Owner  | Encoding |  Collate   |   Ctype    |  Access privileges  |  Size  | Tablespace |                Description
---------------+---------+----------+------------+------------+---------------------+--------+------------+--------------------------------------------
 adb           | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 | =Tc/gpadmin        +| 422 MB | pg_default |
               |         |          |            |            | gpadmin=CTc/gpadmin |        |            |
 diskquota     | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 |                     | 283 MB | pg_default |
 postgres      | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 |                     | 280 MB | pg_default | default administrative connection database
 template0     | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 | =c/gpadmin         +| 276 MB | pg_default | unmodifiable empty database
               |         |          |            |            | gpadmin=CTc/gpadmin |        |            |
 template1     | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 | =c/gpadmin         +| 278 MB | pg_default | default template for new databases
               |         |          |            |            | gpadmin=CTc/gpadmin |        |            |
 test_space_db | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 |                     | 284 MB | newspace   |
(6 rows)

  • Таблицы. Чтобы увидеть табличное пространство, назначенное для конкретной таблицы, можно выполнить следующий SQL-запрос для системной таблицы pg_catalog.pg_tables:

    SELECT * FROM pg_catalog.pg_tables WHERE tablename='test_space_t2';

    Результат:

 schemaname |   tablename   | tableowner | tablespace | hasindexes | hasrules | hastriggers
------------+---------------+------------+------------+------------+----------+-------------
 public     | test_space_t2 | gpadmin    | pg_default | t          | f        | f
(1 row)

  • Индексы. Чтобы увидеть табличное пространство, назначенное для конкретного индекса, можно выполнить следующий SQL-запрос для системной таблицы pg_catalog.pg_indexes:

    SELECT * FROM pg_catalog.pg_indexes WHERE tablename = 'test_space_t2';

    Результат:

 schemaname |   tablename   |   indexname    | tablespace |                              indexdef
------------+---------------+----------------+------------+---------------------------------------------------------------------
 public     | test_space_t2 | test_space_idx | pg_default | CREATE INDEX test_space_idx ON public.test_space_t2 USING btree (b)
(1 row)
Особенности хранения табличного пространства по умолчанию

 
Таблицы pg_catalog.pg_tables и pg_catalog.pg_indexes не содержат какой-либо информации в столбце tablespace для тех таблиц и индексов, которые используют табличное пространство, назначенное для БД по умолчанию. Столбец tablespace в этом случае пуст. Это гарантирует, что если база данных будет клонирована с другим табличным пространством по умолчанию, записи таблиц pg_tables и pg_indexes по-прежнему будут соответствовать местоположению, в которое команда CREATE DATABASE переместит физически скопированные таблицы и индексы.

  • pg_catalog.pg_tables:

    SELECT * FROM pg_catalog.pg_tables WHERE tablename='test_space_t1';

    Результат:

     schemaname |   tablename   | tableowner | tablespace | hasindexes | hasrules | hastriggers
------------+---------------+------------+------------+------------+----------+-------------
 public     | test_space_t1 | gpadmin    |            | f          | f        | f
(1 row)

  • pg_catalog.pg_indexes:

    SELECT * FROM pg_catalog.pg_indexes WHERE tablename = 'test_space_t1';

    Результат:

     schemaname |   tablename   |    indexname    | tablespace |                               indexdef
------------+---------------+-----------------+------------+----------------------------------------------------------------------
 public     | test_space_t1 | test_space_idx2 |            | CREATE INDEX test_space_idx2 ON public.test_space_t1 USING btree (b)
(1 row)

Перемещение объектов

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

  • Таблицы:

    • Чтобы переместить конкретную таблицу в другое табличное пространство, используйте следующий запрос:

      ALTER TABLE test_space_t1 SET TABLESPACE pg_default;

      Результат:

      ALTER TABLE

    • Чтобы переместить все таблицы текущей БД из одного табличного пространства в другое, выполните команду:

      ALTER TABLE ALL IN TABLESPACE newspace SET TABLESPACE pg_default;

      Вывод команды представлен ниже. Он также может содержать сообщение No matching relations in tablespace, если в указанном tablespace не содержится таблиц текущей БД.

      ALTER TABLE
      ВНИМАНИЕ

      Будьте осторожны, используя команду ALTER TABLE ALL. Эта команда перемещает не только определенные пользователем, но и системные таблицы (такие как pg_toast.*). Если в будущем вам потребуется перенести таблицы текущей БД в другое табличное пространство — потребуется вновь использовать выражение ALL.

  • Индексы:

    • Чтобы переместить определенный индекс в другое табличное пространство, используйте запрос:

      ALTER INDEX test_space_idx2 SET TABLESPACE pg_default;

      Результат:

      ALTER INDEX

    • Чтобы переместить все индексы текущей БД из одного табличного пространства в другое, выполните команду:

      ALTER INDEX ALL IN TABLESPACE newspace SET TABLESPACE pg_default;

      Вывод команды представлен ниже. Он также может содержать сообщение No matching relations in tablespace, если в указанном табличном пространстве не содержится индексов текущей БД.

      ALTER INDEX
      ВНИМАНИЕ

      Обратите внимание, что системные каталоги не перемещаются командами ALTER TABLE ALL и ALTER INDEX ALL. Чтобы их переместить, используйте команду ALTER DATABASE или ALTER TABLE/ALTER INDEX. Объекты information_schema не рассматриваются как часть системных каталогов и поэтому будут перемещены.

  • Базы данных. Чтобы изменить используемое по умолчанию табличное пространство на уровне БД, необходимо выполнить команду ALTER DATABASE с выражением SET TABLESPACE. Эта команда физически переносит таблицы и индексы, хранимые в текущем tablespace БД, в новое табличное пространство. Обратите внимание, что таблицы и индексы, сохраненные в иных табличных пространствах (указанных явно при создании этих объектов), не переносятся. Приведенные ниже команды создают новое табличное пространство и назначают его для БД:

    • Создайте табличное пространство newspace2 в директории /newspace2:

      CREATE TABLESPACE newspace2 LOCATION '/newspace2';

    • Подключитесь к другой базе данных (например, adb):

      \c adb

    • Измените табличное пространство, используемое по умолчанию для БД test_space_db:

      ALTER DATABASE test_space_db SET TABLESPACE newspace2;

      Результат:

      ALTER DATABASE
      ВНИМАНИЕ

      • Для выполнения команды ALTER DATABASE необходимы права владельца БД (owner) или суперпользователя.

      • Требуется привилегия CREATE для нового табличного пространства.

      • Перед изменением табличного пространства для открытой в текущей сессии БД подключитесь к любой иной БД.

      • В выбранном табличном пространстве не должно содержаться объектов текущей БД (включая системные таблицы).

Перемещение временных объектов

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

  • Временные таблицы и индексы, создаваемые явно при помощи команды CREATE TEMP TABLE.

  • Временные таблицы и spill-файлы, создаваемые автоматически для обеспечения выполнения запросов, включая хеш-агрегирование (hash aggregate), хеш-соединение (hash join) и сортировку больших наборов данных.

  • Временные таблицы, которые создаются автоматически при обновлении данных материализованного представления.

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

Значением параметра по умолчанию является пустая строка, соответствующая табличному пространству, установленному по умолчанию для текущей БД. Как и для всех session-параметров, вы можете определить значение temp_tablespaces на уровне роли, базы данных и системы в целом. Следующая команда устанавливает значение параметра в рамках текущей сессии пользователя:

SET temp_tablespaces = newspace;

Результат:

SET

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

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

DROP TABLESPACE newspace;

Вывод команды выглядит следующим образом:

DROP TABLESPACE
ВНИМАНИЕ

  • Для удаления табличного пространства необходимы права владельца табличного пространства (owner) или суперпользователя.

  • Только пустые табличные пространства могут быть удалены. Убедитесь, что удаляемое табличное пространство не используется объектами БД. Также табличное пространство не должно содержать временных и транзакционных файлов.
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней