Postgres: тост стол + место + вакуум

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

проблема: размер тоста растет бесконечно. Пока объем данных не такой огромный.

Например: до полного вакуума

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

после полного вакуума

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

Почему автоочистка не помогает (работает с настройками по умолчанию)? Что можно сделать, чтобы решить эту проблему?

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) если вы хотите освободить место. Это не должно быть необходимо, если вы не выполняете массовое удаление или обновление.