Re: Converting MySQL tinyint to PostgreSQL

On Thu, 2005-07-21 at 09:23, Dawid Kuroczko wrote:
> On 7/19/05, Jim C. Nasby <decibel(at)decibel(dot)org> wrote:
> > > CREATE TABLE sample1 (
> > > a boolean,
> > > b int,
> > > c boolean
> > > );
> > >
> > > ...it will take more storage than:
> > >
> > > CREATE TABLE sample2 (
> > > b int,
> > > a boolean,
> > > c boolean
> > > );
> > >
> > Actually, I believe that's the case with just about every database,
>
> I tried making alternating int and boolean fields (8 columns total), and
> the loss due to padding was around 30%.
>
> Out of curiosity I repeated the test using MySQL 4.1 MyISAM (alternating
> int and tinyint fields versus ints fist, then tinyints) -- the resulting files
> had the same size. So, for this case, MySQL MyISAM either reorders
> data or stores data without padding.
>
> Regards,
> Dawid
>
> > though of course each one has different alignment constraints. The point
> > is that I don't know of any database that will silently re-order fields
> > under the covers to optimize storage.

Yep, I'm pretty sure I read it in their docs somewhere that the disk
ordering is determined by the db engine, not by the logical order in the
create table statement.

This is of course bittersweet, since an alter table add column in mysql
results in the entire table being read and rewritten back out, doubling
storage requirements of the table being altered, and often causing a
very long wait for large tables.