postgresql - 如何在postgresql中准确获取数据库大小？

Question

我们在 PostgreSQL 9.1 版上运行，之前我们在一个表中有超过 10 亿行并且已被删除。但是，看起来 \l+ 命令仍然不准确地报告实际数据库大小(它报告了 568GB，但实际上远小于此值)。

568GB 错误的证据是单个表的大小计数没有加起来，如您所见，前 20 个关系的大小为 4292MB，其余 985 个关系都远低于 10MB。事实上，所有这些加起来大约不到 6GB。

知道为什么 PostgreSQL 如此臃肿吗？如果确认，我该如何去肿？我对 VACUUM 不是很熟悉，这是我需要做的吗？如果是这样，如何？

非常感谢。

pmlex=# \l+
                                                                       List of databases
      Name       |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description                 
-----------------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 pmlex           | pmlex    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 568 GB  | pg_default | 
 pmlex_analytics | pmlex    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 433 MB  | pg_default | 
 postgres        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 5945 kB | pg_default | default administrative connection database
 template0       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 5841 kB | pg_default | unmodifiable empty database
                 |          |          |             |             | postgres=CTc/postgres |         |            | 
 template1       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 5841 kB | pg_default | default template for new databases
                 |          |          |             |             | postgres=CTc/postgres |         |            | 
(5 rows)

pmlex=# SELECT nspname || '.' || relname AS "relation",
pmlex-#     pg_size_pretty(pg_relation_size(C.oid)) AS "size"
pmlex-#   FROM pg_class C
pmlex-#   LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
pmlex-#   WHERE nspname NOT IN ('pg_catalog', 'information_schema')
pmlex-#   ORDER BY pg_relation_size(C.oid) DESC;
              relation               |  size   
-------------------------------------+---------
 public.page_page                    | 1289 MB
 public.page_pageimagehistory        | 570 MB
 pg_toast.pg_toast_158103            | 273 MB
 public.celery_taskmeta_task_id_key  | 233 MB
 public.page_page_unique_hash_uniq   | 140 MB
 public.page_page_ad_text_id         | 136 MB
 public.page_page_kn_result_id       | 125 MB
 public.page_page_seo_term_id        | 124 MB
 public.page_page_kn_search_id       | 124 MB
 public.page_page_direct_network_tag | 124 MB
 public.page_page_traffic_source_id  | 123 MB
 public.page_page_active             | 123 MB
 public.page_page_is_referrer        | 123 MB
 public.page_page_category_id        | 123 MB
 public.page_page_host_id            | 123 MB
 public.page_page_serp_id            | 121 MB
 public.page_page_domain_id          | 120 MB
 public.celery_taskmeta_pkey         | 106 MB
 public.page_pagerenderhistory       | 102 MB
 public.page_page_campaign_id        | 89 MB
...
...
...
 pg_toast.pg_toast_4354379           | 0 bytes
(1005 rows)

Answer 1

您的选择包括:

1).确保 autovacuum 已启用并积极设置。

2).按照我之前评论中提到的重新创建表(create-table-as-select + truncate + reload the original table)。

3).如果您能够承受被锁定在该表之外(独占锁)，则在该表上运行 CLUSTER。

4). VACUUM FULL，尽管 CLUSTER 更有效且值得推荐。

5).运行几次普通的 VACUUM ANALYZE 并保持表格不变，最终在新数据进入时填充空间。

6).通过 pg_dump 转储并重新加载表

7). pg_repack (虽然我没有在生产中使用它)