利用dumpall导出数据库,再恢复,恢复后数据库的大小和原来的大小不等

本文深入探讨了PostgreSQL数据库管理中的关键操作:VACUUM与索引维护。通过VACUUM命令有效管理磁盘空间,避免数据膨胀,并通过重新构建索引来解决索引膨胀问题,从而显著提升数据库性能。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

原因如下:

1、没有运行VACUUM,VACUUM是删除那些已经标示为删除的数据并释放空间。数据库总是不断地在执行删除,更新等操作。良好的空间管理非常重要,能够对性能带来大幅提高。在postgresql中用于维护数据库磁盘空间的工具是VACUUM,其重要的作用是删除那些已经标示为删除的数据并释放空间。

2、postgresql索引膨胀问题,重新构建索引后正常

https://wiki.postgresql.org/wiki/Index_Maintenance#Index_Bloat

Index size/usage statistics

Table & index sizes along which indexes are being scanned and how many tuples are fetched. See Disk Usage for another view that includes both table and index sizes.
Performance Snippets
Index statistics
Works with PostgreSQL
>=8.1
Written in
SQL
Depends on
Nothing
SELECT
    t.tablename,
    indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
    pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
    CASE WHEN indisunique THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index 

x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值