# 创建表空间
CREATE TABLESPACE fgedutbs LOCATION ‘/opengauss/fgdata/tbs’;

# 创建表时指定表空间
CREATE TABLE fgedu.fgedu_test (
id INTEGER PRIMARY KEY,
name VARCHAR(100)
) TABLESPACE fgedutbs;

# 查看表空间使用情况
SELECT tablespace_name, pg_size_pretty(total_size) AS total_size,
pg_size_pretty(used_size) AS used_size,风哥提示：
pg_size_pretty(free_size) AS free_size
FROM (SELECT tablespace_name,
sum(pg_tablespace_size(tablespace_name)) AS total_size,
sum(pg_tablespace_size(tablespace_name) – pg_tablespace_size(tablespace_name) * (1 – coalesce(pg_tablespace_free_percent(tablespace_name), 0) / 100)) AS used_size,
sum(pg_tablespace_size(tablespace_name) * (coalesce(pg_tablespace_free_percent(tablespace_name), 0) / 100)) AS free_size
FROM (SELECT DISTINCT tablespace_name FROM pg_tables) AS t
GROUP BY tablespace_name) AS s;

# 案例1：为不同类型的数据创建不同的表空间
# 创建索引表空间
CREATE TABLESPACE fgedu_index_tbs LOCATION ‘/opengauss/fgdata/index’;

# 创建数据文件表空间
CREATE TABLESPACE fgedu_data_tbs LOCATION ‘/opengauss/fgdata/data’;

# 创建表并指定表空间
CREATE TABLE fgedu.fgedu_test (
id INTEGER PRIMARY KEY,
name VARCHAR(100),
value INTEGER
) TABLESPACE fgedu_data_tbs;

# 为表创建索引并指定表空间学习交流加群风哥微信: itpux-com
CREATE INDEX idx_fgedu_test_name ON fgedu.fgedu_test(name) TABLESPACE fgedu_index_tbs;

# 优化存储参数
# 设置shared_buffers
gs_guc set -D /opengauss/fgdata -c “shared_buffers = 1GB”

# 设置work_mem
gs_guc set -D /opengauss/fgdata -c “work_mem = 16MB”

# 设置maintenance_work_mem
gs_guc set -D /opengauss/fgdata -c “maintenance_work_mem = 256MB”

# 设置random_page_cost
gs_guc set -D /opengauss/fgdata -c “random_page_cost = 1.1”

# 重启数据库使参数生效
gs_ctl restart -D /opengauss/fgdata