pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedHow 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