検証環境の構築

デフォルトテーブルスペースのサンプル

-- ユーザーとデータベースの作成
psql -U postgres -c "create user sales with password 'sales';"
psql -U postgres -c "create database salesdb owner sales;"
psql -U sales -d salesdb -c "create schema authorization sales;"

-- 接続してテーブル作成
psql -U sales -d salesdb

-- 従業員テーブルの定義
create table staff_records
    ( staff_no       bigint      primary key
    , given_name     varchar(20)
    , family_name    varchar(25) not null
    , mail_addr      varchar(25) not null
    , tel_no         varchar(20)
    , entry_date     date        not null
    , position_code  bigint      not null
    , annual_pay     numeric(10,2)
    , bonus_rate     numeric(4,2)
    , supervisor_no  bigint
    , division_no    bigint
    , constraint pay_positive check (annual_pay > 0)
    );

-- サンプルデータ投入
insert into staff_records
select generate_series(1,50000) as id,
       substr(md5(random()::text),1,6),
       substr(md5(random()::text),1,10),
       substr(md5(random()::text),1,8)||'@example.com',
       '0'||ceiling(random()*8999999999+1000000000)::bigint::text,
       date '2000-01-01' + (random()*8000)::int,
       (random()*40+10)::int,
       (random()*8000000+2000000)::numeric(10,2),
       (random()*0.5)::numeric(4,2),
       (random()*200+1)::int,
       (random()*15+1)::int;

カスタムテーブルスペースのサンプル

[postgres@dbserver ~]$ psql
psql (14.9)

postgres=# show data_directory;
   data_directory   
--------------------
 /var/lib/pgsql/data
(1 row)

-- テーブルスペース用ディレクトリ作成
postgres=# \! mkdir -p /var/lib/pgsql/custom_ts

-- ユーザー・テーブルスペース・データベース作成
postgres=# create user appuser with password 'appuser';
postgres=# create tablespace custom_ts owner appuser location '/var/lib/pgsql/custom_ts';
postgres=# create database appdb owner appuser tablespace custom_ts;

-- スキーマ作成
[postgres@dbserver ~]$ psql -U appuser -d appdb -c "create schema authorization appuser;"

-- テーブル作成（デフォルトと明示的指定の比較）
psql -U appuser -d appdb

create table transaction_log (seq_id bigint, payload text, recorded_at timestamp);
insert into transaction_log select generate_series(1,200000),md5(random()::text),clock_timestamp();

create table audit_trail (seq_id bigint, payload text, recorded_at timestamp) tablespace custom_ts;
insert into audit_trail select generate_series(1,200000),md5(random()::text),clock_timestamp();

transaction_logはデフォルトテーブルスペースを、audit_trailは明示的にcustom_tsを使用します。

テーブルスペース情報の取得

pg_classのreltablespaceフィールドは0を返す場合があるため、実際のテーブルスペースを特定するにはpg_database.dattablespaceを参照する必要があります。

-- reltablespaceが0の場合の確認
testdb=# select reltablespace, current_database(), 
         relnamespace::regnamespace, relname, oid, relfilenode 
from pg_class where relname ~ 'transaction_log|audit_trail';

 reltablespace | current_database | relnamespace |    relname      |  oid  | relfilenode 
---------------+------------------+--------------+-----------------+-------+-------------
             0 | appdb            | appuser      | transaction_log | 16432 |       16432
             0 | appdb            | appuser      | audit_trail     | 16438 |       16438

物理ファイルパスを特定するSQL

with 
db_metadata as (
    select oid as database_oid, 
           datname as database_name, 
           dattablespace as default_ts_oid 
    from pg_database 
    where datname = current_database()
),
ts_metadata as (
    select ts.oid as ts_oid, 
           ts.spcname as ts_name,
           case 
               when ts.oid = 1663 then 'pg_global'
               when ts.oid = 1664 then 'pg_default'
               else ts.spcname 
           end as display_name
    from pg_tablespace ts
    join db_metadata db on ts.oid = db.default_ts_oid
       or exists (
           select 1 from pg_class c 
           where c.reltablespace = ts.oid 
           and c.relnamespace = '対象スキーマ'::regnamespace
       )
),
data_dir as (
    select setting as pg_data_path 
    from pg_settings 
    where name = 'data_directory'
)
select 
    format('TS:%s (OID:%s)', ts.display_name, ts.ts_oid) as tablespace_detail,
    format('DB:%s (OID:%s)', db.database_name, db.database_oid) as database_detail,
    format('Schema:%s (OID:%s)', 
           c.relnamespace::regnamespace::text, 
           c.relnamespace) as schema_detail,
    c.oid as object_id,
    c.relfilenode as file_node,
    format('%s/%s', 
           d.pg_data_path, 
           pg_relation_filepath(c.oid)) as physical_location,
    c.relname as table_name,
    pg_size_pretty(pg_relation_size(c.oid)) as current_size
from pg_class c
cross join db_metadata db
cross join data_dir d
left join ts_metadata ts on true
where c.relnamespace::regnamespace::text = '対象スキーマ名'
  and c.relname = '対象テーブル名'
  and c.relkind = 'r';  -- 通常テーブルのみ

実行結果の例

デフォルトテーブルスペースの場合

-- sales.staff_records の確認

tablespace_detail  | TS:pg_default (OID:1664)
database_detail    | DB:salesdb (OID:16401)
schema_detail      | Schema:sales (OID:16402)
object_id          | 16415
file_node          | 16415
physical_location  | /var/lib/pgsql/data/base/16401/16415
table_name         | staff_records
current_size       | 4528 kB

salesdb=# \! ls -lh /var/lib/pgsql/data/base/16401/16415
-rw------- 1 postgres postgres 4.5M Jul 15 14:32 /var/lib/pgsql/data/base/16401/16415

カスタムテーブルスペースの場合

-- appuser.audit_trail の確認

tablespace_detail  | TS:custom_ts (OID:16388)
database_detail    | DB:appdb (OID:16389)
schema_detail      | Schema:appuser (OID:16390)
object_id          | 16438
file_node          | 16438
physical_location  | /var/lib/pgsql/data/pg_tblspc/16388/PG_14_202107181/16389/16438
table_name         | audit_trail
current_size       | 14 MB

appdb=# \! ls -lh /var/lib/pgsql/data/pg_tblspc/16388/PG_14_202107181/16389/16438
-rw------- 1 postgres postgres 14M Jul 15 14:35 /var/lib/pgsql/data/pg_tblspc/16388/PG_14_202107181/16389/16438

重要な注意事項

テーブルのoidとrelfilenodeは必ずしも一致しません。oidはテーブル作成後変更されませんが、relfilenodeは以下の操作で変化します：

• TRUNCATE
• REINDEX
• CLUSTER
• VACUUM FULL

実際のディスク上のファイル名はrelfilenodeに追従します。

-- TRUNCATE前後の変化を確認
appdb=# select oid, relfilenode, relname from pg_class where relname = 'audit_trail';
  oid   | relfilenode |  relname    
--------+-------------+-------------
  16438 |       16438 | audit_trail

appdb=# truncate appuser.audit_trail;
TRUNCATE TABLE

appdb=# select oid, relfilenode, relname from pg_class where relname = 'audit_trail';
  oid   | relfilenode |  relname    
--------+-------------+-------------
  16438 |       16455 | audit_trail  -- relfilenodeが変更

-- 新しいファイルパスを確認
physical_location  | /var/lib/pgsql/data/pg_tblspc/16388/PG_14_202107181/16389/16455

ファイルが1GBを超えると、PostgreSQLは自動的にセグメントファイルを分割します（16455.1, 16455.2など）。この場合、pg_relation_filepathはベースファイルのみ返し、追加セグメントは別途確認が必要です。