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 1sRem3-00HUZi-NE for pgsql-general@arkaria.postgresql.org; Wed, 10 Jul 2024 21:22:52 +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 1sRem2-0097u2-EU for pgsql-general@arkaria.postgresql.org; Wed, 10 Jul 2024 21:22:50 +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 1sRem0-0097tt-TV for pgsql-general@lists.postgresql.org; Wed, 10 Jul 2024 21:22:50 +0000 Received: from fout4-smtp.messagingengine.com ([103.168.172.147]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sRelx-001S52-5o for pgsql-general@lists.postgresql.org; Wed, 10 Jul 2024 21:22:48 +0000 Received: from compute3.internal (compute3.nyi.internal [10.202.2.43]) by mailfout.nyi.internal (Postfix) with ESMTP id E47BC1380468; Wed, 10 Jul 2024 17:22:42 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute3.internal (MEProxy); Wed, 10 Jul 2024 17:22:42 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= 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=fm1; t=1720646562; x=1720732962; bh=sGGfYFGLYFfjuqRB/BOc3jKer04bCn8tKFlgdnL/W6M=; b= yTVC4rqPUG205nclCEVS5YuLG8zWn/piFdhur+6iPruLBgQRqoHu1fCyqqUf0iRY Q3TEaG9mrgmI/S8VWoeJVhUqIpHl2pb+Udm3tP4wRnwnscxXCtYWly1r+6u0uq6L wwwXWpyh2p+8QTFgiH/JqDFEnE7C4N/ZntggLdWiNyOe/6LDgJeUb/DyUQwKOosQ MN0C11XfKfUFJ/1fp+d3MeDcdC88s6gNJV7u17P0H5D098PaqZbuwzcTTsjE1vea bAp7mnh8Do4Cn/PNT0jg8xtatPi4U4pHFdn9MDJsjYdV8YuAIeOztIKhupFnqY0E l48YjI/h6h8z+m/1Sm1DcA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=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=fm2; t=1720646562; x= 1720732962; bh=sGGfYFGLYFfjuqRB/BOc3jKer04bCn8tKFlgdnL/W6M=; b=h LbJiDDb2ZXTGKVFLh/fxGou7F/ECLZ3BhjccuqyEOUn3opK4q7n1zvMgbZDEKupO Wuv/gxXXgBekZQ8KUUrnPiZvjsHph677IEB1g87KzXmZN6pgm6hfrOVTPvE2rrea lqK+CvgHRinPuODaX3p6doOgU37K6lh8tdlbv6ppxTRi83wUX0IaLORQA5N+4PC2 lho/iM3KLl79JUArlMHq4Bf4trJpC9Sl1BJAVDHli5YXvgFM2Z/piH/kToM46iNm TxoPI0uW09kkY1iFCXDyTD6LxX1DoNzZE1EkiwfLhq5QDG34Fkss22cgU0Fi6ooU ESvkNdWa94wlByZ4uxZ2g== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeeftddrfedvgddutddtucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucenucfjughrpefkffggfgfuvfhfhfgjtgfgsehtje ertddtvdejnecuhfhrohhmpeetughrihgrnhcumfhlrghvvghruceorggurhhirghnrdhk lhgrvhgvrhesrghklhgrvhgvrhdrtghomheqnecuggftrfgrthhtvghrnhepkeefheduvd ejiefgieefjedtudduffelvdefleehfedtieffuefgvdekleegtddvnecuffhomhgrihhn pehpohhsthhgrhgvshhqlhdrohhrghenucevlhhushhtvghrufhiiigvpedtnecurfgrrh grmhepmhgrihhlfhhrohhmpegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgt ohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Wed, 10 Jul 2024 17:22:41 -0400 (EDT) Message-ID: Date: Wed, 10 Jul 2024 14:22:40 -0700 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Dropping column from big table To: sud , pgsql-general References: Content-Language: en-US From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 7/10/24 13:13, sud wrote: > Hi All, > It's postgres database version 15.4. We have a table which is daily and > is approx. ~2TB in size having a total ~90 partitions. We have a > requirement to drop columns and add new columns to this table. > > I Want to understand, If this can be done online? what is the fastest > way to drop/add columns from such a big table and what will be the > consequence of this in regards to vacuum, post this activity? Or if any > other issues we may face post this? > > Regards > Sud https://www.postgresql.org/docs/current/sql-altertable.html "The DROP COLUMN form does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent insert and update operations in the table will store a null value for the column. Thus, dropping a column is quick but it will not immediately reduce the on-disk size of your table, as the space occupied by the dropped column is not reclaimed. The space will be reclaimed over time as existing rows are updated. To force immediate reclamation of space occupied by a dropped column, you can execute one of the forms of ALTER TABLE that performs a rewrite of the whole table. This results in reconstructing each row with the dropped column replaced by a null value. " -- Adrian Klaver adrian.klaver@aklaver.com