Re: diskspace - Mailing list pgsql-admin

On Tue, Feb 5, 2013 at 9:51 AM, Albe Laurenz <laurenz.albe@wien.gv.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@openscg.com

http://www.openscg.com

 

Yours,
Laurenz Albe


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin