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@xxxxxxxxxxx> wrote:
On Tue, Feb 5, 2013 at 9:51 AM, Albe Laurenz <laurenz.albe@xxxxxxxxxx> wrote:
I don't think that pg_column_size() is a good tool toGeoff 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 :)
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
Yours,
Laurenz Albe
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
:wq
