Re: alter table type from double precision to real

Michael Fuhr <mike(at)fuhr(dot)org> writes:
> On Mon, Jun 25, 2007 at 12:35:11AM -0400, Tom Lane wrote:
>> Even with no other columns involved, if you're on a machine with
>> MAXALIGN = 8 (which includes all 64-bit platforms as well as some
>> that aren't), the row width won't shrink.

> I see table sizes shrink on 64-bit sparc and x86 architectures, as
> in the following example that results in adjacent 4-byte columns.
> Or am I misinterpreting what's happening?

Sorry, I should've clarified that this depends on whether the total row
length is a multiple of 8. In your example, you have an 8-byte column
followed by a 4-byte column. MAXALIGN-8 machines will pad the row
length to 16 bytes. You then altered it to be two 4-byte columns,
requiring no padding to have a row length of 8 bytes. (Plus overhead
of course, but the overhead is MAXALIGN'd anyway.)

The case I was thinking of was more like this:

regression=# create table test (col1 double precision);
CREATE TABLE
regression=# insert into test select 1.0 from generate_series(1, 10000);
INSERT 0 10000
regression=# select pg_relation_size('test');
pg_relation_size
------------------
368640
(1 row)

regression=# alter table test alter col1 type real;
ALTER TABLE
regression=# select pg_relation_size('test');
pg_relation_size
------------------
368640
(1 row)

The space savings disappears into alignment padding.

regards, tom lane