Pgsql存储占用分析

基础命令

-- 查询表大小
SELECT pg_total_relation_size('table_name');-- 查询表大小（不带索引）
SELECT pg_table_size('table_name');-- 查询表索引大小
SELECT pg_indexes_size('table_name');-- 查询表具体大小
SELECT pg_relation_size('table_name');
SELECT pg_relation_size('table_name', 'fsm');
SELECT pg_relation_size('table_name', 'vm');
SELECT pg_relation_size('table_name', 'init');-- 查询列大小
SELECT pg_column_size(column_name);-- 转换人类可读
SELECT pg_size_pretty(pg_relation_size('table_name'));-- 查询库大小
SELECT pg_database_size(database_name);-- 查询全库表大小
SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size FROM information_schema.tables ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC;

pg_total_relation_size包含pg_table_size和pg_indexes_size

pg_table_size包含了表的main、fsm、vm和init文件大小，pg_relation_size('table_name')等价pg_relation_size('table_name', 'main')

pg_size_pretty转换人类可读单位

行存储分析

Pg表采用8K最小存储方式，当表中数据不足8K时，整体占用大小仍为8K

当8K占满时，开辟新的8K空间占用，即按8K、16K、24K....逐步扩大占用

列存储分析

Pg采用bit编码标记的方式压缩存储， 具体参考：https://zhuanlan.zhihu.com/p/266942446

1、针对Null，可以做到Null不占用空间

2、varchar和text类型占用一致，只是varchar有长度限制

3、int固定占用8位，而针对10000以内的整数，字符类型一般只占用2-7位，可以考虑字符类型存储，以节省空间

4、数组方式比json方式节省空间

造数据相关命令

-- 清空表数据
truncate table table_name;-- 迁移数据到新表
insert into new_table select * from old_table;-- 快速复制数据
insert into table_name select * from table_name;