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 1rOKGi-003GA1-IP for pgsql-general@arkaria.postgresql.org; Fri, 12 Jan 2024 16:20: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 1rOKGh-0037q7-Je for pgsql-general@arkaria.postgresql.org; Fri, 12 Jan 2024 16:20: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 1rOKGh-0037pz-9f for pgsql-general@lists.postgresql.org; Fri, 12 Jan 2024 16:20:27 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rOKGe-001DE1-5k for pgsql-general@postgresql.org; Fri, 12 Jan 2024 16:20:26 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 40CGKMvx482391; Fri, 12 Jan 2024 11:20:22 -0500 From: Tom Lane To: Ron Johnson cc: pgsql-general Subject: Re: How much size saved by updating column to NULL ? In-reply-to: References: Comments: In-reply-to Ron Johnson message dated "Fri, 12 Jan 2024 10:48:29 -0500" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <482389.1705076422.1@sss.pgh.pa.us> Content-Transfer-Encoding: 8bit Date: Fri, 12 Jan 2024 11:20:22 -0500 Message-ID: <482390.1705076422@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Ron Johnson writes: > On Fri, Jan 12, 2024 at 7:45 AM Sébastien TANIERE > wrote: >> Do we save 8 bytes by timestamp column updated to NULL or not ? > I don't think the record on the page is rewritten in a more compact form. > Even if it were, the page would just have a small hole in it. > A different version of this question might be whether the tuple is deleted > and then the hopefully smaller row inserted some where else when > "UPDATE example SET datecreation = NULL;" is executed. But of course then > you'd use *more* disk space, since now the file(s) will be full of holes. > Vacuum will clean them up so that new data can be inserted there, but the > files would not be shrunk. Yes, that's exactly what happens. As to whether there is space savings: as David mentioned, if a tuple has any null columns then it includes a "nulls bitmap", costing 1 bit per column. Therefore, if you rewrite a row that had been all not null with one null column, it could actually get wider, especially when there are many columns. But once the bitmap is present, setting additional columns to null is pure savings (and yes, there is savings -- we simply don't store anything for a null column). There's more detail at https://www.postgresql.org/docs/current/storage-page-layout.html regards, tom lane