Re: [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables) - Mailing list pgsql-hackers

<div dir="ltr"><div class="gmail_extra"><div class="gmail_quote">On Wed, Aug 24, 2016 at 12:39 PM, Andres Freund <span
dir="ltr"><<ahref="mailto:andres@anarazel.de" target="_blank">andres@anarazel.de</a>></span> wrote:<br
/><blockquoteclass="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span
class=""><br/><br /> On August 24, 2016 9:32:48 AM PDT, Tomas Vondra <<a
href="mailto:tomas.vondra@2ndquadrant.com">tomas.vondra@2ndquadrant.com</a>>wrote:<br /> ><br /> ><br />
>On08/24/2016 12:20 AM, Andres Freund wrote:<br /> >> On 2016-08-23 19:18:04 -0300, Claudio Freire wrote:<br
/>>>> On Tue, Aug 23, 2016 at 7:11 PM, Tomas Vondra<br /> >>> <<a
href="mailto:tomas.vondra@2ndquadrant.com">tomas.vondra@2ndquadrant.com</a>>wrote:<br /> >>>> Could
someoneplease explain how the unlogged tables are supposed<br /> >to fix the<br /> >>>> catalog bloat
problem,as stated in the initial patch submission?<br /> >We'd still<br /> >>>> need to insert/delete
thecatalog rows when creating/dropping the<br /> >temporary<br /> >>>> tables, causing the bloat. Or is
theresomething I'm missing?<br /> >><br /> >> Beats me.<br /> >><br /> ><br /> >Are you puzzled
justlike me, or are you puzzled why I'm puzzled?<br /><br /></span>Like you. I don't think this addresses the problem
toa significant enough degree to care.<br /><span class="HOEnZb"><font color="#888888"><br /> Andres<br /> --<br />
Sentfrom my Android device with K-9 Mail. Please excuse my brevity.<br /></font></span><div class="HOEnZb"><div
class="h5"><br/><br /> --<br /> Sent via pgsql-hackers mailing list (<a
href="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a>)<br/> To make changes to your
subscription:<br/><a href="http://www.postgresql.org/mailpref/pgsql-hackers" rel="noreferrer"
target="_blank">http://www.postgresql.org/<wbr/>mailpref/pgsql-hackers</a><br /></div></div></blockquote></div><br
/></div><divclass="gmail_extra">Ok, here's a wild idea, and it probably depends on having native partitioning
implemented.</div><divclass="gmail_extra"><br /></div><div class="gmail_extra">Propagate relpersistence, or a boolean
flagon (relpersistence = 't') from pg_class into the child pg_attribute records.</div><div class="gmail_extra"><br
/></div><divclass="gmail_extra">Partition the tables pg_class and pg_attribute first by relpersistence, and then by
oid.<br/><br />The partitions holding data on persistent objects would basically stay as-is, but the partition wouldn't
havemuch activity and no temp-table churn.<br /><br />The temporary ones, however, would fall into essentially a
rotatingset of partitions. Pick enough partitions such that the active transactions only cover some of the partitions.
Therest can be safely truncated by vacuum.<br /><br />It would mitigate the bloat, existing dictionary queries would
stillwork, but the additional lookup cost might not be worth it.</div></div>