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 1raepc-00AptK-TY for pgsql-general@arkaria.postgresql.org; Thu, 15 Feb 2024 16:43:29 +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 1raepb-00EMen-Cw for pgsql-general@arkaria.postgresql.org; Thu, 15 Feb 2024 16:43:27 +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 1raepa-00EMef-He for pgsql-general@lists.postgresql.org; Thu, 15 Feb 2024 16:43:27 +0000 Received: from wfhigh1-smtp.messagingengine.com ([64.147.123.152]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1raepX-007MMM-5r for pgsql-general@lists.postgresql.org; Thu, 15 Feb 2024 16:43:25 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailfhigh.west.internal (Postfix) with ESMTP id AFD7618000A1; Thu, 15 Feb 2024 11:43:19 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute5.internal (MEProxy); Thu, 15 Feb 2024 11:43:20 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:cc:content-transfer-encoding:content-type:content-type:date :date:from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to; s=fm3; t=1708015399; x=1708101799; bh=FOT0UL5Ps86t/CPVgHeqTOZeQFxbB7rlS0cNMNKQAB0=; b= cMEdVsP2sCENC3jRquBl6ddIr+2F7cpCmAwOYJfB6Lh+QjQxxA0Ly4FQeY9WvASJ FXfqC9xR1OWZhVfw7p9SDNI8hE7QTJjmLhUP5LWYIDEQAzvZpQdMGn11bzdTN/am 5Ga+mZQ26j+2+NhNx75Le3sXqAIKiRX3QsefGU3k+Clb2AfofQaIr09+ceT3bqqH CMyO4clxRt3uYmcSDG19hmm3+uwxkP7A1tfCNDRVBZ8nixzn8lAAmxs0BsZ1H54t 7znDpAbRah9XeX3XwOB69T1A4lzXQSMumJTnwUB9pECrHBeIgVdn5+isuMXRjmF3 qmcBg0t2gLK8untwcSSZmQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:content-type:date:date:feedback-id:feedback-id :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t=1708015399; x= 1708101799; bh=FOT0UL5Ps86t/CPVgHeqTOZeQFxbB7rlS0cNMNKQAB0=; b=N Sz6jw3eLElce7CxNBg5VeZeACR0iB6fdDoJn34FrBJ7Z4uEcSxCfJd6L/TD8IPFE p0t49CgKaeRFWhtxeLea2TFxTccCFD+eMSunP5E6qImS5d5RQlI90W4DT6cUnHKb 5LTGZ2qIrVh50/tjMC07U2UoZfDSP6yO0Pi4qQ1mKop3c2CLn7k9jy2Cxij+QPoo H3F0R13rvDVHXJ9RZoMtYYKkjexMq5mmkGCncteCxUY8C3uwYpuEIxSHokzDC+xD WxmPvddnRGlnQ1vRKAqkmA0EW/kdgPxAVtldVrlKM9XPqKjuCE3PhjPiBKUem/hG jb9eSvZ6Ui/nWwndUXxpw== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvledrvddtgdelvdcutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpefkffggfgfuvfevfhfhjggtgfesthekredttddvjeenucfhrhhomheptegurhhi rghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtoh hmqeenucggtffrrghtthgvrhhnpeefgeefieeutdfggfetgefgheekjeehteeileeigfet ieekjedvieeviefgheevtdenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmh grihhlfhhrohhmpegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 15 Feb 2024 11:43:18 -0500 (EST) Message-ID: <9da45458-e5dc-44e2-80f3-6a0462443a1c@aklaver.com> Date: Thu, 15 Feb 2024 08:43:17 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: How to do faster DML To: Greg Sabino Mullane , veem v Cc: Ron Johnson , "Peter J. Holzer" , Laurenz Albe , "pgsql-generallists.postgresql.org" References: <20240211133203.zw6ezhdzefyxmkd7@hjp.at> <20240211222230.tt3mpjdvgrimeodu@hjp.at> <20240213150234.ydiitfoowojhj2n3@hjp.at> Content-Language: en-US From: Adrian Klaver In-Reply-To: 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 2/15/24 08:16, Greg Sabino Mullane wrote: > So as I also tested the same as you posted, there has been no change > in "ctid" , when I altered the column data type from 'int' to > 'bigint' in the table, so that means full table rewriting > won't happen in such a scenario. > > > No it was definitely rewritten - do not depend on the ctid to verify > that. Take our word for it, or > use*pg_relation_filenode('int_test');* before and after, as well as > *pg_relation_size('int_test')*; That is a mixed bag: test=# select pg_relation_filenode('int_test'); pg_relation_filenode ---------------------- 69999 (1 row) test=# select pg_relation_size('int_test'); pg_relation_size ------------------ 368640 (1 row) test=# alter table int_test alter column int_fld set data type bigint; ALTER TABLE test=# select pg_relation_filenode('int_test'); pg_relation_filenode ---------------------- 70002 (1 row) test=# select pg_relation_size('int_test'); pg_relation_size ------------------ 368640 > > Cheers, > Greg > -- Adrian Klaver adrian.klaver@aklaver.com