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 1kHA6I-0003CJ-4a for pgsql-bugs@arkaria.postgresql.org; Sat, 12 Sep 2020 18:18:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kHA6G-0002sx-PN for pgsql-bugs@arkaria.postgresql.org; Sat, 12 Sep 2020 18:18:12 +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 1kHA6G-0002sp-IP for pgsql-bugs@lists.postgresql.org; Sat, 12 Sep 2020 18:18:12 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kHA69-0001F5-Vr for pgsql-bugs@lists.postgresql.org; Sat, 12 Sep 2020 18:18:11 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 08CII40g653163; Sat, 12 Sep 2020 14:18:04 -0400 From: Tom Lane To: Giorgio Saviane cc: Francisco Olarte , Michael Paquier , PostgreSQL mailing lists Subject: Re: BUG #16614: Stale temporary objects makes vacuum ineffective when 1 million transactions remain In-reply-to: References: <16614-23e5c4c4567d1576@postgresql.org> <182231.1599749034@sss.pgh.pa.us> <20200911063036.GH2743@paquier.xyz> <371035.1599834081@sss.pgh.pa.us> <553829.1599925774@sss.pgh.pa.us> Comments: In-reply-to Giorgio Saviane message dated "Sat, 12 Sep 2020 18:06:10 +0200" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <653161.1599934684.1@sss.pgh.pa.us> Date: Sat, 12 Sep 2020 14:18:04 -0400 Message-ID: <653162.1599934684@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Giorgio Saviane writes: > Ok, let me add one more step in that sequence. > Before shutting down Postgres and going down to single mode we stopped the > application server and other services around it. I'm pretty sure that there > was a reasonable window of time before we went to single mode. Hmm, but did you stop the connection pooler? That's what was holding the database session open, if I'm understanding things correctly. Unless you've changed the default autovacuum settings (particularly autovacuum_naptime), I'd have expected autovacuum to start zapping temp tables within a minute or so after they become orphaned. Even more to the point, if the database session was allowed to stop normally, it should've cleaned them up itself. regards, tom lane