Postgresql表和索引占用空间回收释放（表空间膨胀）

-- 1.创建测试表t_user
create table if not exists t_user(id serial primary key,user_name varchar(255),pass_word varchar(255),create_time date,dr char(1)
);create index ind_time on t_user(create_time);-- 2.注释
comment on column t_user.id is '测试表';
comment on column t_user.user_name is '账号';
comment on column t_user.pass_word is '密码';
comment on column t_user.create_time is '创建日期';
comment on column t_user.dr is 'delete remark';-- 创建存储过程插入数据
create or replace function batch_insert_proc(num int) returns void as 
$$
beginwhile num > 0 loopinsert into t_user(user_name,pass_word,create_time,dr) values('username'||round(random()*num),'password'||round(random()*num),now(),0);num = num -1;end loop;
exceptionwhen others thenraise exception'(%)',SQLERRM;
end;
$$ language plpgsql;-- 插入100*10000条数据
select batch_insert_proc(1000*1000); --分析表统计信息
analyze t_user;--查询统计信息
SELECTrelname AS "表名",seq_scan AS "顺序扫描次数",seq_tup_read AS "顺序扫描行数",idx_scan AS "索引扫描次数",idx_tup_fetch AS "通过索引获取的行数",n_tup_ins AS "插入的行数",n_tup_upd AS "更新的行数",n_tup_del AS "删除的行数",n_live_tup AS "表中当前行数",n_dead_tup AS "表中已删除的行数",last_vacuum AS "上次VACUUM操作的时间",last_autovacuum AS "上次自动VACUUM操作的时间",last_analyze AS "上次ANALYZE操作的时间",last_autoanalyze AS "上次自动ANALYZE操作的时间"
FROM pg_stat_user_tables;--查询表数据量大小信息SELECTtable_size.relname 表名,pg_size_pretty ( pg_relation_size ( relid ) ) 表数据大小,pg_size_pretty ( pg_indexes_size ( relid ) ) 表总索引大小,pg_size_pretty ( pg_total_relation_size ( relid ) ) 表总大小,表行数 
FROM
pg_stat_user_tables table_sizeLEFT JOIN (SELECTrelname,reltuples :: DECIMAL ( 19, 0 ) 表行数 FROMpg_class rJOIN pg_namespace n ON ( relnamespace = n.oid ) WHERErelkind = 'r' AND n.nspname = 'public' ) table_num ON table_num.relname = table_size.relname 
WHEREschemaname = 'public' 
ORDER BYpg_relation_size ( relid ) DESC;--查询表的大小信息表名  | 表数据大小 | 表总索引大小 |  表总大小  | 表行数  
--------+------------+--------------+------------+---------t_user | 71 MB      | 21 MB        | 93 MB      | 1000000tab1   | 8192 bytes | 0 bytes      | 8192 bytes |       1
(2 rows)--物理文件大小信息
[postgres@SJZTproxy-103-38 16646]$ du -sh 16675
72M     16675
[postgres@SJZTproxy-103-38 16646]$ du -sh 16677
22M     16677--备注：
--获取表的物理文件路径
select pg_relation_filenode('t_user'),pg_relation_filepath('t_user');
--查看索引对应的物理文件路劲
select pg_relation_filenode('ind_time'),pg_relation_filepath('ind_time');--truncate前数据文件大小 
[postgres@SJZTproxy-103-38 16646]$ ls -l 16661
-rw------- 1 postgres postgres 6832128 Sep  2 16:54 16661
[postgres@SJZTproxy-103-38 16646]$ 
[postgres@SJZTproxy-103-38 16646]$ du -sh 16661
6.6M    16661dbtest=> truncate table t_user;
TRUNCATE TABLE--truncate后数据文件大小
[postgres@SJZTproxy-103-38 16646]$ du -sh 16661
0       16661--删除索引drop index t_user_pkey;