Re: Alternative for vacuuming queue-like tables

Tom Lane wrote:
> "Jim C. Nasby" <jnasby@pervasive.com> writes:
>
>>I'd actually been thinking about this recently, and had come up with the
>>following half-baked ideas:
>
>>Allow a transaction to specify exactly what tables it will be touching,
>>perhaps as an extension to BEGIN. Should any action that transaction
>>takes attempt to access a table not specified, throw an error.
>
>>A possible variant on that would be to automatically determine at
>>transaction start all the tables that would be accessed by that
>>transaction.
>
>>Once that list is available, vacuum should be able to use it to ignore
>>any transactions that have promised not to touch whatever table it's
>>vacuuming.
>
> No, you missed my point entirely.  The above would help not at all,
> unless the restrictions were somehow propagated through XMIN
> calculations, which seems impracticable.  (Every backend calculate a
> separate XMIN with respect to every table that's being mentioned by any
> other backend?  I don't think so...)

So basically the problem is that calculating a cluster-wide XMIN is fine from
a performance point of view, but to coarse from a vacuum point of view. OTOH
calculating a table-specific XMIN is fine enough for vacuum to vacuum queue-like
tables efficiently, but bad from a performance point of view.

What about doing the xmin-calculations and the xmin exporting per tablespace instead?
This would be more generic than the local/global split, because all global objects
are in the pg_global tablespace I believe. OTOH most people probably don't have more
than a handfull of tablespaces, which seems a manageable amount...

greetings, Florian Pflug