Hi
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 :)
Cheers
Geoff