Обсуждение: [ADMIN] Toast Table Bloat and Autovacuum Question

Hi all,

I'm having an issue with TOAST table bloat and I'm hoping to get some suggestions.

The system uses a very minimal schema, a text ID and a bytea value, that can be created or deleted but are never updated.

The bytea values average 50kb in size. 

I'm currently running some scaling tests and running into an issue where it appears that the deleted rows aren't being reclaimed from the TOAST table.

Specifically, I'm running PostgreSQL via RDS in AWS and the table is using about 10x the amount of disk space as expected. Autovacuum is ON, and does appear

to be running, but the table continues to grow.

If I stop the system and run a VACUUM FULL, the total size of the table returns to the expected size. 

Of course, this requires a substantial amount of disk space and downtime for the system.

Outputs from two recent runs of the VACUUM FULL:

mydb=> vacuum full verbose analyze;

INFO: vacuuming "public.mydb"

INFO: "mydb": found 783491 removable, 20768058 nonremovable row versions in 260529 pages

DETAIL: 1 dead row versions cannot be removed yet.

CPU 1475.42s/9318.44u sec elapsed 39085.85 sec.

INFO: analyzing "public.mydb"

INFO: "mydb": scanned 30000 of 236433 pages, containing 2635469 live rows and 1 dead rows; 30000 rows in sample, 20768354 estimated total rows

---

mydb=> vacuum full verbose;

INFO: vacuuming "public.mydb"

INFO: "mydb": found 662690 removable, 25261862 nonremovable row versions in 315152 pages

DETAIL: 0 dead row versions cannot be removed yet.

CPU 1698.80s/11683.10u sec elapsed 46855.08 sec.

--

I just ran a VACUUM FULL yesterday, restarted my system, and the bloat query from this source: 

indicates that the table already has a bloat ratio of 15:

 current_database |     schemaname     |         tblname         | real_size  | extra_size |   extra_ratio    | fillfactor | bloat_size |   bloat_ratio    | is_na

 -----------------+--------------------+-------------------------+------------+------------+------------------+------------+------------+------------------+------ 

 mydb             | public             | mydb                    | 2719326208 |  427401216 | 15.7171734212183 |        100 |  427401216 | 15.7171734212183 | f

I'm hoping you can suggest some tuning or options for the autovacuum system that might enable it to reclaim the dead rows without having to resort to VACUUM FULL.

Thanks!