PostgreSQL tablespace 테이블스페이스 생성, 사용방법 정리

PostgreSQL 테이블스페이스 정리

오라클에서 tablespace를 빡빡하게 관리했던 것과 달리 postgresql에서는 거의 tablespace를 사용하지 않는다.
postgresql에서는 테이블스페이스를 생성하는 것이 관리 복잡성이 늘어나는 것에 반해 이점이 크게 없는데 관련하여 알아보자.

postgresql tablespace 정리

---

PostgreSQL의 테이블스페이스는 각 오브젝트들의 데이터 파일을 담는 디렉터리라고 보면 된다.
특히, postgresql은 테이블, 시퀀스, 인덱스 및 materialized view 등 각 DB오브젝트들 마다 OS에 개별적으로 데이터 파일이 생성된다. (`$PGDATA/base`에서 확인 가능)

하나의 파일이 계속 커질수는 없으니, 대용량 오브젝트는 1GB 단위 세그먼트로 분리되서 저장된다.(참고로 pg build 시 `--with-segsize` 를 조정해서 변경 할 수 있다)

 

postgresql에서 TABLESPACE 생성과 사용 방법은 간단하다.

1. 디렉토리 생성(소유자는 pg로 변경)
2. tablespace 생성
3. 오브젝트 매핑

테이블스페이스 생성 방법

CREATE TABLESPACE 이름 owner 소유자 LOCATION 'OS 디렉토리 경로';
CREATE TABLESPACE tablespace_name
[ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]
LOCATION 'directory'
[ WITH ( tablespace_option = value [, ... ] ) ]''

※ [ ] 대괄호는 optional 선택사항으로 생략가능

---

테이블스페이스 사용 방법

CREATE TABLE foo(i int) TABLESPACE space1;
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ name ] ON table [ USING method ]
( { column | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ WITH ( storage_parameter = value [, ... ] ) ]
[ TABLESPACE tablespace ]
[ WHERE predicate ]

---

테이블스페이스 사용 이점

만약 Tablespace를 사용한다면 있을 몇가지 장점들을 알아보자.

1) 여러 파일시스템으로 I/O 분산

다만, OS단에서 RAID로 스트라이핑 할 수 있고 더 낫다
2) 기존 디스크 용량이 꽉 차고 확장이 안될 때 추가 디바이스 활용

신규 테이블, 인덱스, 데이터베이스 등을 별도 테이블스페이스에서 기존 디스크 재구성까지 임시 활용 가능하다.
3) 특정 테이블들 용량을 제한하고 싶을떄
4) 비싸고 빠른, 싸고 느린 영역별로 object들을 분리 운영

옵티마이저 옵션(seq_page_cost, random_page_cost, effective_io_concurrency)을 조정하여 사용

(옵션에 대한 내용은 https://postgresqlco.nf/doc/en/param/random_page_cost/ 여기 참고)
5) temp tablespace 분리 관리★

$PGDATA 가 꽉 찰 수 있으니, temp_file_limit 설정과 분리관리 하여 파일시스템 full 방지

2024.05.17 - [CS/DBMS] - PostgreSQL Temp file 관련 정리 (Temp tablespace)

PostgreSQL Temp file 관련 정리 (Temp tablespace)

---

테이블스페이스 유의사항

postgresql에서 테이블스페이스를 쓴다면 몇가지  주의사항이 있다.

• `pg_basebackup` 할 때 기본적으로 default tablespace($PGDATA)에 생성된다.
  다른 곳에 저장하려면 `--tablespace-mapping=olddir=newdir` 옵션을 사용해야된다.
  (만약, 여러 테이블스페이스가 있는 경우 여러개 매핑 가능)
• 테이블스페이스가 `$PGDATA`의 외부 디렉토리에 있더라도 DB 클러스터의 한부분으로 TABLESPACE가 삭제되거나 깨지면 DB 기동이 안된다. DATA영역과 동일한 수준으로 같이 관리 필요하다.
• 오라클처럼 테이블스페이스 단위로 복구는 안된다.
• filesystem 백업을 하는 경우 반드시 테이블스페이스도 같이 백업이 필요하다.
• pg_upgrade 시 복잡성이 증가하는데, 실패 문서가 다수있다.(서칭 추가 필요)
• rsync로 replication 시 테이블스페이스 디렉토리 누락 주의해야한다

---

TBS 생성 테스트

[root@testdb01 ~]$ mkdir /pg_tbs
[root@testdb01 ~]$ chown postgres:postgres /pg_tbs

postgres=# select * from pg_tablespace;
 oid  |  spcname   | spcowner | spcacl | spcoptions
------+------------+----------+--------+------------
 1663 | pg_default |       10 |        |
 1664 | pg_global  |       10 |        |
(2 rows)

postgres=# create tablespace tbs_nm location '/pg_tbs';
CREATE TABLESPACE
postgres=# select * from pg_tablespace;
  oid  |  spcname   | spcowner | spcacl | spcoptions
-------+------------+----------+--------+------------
  1663 | pg_default |       10 |        |
  1664 | pg_global  |       10 |        |
 24665 | tbs_nm     |       10 |        |
(3 rows)
postgres=# create table test (col1 int) tablespace tbs_nm;
CREATE TABLE
postgres=# insert into test values(1);
INSERT 0 1
postgres=# exit

[postgres@testdb01 pg_tbs]$ cd /pgdata/pg_tblspc/
[postgres@testdb01 pg_tblspc]$ ls -al
total 4
drwx------.  2 postgres postgres   19 Jul 31 14:02 .
drwx------. 18 postgres postgres 4096 Jul 31 13:59 ..
lrwxrwxrwx.  1 postgres postgres    7 Jul 31 14:02 24665 -> /pg_tbs

생성 할 때 지정한 Tablespace 디렉토리에 OS파일이 생성되며, 실제 $PGDATA에는 Symbolic 링크가 생성되는 것을 확인 할 수 있다.

---

구글링을 해봤는데 pg에서의 tablespace 사용 필요성은 굳이 못느끼겠다. 여러 사이트에서 보통 하나의 통 디렉토리로 관리하는 듯하고, 엄청 헤비한 DB 아니면 굳이 분리할 필요가 있나 싶다.
다만, temp tablespace 용이나 hdd랑 ssd 성능 비교를 위해 디렉토리를 분리하는 정도로 사용 할 예정이다.

 

참고자료

https://www.postgresql.org/docs/current/manage-ag-tablespaces.html
https://www.cybertec-postgresql.com/en/when-to-use-tablespaces-in-postgresql/