Thread: fixed size columns
I have a very long, very narrow table that is taking up a few gigabytes. The table is defined with varchar(n) fields and some character(n) fields. I am assuming that the majority of all fields are filled. Are these rows being stored on disk as aligned varlenas? Can I squish out some space by arranging the columns to groups of 8 bytes? Or is each column aligned separately on a new 8 byte boundary? Or is this a futile exercise? thanks, elein -- ---------------------------------------------------------------------------------------- elein@varlena.com Database Consulting www.varlena.com I have always depended on the [QA] of strangers.
elein <elein@sbcglobal.net> writes: > I have a very long, very narrow table that is taking up a few gigabytes. > The table is defined with varchar(n) fields and some character(n) fields. > I am assuming that the majority of all fields are filled. > Are these rows being stored on disk as aligned varlenas? Can I squish out some > space by arranging the columns to groups of 8 bytes? Or is each column > aligned separately on a new 8 byte boundary? Or is this a futile exercise? Those fields will be aligned on 4-byte boundaries within a row. Depending on your machine architecture, the total length of a row might be constrained to an 8-byte multiple or a 4-byte multiple. My guess is that you'd be spending your time more productively by figuring a way to make the table less narrow. PG's 28-or-more-byte overhead per row is usually bad news for narrow table designs, long before you worry about alignment. regards, tom lane
Thanks. That is most of what I wanted to know. Alignment is by field on 4 byte boundaries or possibly 8 bytes depending on the machine. I knew long, skinny tables were bad. (I didn't design this one :-\ I just have to deal with it.) But are the fixed size varchars and characters being stored as varlenas with a 4byte overhead or just as characters with or without the EOS? varcharout() converts the varlena to a string. This is the function used to provide the data which is eventually written to disk, isn't it? If so, the answer to my question is that they are being stored with one byte overhead. Please confirm or correct me. thanks! elein On Tuesday 29 April 2003 21:55, Tom Lane wrote: > elein <elein@sbcglobal.net> writes: > > I have a very long, very narrow table that is taking up a few gigabytes. > > The table is defined with varchar(n) fields and some character(n) fields. > > I am assuming that the majority of all fields are filled. > > > > Are these rows being stored on disk as aligned varlenas? Can I squish out > > some space by arranging the columns to groups of 8 bytes? Or is each > > column aligned separately on a new 8 byte boundary? Or is this a futile > > exercise? > > Those fields will be aligned on 4-byte boundaries within a row. > Depending on your machine architecture, the total length of a row might > be constrained to an 8-byte multiple or a 4-byte multiple. > > My guess is that you'd be spending your time more productively by > figuring a way to make the table less narrow. PG's 28-or-more-byte > overhead per row is usually bad news for narrow table designs, long > before you worry about alignment. > > regards, tom lane -- ---------------------------------------------------------------------------------------- elein@varlena.com Database Consulting www.varlena.com I have always depended on the [QA] of strangers.
If it's a many to many relationship, with just one ID in each of two columns from two, (or more) tables, then skinny tablesare just the ticket. :-) elein wrote: > Thanks. That is most of what I wanted to know. > Alignment is by field on 4 byte boundaries > or possibly 8 bytes depending on the machine. > > I knew long, skinny tables were bad. (I didn't > design this one :-\ I just have to deal with it.) > > But are the fixed size varchars and characters > being stored as varlenas with a 4byte overhead > or just as characters with or without the EOS? > > varcharout() converts the varlena to a string. > This is the function used to provide the data which > is eventually written to disk, isn't it? If so, > the answer to my question is that they are being > stored with one byte overhead. > > Please confirm or correct me. > > thanks! > > elein > > On Tuesday 29 April 2003 21:55, Tom Lane wrote: > >>elein <elein@sbcglobal.net> writes: >> >>>I have a very long, very narrow table that is taking up a few gigabytes. >>>The table is defined with varchar(n) fields and some character(n) fields. >>>I am assuming that the majority of all fields are filled. >>> >>>Are these rows being stored on disk as aligned varlenas? Can I squish out >>>some space by arranging the columns to groups of 8 bytes? Or is each >>>column aligned separately on a new 8 byte boundary? Or is this a futile >>>exercise? >> >>Those fields will be aligned on 4-byte boundaries within a row. >>Depending on your machine architecture, the total length of a row might >>be constrained to an 8-byte multiple or a 4-byte multiple. >> >>My guess is that you'd be spending your time more productively by >>figuring a way to make the table less narrow. PG's 28-or-more-byte >>overhead per row is usually bad news for narrow table designs, long >>before you worry about alignment. >> >> regards, tom lane > >
On Wed, 30 Apr 2003 12:28:27 -0700, elein <elein@sbcglobal.net> wrote: >But are the fixed size varchars and characters >being stored as varlenas with a 4byte overhead >[...]? Yes. One of the reasons for this is that a fixed number of characters does not necessarily mean a fixed number of bytes. Servus Manfred