pg_autovacuum: short, wide tables - Mailing list pgsql-bugs

Hi,

I've been using pg_autovacuum for a while, and for the most part it's
been great.  There's one case in my system where it won't run on a
particular type of table, even though the table apparently needs it.

I have a table called "properties" that has key->value pairs.  Usually
there are only a handful of rows, some of which are updated relatively
frequently compared to the number of rows (hundreds or thousands of
times per day).  The problem is that some of the rows have long strings
for their value (on the order of a few hundred kilobytes), so if I
understand correctly, the bulk of the data gets offloaded to a toast
table.

What I believe is happening is that the main table doesn't meet the
minimum activity level for pg_autovacuum based on size / update
frequency, but the toast table would, though it isn't specifically
checked by pg_autovacuum.  The result is that the toast table grows
really big before triggering autovacuum (or until I manually vacuum the
"properties" table).  Not the end of the world, obviously, but might be
a "gotcha" for some people with similar situations.

Below is a snippet of output from a run of vacuumdb --full --analyze
--verbose that should illustrate the problem.

-Mark.

Table Def:
        Table "schema_name.properties"
 Column |       Type        | Modifiers
--------+-------------------+-----------
 name   | character varying |
 value  | character varying |
Indexes:
    "properties_name_key" unique, btree (name)


Vacuum verbose output:

INFO:  vacuuming "schema_name.properties"
INFO:  "properties": found 1361 removable, 8 nonremovable row versions
in 172 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 63 to 1705 bytes long.
There were 4827 unused item pointers.
Total free space (including removable row versions) is 1376288 bytes.
164 pages are or will become empty, including 0 at the end of the table.
172 pages containing 1376288 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.06 sec.
INFO:  index "properties_name_key" now contains 8 row versions in 15 pages
DETAIL:  1361 index row versions were removed.
8 index pages have been deleted, 8 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  "properties": moved 8 row versions, truncated 172 to 1 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  index "properties_name_key" now contains 8 row versions in 15 pages
DETAIL:  8 index row versions were removed.
8 index pages have been deleted, 8 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "pg_toast.pg_toast_10043014"
INFO:  "pg_toast_10043014": found 21052 removable, 24 nonremovable row
versions in 21100 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 1117 to 2030 bytes long.
There were 63278 unused item pointers.
Total free space (including removable row versions) is 172044376 bytes.
21093 pages are or will become empty, including 0 at the end of the table.
21096 pages containing 172044264 free bytes are potential move destinations.
CPU 0.41s/0.06u sec elapsed 3.63 sec.
INFO:  index "pg_toast_10043014_index" now contains 24 row versions in
321 pages
DETAIL:  21052 index row versions were removed.
317 index pages have been deleted, 317 are currently reusable.
CPU 0.00s/0.01u sec elapsed 0.40 sec.
INFO:  "pg_toast_10043014": moved 24 row versions, truncated 21100 to 6
pages
DETAIL:  CPU 0.32s/1.04u sec elapsed 5.27 sec.
INFO:  index "pg_toast_10043014_index" now contains 24 row versions in
321 pages
DETAIL:  24 index row versions were removed.
317 index pages have been deleted, 317 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "schema_name.properties"
INFO:  "properties": 1 pages, 8 rows sampled, 8 estimated total rows