Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jPVN4-0000ky-5x for pgsql-bugs@arkaria.postgresql.org; Fri, 17 Apr 2020 18:05:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jPVN2-0001fs-Qo for pgsql-bugs@arkaria.postgresql.org; Fri, 17 Apr 2020 18:05:44 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jPVN2-0001fk-L1 for pgsql-bugs@lists.postgresql.org; Fri, 17 Apr 2020 18:05:44 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jPVMw-00035O-8M for pgsql-bugs@lists.postgresql.org; Fri, 17 Apr 2020 18:05:43 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id 03HI5a91018687; Fri, 17 Apr 2020 14:05:36 -0400 From: Tom Lane To: Hritik Gupta cc: pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16373: Behavior of Temporary table creation In-reply-to: References: <16373-a2325a5f0e87714c@postgresql.org> <19190.1587130449@sss.pgh.pa.us> Comments: In-reply-to Hritik Gupta message dated "Fri, 17 Apr 2020 23:14:27 +0530" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <18685.1587146736.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Fri, 17 Apr 2020 14:05:36 -0400 Message-ID: <18686.1587146736@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Hritik Gupta writes: > Q=3D> So does that mean postgres does not have *only* =E2=80=9Cin memory= =E2=80=9D temporary > tables in any case? > At best, what it is going to do is to cache the temp. table pages in the > temp buffer, but the blocks are still there on the storage, its just cac= hed > in memory for the time being, no matter how small the temporary table is= . > is it correct? No. We *reserve space* on disk, typically by writing zeroes. But actual data would only get spilled there if we run out of temp buffer space. It's entirely possible for a temp table's data to stay in memory for its whole existence. For regular tables, there are effects such as checkpoints that would cause data to get written out from shared buffers periodically, even if there's plenty of buffer space. That doesn't apply to temp tables. The reason for reserving space is to try to avoid having to deal with ENOSPC failures during buffer spills. On newer filesystems that can happen anyway, of course, but it's still useful on non-COW filesystems. regards, tom lane