Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bZFnR-0004LB-5A for pgsql-hackers@arkaria.postgresql.org; Mon, 15 Aug 2016 11:11:09 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bZFnP-0003J7-FM for pgsql-hackers@arkaria.postgresql.org; Mon, 15 Aug 2016 11:11:07 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bZFnO-0003J0-RZ for pgsql-hackers@postgresql.org; Mon, 15 Aug 2016 11:11:06 +0000 Received: from feynman.df7cb.de ([195.49.152.168]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bZFnH-0000WO-Ew for pgsql-hackers@postgresql.org; Mon, 15 Aug 2016 11:11:06 +0000 Received: from msg.df7cb.de (aftr-37-201-193-11.unity-media.net [37.201.193.11]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client did not present a certificate) by feynman.df7cb.de (Postfix) with ESMTPSA id 3sCXq55rbVz3F0R; Mon, 15 Aug 2016 13:10:57 +0200 (CEST) Date: Mon, 15 Aug 2016 13:10:57 +0200 From: Christoph Berg To: Tom Lane Cc: Pavel Stehule , Tomas Vondra , PostgreSQL Hackers , Aleksander Alekseev Subject: Re: [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables) Message-ID: <20160815111057.v2mqqjp4aabvwqnc@msg.df7cb.de> Mail-Followup-To: Christoph Berg , Tom Lane , Pavel Stehule , Tomas Vondra , PostgreSQL Hackers , Aleksander Alekseev References: <20160729141552.4062e19b@fujitsu> <39b0f423-a4df-3564-3b4a-b1de6f9eea80@2ndquadrant.com> <1184.1469890030@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1184.1469890030@sss.pgh.pa.us> User-Agent: Mutt/1.6.1-neo (2016-06-11) X-Pg-Spam-Score: -1.7 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org Re: Tom Lane 2016-07-30 <1184.1469890030@sss.pgh.pa.us> > In short, I think that the way to make something like this work is to > figure out how to have "virtual" catalog rows describing a temp table. > Or maybe to partition the catalogs so that vacuuming away temp-table > rows is easier/cheaper than today. We should also be thinking about how the opposite idea of "global" temp tables (I believe that's what Oracle calls them) would work. These have a persistent structure in the catalogs, just the data is private to every session (or transaction); every connection starts with an empty temp table and for their use. I'd guess that type of global temp tables would fix the bloat problem also very efficiently. (Ad-hoc temp tables shouldn't occur that often so the bloat caused by them wouldn't matter that much. If they do, their structure is likely always the same, and they could be made "global" in the schema.) The bit that needs to be thought out here would be how to maintain statistics for these tables. Obviously ANALYZE shouldn't update any globally visible data. Christoph -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers