Re: diskspace

Additionally, postgres has a number of "metadata" columns (e.g., xmin,
xmax, cmin, cmax, &c). Those can add up, particularly when their net size
is greater than the user data size of a row.

rls

On Tue, Feb 5, 2013 at 11:00 AM, Scott Mead <scottm(at)openscg(dot)com> wrote:

> 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
>>
>
>

--
:wq