Postgres: toast table + space + vacuum

Question

environment: postgres: 9.5

table:

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)

Attributes field is jsonb. And it can be a huge json. So Postgres created TOAST table to store that column.

Stats for TOAST table

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

the problem: size of toast table is growing indefinitely. While the amount of data is not so huge.

For example: before full vacuum

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

after full vacuum

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

Why autovacuum doesn't help (running with default conf)? What can be done to resolve that problem?

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

result of vacuum verbous

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

Normal VACUUM and autovacuum won't shrink the tables, it will only make the free space available for reuse.

You have to use VACUUM (FULL) if you want to reclaim space. That should not be necessary unless you run bulk deletes or updates.