文档首页/ 数据仓库服务 DWS/ 最佳实践/ 数据库管理/ 表大小查询最佳实践
更新时间：2026-05-30 GMT+08:00
查看PDF
分享

表大小查询最佳实践

本文主要介绍DWS表大小查询的多种方法以及一些特殊表大小查询的最佳实践。

表大小查询

普通表大小按查询的范围不同，主要分为以下几种场景。

表1 表大小查询场景

场景

查询范围

使用函数

查询指定表的全部磁盘空间，包括索引，数据文件以及列存辅助表等。单位：字节（bytes）。

表（数据文件 + cudesc表 + delta表) + 表索引 + toast

pg_total_relation_size

查询指定表或者索引使用的磁盘空间，不计表索引和toast。单位：字节（bytes）。

表（数据文件 + cudesc表 + delta表）

pg_relation_size

查询指定的表使用的磁盘空间，不计索引（但是包含toast，自由空间映射和可见性映射）。单位：字节（bytes）。

表（数据文件 + cudesc表 + delta表）+ toast

pg_table_size

查询指定表的索引使用的总磁盘空间。单位：字节（bytes）。

索引

pg_indexes_size
  1. 准备测试表。 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
    		 
    DROP TABLE IF EXISTS user_info;

CREATE TABLE IF NOT EXISTS user_info (
    id BIGSERIAL,
    user_name VARCHAR(50) NOT NULL,
    gender CHAR(1),
    age INT,
    phone VARCHAR(20) NOT NULL,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
DISTRIBUTE BY HASH(phone);

CREATE UNIQUE INDEX IF NOT EXISTS idx_user_info_phone ON user_info(phone);

INSERT INTO user_info (user_name, gender, age, phone)
SELECT
    '用户_' || gs,
    CASE WHEN random() > 0.5 THEN 'M' ELSE 'F' END,
    floor(18 + random() * 40)::INT,
    '138' || lpad(gs::TEXT, 8, '0')
FROM generate_series(1, 10000) AS gs;

  2. 查询表的全部磁盘空间，包括索引，数据文件以及列存辅助表等（以下语句等价）。 

    1
2
3
4
    		 
    SELECT * FROM pg_total_relation_size('user_info');       ---方式一，根据表名查询
SELECT * FROM pg_total_relation_size('user_info'::regclass);   ---方式二，系统根据表名自动转oid查询
SELECT oid FROM PG_CLASS WHERE  relname = 'user_info';    ---方式三，先查询oid，再根据oid查询
SELECT * FROM pg_total_relation_size(oid);

  3. 以上结果返回单位是字节（bytes），如果需要换成MB或KB，可以用pg_size_pretty 函数转换包装一下。 

    1
    		 
    SELECT pg_size_pretty(pg_total_relation_size('user_info'));

  4. 查询表使用的磁盘空间，不计表索引和toast。 

    1
2
3
    		 
    SELECT * FROM pg_relation_size('user_info');
SELECT * FROM pg_relation_size('user_info'::regclass); 
SELECT * FROM pg_relation_size(oid);

  5. 查询表使用的磁盘空间，不计索引（但是包含toast，自由空间映射和可见性映射）。 

    1
2
3
    		 
    SELECT * FROM pg_table_size('user_info');
SELECT * FROM pg_table_size('user_info'::regclass); 
SELECT * FROM pg_table_size(oid);

  6. 指定表的索引使用的总磁盘空间。 

    1
2
3
4
    		 
    SELECT * FROM pg_indexes_size('user_info');
SELECT * FROM pg_indexes_size('user_info'::regclass); 
SELECT * FROM pg_indexes_size(oid);
SELECT pg_size_pretty(pg_indexes_size('user_info'));

分区和分区索引大小查询

分区表的表大小可以通过以上的表大小查询方法查询，以下只提供分区或分区索引大小的查询方法。

表2 分区和分区索引查询

场景

查询范围

使用函数

查询分区使用的磁盘空间，不计索引。单位：字节（bytes）。

分区（数据文件 + cudesc表 + delta表) + 分区toast

pg_partition_size

查询分区的索引使用的磁盘空间。单位：字节（bytes）。

分区索引

pg_partition_indexes_size
  1. 准备测试分区表和数据： 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
    		 
    DROP TABLE IF EXISTS customer_address;

CREATE TABLE customer_address
(
    ca_address_sk       INTEGER                  NOT NULL   ,
    ca_address_id       CHARACTER(16)            NOT NULL   ,
    ca_street_number    CHARACTER(10)                       ,
    ca_street_name      CHARACTER varying(60)               ,
    ca_street_type      CHARACTER(15)                       ,
    ca_suite_number     CHARACTER(10)                    
)
DISTRIBUTE BY HASH (ca_address_sk)
PARTITION BY RANGE(ca_address_sk)
(
        PARTITION P1 VALUES LESS THAN(2450815),
        PARTITION P2 VALUES LESS THAN(2451179),
        PARTITION P3 VALUES LESS THAN(2451544),
        PARTITION P4 VALUES LESS THAN(MAXVALUE)
);

CREATE INDEX customer_address_idx ON customer_address (ca_address_sk) LOCAL
(
       PARTITION ca_address_sk_idx1,
       PARTITION ca_address_sk_idx2,
       PARTITION ca_address_sk_idx3,
       PARTITION ca_address_sk_idx4
);


INSERT INTO customer_address (
    ca_address_sk,
    ca_address_id,
    ca_street_number,
    ca_street_name,
    ca_street_type,
    ca_suite_number
)
SELECT
    2450000 + gs,
    lpad(('ADDR' || gs)::TEXT, 16, '0'),
    lpad((gs % 1000)::TEXT, 10, ' '),
    'Street_' || gs,
    CASE WHEN random() > 0.5 THEN 'St' ELSE 'Ave' END,
    lpad(gs::TEXT, 10, '0')  
FROM generate_series(1, 10000) AS gs;

  2. 查询分区使用的磁盘空间，不计索引。以下两个方式等价，其中方式二，先通过PGXC_GET_STAT_ALL_PARTITIONS视图查询对应的表和分区的oid，再通过oid查询。 

    1
2
3
    		 
    SELECT * FROM pg_partition_size('customer_address','p1');    ---方式一，通过表名、分区名查询。
SELECT relid,partid,partname  FROM PGXC_GET_STAT_ALL_PARTITIONS  WHERE relname = 'customer_address';   ---方式二，先查询表和分区的oid，再根据oid查询。
SELECT * FROM pg_partition_size(表oid,分区oid);

  3. 查询分区索引使用的磁盘空间。 

    1
2
3
    		 
    SELECT * FROM pg_partition_indexes_size('customer_address','p1');
SELECT relid,partid,partname  FROM PGXC_GET_STAT_ALL_PARTITIONS  WHERE relname = 'customer_address';
SELECT * FROM pg_partition_indexes_size(表oid,分区oid);

冷热表大小查询

冷热表大小，也可以通过普通表大小查询函数来查询，参见表大小查询，但是查询结果会包含冷数据大小，如果要分别计算冷、热数据的大小，可通过pg_lifecycle_table_data_distribute查询，如查询所有冷热表的大小则通过pg_lifecycle_node_data_distribute查询。

  1. 准备测试数据： 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
    		 
    DROP TABLE IF EXISTS lifecycle_table;
CREATE TABLE lifecycle_table(i int, val text) WITH (ORIENTATION = COLUMN, storage_policy = 'LMT:100')
PARTITION BY RANGE (i)
(
PARTITION P1 VALUES LESS THAN(5),
PARTITION P2 VALUES LESS THAN(10),
PARTITION P3 VALUES LESS THAN(15),
PARTITION P8 VALUES LESS THAN(MAXVALUE)
)
ENABLE ROW MOVEMENT;

INSERT INTO lifecycle_table(i, val)
SELECT 
    (generate_series % 100),         -- 0~99 循环，覆盖所有分区
    'lifecycle_val_' || generate_series
FROM generate_series(1, 10000);

DROP TABLE IF EXISTS cold_hot_table;
CREATE TABLE cold_hot_table
(
    W_WAREHOUSE_ID            CHAR(16)              NOT NULL,
    W_WAREHOUSE_NAME          VARCHAR(20)                   ,
    W_STREET_NUMBER           CHAR(10)                      ,
    W_STREET_NAME             VARCHAR(60)                   ,
    W_STREET_ID               CHAR(15)                      ,
    W_SUITE_NUMBER            CHAR(10)                     
)
WITH (ORIENTATION = COLUMN, storage_policy = 'LMT:30')
DISTRIBUTE BY HASH (W_WAREHOUSE_ID)
PARTITION BY RANGE(W_STREET_ID)
(
    PARTITION P1 VALUES LESS THAN(100000),
    PARTITION P2 VALUES LESS THAN(200000),
    PARTITION P3 VALUES LESS THAN(300000),
    PARTITION P4 VALUES LESS THAN(MAXVALUE)
)ENABLE ROW MOVEMENT;

INSERT INTO cold_hot_table (
    W_WAREHOUSE_ID,
    W_WAREHOUSE_NAME,
    W_STREET_NUMBER,
    W_STREET_NAME,
    W_STREET_ID,
    W_SUITE_NUMBER
)
SELECT
    -- 16 位固定长度仓库ID
    lpad((generate_series % 1000000)::text, 16, '0'),
    -- 仓库名称
    'WAREHOUSE_' || generate_series,
    -- 10 位街道门牌号
    lpad((random() * 9999999999)::bigint::text, 10, '0'),
    -- 街道名称
    'STREET_' || (random() * 10000)::int,
    -- 分区字段：0 ~ 40万 均匀分布，自动落入 P1/P2/P3/P4
    (generate_series % 400000)::text,
    -- 10 位房间号
    lpad((random() * 9999999999)::bigint::text, 10, '0')
FROM generate_series(1, 5000);

  2. 查看某张表的冷数据，热数据的大小。 

    1
    		 
    SELECT * FROM pg_lifecycle_table_data_distribute ('lifecycle_table');

  3. 查看所有冷热表的冷数据、热数据的大小。 

    1
    		 
    SELECT * FROM pg_lifecycle_node_data_distribute() ORDER BY tablename;

数据库大小查询

通过pg_database_size可以查询指定名称的数据库使用的磁盘空间。

支持通过指定名称或oid查询。

  • DWS 9.1.1.200以上版本，设置GUC参数fast_obs_dbsize_method为2，可以快速计算存算分离下数据库大小，相比之前版本，性能提升10倍以上。
  • DWS 9.1.1.200以下版本，当集群中只有一个数据库和tablespace时，统计OBS空间会直接使用OBS桶的大小（具体介绍见表大小相关GUC介绍中fast_obs_dbsize_method的介绍），该方法的好处是性能更好，但会统计到备份集的大小。
  1. 通过指定名称查询数据库大小。 

    1
    		 
    SELECT pg_database_size('gaussdb');

  2. 通过指定oid查询数据库大小。首先通过PG_STAT_DATABASE查询数据库的oid。 

    SELECT datid FROM PG_STAT_DATABASE WHERE datname = 'gaussdb';
SELECT pg_database_size(2147483821);

其他大小（CU大小）查询

针对V3表，可以通过pg_obs_file_size函数查询表在OBS上的CU大小。

针对Hstore_opt表，可以通过pgxc_get_cstore_dirty_ratio函数查询目标表在各个DN上的cu、delta以及cudesc的脏页率以及大小。

  1. 创建存算分离V3表和导入数据。 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
    		 
    DROP TABLE IF EXISTS test_t5;
CREATE TABLE  test_t5
( 
id integer not null,  
data integer, 
age integer 
)  
WITH (ORIENTATION =COLUMN, COLVERSION =3.0) 
DISTRIBUTE BY ROUNDROBIN;

INSERT INTO test_t5 (id, data, age)
SELECT
    id,
    (random() * 10000)::integer,  -- 0~10000 随机整数
    (20 + random() * 60)::integer  -- 年龄 20~80 随机
FROM generate_series(1, 10000) AS t(id);
UPDATE test_t5 SET age = 25 WHERE id = 10;

  2. 查询该V3表在OBS上存储的CU文件大小。 

    1
    		 
    SELECT * FROM pg_obs_file_size('test_t5');

  3. 如果V3为分区表，还可以查询指定分区在OBS上的CU文件大小。 

    1
    		 
    SELECT * FROM pg_obs_file_size('表名'，'分区名');

  4. 创建Hstore_opt表。 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
    		 
    DROP TABLE IF EXISTS hstore_opt_table_demo;
CREATE TABLE hstore_opt_table_demo(
t_code character varying(20),
t_gisid character varying(800),
t_datatime timestamp(6) without time zone,
t_gmid character varying(64)
)
WITH (orientation=column, enable_hstore_opt=on)    -- 创建表时，默认使用该配置。
DISTRIBUTE BY hash (t_gmid)   --分布键，选择主键或者关联字段
PARTITION BY range(t_datatime)    -- 分区键
(
partition p2024_1 start('2024-01-01') end ('2024-06-01') every (interval '1 month'),
partition p2024_7 start('2024-06-01') end ('2024-12-31') every (interval '1 month')
);

INSERT INTO hstore_opt_table_demo (t_code, t_gisid, t_datatime, t_gmid)
VALUES
('T001','GIS001','2024-01-01 10:00:00','M001'),
('T002','GIS002','2024-01-02 10:00:00','M002'),
('T003','GIS003','2024-01-03 10:00:00','M003'),
('T004','GIS004','2024-01-04 10:00:00','M004'),
('T005','GIS005','2024-01-05 10:00:00','M005'),
('T006','GIS006','2024-01-06 10:00:00','M006'),
('T007','GIS007','2024-01-07 10:00:00','M007'),
('T008','GIS008','2024-01-08 10:00:00','M008'),
('T009','GIS009','2024-01-09 10:00:00','M009'),
('T010','GIS010','2024-01-10 10:00:00','M010');

  5. 查询该表在各个DN上的cu、delta以及cudesc的脏页率以及大小。 

    1
    		 
    SELECT * FROM pgxc_get_cstore_dirty_ratio('hstore_opt_table_demo');

  6. 也可以查指定分区在各个DN上的cu、delta以及cudesc的脏页率以及大小。 

    1
    		 
    SELECT * FROM pgxc_get_cstore_dirty_ratio('hstore_opt_table_demo','p2024_1_4');

表大小相关GUC介绍

表3 表大小相关GUC介绍

参数名称

描述

取值范围

默认值

fast_obs_tablesize_method

设置快速计算列存V3和V3 hstore_opt表大小的方式。该参数仅9.1.0.100及以上集群版本支持。

参数类型：USERSET

取值范围：枚举型

  • 0：通过遍历OBS文件的方式计算V3表大小。
  • 1：以wlm后台统计的方式，通过pg_relfilenode_size计算表大小。
  • 2：9.1.0.100版本通过cudesc的每个文件的最大偏移和近似估算表大小；9.1.1.200及以上版本通过扫描pg_db_file系统表获取大小，若enable_pg_db_file 为off，则通过估算cudesc表来获取表大小 。

2

fast_obs_dbsize_method

设置快速计算OBS上数据库大小的方式。该参数仅9.1.0.100及以上集群版本支持。

参数类型：USERSET

取值范围：枚举型

  • 0：直接根据OBS桶来估算数据库大小。
  • 1：遍历OBS目录计算数据库大小。
  • 2：遍历pg_db_file系统表计算数据库大小。（9.1.1.200及以上集群有该选项）
  • 9.1.0.100版本为0。
  • 9.1.0.200及以上版本为2。

查询V3表大小最佳配置实践

  1. 表大小查询函数，在查询V3表大小时，所查询的结果都是包含存放在OBS上的CU文件大小。
  2. 仅查询V3表OBS文件大小可以使用pg_obs_file_size函数。
  3. 9.1.0.100版本查询V3表默认使用fast_obs_tablesize_method为2的方法，该方法性能最好，但由于是对cudesc表的估算，精准度不如fast_obs_tablesize_method为0时的方法（精确度最高，但性能最差）。
  4. 9.1.1.200版本查询V3表大小的精确度和性能达到V2表的能力，但要确保obs list优化特性打开(新装默认打开，升级默认关闭，打开方式：GUC设置enable_pg_db_file = on，特性打开后需要等待autovacuum重建完pg_db_file系统表才可以使用)。
  5. 9.1.1.100版本中，计算包含V3表的数据库大小，当集群中只有一个数据库时可通过获取OBS桶信息来计算，性能较好，若有多个数据库时需要遍历OBS上的文件，性能较差；在9.1.1.200及以上版本对数据库大小计算进行优化，相比9.1.1.100性能提升10倍，但需要确保obs list优化特性打开。

查询冷热表大小最佳配置实践

  1. 在查询冷热表大小时，如使用普通表大小查询函数（参见表大小查询），所查询的结果都是包含冷数据的大小。
  2. 若要分别查询冷热表的热数据和冷数据大小时，可以使用pg_lifecycle_table_data_distribute函数，参见冷热表大小查询。
父主题： 数据库管理

相关文档