Thread: handling TOAST tables in autovacuum
Hi, We've been making noises about dealing with TOAST tables as separate entities in autovacuum for some time now. So here's a proposal: Let's do it. That's about it :-) The only change of some consideration is that we will need two passes over pg_class to get the list of relations to vacuum, instead of one as we do currently. The problem is that we first need to fetch the (heap relid, toast relid) mapping before attempting to figure out if any given TOAST table needs vacuuming. This is because we want to be using the main table's pg_autovacuum, and we can't get at that unless we know the main relid. Another open question is whether the TOAST table should be processed at all if the main table is vacuumed. My opinion is we don't -- if we're going to deal with them separately, let's go the whole nine yards. Autovacuum will only process a toast table when, by itself, it shows that it needs processing. (Obviously this doesn't mean we change semantics of user-invoked VACUUM -- those will continue to vacuum the TOAST table along the main table). Should we display TOAST tables separately in pg_stat_*_tables? (Maybe pg_stat_toast_tables?) Thoughts? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes:
> The only change of some consideration is that we will need two passes
> over pg_class to get the list of relations to vacuum, instead of one as
> we do currently. The problem is that we first need to fetch the
> (heap relid, toast relid) mapping before attempting to figure out if any
> given TOAST table needs vacuuming. This is because we want to be using
> the main table's pg_autovacuum, and we can't get at that unless we know
> the main relid.
Umm ... is it chiseled in stone someplace that toast tables shouldn't
have their own pg_autovacuum entries? Seems like that might be a
reasonable component of a "whole nine yards" approach.
> Should we display TOAST tables separately in pg_stat_*_tables? (Maybe
> pg_stat_toast_tables?)
+1 for pg_stat_toast_tables, I think. If you separate them out then
there will need to be some kind of smarts to help the user figure out
which main table a toast table belongs to. This would be easy with a
separate view.
regards, tom lane
Tom Lane wrote: > Alvaro Herrera <alvherre@commandprompt.com> writes: > > The only change of some consideration is that we will need two passes > > over pg_class to get the list of relations to vacuum, instead of one as > > we do currently. The problem is that we first need to fetch the > > (heap relid, toast relid) mapping before attempting to figure out if any > > given TOAST table needs vacuuming. This is because we want to be using > > the main table's pg_autovacuum, and we can't get at that unless we know > > the main relid. > > Umm ... is it chiseled in stone someplace that toast tables shouldn't > have their own pg_autovacuum entries? Seems like that might be a > reasonable component of a "whole nine yards" approach. No, but I think it's a bit awkward for users to follow _only_ its own entry. I forgot to mention that in the patch I currently have, what autovacuum does is try to get the TOAST table's own pg_autovacuum entry, and if that fails, get the main rel's entry. The point here is that if the user disables autovac for the main table, then it's expected that it is automagically disabled for the toast table as well, for the usual case where they are disabling it because the table is too big. Automatically processing the toast table would be completely unexpected, and most likely unwelcome. Of course, for the even rarer cases when you want to disable it for the main rel and enable it for the toast table, you can do that too. (I can't think of a case where this would be useful though.) -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes:
> The point here is that if the user disables autovac for the main table,
> then it's expected that it is automagically disabled for the toast table
> as well, for the usual case where they are disabling it because the
> table is too big.
Hmm, good point. OK, two passes it is. (I thought about remembering
the toast table rows in memory so as not to scan the catalog twice,
but I'm not sure you really save much that way.)
Another thing to think about here is locking: I believe you need to get
a vacuum-type lock on the parent table not only the toast table, so
vacuuming a toast table without any knowledge of which table is its
parent ain't gonna fly anyway.
regards, tom lane
Alvaro Herrera wrote: > We've been making noises about dealing with TOAST tables as separate > entities in autovacuum for some time now. So here's a proposal: > Let's keep it simple. Why not just adding a toast_enabled flag (disabled by default) in pg_autovacuum? If it's set then main and toast tables are processed by autovac. FYI, I have a WIP patch to remove pg_autovacuum in favor of reloptions. And based on your proposal, it'll be needed to add reloptions to toast tables too. IMO, we should keep that code as simple as possible. -- Euler Taveira de Oliveira http://www.timbira.com/
Euler Taveira de Oliveira wrote: > FYI, I have a WIP patch to remove pg_autovacuum in favor of reloptions. Really? Please send it my way to review/apply as soon as you have it ready, independently of what we do with toast tables. > Let's keep it simple. Why not just adding a toast_enabled flag (disabled > by default) in pg_autovacuum? If it's set then main and toast tables are > processed by autovac. Actually I think your proposal is more cumbersome to use and less flexible, because you can't set specific values for the other options for toast tables. > And based on your proposal, it'll be needed to add reloptions to toast > tables too. IMO, we should keep that code as simple as possible. Sure, what's the problem with that? We only need to make sure that ALTER TABLE works for setting reloptions for toast tables. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Euler Taveira de Oliveira wrote:
>> And based on your proposal, it'll be needed to add reloptions to toast
>> tables too. IMO, we should keep that code as simple as possible.
> Sure, what's the problem with that? We only need to make sure that
> ALTER TABLE works for setting reloptions for toast tables.
... actually, the problem is going to be "how do you get pg_dump to dump
and reload such settings"? The toast tables are not going to have the
same names after dump/reload.
regards, tom lane
Alvaro Herrera napsal(a): > Hi, > > We've been making noises about dealing with TOAST tables as separate > entities in autovacuum for some time now. So here's a proposal: Maybe dumb idea - whats about make a queue of toast pointers ready for vacuum and remove this toast items directly from toast table and index? Zdenek