Thread: alter table type from double precision to real
Real type takes 4 byte storage sizes and double precision takes 8 bytes. I altered a data type from double precision to real and vacuumed DB. But PostgreSQL's data disk usage did not shrinked. And pg_dump size remained same. It seems that real takes 8 byte storage sizes. Here's my environments: CPU: AMD Opteron OS: Solaris 10 PostgreSQL: 8.2.4, compiled w/ Sun CC using 64-bit flags
On Mon, Jun 25, 2007 at 09:51:30AM +0900, ssoo@siliconfile.com wrote: > Real type takes 4 byte storage sizes and double precision takes 8 bytes. > I altered a data type from double precision to real and vacuumed DB. Altering a column's type rewrites the table so vacuuming afterward shouldn't be necessary. > But PostgreSQL's data disk usage did not shrinked. > And pg_dump size remained same. > It seems that real takes 8 byte storage sizes. Real is 4 bytes but other columns' alignment requirements might result in no space being saved. -- Michael Fuhr
Michael Fuhr <mike@fuhr.org> writes:
> On Mon, Jun 25, 2007 at 09:51:30AM +0900, ssoo@siliconfile.com wrote:
>> But PostgreSQL's data disk usage did not shrinked.
>> And pg_dump size remained same.
>> It seems that real takes 8 byte storage sizes.
> Real is 4 bytes but other columns' alignment requirements might
> result in no space being saved.
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.
As for that pg_dump measurement, the text form isn't going to get
smaller ... "1.2" is the same length as "1.2".
regards, tom lane
On Mon, Jun 25, 2007 at 12:35:11AM -0400, Tom Lane wrote:
> Michael Fuhr <mike@fuhr.org> writes:
> > On Mon, Jun 25, 2007 at 09:51:30AM +0900, ssoo@siliconfile.com wrote:
> >> It seems that real takes 8 byte storage sizes.
>
> > Real is 4 bytes but other columns' alignment requirements might
> > result in no space being saved.
>
> 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?
test=> create table test (col1 double precision, col2 integer);
CREATE TABLE
test=> insert into test select 1.0, 1 from generate_series(1, 10000);
INSERT 0 10000
test=> select pg_relation_size('test');
pg_relation_size
------------------
524288
(1 row)
test=> alter table test alter col1 type real;
ALTER TABLE
test=> select pg_relation_size('test');
pg_relation_size
------------------
450560
(1 row)
--
Michael Fuhr
On Mon, Jun 25, 2007 at 12:35:11AM -0400, Tom Lane wrote: > As for that pg_dump measurement, the text form isn't going to get > smaller ... "1.2" is the same length as "1.2". Non-text formats like -Fc should (or might) shrink, right? They appear to in the tests I've done. -- Michael Fuhr
Actually, table size shrinked a little.
But I misinterpreted it as no shrinking.
I expected much more shrinking.
Thank you for your concerns.
Michael Fuhr <mike@fuhr.org> wrote:
> 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?
>
> test=> create table test (col1 double precision, col2 integer);
> CREATE TABLE
> test=> insert into test select 1.0, 1 from generate_series(1, 10000);
> INSERT 0 10000
> test=> select pg_relation_size('test');
> pg_relation_size
> ------------------
> 524288
> (1 row)
>
> test=> alter table test alter col1 type real;
> ALTER TABLE
> test=> select pg_relation_size('test');
> pg_relation_size
> ------------------
> 450560
> (1 row)
Michael Fuhr <mike@fuhr.org> wrote: > Altering a column's type rewrites the table so vacuuming afterward > shouldn't be necessary. I'm gonna alter another table type from double precision to real. This table size is lager than disk free space. Can it be possible? Previsouly, I saw continuous increasing in disk usage until alter complete. I'm afraid of disk full and aftereffects.
<ssoo@siliconfile.com> writes: > Real type takes 4 byte storage sizes and double precision takes 8 bytes. > I altered a data type from double precision to real and vacuumed DB. > But PostgreSQL's data disk usage did not shrinked. > And pg_dump size remained same. > It seems that real takes 8 byte storage sizes. Keep in mind that vacuum doesn't immediately shorten the table when it finds free space. It just marks the space as free and available for reuse. This could also be due to alignment restrictions on the other columns or the row as a whole. If you're curious exactly what's going on and how to optimize your table layout send your table definition and we can tell you exactly how it's being laid out and where the extra 4 bytes are going. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
Gregory Stark <stark@enterprisedb.com> wrote: > This could also be due to alignment restrictions on the other columns or the > row as a whole. If you're curious exactly what's going on and how to optimize > your table layout send your table definition and we can tell you exactly how > it's being laid out and where the extra 4 bytes are going. Here's my table: create table WaferTestItem ( WaferID integer NOT NULL REFERENCES Wafer (ID), X integer NOT NULL, Y integer NOT NULL, TestItemID integer NOT NULL REFERENCES TestItem (ID), Value double precision NOT NULL, PRIMARY KEY (WaferID, X, Y, TestItemID) ); What happen if type of Value altered to real?
<ssoo@siliconfile.com> writes: > Gregory Stark <stark@enterprisedb.com> wrote: >> This could also be due to alignment restrictions on the other columns or the >> row as a whole. If you're curious exactly what's going on and how to optimize >> your table layout send your table definition and we can tell you exactly how >> it's being laid out and where the extra 4 bytes are going. > > Here's my table: > > create table WaferTestItem ( > WaferID integer NOT NULL REFERENCES Wafer (ID), > X integer NOT NULL, > Y integer NOT NULL, > TestItemID integer NOT NULL REFERENCES TestItem (ID), > Value double precision NOT NULL, > PRIMARY KEY (WaferID, X, Y, TestItemID) > ); > > What happen if type of Value altered to real? That table is pretty much optimally packed. It's 4+4+4+4+8 or 24 bytes with no padding between any of the fields. The next record can start immediately after the end of the previous one because 24 is a multiple of 8. If you reduce the last field to 4 bytes then it'll be 4+4+4+4+4 or 20 bytes. On your 64-bit platform you need 8-byte alignment for the next record so there'll be 4 bytes of padding before the next record. I haven't done the arithmetic but there's a small chance (like 1 in 14 think) that this size will leave just enough space at the end of the page for one extra record with the reduced size. In which case you would expect about 0.7% space savings (these are realy small tuples, one more on a page doesn't represent much saving). If you had happened to have one more integer then going to real would save you a lot of space though. Then it would be the difference between 32 and 24 bytes (plus record headers). That would be more than 12% saving (on 8.2 -- on 8.3 it would be over 14%). -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
On Monday 25 June 2007 03:35, ssoo@siliconfile.com wrote:
> Michael Fuhr <mike@fuhr.org> wrote:
> > Altering a column's type rewrites the table so vacuuming afterward
> > shouldn't be necessary.
>
> I'm gonna alter another table type from double precision to real.
> This table size is lager than disk free space.
> Can it be possible?
> Previsouly, I saw continuous increasing in disk usage until
> alter complete.
> I'm afraid of disk full and aftereffects.
Correct, modifying table data types requires a table re-write, so you're
likely to run out of space. You're not too likely to have much trouble by
trying, when you run out of disk space the transaction will roll back and you
should be ok. One method to try and get around this might be to drop and
then recreate relevant indexes (thereby freeing up space ahead of time)
though you'll need to commit between those commands. HTH
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
Michael Fuhr <mike@fuhr.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