Обсуждение: How much size saved by updating column to NULL ?
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
)
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
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.
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.
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