PostgreSQL:如何清除 Toast 表中包含某些列的表？

Question

当我使用此命令清除表时(名称为 v_xml_cdr):

DELETE FROM v_xml_cdr;

然后用命令\d+ 检查表的大小，但是表 v_xml_cdr 的大小和以前几乎一样。

我在 stackoverflow 中搜索并了解到这是因为 Toast 表，一些列(例如 Text 类型)使用扩展存储，PostgreSQL 将它们视为 blob 并使用 toast 表来存储实际数据。

\d+ v_xml_cdr

                                         Table "public.v_xml_cdr”
         Column         |            Type             | Modifiers | Storage  | Stats target | Description 
------------------------+-----------------------------+-----------+----------+--------------+-------------
 uuid                   | uuid                        | not null  | plain    |              | 
 domain_uuid            | uuid                        |           | plain    |              | 
 extension_uuid         | uuid                        |           | plain    |              | 
 domain_name            | text                        |           | extended |              | 
 accountcode            | text                        |           | extended |              | 
 direction              | text                        |           | extended |              | 
...

出现这个问题后，有人说“vacuum full”命令可以缩小表的大小。

但我想知道除了使用“vacuum full”命令之外，在清除表时是否有避免此问题的好方法。

Answer 1

https://www.postgresql.org/docs/current/static/routine-vacuuming.html#VACUUM-FOR-SPACE-RECOVERY

In PostgreSQL, an UPDATE or DELETE of a row does not immediately remove the old version of the row. This approach is necessary to gain the benefits of multiversion concurrency control (MVCC, see Chapter 13): the row version must not be deleted while it is still potentially visible to other transactions. But eventually, an outdated or deleted row version is no longer of interest to any transaction. The space it occupies must then be reclaimed for reuse by new rows, to avoid unbounded growth of disk space requirements. This is done by running VACUUM.

The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained. In contrast, VACUUM FULL actively compacts tables by writing a complete new version of the table file with no dead space. This minimizes the size of the table, but can take a long time. It also requires extra disk space for the new copy of the table, until the operation completes.

还有:

If you have a table whose entire contents are deleted on a periodic basis, consider doing it with TRUNCATE rather than using DELETE followed by VACUUM. TRUNCATE removes the entire content of the table immediately, without requiring a subsequent VACUUM or VACUUM FULL to reclaim the now-unused disk space. The disadvantage is that strict MVCC semantics are violated.

强调我的