postgresql - Postgres : toast table + space + vacuum

Question

环境:postgres: 9.5

表格:

segmentation=> \d+ sourceTable;
                                       Table 
"sourceTable"           Column            |           Type           |       Modifiers        | Storage  | Stats target | Description 
-----------------------------+--------------------------+------------------------+----------+--------------+-------------
tracking_id                 | character varying(40)    | not null               | extended |              | 
attributes                  | jsonb                    | not null               | extended |              | 
last_modification_timestamp | timestamp with time zone | not null default now() | plain    |              | 
version                     | bigint                   | not null default 1     | plain    |              | 
Indexes:
"client_attributes_pkey" PRIMARY KEY, btree (tracking_id)

属性字段是jsonb。它可以是一个巨大的 json。所以 Postgres 创建了 TOAST 表来存储该列。

TOAST 表的统计信息

segmentation=> select * from "pg_catalog"."pg_stat_all_tables" where relname='pg_toast_237733296';
relid   | schemaname |      relname       | seq_scan | seq_tup_read | idx_scan  | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | last_vacuum |        last_autovacuum        | last_analyze | last_auto
analyze | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count 
 -----------+------------+--------------------+----------+--------------+-----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+--------------------------------------------+--------------+------------------+--------------+------------------+---------------+-------------------
 237733301 | pg_toast   | pg_toast_237733296 |        1 |            0 | 710119316 |    1138457190 | 236069110 |         0 | 235760336 |             0 |    9231431 |    8769021 |           471829446 |             | 2018-01-29 06:13:29.169999+00 |              |          
    |            0 |              568 |             0 |                 0

问题:toast table 的大小无限增长。虽然数据量不是很大。

例如:全真空前

                relation                 |  size   
-----------------------------------------+---------
pg_toast.pg_toast_237738400             | 75 GB

完全真空后

            relation                 |  size   
-----------------------------------------+---------
pg_toast.pg_toast_237738400             | 416 MB

为什么 autovacuum 没有帮助(使用默认配置运行)？可以做些什么来解决这个问题？

segmentation=> select name, setting from pg_settings where name like     'autovacuum%';
                name                 |  setting  
-------------------------------------+-----------
 autovacuum                          | on
 autovacuum_analyze_scale_factor     | 0.1

真空冗长的结果

segmentation=> VACUUM (VERBOSE) ss_admin.client_attributes;
INFO:  vacuuming "ss_admin.client_attributes"
INFO:  scanned index "client_attributes_pkey" to remove 89097 row         versions
DETAIL:  CPU 0.11s/0.75u sec elapsed 1.32 sec.
INFO:  "client_attributes": removed 89097 row versions in 85197 pages
DETAIL:  CPU 0.87s/0.44u sec elapsed 12.00 sec.
INFO:  index "client_attributes_pkey" now contains 2462438 row versions in 17738 pages
DETAIL:  88338 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "client_attributes": found 132824 removable, 2463417 nonremovable row versions in 404663 out of 404860 pages
DETAIL:  5295 dead row versions cannot be removed yet.
There were 46898 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 3.13s/2.36u sec elapsed 32.41 sec.
INFO:  vacuuming "pg_toast.pg_toast_237738400"
INFO:  scanned index "pg_toast_237738400_index" to remove 370799 row versions
DETAIL:  CPU 0.01s/0.10u sec elapsed 0.17 sec.
INFO:  "pg_toast_237738400": removed 370799 row versions in 93742 pages
DETAIL:  CPU 0.65s/0.28u sec elapsed 5.84 sec.
INFO:  index "pg_toast_237738400_index" now contains 301508 row versions in 2332 pages
DETAIL:  353494 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_237738400": found 315275 removable, 301508 nonremovable row versions in 128628 out of 128628 pages
DETAIL:  1779 dead row versions cannot be removed yet.
There were 70590 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 1.49s/0.76u sec elapsed 9.80 sec.

Answer 1

正常的 VACUUM 和 autovacuum 不会收缩表，它只会使可用空间可供重用。

如果你想回收空间，你必须使用VACUUM (FULL)。除非您运行批量删除或更新，否则没有必要这样做。