在PostgreSql中如何计算表字段,表,数据库,以及索引等的大小?
需要介绍PostgreSql的内置函数,在文档中就有,如下:
pg_column_size(any) | int | Number of bytes used to store a particular value (possibly compressed) |
pg_database_size(oid) | bigint | Disk space used by the database with the specified OID |
pg_database_size(name) | bigint | Disk space used by the database with the specified name |
pg_indexes_size(regclass) | bigint | Total disk space used by indexes attached to the specified table |
pg_relation_size(relation regclass, fork text) | bigint | Disk space used by the specified fork ('main', 'fsm' or 'vm') of the specified table or index |
pg_relation_size(relation regclass) | bigint | Shorthand for pg_relation_size(..., 'main') |
pg_size_pretty(bigint) | text | Converts a size in bytes expressed as a 64-bit integer into a human-readable format with size units |
pg_size_pretty(numeric) | text | Converts a size in bytes expressed as a numeric value into a human-readable format with size units |
pg_table_size(regclass) | bigint | Disk space used by the specified table, excluding indexes (but including TOAST, free space map, and visibility map) |
pg_tablespace_size(oid) | bigint | Disk space used by the tablespace with the specified OID |
pg_tablespace_size(name) | bigint | Disk space used by the tablespace with the specified name |
pg_total_relation_size(regclass) | bigint | Total disk space used by the specified table, including all indexes and TOAST data |
第一个函数的参数是relation和fork,relation是一个对象应该是一个表(以我目前的对pg的了解),fork指的是这个表在磁盘中存储的三个文件,文档说明也提到了,特指main,fsm,vm,那么这三种参数值表示说明意思呢?
这里涉及到PostgreSql的存储方式,每一个数据库以其OID值会在磁盘中创建名称为OID的文件夹,文件夹里面存储的是这个数据库下的表的文件,也已OID为名称存储,其中就存在以fsm,vm为后缀的文件。fsm代表的是空闲空间映射表文件,vm表示可见性映射表文件main应该是没有后缀的那个文件(这个暂时是猜的),如我有一个表emp,其oid=19467
并且其在oid=12029的database下。使用sql:select pg_relation_size('emp'::regclass, 'vm') 结果为8kb,查看文件夹下的此表的vm文件也是8kb,emp表总共是40kb,正好是