Обсуждение: 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