Tablespace

테이블스페이스는 미리 테이블이 저장될 공간을 마련하고, 테이블을 저장하는 공간을 의미합니다. 각각의 테이블을 테이블스페이스 단위로 나누어 관리와 퍼포먼스 향상을 가져옵니다.

테이블스페이스 생성 과정은 다음과 같습니다.

서버에 테이블스페이스로 사용할 물리적 공간 생성

[root@lenovo2 ~]# mkdir -p /tbspace/agens3

PostgreSQL에서 테이블스페이스 생성

postgres=# create tablespace tb_agens3 location '/tbspace/agens3';
CREATE TABLESPACE

특정 테이블스페이스에 데이터베이스를 생성하는 DDL문은 다음과 같습니다.

postgres=# CREATE DATABASE my_database TABLESPACE tb_agens3;
CREATE DATABASE

특정 테이블스페이스에 테이블을 생성하는 DDL문은 다음과 같습니다.

postgres=# create table test (
postgres(# a int,
postgres(# b text
postgres(# ) tablespace tb_agens3 ;
CREATE TABLE

특정 테이블스페이스에 인덱스를 생성하는 DDL문은 다음과 같습니다.

postgres=# CREATE INDEX my_index ON test(a) TABLESPACE tb_agens3;
CREATE INDEX

특정 테이블스페이스에 파티션 테이블을 생성하는 DDL문은 다음과 같습니다.

postgres=# CREATE TABLE my_partitioned_table ( 
postgres(# id serial,
postgres(# data text, 
postgres(# created_at timestamp,
postgres(# PRIMARY KEY (id, created_at) 
postgres(# ) PARTITION BY RANGE (created_at) tablespace tb_agens3; 
CREATE TABLE

postgres=# CREATE TABLE my_partition_2023 PARTITION OF my_partitioned_table 
postgres-# FOR VALUES FROM ('2023-01-01') TO ('2024-01-01') 
postgres-# TABLESPACE tb_agens3;
CREATE TABLE

기존에 존재하던 오브젝트의 테이블스페이스를 변경하는 DDL 문은 다음과 같습니다.

Database

ALTER DATABASE my_database SET TABLESPACE new_tablespace;

Table

postgres=# ALTER TABLE test SET TABLESPACE new;
ALTER TABLE

Index

postgres=# ALTER INDEX my_index SET TABLESPACE new;
ALTER INDEX

참고 사항

• 테이블과 인덱스는 ALTER 명령을 사용하여 즉시 테이블스페이스를 변경할 수 있습니다.
• 데이터베이스의 기본 테이블스페이스를 변경해도 기존 객체는 이동되지 않으며, 새로운 객체가 생성될 때 기본적으로 지정된 테이블스페이스를 사용하게 됩니다.
• 대규모 데이터베이스 객체를 이동할 경우, 이 과정이 시간이 많이 걸릴 수 있으며, 성능에 영향을 줄 수 있으므로 주의해야 합니다.
• 테이블스페이스를 변경하는 과정에서 해당 객체에 대한 액세스가 잠시 차단될 수 있습니다.

Schema

PostgreSQL 에서 schema를 생성하고 적용하는 방법에 대해 알아봅니다.
테스트를 위한 데이터베이스를 생성하고 해당 데이터베이스에 접근합니다.

postgres=# create database shinbo;
CREATE DATABASE
postgres=# \c shinbo
You are now connected to database "shinbo" as user "agensdb".

우선, 현재 존재하는 schema를 확인합니다.

shinbo=# \dn
 List of schemas
  Name  |  Owner  
--------+---------
 public | agensdb
(1 row)

위의 결과는 system 정보와 관련된 스키마는 제외하고 조회합니다. 만약 존재하는 모든 schema를 조회하고 싶은 경우 아래의 쿼리문을 통해 확인할 수 있습니다.

shinbo=# select * from information_schema.schemata ;
 catalog_name |    schema_name     | schema_owner | default_character_set_catalog | default_cha
racter_set_schema | default_character_set_name | sql_path 
--------------+--------------------+--------------+-------------------------------+------------
------------------+----------------------------+----------
 shinbo       | pg_toast           | agensdb      |                               |            
                  |                            | 
 shinbo       | pg_catalog         | agensdb      |                               |            
                  |                            | 
 shinbo       | public             | agensdb      |                               |            
                  |                            | 
 shinbo       | information_schema | agensdb      |                               |            
                  |                            | 
 shinbo       | shinbo_mobile      | agensdb      |                               |            
                  |                            | 
(5 rows)

스키마 생성 DDL문은 아래와 같습니다.

shinbo=# create schema shinbo_mobile;
CREATE SCHEMA

생성한 스키마를 조회합니다.

shinbo=# \dn
     List of schemas
     Name      |  Owner  
---------------+---------
 public        | agensdb
 shinbo_mobile | agensdb
(2 rows)

SearchPath

PostgreSQL에는 search_path이 존재합니다. 단어 그대로 기본적으로 탐색을 진행하는 경로를 설정하는 것으로, search_path에 등록된 스키마에 한해서는 쿼리문에서 오브젝트를 사용할 때 스키마 명을 따로 명시해주지 않아도 조회가 가능합니다. 만약 search_path에 등록되지 않은 스키마의 오브젝트를 조회할 시 스키마를 명시해야만 조회가 가능합니다.

현재 search_path를 확인해봅니다.

shinbo=# show search_path ;
   search_path   
-----------------
 "$user", public
(1 row)

위에서 만든 ‘shinbo_mobile’ 스키마를 search_path에 등록합니다. 이 방법은 해당 세션에만 적용됩니다.

shinbo=# set search_path to "$user", public, shinbo_mobile;
SET

다시 search_path를 확인해봅니다. 잘 등록되어있음을 확인할 수 있습니다.

shinbo=# show search_path ;
          search_path           
--------------------------------
 "$user", public, shinbo_mobile
(1 row)

User별 search_path를 등록할 수 있습니다. 아래의 방법은 User에 고정적으로 search_path를 등록하는 방법으로 세션이 종료되어도 해당 설정값은 유지됩니다.

shinbo=# alter user agensdb set search_path to "$user", public, shinbo_mobile;
ALTER ROLE

shinbo=# show search_path; 
          search_path           
--------------------------------
 "$user", public, shinbo_mobile
(1 row)