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.
Yours,
Laurenz Albe