pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How much size saved by updating column to NULL ?
4+ messages / 4 participants
[nested] [flat]

* How much size saved by updating column to NULL ?
@ 2024-01-12 10:53  Sébastien TANIERE <seb.taniere@gmail.com>
  0 siblings, 2 replies; 4+ messages in thread

From: Sébastien TANIERE @ 2024-01-12 10:53 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

Hello,
in my company, some columns rarely used in a PG database 14.8 have been set
to NULL in order to save disk space (datecreation & acteurcreation in
following table) .

create table example
(
 id                  varchar(25) not null,
    datecreation        timestamp(6),
    acteurcreation      varchar(50),
    valeurunit          smallint
)

I am wondering if it is really useful for every type of column.
Intuitively, i would say that it does not save space for fixed field
datecreation as it is a fixed size column.

Do we save 8 bytes by timestamp column updated to NULL or not ?


Have a good day

Sébastien TANIERE

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: How much size saved by updating column to NULL ?
@ 2024-01-12 13:58  David G. Johnston <david.g.johnston@gmail.com>
  parent: Sébastien TANIERE <seb.taniere@gmail.com>
  1 sibling, 0 replies; 4+ messages in thread

From: David G. Johnston @ 2024-01-12 13:58 UTC (permalink / raw)
  To: Sébastien TANIERE <seb.taniere@gmail.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On Friday, January 12, 2024, Sébastien TANIERE <seb.taniere@gmail.com>
wrote:

> Hello,
> in my company, some columns rarely used in a PG database 14.8 have been
> set to NULL in order to save disk space (datecreation & acteurcreation in
> following table) .
>
> create table example
> (
>  id                  varchar(25) not null,
>     datecreation        timestamp(6),
>     acteurcreation      varchar(50),
>     valeurunit          smallint
> )
>
> I am wondering if it is really useful for every type of column.
> Intuitively, i would say that it does not save space for fixed field
> datecreation as it is a fixed size column.
>
> Do we save 8 bytes by timestamp column updated to NULL or not ?
>
>
You probably should just measure it yourself.  But yes, the representation
of null in a tuple is the absence of data and a bit in the nulls bitmap.
So there is overhead but also savings.  The net effect is case specific.

David J.

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: How much size saved by updating column to NULL ?
@ 2024-01-12 15:48  Ron Johnson <ronljohnsonjr@gmail.com>
  parent: Sébastien TANIERE <seb.taniere@gmail.com>
  1 sibling, 1 reply; 4+ messages in thread

From: Ron Johnson @ 2024-01-12 15:48 UTC (permalink / raw)
  To: pgsql-general

On Fri, Jan 12, 2024 at 7:45 AM Sébastien TANIERE <seb.taniere@gmail.com>
wrote:

> Hello,
> in my company, some columns rarely used in a PG database 14.8 have been
> set to NULL in order to save disk space (datecreation & acteurcreation in
> following table) .
>
> create table example
> (
>  id                  varchar(25) not null,
>     datecreation        timestamp(6),
>     acteurcreation      varchar(50),
>     valeurunit          smallint
> )
>
> I am wondering if it is really useful for every type of column.
> Intuitively, i would say that it does not save space for fixed field
> datecreation as it is a fixed size column.
>
> 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.

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: How much size saved by updating column to NULL ?
@ 2024-01-12 16:20  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Ron Johnson <ronljohnsonjr@gmail.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 2024-01-12 16:20 UTC (permalink / raw)
  To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: pgsql-general

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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2024-01-12 16:20 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-01-12 10:53 How much size saved by updating column to NULL ? Sébastien TANIERE <seb.taniere@gmail.com>
2024-01-12 13:58 ` David G. Johnston <david.g.johnston@gmail.com>
2024-01-12 15:48 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-01-12 16:20   ` Tom Lane <tgl@sss.pgh.pa.us>

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