From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: How much size saved by updating column to NULL ?
Date: Fri, 12 Jan 2024 11:20:22 -0500
Message-ID: <482390.1705076422@sss.pgh.pa.us> (raw)
In-Reply-To: <CANzqJaAWS0kO7HACiv_rXa2y=svTWDy-aRErVvzr1QLzs=znjA@mail.gmail.com>
References: <CA+xUx5=SypY4Ga6x4eB1VitgXnQWWpHJp_rJqWoZu50yk26wsg@mail.gmail.com>
<CANzqJaAWS0kO7HACiv_rXa2y=svTWDy-aRErVvzr1QLzs=znjA@mail.gmail.com>
Ron Johnson <ronljohnsonjr@gmail.com> writes:
> On Fri, Jan 12, 2024 at 7:45 AM Sébastien TANIERE <seb.taniere@gmail.com>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: tgl@sss.pgh.pa.us, ronljohnsonjr@gmail.com
Subject: Re: How much size saved by updating column to NULL ?
In-Reply-To: <482390.1705076422@sss.pgh.pa.us>
References: <CA+xUx5=SypY4Ga6x4eB1VitgXnQWWpHJp_rJqWoZu50yk26wsg@mail.gmail.com> <CANzqJaAWS0kO7HACiv_rXa2y=svTWDy-aRErVvzr1QLzs=znjA@mail.gmail.com> <482390.1705076422@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox