ANBOB™

试想一下如果你的OpenGauss或postgreSQL数据库主机告警使用率超过了90%， 且因为使用local 存储，所有硬盘槽位已用完，除了迁移或扩展外部存储以外，是否可以给数据库做”瘦身”， 在PostgreSQL数据库中，有几种技术可以帮助减少数据库存储空间的使用：

1. 检查临时文件：可以使用以下查询来检查是否有大量的临时文件被生成：

   SELECT pg_size_pretty(SUM(temp_files)) AS total_temp_files
   FROM pg_stat_database;

如果total_temp_files的值较大，表示有大量的临时文件被生成。

对于临时文件，可以使用以下查询来删除它们：

   SELECT pg_terminate_backend(pid)
   FROM pg_stat_activity
   WHERE temp_files > 0;

2. 检查xlog文件：可以使用以下查询来检查未清理的xlog文件数量：

   SELECT COUNT(*) AS total_xlog_files
   FROM pg_stat_bgwriter;

如果total_xlog_files的值较大，表示有未清理的xlog文件。对于xlog文件，可以使用pg_archivecleanup工具或手动删除旧的xlog文件。

3. 检查自动清理设置：检查PostgreSQL的配置文件（通常是postgresql.conf）中的以下参数，确保它们被正确设置：
– `temp_file_limit`：控制临时文件的最大大小。确保其值不过大，以避免生成过多的临时文件。
– `max_wal_size`和`min_wal_size`：控制xlog文件的大小。确保这些值适当，以避免生成过多的xlog文件。

4. 要检查PostgreSQL中是否存在大量的膨胀对象？

查询pg_stat_all_tables视图，查看表的膨胀对象数量：

   SELECT schemaname, tablename, n_dead_tup
   FROM pg_stat_all_tables
   WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
   AND n_dead_tup > 1000 
   order by 3 desc ;

查询pg_stat_all_indexes视图，查看索引的膨胀对象数量：

   SELECT schemaname, tablename, indexname, idx_tup_del, idx_tup_hot_upd
   FROM pg_stat_all_indexes
   WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
   AND (idx_tup_del > 0 OR idx_tup_hot_upd > 0)
 order by idx_tup_del+idx_tup_hot_upd  desc;

5. 分区表：查看数据通过使用分区表，可以将大型表拆分为更小的子表，每个子表只包含特定范围的数据。 可以创建一个定期的清理任务或脚本，删除不再需要的历史数据，定期清理无用的数据和归档历史数据可以释放存储空间。可以根据时间范围、数据的状态或其他条件来确定要删除的数据。

查询pg_total_relation_size函数，获取每个表的总大小：

   SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size
   FROM pg_tables
   ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
   LIMIT 10;

6. 删除PostgreSQL中不使用的索引

查询pg_stat_user_indexes视图，查看索引的使用情况：

SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
   FROM pg_stat_user_indexes
   WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
   ORDER BY idx_scan ASC;

7. 索引优化：优化索引可以减少索引占用的存储空间。可以考虑使用适当的索引类型、选择合适的索引列和使用部分索引、BRIN等。

SELECT tablename, attname, correlation
FROM pg_stats
WHERE tablename = 'sale_fact';

当correlation接近1时，表示是有序的， 并且索引较大时，如id 或time 可以考虑brin索引类型.

8. 删除 PostgreSQL 中的重复列索引： 在POSTGRESQL中同样的列可以创建多个索引，可以删除具有相同列的重复索引，可以根据 indexdef 列的定义来判断索引是否重复。

SELECT schemaname, tablename, indexname, indexdef
   FROM pg_indexes
   WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
   ORDER BY tablename, indexname;

# 重复相同的列
select
  relname,
  (array_agg(idx))[1] idx1,
  (array_agg(idx))[2] idx2,
  (array_agg(idx))[3] idx3
from
  (
    select
      indrelid::regclass as relname,
      indexrelid::regclass as idx,
      (indrelid::text || indclass::text || indkey::text || coalesce(indexprs::text, '') || coalesce(indpred::text, '')) as key
    from
      pg_index
  ) sub
group by
  relname,
  key
having
  count(*) > 1;

# 列不完全一样，但左侧包含
select
  a.indrelid::regclass,
  a.indexrelid::regclass as idx1, 
  b.indexrelid::regclass as idx2, 
  trim(trailing ')' from  split_part(pg_get_indexdef(a.indexrelid), '(', 2)) idx1_col,
  trim(trailing ')' from  split_part(pg_get_indexdef(b.indexrelid), '(', 2)) idx2_col
from
  pg_index a 
  join pg_index b 
    on a.indrelid = b.indrelid and a.indexrelid <> b.indexrelid 
    and (strpos(b.indkey::text , (a.indkey::text  || ' ')) = 1 or a.indkey = b.indkey)
order by 1,2

9. 压缩：PostgreSQL支持压缩来减小存储空间的使用。可以使用内置的压缩算法（如TOAST压缩）或第三方扩展来进行压缩。在openGauss 5.0中支持更好的压缩比例的算法,行存表不支持压缩。

— Auto-compression TOAST-able data

— Postgres Pro compression / encryption

— Cstore_fdw columnar store extension

— ZSON extension for jsonb format

— Compression file systems (like ZFS or Btrfs)

— TimeScaleDB for time-series data

POSTGRESQL的一个Postgres Pro 企业版分支支持压缩表空间，之后表空间里创建的表都会压纹使用zstd，也可以指定zlib。目前社区版还不支持.

  
    postgres=# CREATE TABLESPACE zfs LOCATION '/var/data/cfs' WITH (compression=true);

  postgres=# CREATE TABLESPACE zfs1 LOCATION '/var/data/cfs1' WITH (compression='zlib');

压缩列

  
CREATE TABLE tab_compression (
     a text COMPRESSION pglz,
     b text COMPRESSION lz4);

注意： 当前的pg 支持Columnar storae、TOAST、WAL、pg_dump、pg_basebackup、 COPY、network的压缩,（看fujitsu说未来版本应该会支持row table , page data压缩） 之前看中国某动写从oracle迁移到openGauss系库变小了，就是因有oracle的lob 迁移到opengauss后TOAST(默认使用了压缩)， 这样的宣传我觉的对用户是一种误导，如果对oracle lob 启用高级压缩，不妨再来比比。

10. 数据类型选择：选择适当的数据类型可以减少存储空间的使用。例如，使用小整数类型代替整数类型，使用变长字符串类型（如varchar）代替定长字符串类型（如char）等。

11. 可以使用 `VACUUM FULL` 命令会对整个表进行重组。`VACUUM FULL` 命令会对整个表进行重组，而不仅仅是删除过期的行。这意味着它会创建一个新的表，并将数据从旧表复制到新表中，然后删除旧表，`VACUUM FULL` 命令需要足够的磁盘空间来创建新的表。因此，在执行 `VACUUM FULL` 命令期间，表将被锁定，并且在大型表上可能需要较长的时间。

这些技术可以结合使用，根据具体的应用场景和需求来减少数据库存储空间的使用。

打赏

微信扫一扫，打赏作者吧～