Search Postgresql Archives

Re: How much size saved by updating column to NULL ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Ron Johnson <ronljohnsonjr@xxxxxxxxx> writes:
> On Fri, Jan 12, 2024 at 7:45 AM Sébastien TANIERE <seb.taniere@xxxxxxxxx>
> 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





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux