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

最新推荐文章于 2022-09-16 13:34:53 发布

原创最新推荐文章于 2022-09-16 13:34:53 发布 · 1.3k 阅读

0 ·

CC 4.0 BY-SA版权

postgres 专栏收录该内容

2 篇文章

订阅专栏

本文深入探讨了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;