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 1nBGbF-00036N-Mv for pgsql-admin@arkaria.postgresql.org; Sat, 22 Jan 2022 13:38:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nBGbE-0001gS-Cb for pgsql-admin@arkaria.postgresql.org; Sat, 22 Jan 2022 13:38:36 +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 1nBGbD-0001gJ-Ve for pgsql-admin@lists.postgresql.org; Sat, 22 Jan 2022 13:38:36 +0000 Received: from mail-ot1-x32f.google.com ([2607:f8b0:4864:20::32f]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nBGb7-0003AP-5h for pgsql-admin@lists.postgresql.org; Sat, 22 Jan 2022 13:38:34 +0000 Received: by mail-ot1-x32f.google.com with SMTP id l64-20020a9d1b46000000b005983a0a8aaaso15550844otl.3 for ; Sat, 22 Jan 2022 05:38:29 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=xIHrJfSI8CD8IMaX2C7WkX0IE0/uYI14MKGS+LnIZps=; b=CMR7TVn0oN6j6fmn1EXh0vKGmyrVj/+dDHM45UZAXGYPtWnROKpK7bAGFQUxdepeXK zXtQMZEVOjKteqRq7UertPahs8Th+t00H2MSbPD5PlUwqRY906vXywddRYXYRjPbxiaT PTnQUqaGcERw/Lg35a+VCpu43jG7bGHpUsVdecOB0uP8X6OH9wS9xN4vyltQm02YQSPH Rt0Vj3cEoc+dIYL3aFHzFVjOowUUtDkeU+gZjR0kdPR1vTIo7N+/KkdvvPxBHJSY0W44 vgZ8bKqsq/20fFoWSbpDC38SZZkJLR5yJdbRWvt7F67K2pfJCwT6Aj6PhO/seZzYwN75 R/pw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=xIHrJfSI8CD8IMaX2C7WkX0IE0/uYI14MKGS+LnIZps=; b=O8Ww/Iu3Iar33aRac7aphiGpCHlkRnqySLaUW7mtlZAqhd1ShQMs0RPVvggUks41Bj RNKs5Nx5cdL/d9YwSbpI23x+R/T0S5Fw1ozvwRYp9oUz8NQD7yDSp15SaEuOJBwZ9vYl RJBDwdohBcnn6gNYqoaLErhfGjmWaVhezEXTJXJOT7E4IqXIXdz3rAP0iiMasssPqsKD XfXRL6roHOW1JqDqI4TA8moQ6zkX+Whnne1PE63yuEOHIDXCF8q0o9ZE0JS9sHeiVEgr ygAJd/LK8p1GwYDNNVQe3v3xeULCwiAyjqHwSlIEZUNtP7N4IM/7UtkLJanpKuZB1aSb ORIg== X-Gm-Message-State: AOAM532VzwGzEEc4HTMmmvpGW1wBVrof4mbSf1wXgOPEAT3tNghwHAn3 jCUoPc7pViqr48T1+fj/jFAys15z8FE= X-Google-Smtp-Source: ABdhPJzhqFFin9SOQYJVitS2wPzAeEDt+QMJ1JcdoJKKMdvy1yQdqC1SxbaX26dDTVSXGved519mDQ== X-Received: by 2002:a05:6830:1da9:: with SMTP id z9mr624973oti.327.1642858708330; Sat, 22 Jan 2022 05:38:28 -0800 (PST) Received: from [192.168.88.10] (ip68-11-68-85.no.no.cox.net. [68.11.68.85]) by smtp.googlemail.com with ESMTPSA id a124sm470701oif.48.2022.01.22.05.38.27 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 22 Jan 2022 05:38:27 -0800 (PST) Subject: Re: Determine potential change in table size after a column dropped? To: pgsql-admin@lists.postgresql.org References: From: Ron Message-ID: Date: Sat, 22 Jan 2022 07:38:26 -0600 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.14.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------6827B3F3647468104161B740" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------6827B3F3647468104161B740 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 1/22/22 1:43 AM, Vijaykumar Jain wrote: > > > On Sat, Jan 22, 2022, 12:47 PM Wells Oliver > wrote: > > I have a large large large table with many many many rows, and it's a > certain size in pg_relation_size -- there's a timestamp with tz column > on this table that's mostly kind of useless, and I want to figure out > how much space it would free if we just dropped it. Can I easily do this? > > > 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. What about VACUUM FULL? -- Angular momentum makes the world go 'round. --------------6827B3F3647468104161B740 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit On 1/22/22 1:43 AM, Vijaykumar Jain wrote:


On Sat, Jan 22, 2022, 12:47 PM Wells Oliver <wells.oliver@gmail.com> wrote:
I have a large large large table with many many many rows, and it's a certain size in pg_relation_size -- there's a timestamp with tz column on this table that's mostly kind of useless, and I want to figure out how much space it would free if we just dropped it. Can I easily do this?


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.

What about VACUUM FULL?

--
Angular momentum makes the world go 'round.
--------------6827B3F3647468104161B740--