Re: Per-tablespace autovacuum settings

Hello,

Oleksii Kliukin <alexk(at)hintbits(dot)com> wrote:

> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>
>> I don't know how to make this better, but I wish we'd take a step
>> back and think about it rather than just accreting more and more
>> complexity.
>
> I am willing to do the refactoring when necessary, any particular place in
> the code that is indicative of the issue?

I’ve managed to return to that and here’s the first iteration of the patch
to add autovacuum parameters to tablespaces. I tried to make it as simple as
possible and didn’t make any decisions I found questionable, opting to
discuss them here instead. Some of them are probably linked to the kind of
issues mentioned by Tom upthread.

Things worth mentioning are:

- Fallbacks to autovacuum parameters in another scope. Right now in the
absence of the per-table and per-tablespace autovacuum parameters the code
uses the ones from the global scope. However, if only some of the reloptions
are set on a per-table level (i.e. none of the autovacuum related ones), we
assume defaults for the rest of reloptions without consulting the lower
level (i.e .per-tablespace options). This is so because we don’t have the
mechanism to tell whether the option is set to its default value (some of
them use -1 to request the fallback to the outer level, but for some it’s
not possible, i.e. autovacuum_enabled is just a boolean value).

- There are no separate per-tablespace settings for TOAST tables. I couldn't
find a strong case for setting all TOAST autovacuum options in a tablespace
to the same value that is distinct from the corresponding settings for the
regular tables. The difficulty of implementing TOAST options lies in the
fact that we strip the namespace part from the option name before storing it
in reltoptions. Changing that would break compatibility with previous
versions and require another step for pg_upgrade, I don’t think it is worth
the troubles. We could also come with a separate set of tablespace options,
i.e. prefixed with “toast_”, but that seems an ugly solution for the problem
that doesn’t seem real. As a result, if per-tablespace autovacuum options
are set and there are no table-specific TOAST options, the TOAST table will
inherit autovacuum options from the tablespace, rather than taking them from
the regular table it is attached to.

- There are a few relatively recently introduced options
(vacuum_index_cleanup, vacuum_truncate and
vacuum_cleanup_index_scale_factor) that I haven’t incorporated into the
per-tablespace options, as they are not part of autovacuum options and I see
no use for setting them on a tablespace level. This can be changed easily if
people think otherwise.

The patch is attached. It has a few tests and no documentation, I will
improvise both one we get in agreement on how the end result should look.

Kind regards,
Oleksii Kliukin