Re: [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On 08/24/2016 12:18 AM, Claudio Freire wrote:
> On Tue, Aug 23, 2016 at 7:11 PM, Tomas Vondra
> <tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
>> On 08/22/2016 10:32 PM, Robert Haas wrote:
>>>
>>>
>>> ...
>>>
>>> 1. The number of tables for which we would need to add a duplicate,
>>> unlogged table is formidable. You need pg_attribute, pg_attrdef,
>>> pg_constraint, pg_description, pg_type, pg_trigger, pg_rewrite, etc.
>>> And the backend changes needed so that we used the unlogged copy for
>>> temp tables and the permanent copy for regular tables is probably
>>> really large.
>>>
>>> 2. You can't write to unlogged tables on standby servers, so this
>>> doesn't help solve the problem of wanting to use temporary tables on
>>> standbys.
>>>
>>> 3. While it makes creating temporary tables a lighter-weight
>>> operation, because you no longer need to write WAL for the catalog
>>> entries, there's probably still substantially more overhead than just
>>> stuffing them in backend-local RAM. So the performance benefits are
>>> probably fairly modest.
>>>
>>> Overall I feel like the development effort that it would take to make
>>> this work would almost certainly be better-expended elsewhere. But of
>>> course I'm not in charge of how people who work for other companies
>>> spend their time...
>>>
>>
>> Could someone please explain how the unlogged tables are supposed to fix the
>> catalog bloat problem, as stated in the initial patch submission? We'd still
>> need to insert/delete the catalog rows when creating/dropping the temporary
>> tables, causing the bloat. Or is there something I'm missing?
>
> Wouldn't more aggressive vacuuming of catalog tables fix the bloat?
>
> Perhaps reserving a worker or N to run only on catalog schemas?
>
> That'd be far simpler.

Maybe, although IIRC the issues with catalog bloat were due to a
combination of long queries and many temporary tables being
created/dropped. In that case simply ramping up autovacuum (or even
having a dedicated workers for catalogs) would not realy help due to the
xmin horizon being blocked by the long-running queries.

Maybe it's entirely crazy idea due to the wine I drank at the dinner,
but couldn't we vacuum the temporary table records differently? For
example, couldn't we just consider them removable as soon as the backend
that owns them disappears?

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services