Postgresql中删除大量数据优化表空间

冷不防 2021-06-22 15:38 794阅读 0赞

1.  步骤一

-- 禁用触发器
    ALTER TABLE 表名 DISABLE TRIGGER ALL;
    --删除表记录
    delete from 表名 where  id in(select id from ha_movhis_temp);
    --启用触发器
    ALTER TABLE 表名 ENABLE TRIGGER ALL;

1.  步骤二

--查看表空间大小
    select pg_size_pretty(pg_relation_size('表名'));
    
    -- 优化表空间
    -- 维护数据库磁盘，释放空间
    vacuum FULL 表名;

1.  步骤三

-- 重建索引，替换查询效率
    REINDEX TABLE 表名;

其他技能，PG常用命令

<table> 
 <thead> 
  <tr> 
   <th>函数名</th> 
   <th>返回类型</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>pg_column_size(any)</td> 
   <td>int</td> 
   <td>存储一个指定的数值需要的字节数（可能压缩过）</td> 
  </tr> 
  <tr> 
   <td>pg_database_size(oid)</td> 
   <td>bigint</td> 
   <td>指定OID的数据库使用的磁盘空间</td> 
  </tr> 
  <tr> 
   <td>pg_database_size(name)</td> 
   <td>bigint</td> 
   <td>指定名称的数据库使用的磁盘空间</td> 
  </tr> 
  <tr> 
   <td>pg_indexes_size(regclass)</td> 
   <td>bigint</td> 
   <td>关联指定表OID或表名的表索引的使用总磁盘空间</td> 
  </tr> 
  <tr> 
   <td>pg_relation_size(relation regclass, fork text)</td> 
   <td>bigint</td> 
   <td>指定OID或名的表或索引，通过指定fork(‘main’, ‘fsm’ 或’vm’)所使用的磁盘空间</td> 
  </tr> 
  <tr> 
   <td>pg_relation_size(relation regclass)</td> 
   <td>bigint</td> 
   <td>pg_relation_size(…, ‘main’)的缩写</td> 
  </tr> 
  <tr> 
   <td>pg_size_pretty(bigint)</td> 
   <td>text</td> 
   <td>Converts a size in bytes expressed as a 64-bit integer into a human-readable format with size units</td> 
  </tr> 
  <tr> 
   <td>pg_size_pretty(numeric)</td> 
   <td>text</td> 
   <td>把以字节计算的数值转换成一个人类易读的尺寸单位</td> 
  </tr> 
  <tr> 
   <td>pg_table_size(regclass)</td> 
   <td>bigint</td> 
   <td>指定表OID或表名的表使用的磁盘空间，除去索引（但是包含TOAST，自由空间映射和可视映射）</td> 
  </tr> 
  <tr> 
   <td>pg_tablespace_size(oid)</td> 
   <td>bigint</td> 
   <td>指定OID的表空间使用的磁盘空间</td> 
  </tr> 
  <tr> 
   <td>pg_tablespace_size(name)</td> 
   <td>bigint</td> 
   <td>指定名称的表空间使用的磁盘空间</td> 
  </tr> 
  <tr> 
   <td>pg_total_relation_size(regclass)</td> 
   <td>bigint</td> 
   <td>指定表OID或表名使用的总磁盘空间，包括所有索引和TOAST数据</td> 
  </tr> 
 </tbody> 
</table>

--查看数据库大小，不计算索引
    select pg_size_pretty(pg_database_size('mydb'));
    --查看数据库大小，包含索引
    select pg_size_pretty(pg_total_size('mydb'));
    --查看表中索引大小
    select pg_size_pretty(pg_indexes_size('test_1'));
    --查看表大小,不包括索引
    select pg_size_pretty(pg_relation_size('test_1'));  
    --查看表大小,包括索引
    select pg_size_pretty(pg_total_relation_size('test_1'));  
    --查看某个模式大小，包括索引。不包括索引可用pg_relation_size
    select schemaname,round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024) "Mb" from pg_tables where schemaname='mysch' group by 1;
    --查看表空间大小
    select pg_size_pretty(pg_tablespace_size('pg_global'));
    --查看表对应的数据文件
    select pg_relation_filepath('test_1');
    --切换log日志文件到下一个
    select pg_rotate_logfile();
    --切换日志
    select pg_switch_xlog();
    checkpoint