Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qNWJa-005DDU-Cz for pgsql-general@arkaria.postgresql.org; Sun, 23 Jul 2023 10:27:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qNWJY-0031NT-3y for pgsql-general@arkaria.postgresql.org; Sun, 23 Jul 2023 10:27:48 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qNWJX-0031NA-OL for pgsql-general@lists.postgresql.org; Sun, 23 Jul 2023 10:27:47 +0000 Received: from mail.hjp.at ([212.17.106.138] helo=rorschach.hjp.at) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qNWJN-0010ql-8R for pgsql-general@lists.postgresql.org; Sun, 23 Jul 2023 10:27:46 +0000 Received: by rorschach.hjp.at (Postfix, from userid 1000) id 70DC46762E; Sun, 23 Jul 2023 12:27:33 +0200 (CEST) Date: Sun, 23 Jul 2023 12:27:33 +0200 From: "Peter J. Holzer" To: pgsql-general@lists.postgresql.org Subject: Re: Effects of dropping a large table Message-ID: <20230723102733.shfvgs6dsev2l6xa@hjp.at> Mail-Followup-To: pgsql-general@lists.postgresql.org References: <430335C4-3803-4F88-9042-96BA65166043@gmail.com> <20230723095138.vrlw4jlbafy6iktn@hjp.at> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="s7oc4ux64t6lcasi" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --s7oc4ux64t6lcasi Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On 2023-07-23 06:09:03 -0400, Gus Spier wrote: > Ah! Truncating a table does not entail all of WAL processes. From the > documentation, "TRUNCATE=A0quickly removes all rows from a set of tables.= It has > the same effect as an unqualified=A0DELETE=A0on each table, but since it = does not > actually scan the tables it is faster. Furthermore, it reclaims disk space > immediately, rather than requiring a subsequent=A0VACUUM=A0operation. Thi= s is most > useful on large tables."=A0https://www.postgresql.org/docs/14/sql-truncat= e.html I assumed that by "deleting the now empty table" you meant DROPing it. (Performing a =ABDELETE FROM t=BB just after a =ABTRUNCATE t=BB would obvio= usly be pointless). So let me rephrase the question: What's the advantage of=20 TRUNCATE t DROP t over just DROP t hp --=20 _ | Peter J. Holzer | Story must make more sense than reality. |_|_) | | | | | hjp@hjp.at | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" --s7oc4ux64t6lcasi Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmS9AJAACgkQ8g5IURL+ KF2LwxAAgf3xBKNGzCK0nl0H/XVHpQudMjL+54RNs945gmlexRkoUVoUiy4X/y1s wSQrzBYZacntDqg4q4gJhHIaqyrzObrXSnuz7HG1NfbsKVXkcAJHrR+eVo1m6xZ8 tqWCyZEmRxwSwt5cA88f3q/ATEZehyV2Uj1NhZdVTEvcTK2kCA7jfpis9fXrBbP8 mVcNPTd9kfmrMAiHcqex/2Ydfmm4kSwXosx68IieRXguUi3Lcrbw8Mhj9W/e9EQ5 GkwbMeH+WjvBAXuzIi+nWjLlw7KAdk39+35ukhhuz+crUEHGGtLA1cGqjl+EsJFP lxSRBAuXoDife89qRJYGwV1iD30Tye5ZvsFJ9yEpLJK1Z+FSsuhsAsMxdLFrxgHa lRmcCNXtq/zykeX3K+TIQfkhvmp/2V/1x8kZ9yocB09nOjzC5JzOyYvDKqh0H8Dl rPjiA3RZiE9UjgZeLhbGdbmEaaHMptP9LuFkVoUsKoMUhvb+n+LSVARj63i/LBW7 0EVkX/vJ/uPL1bfVwVPwcL6fkMbsBE3V9kyFMzX9nGu7EzOI23gZP0W6EW+R0JE9 O7Cq5TYHdgmLfAn0FPUyzNCI4Q8o+Ij9Lw9BaElrQgyvTwPL5cRpRsT2kAOIivux 0ti+C9H0t8IHsnP0EmGfdppkIdsVj7MEgBexmva1IyT0Luy+gu0= =aIRJ -----END PGP SIGNATURE----- --s7oc4ux64t6lcasi--