数据库
列出所有数据库，可以看到数据库占用的空间和使用的表空间。
gaussdb=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+--------------+----------+---------+-------+-----------------------+--------+------------+-------------------------------------------------
db4mysqlb | rdsAdmin | UTF8 | C | C | | 24 MB | pg_default |
db4mysqlm | rdsAdmin | UTF8 | C | C | | 19 MB | pg_default |
db4ora | rdsAdmin | UTF8 | C | C | | 24 MB | pg_default |
db4pg | rdsAdmin | UTF8 | C | C | | 24 MB | pg_default |
postgres | rdsAdmin | UTF8 | C | C | =Tc/rdsAdmin +| 391 MB | pg_default | default administrative connection database
| | | | | rdsAdmin=CTc/rdsAdmin+| | |
| | | | | rdsBackup=c/rdsAdmin +| | |
| | | | | rdsRepl=c/rdsAdmin +| | |
| | | | | root=c/rdsAdmin +| | |
| | | | | rdsMetric=c/rdsAdmin | | |
template0 | rdsAdmin | UTF8 | C | C | =c/rdsAdmin +| 24 MB | pg_default | default template for new databases
| | | | | rdsAdmin=CTc/rdsAdmin | | |
template1 | rdsAdmin | UTF8 | C | C | =c/rdsAdmin +| 24 MB | pg_default | unmodifiable empty database
| | | | | rdsAdmin=CTc/rdsAdmin | | |
templatem | rdsAdmin | UTF8 | C | C | =c/rdsAdmin +| 19 MB | pg_default | unmodifiable MySQL compatible template database
| | | | | rdsAdmin=CTc/rdsAdmin | | |
tpcc | benchmarksql | UTF8 | C | C | | 77 GB | tpcc |
(9 rows)
查询单个数据库占用的磁盘空间：
gaussdb=# select pg_size_pretty(pg_database_size('tpcc'));
pg_size_pretty
----------------
77 GB
(1 row)
表空间
列出所有表空间，但是看不到表空间的大小。
gaussdb=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Description
------------+--------------+-----------------+-------------------+-------------
pg_default | rdsAdmin | | |
pg_global | rdsAdmin | | |
tpcc | benchmarksql | tablespace/tpcc | |
(3 rows)
查询单个表空间占用的磁盘空间：
gaussdb=# select pg_size_pretty(pg_tablespace_size('tpcc'));
pg_size_pretty
----------------
77 GB
(1 row)
查询所有表空间占用的磁盘空间：
gaussdb=# select spcname,pg_size_pretty(pg_tablespace_size(oid)) from pg_tablespace order by pg_tablespace_size(oid);
spcname | pg_size_pretty
------------+----------------
pg_global | 513 MB
pg_default | 550 MB
tpcc | 77 GB
(3 rows)
查看表空间容量上限：
tpcc=# select * from pg_tablespace;
spcname | spcowner | spcacl | spcoptions | spcmaxsize | relative
------------+----------+--------+------------+-------------+----------
pg_default | 10 | | | | f
pg_global | 10 | | | | f
tpcc | 19408 | | | | t
tbs1 | 10 | | | 104857600 K | t
(4 rows)
字段spcmaxsize为空表示无限制（unlimited）。
修改表空间容量上限：
tpcc=# alter tablespace tbs1 resize maxsize '200G';
ALTER TABLESPACE
tpcc=# select * from pg_tablespace;
spcname | spcowner | spcacl | spcoptions | spcmaxsize | relative
------------+----------+--------+------------+-------------+----------
pg_default | 10 | | | | f
pg_global | 10 | | | | f
tpcc | 19408 | | | | t
tbs1 | 10 | | | 209715200 K | t
(4 rows)
修改表空间上限为unlimited：
tpcc=# alter tablespace tbs1 resize maxsize unlimited;
ALTER TABLESPACE
tpcc=# select * from pg_tablespace;
spcname | spcowner | spcacl | spcoptions | spcmaxsize | relative
------------+----------+--------+------------+------------+----------
pg_default | 10 | | | | f
pg_global | 10 | | | | f
tpcc | 19408 | | | | t
tbs1 | 10 | | | | t
(4 rows)
🐘不同于Oracle数据库，GaussDB数据库中的表空间直接对应磁盘上的文件系统路径，无需添加数据文件，关注磁盘空间使用量即可。
表
列出所有模式：
gaussdb=# \dn
列出当前数据库中的所有表，可以看到表的大小。
gaussdb=# \c tpcc
tpcc=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Storage |
--------+------------------+-------+--------------+---------+--------------------------------------------------------------------------------+
public | bmsql_config | table | benchmarksql | 32 kB | {orientation=row,compression=no,storage_type=USTORE,segment=off} |
public | bmsql_customer | table | benchmarksql | 14 GB | {orientation=row,fillfactor=80,compression=no,storage_type=USTORE,segment=off} |
public | bmsql_district | table | benchmarksql | 960 kB | {orientation=row,fillfactor=80,compression=no,storage_type=USTORE,segment=off} |
public | bmsql_history | table | benchmarksql | 1730 MB | {orientation=row,fillfactor=80,compression=no,storage_type=USTORE,segment=off} |
public | bmsql_item | table | benchmarksql | 9784 kB | {orientation=row,compression=no,storage_type=USTORE,segment=off} |
public | bmsql_new_order | table | benchmarksql | 202 MB | {orientation=row,fillfactor=80,compression=no,storage_type=USTORE,segment=off} |
public | bmsql_oorder | table | benchmarksql | 1273 MB | {orientation=row,fillfactor=80,compression=no,storage_type=USTORE,segment=off} |
public | bmsql_order_line | table | benchmarksql | 19 GB | {orientation=row,fillfactor=80,compression=no,storage_type=USTORE,segment=off} |
public | bmsql_stock | table | benchmarksql | 26 GB | {orientation=row,fillfactor=80,compression=no,storage_type=USTORE,segment=off} |
public | bmsql_warehouse | table | benchmarksql | 120 kB | {orientation=row,fillfactor=80,compression=no,storage_type=USTORE,segment=off} |
(10 rows)
注意这里只会显示public模式下的表。
查看单个表占用的磁盘空间大小：
tpcc=# select pg_size_pretty(pg_total_relation_size('bmsql_customer')); --表数据+索引数据
pg_size_pretty
----------------
16 GB
(1 row)
tpcc=# select pg_size_pretty(pg_table_size('bmsql_customer')); --只看表数据
pg_size_pretty
----------------
14 GB
(1 row)
tpcc=# select pg_size_pretty(pg_indexes_size('bmsql_customer')); --只看索引数据
pg_size_pretty
----------------
1988 MB
(1 row)
可以看到\dt命令现实的表大小是数据文件的大小，不包含索引占用的空间。
📖 PG数据库中，表数据 = 主分支 + fsm分支 + vm分支 + init分支 + TOAST表大小
tpcc=# select pg_size_pretty(pg_relation_size('bmsql_customer')); --主分支大小（数据文件）
pg_size_pretty
----------------
14 GB
(1 row)
tpcc=# select pg_size_pretty(pg_relation_size('bmsql_customer','main')); --主分支大小（数据文件）
pg_size_pretty
----------------
14 GB
(1 row)
tpcc=# select pg_size_pretty(pg_relation_size('bmsql_customer','fsm')); --fsm分支大小
pg_size_pretty
----------------
3640 kB
(1 row)
tpcc=# select pg_size_pretty(pg_relation_size('bmsql_customer','vm')); --vm分支大小
pg_size_pretty
----------------
0 bytes
(1 row)
tpcc=# select pg_size_pretty(pg_relation_size('bmsql_customer','init')); --init分支大小
pg_size_pretty
----------------
0 bytes
(1 row)
tpcc=# select pg_size_pretty(pg_total_relation_size(reltoastrelid))
from pg_class where relname='bmsql_customer'; --TOAST表大小
pg_size_pretty
----------------
0 bytes
(1 row)
列出当前数据库中指定条件的所有表：
\c DBNAME
select schemaname,tablename,tableowner,tablespace from pg_tables where tableowner='benchmarksql';
select schemaname,tablename,tableowner,tablespace from pg_tables where schemaname='public';
对当前数据库中的所有用户表按大小排序：
tpcc=# select schemaname,relname, pg_size_pretty(pg_relation_size(relid))
from pg_stat_user_tables order by pg_relation_size(relid) desc limit 10;
schemaname | relname | pg_size_pretty
------------+------------------+----------------
public | bmsql_stock | 26 GB
public | bmsql_order_line | 19 GB
public | bmsql_customer | 14 GB
public | bmsql_history | 1730 MB
public | bmsql_oorder | 1273 MB
public | bmsql_new_order | 202 MB
public | bmsql_item | 9760 kB
public | bmsql_district | 936 kB
public | bmsql_warehouse | 96 kB
public | bmsql_config | 8192 bytes
(10 rows)
对当前数据库中的所有表（包含用户表和系统表）按大小排序：
tpcc=# select schemaname,relname, pg_size_pretty(pg_relation_size(relid))
from pg_stat_all_tables order by pg_relation_size(relid) desc limit 10;
schemaname | relname | pg_size_pretty
------------+------------------+----------------
public | bmsql_stock | 26 GB
public | bmsql_order_line | 19 GB
public | bmsql_customer | 14 GB
public | bmsql_history | 1730 MB
public | bmsql_oorder | 1273 MB
public | bmsql_new_order | 202 MB
public | bmsql_item | 9760 kB
pg_toast | pg_toast_2618 | 5800 kB
pg_catalog | pg_attribute | 2424 kB
pg_catalog | pg_proc | 2160 kB
(10 rows)
对当前数据库中的所有对象（包括表/toast表/索引/视图/序列）按大小排序：
tpcc=# with vtbs as (
select oid as spcoid,spcname from pg_tablespace
union all
select 0 as spcoid,spcname from pg_tablespace
where oid=(select dattablespace from pg_database
where datname=(select current_database()))
)
select relname,case
when relkind='r' then 'regular table'
when relkind='i' then 'index'
when relkind='s' then 'sequence'
when relkind='v' then 'view'
when relkind='m' then 'materialized view'
when relkind='t' then 'toast table'
when relkind='f' then 'foreign table'
when relkind='c' then 'composite type'
end as objkind,
pa.rolname,vtbs.spcname,
pg_size_pretty(pg_relation_size(pc.oid)) as objsize
from pg_class pc, pg_authid pa, vtbs
where pc.relowner = pa.oid and pc.reltablespace = vtbs.spcoid
order by pg_relation_size(pc.oid) desc limit 20;
relname | objkind | rolname | spcname | objsize
-----------------------+---------------+--------------+---------+---------
bmsql_stock | regular table | benchmarksql | tpcc | 26 GB
bmsql_order_line | regular table | benchmarksql | tpcc | 19 GB
bmsql_customer | regular table | benchmarksql | tpcc | 14 GB
bmsql_order_line_pkey | index | benchmarksql | tpcc | 7650 MB
bmsql_stock_pkey | index | benchmarksql | tpcc | 1978 MB
bmsql_history | regular table | benchmarksql | tpcc | 1730 MB
bmsql_oorder | regular table | benchmarksql | tpcc | 1273 MB
bmsql_customer_idx1 | index | benchmarksql | tpcc | 1223 MB
bmsql_oorder_idx1 | index | benchmarksql | tpcc | 817 MB
bmsql_customer_pkey | index | benchmarksql | tpcc | 765 MB
bmsql_oorder_pkey | index | benchmarksql | tpcc | 765 MB
bmsql_history_pkey | index | benchmarksql | tpcc | 593 MB
bmsql_new_order_pkey | index | benchmarksql | tpcc | 229 MB
bmsql_new_order | regular table | benchmarksql | tpcc | 202 MB
bmsql_item | regular table | benchmarksql | tpcc | 9760 kB
pg_toast_2618 | toast table | rdsAdmin | tpcc | 5800 kB
bmsql_item_pkey | index | benchmarksql | tpcc | 3096 kB
pg_attribute | regular table | rdsAdmin | tpcc | 2424 kB
pg_proc | regular table | rdsAdmin | tpcc | 2160 kB
pg_toast_3243 | toast table | rdsAdmin | tpcc | 1632 kB
(20 rows)