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 1qNfKj-005bi0-SN for pgsql-general@arkaria.postgresql.org; Sun, 23 Jul 2023 20:05:37 +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 1qNfKi-005I3x-EF for pgsql-general@arkaria.postgresql.org; Sun, 23 Jul 2023 20:05:36 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qNfKi-005I3o-43 for pgsql-general@lists.postgresql.org; Sun, 23 Jul 2023 20:05:36 +0000 Received: from mail-oo1-xc2f.google.com ([2607:f8b0:4864:20::c2f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qNfKe-001MyH-DK for pgsql-general@lists.postgresql.org; Sun, 23 Jul 2023 20:05:35 +0000 Received: by mail-oo1-xc2f.google.com with SMTP id 006d021491bc7-56368c40e8eso2277303eaf.0 for ; Sun, 23 Jul 2023 13:05:33 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1690142731; x=1690747531; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=7u8ozJahEpGuuFPaGUOjWjtgaBaBQJQRdXUJGsDFO2E=; b=cF6ABfwTl1IQJbxcMWv065Nd8TqgOqQ8eSSx/7GcbsD5kFDmoSribycfkJHz3W/dos uz/5Rx3ruerDRzkit4ZglhRCxtHFu1ee4QGaca6ewGZbxXojspQ8x4d169RSU2sfK6Yp eRvRxlWQ7uouauvBCxdyqqxd3kyaABqhXza09lA8B8aSzEt1jbvALFF9q1N7kL2OT2je mxoPp2yyLpuLDLw9zFSuQ54SxkQ9gVI9oT679ZCmID/s0CwT9LGIDd3CnfpBUji7u+pi SQoH8bO652EbMY0EU25Sj6KnIJfnwdCiPplC43elYnTx/ThUjgyrjIznJ3NA/Odw9Af3 WQJA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1690142731; x=1690747531; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=7u8ozJahEpGuuFPaGUOjWjtgaBaBQJQRdXUJGsDFO2E=; b=kpOM8L/r+iluR40nEPXx0Ikimp3Wd9kW60qVbLv72a+JGFCp5n6HQeS+8Xm9OLD4q4 v3Orf3AD5cLBwjmCa8a/6Eq2gh8cuP5DlQQWGnpDzDtS08yQEfJnNXTcpDAsuXnyi51l ee48eNizHfuTfg0cJAjmq/JeQyMpEOd2KgfMKY0BVTK0CAuPStmOn/fBfRjBPwvOitip j4oUSC7gBuEUDJ7M1IXZL0JHj8/hi/nRYXCKTbbBWXBzKAmaX8YlnfiqV4sGP3z3697Y CCO93QK9eMceIDaSKd6XOdx3q9knpcfJ3jHCUFlsBnpC8p6kDwyZTrTnlrvzC79rq2If ATdA== X-Gm-Message-State: ABy/qLb85fkrrIbH1vYQFCr96liV6u7LxCR5k4ESKVfcaymgVra4WPzy Nkv5VL/Ei1yEEsB/hFAGe/X1SZjI2zI= X-Google-Smtp-Source: APBJJlEj6NuDqIFCwr0TrpxegLZGGDWvkae7Oo53djjy25T6e9p4tsHfj7ekMaHD5Y4REVGDRNdb3g== X-Received: by 2002:a4a:2401:0:b0:566:f94f:cd28 with SMTP id m1-20020a4a2401000000b00566f94fcd28mr6946517oof.3.1690142731462; Sun, 23 Jul 2023 13:05:31 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:bd34:4545:8b91:65dd? ([2600:1700:bd2c:b810:bd34:4545:8b91:65dd]) by smtp.googlemail.com with ESMTPSA id d136-20020a4a528e000000b005660ed0becesm1340124oob.39.2023.07.23.13.05.30 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sun, 23 Jul 2023 13:05:31 -0700 (PDT) Message-ID: Date: Sun, 23 Jul 2023 15:05:30 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: Effects of dropping a large table Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <430335C4-3803-4F88-9042-96BA65166043@gmail.com> <20230723095138.vrlw4jlbafy6iktn@hjp.at> <20230723102733.shfvgs6dsev2l6xa@hjp.at> From: Ron In-Reply-To: <20230723102733.shfvgs6dsev2l6xa@hjp.at> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 7/23/23 05:27, Peter J. Holzer wrote: > 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 quickly removes all rows from a set of tables. It has >> the same effect as an unqualified DELETE on 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 VACUUM operation. This is most >> useful on large tables." https://www.postgresql.org/docs/14/sql-truncate.html > I assumed that by "deleting the now empty table" you meant DROPing it. > (Performing a «DELETE FROM t» just after a «TRUNCATE t» would obviously > be pointless). > > So let me rephrase the question: > > What's the advantage of > > TRUNCATE t > DROP t > > over just > > DROP t Catalog or serialization locking?  (I don't know; just asking.) -- Born in Arizona, moved to Babylonia.