Lately there have been couple of discussions on IRC, Slack, and Reddit that showed that people assume that by using int4/integer they use 4 bytes less than they would in case of int8/bigint. This is not really the case. Let me explain why.
Some time ago on Slack some person said:
varchar is better (storage efficiency), i recommend using it for less than 2048 chars, for the best : TEXT
There was discussion that followed, the person that claimed this efficiency never backed their claim, saying only that:
VARChar takes much less ‘place' than TEXT … but have to face it to believe it
and when I asked for
Show me db fiddle with queries that show this difference and I'll believe
HA ha ! i let you dig it ￼ i've some work ….
I reacted (over reacted), and the thread died.
I know (knew?) that varchar/text are the same internally (wrote about it), but perhaps there have been recent change?
If you have production DB servers, chances are you're running variant of these queries:
SELECT sum(pg_relation_size(oid)) from pg_class where relkind = 'i' SELECT sum(pg_relation_size(oid)) from pg_class where relkind = 'r'
To get summarized size of tables and/or indexes in your database (for example for graphing purposes).
This (getting pg_relation_size for rows in pg_class) has one problem – it can lock, or it can fail.
On 19th of January Tom Lane committed really brilliant patch:
Log Message: ----------- Add pg_table_size() and pg_indexes_size() to provide more user-friendly wrappers around the pg_relation_size() function. Bernd Helmle, reviewed by Greg Smith