Subject: Re: [Patch] Temporary tables that do not bloat pg_catalog
 (a.k.a fast temp tables)
To: Claudio Freire <klaussfreire@gmail.com>
References: <20160801124531.66cde374@fujitsu>
 <26c3bc60-6613-e7ab-517a-53136f01fb6c@2ndquadrant.com>
 <13191.1470072250@sss.pgh.pa.us> <20160804151451.6fb87507@fujitsu>
 <CA+Tgmob+AS4vPA-wp+KevL7q+qK4c7yZ5wjGvEv6EqPChE2oYQ@mail.gmail.com>
 <CA+q6zcVLO7VoHHpRn9ExkiWksF6TwJ-rabqgwRJgC2_ci8_UEQ@mail.gmail.com>
 <CA+TgmoYstEXxa0KCAPVAVseTwBpb51t=bK1FBU8L8h4sC3XWBQ@mail.gmail.com>
 <14212.1470595566@sss.pgh.pa.us>
 <20160815005755.gswdyjfob7dzw4yp@alap3.anarazel.de>
 <20319.1471223097@sss.pgh.pa.us>
 <20160815010852.5ijua2wm2edzimd4@alap3.anarazel.de>
 <20160815121216.46bd758d@e733>
 <CA+TgmoaTbFPTWEzccd1p6k_v7WwmYGwMOZ3qJYhfQo0-JmyhNQ@mail.gmail.com>
 <95bdc353-1a89-3fe3-1181-eba558c4df83@BlueTreble.com>
 <CA+TgmobRoPj2c9W9g23yeiCZu9ggFGkG8reDhFz8RCON_gMTYg@mail.gmail.com>
 <d6adce51-96f4-c508-d0dc-5311cfb223b6@2ndquadrant.com>
 <CAGTBQpaD_wtUrp+PsL9hDshiVrOCrObeCPFAu+uFkrLKyh-Xtw@mail.gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>, Jim Nasby
 <Jim.Nasby@bluetreble.com>, Aleksander Alekseev <a.alekseev@postgrespro.ru>,
 PostgreSQL Hackers <pgsql-hackers@postgresql.org>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <40e4f58f-6cd9-01b0-2cc1-67dcb71a5d61@2ndquadrant.com>
Date: Wed, 24 Aug 2016 00:25:52 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:46.0) Gecko/20100101
 Thunderbird/46.0a2
MIME-Version: 1.0
In-Reply-To: 
 <CAGTBQpaD_wtUrp+PsL9hDshiVrOCrObeCPFAu+uFkrLKyh-Xtw@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org


On 08/24/2016 12:18 AM, Claudio Freire wrote:
> On Tue, Aug 23, 2016 at 7:11 PM, Tomas Vondra
> <tomas.vondra@2ndquadrant.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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers