Re: diskspace

On Tue, Feb 5, 2013 at 9:51 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>wrote:

> Geoff Winkless wrote:
> > I'm trying to migrate a database from MySQL to postgres and I'm
> struggling with the amount of
> > diskspace the resulting db takes.
> >
> > I may be missing a setting somewhere but I can't see one anywhere
> obvious (apologies if I'm being
> > stupid...)
> >
> > Even postgres' idea of the size of the columns don't match up to its own
> reported size of the data.
> >
> > eg I have a table "result":
> >
> > CREATE TABLE result (
> > st_id integer NOT NULL,
> > log smallint NOT NULL,
> > "time" integer NOT NULL,
> > token character(4) NOT NULL,
> > data character varying(500) DEFAULT NULL::character varying
> > );
> >
> >
> > # SELECT pg_size_pretty(sum(pg_column_size(data) + pg_column_size(st_id)
> + pg_column_size(log) +
> > pg_column_size(token) + pg_column_size(time))) FROM result;
> > pg_size_pretty
> > ----------------
> > 178 MB
> > (1 row)
> >
> > # SELECT pg_size_pretty(pg_relation_size('result'));
> > pg_size_pretty
> > ----------------
> > 613 MB
> > (1 row)
> >
> >
> > I'd naively expected these two figures to be similar.
> >
> > I've run vacuum analyze and it made no difference (not a major surprise
> because all I've done so far
> > is create the database and sequentially insert the data into the tables).
> >
> > I expected a little overhead from what I'd read before the migration but
> that's a fairly huge
> > difference.
> >
> > As I said, sorry if I've missed the obvious "use loads of extra space"
> setting but I'd appreciate any
> > suggestion as to what that setting might be called :)
>
> I don't think that pg_column_size() is a good tool to
> measure table size.
>
> I'd suggest that you use pg_table_size for the table itself
> and pg_indexes_size for the size of ist indexes.
> That should come close to the amount of disk space taken.
>

Agreed, don't forget, you have indexes, free space, vacuum-able stuff,
etc... all laying in your datafiles. Your measurements are telling you
what you have purely in a raw form.

--Scott Mead
scottm(at)openscg(dot)com
http://www.openscg.com

>
> Yours,
> Laurenz Albe
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>