PostgreSQL-恢复的数据库小于原始数据库 [英] PostgreSQL - restored database smaller than original

问题描述

我已经使用pg_dump将PostgreSQL数据库备份到 .sql文件中。
还原数据库时，其大小为2.8GB，而源（原始）数据库的大小为3.7GB。

I have made a backup of my PostgreSQL database using pg_dump to ".sql" file. When I restored the database, its size was 2.8GB compared with 3.7GB of the source (original) database. The application that access the database appears to work fine.

减小还原数据库大小的原因是什么？

What is the reason to smaller size of the restored database?

推荐答案

简短的答案是，数据库存储在速度方面比空间方面更优化。

The short answer is that database storage is more optimised for speed than space.

例如，如果您插入100行到表中，然后删除具有奇数ID的每一行，DBMS可以写出只有50行的新表，但是将其简单地标记为可用空间并在下次插入时重复使用它们的效率更高一排。因此，该表占用的空间是当前所需空间的两倍。

For instance, if you inserted 100 rows into a table, then deleted every row with an odd numbered ID, the DBMS could write out a new table with only 50 rows, but it's more efficient for it to simply mark the deleted rows as free space and reuse them when you next insert a row. Therefore the table takes up twice as much space as is currently needed.

Postgres使用 MVCC而非锁定来进行事务管理使这种可能性更大，因为UPDATE通常包括将新行写入存储，然后在没有事务查看时将旧行标记为删除。

Postgres's use of "MVCC", rather than locking, for transaction management makes this even more likely, since an UPDATE usually involves writing a new row to storage, then marking the old row deleted once no transactions are looking at it.

通过转储和还原数据库，您可以重新创建数据库没有所有这些可用空间的数据库。本质上，这就是 VACUUM FULL 命令的作用-将当前数据重写为新文件，然后删除旧文件。

By dumping and restoring the database, you are recreating a DB without all this free space. This is essentially what the VACUUM FULL command does - it rewrites the current data into a new file, then deletes the old file.

有一个与Postgres一起发布的扩展名为 pg_freespace ，您可以检查其中的一些内容。例如您可以通过以下方式列出主表的大小（不包括存储在单独的 TOAST表中的索引和列）和每个表使用的可用空间：

There is an extension distributed with Postgres called pg_freespace which lets you examine some of this. e.g. you can list the main table size (not including indexes and columns stored in separate "TOAST" tables) and free space used by each table with the below:

Select oid::regclass::varchar as table,
      pg_size_pretty(pg_relation_size(oid)/1024 * 1024) As size,
      pg_size_pretty(sum(free)) As free
 From (
   Select c.oid,
       (pg_freespace(c.oid)).avail As free
     From pg_class c
     Join pg_namespace n on n.oid = c.relnamespace
    Where c.relkind = 'r'
      And n.nspname Not In ('information_schema', 'pg_catalog')
 ) tbl
 Group By oid
 Order By pg_relation_size(oid) Desc, sum(free) Desc;

这篇关于PostgreSQL-恢复的数据库小于原始数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！