postgresql_text_vs_varchar.sql

create table table_text
(
    message text
);

create table table_varchar
(
    message varchar(64)
);

create table table_varchar_fixed
(
    message varchar(5)
);

create table table_long_varchar
(
    message varchar(1024)
);

insert into table_text select 'hello' from generate_series(1,10000000);

insert into table_varchar select 'hello' from generate_series(1,10000000);

insert into table_varchar_fixed select 'hello' from generate_series(1,10000000);

insert into table_long_varchar select 'hello' from generate_series(1,10000000);

select pg_total_relation_size('table_text')::float / 1024 / 1024 as table_text_mb, pg_total_relation_size('table_varchar')::float / 1024 / 1024 as table_varchar_mb, pg_total_relation_size('table_long_varchar')::float / 1024 / 1024 as table_long_varchar_mb;

-- 345.8125  345.8046875  345.8046875  345.8125

select pg_total_relation_size('table_text')::float / pg_total_relation_size('table_varchar')::float;

-- 1.0000225922327903

select pg_total_relation_size('table_text')::float - pg_total_relation_size('table_varchar')::float;

-- 8192