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.
Continue reading How much disk space you can save by using INT4/INT instead of INT8/BIGINT?
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?
Continue reading Does varchar(n) use less disk space than varchar() or text?
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.
Continue reading Tips N' Tricks – getting sizes of relations without locks
On 19th of January Tom Lane committed really brilliant patch:
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
Continue reading Waiting for 9.0 – table and index sizes