Does varchar(n) use less disk space than varchar() or text?

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

I got:

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?

Waiting for 9.1 – Rewrite-less changing types of column

On 12th of February, Robert Haas committed patch:

Teach ALTER TABLE .. SET DATA TYPE TO avoid SOME TABLE rewrites.
 
WHEN the OLD TYPE IS BINARY coercible TO the NEW TYPE AND the USING     
clause does NOT CHANGE the COLUMN contents, we can avoid a FULL TABLE
rewrite, though any indexes ON the affected COLUMNS will still need
TO be rebuilt.  This applies, FOR example, WHEN changing a VARCHAR
COLUMN TO be OF TYPE text.
 
The prior coding assumed that the SET OF operations that force a
rewrite IS identical TO the SET OF operations that must be propagated
TO TABLES making USE OF the affected TABLE's rowtype.  This is
no longer true: even though the tuples in those tables wouldn't
need TO be modified, the DATA TYPE CHANGE invalidate indexes built
USING those composite TYPE COLUMNS.  Indexes ON the TABLE we're
actually modifying can be invalidated too, of course, but the
existing machinery is sufficient to handle that case.
 
Along the way, add some debugging messages that make it possible
to understand what operations ALTER TABLE is actually performing
in these cases.                                                      
 
Noah Misch and Robert Haas

Later on, on 15th, he committed second patch with few more cases where rewrite can be avoided.

Continue reading Waiting for 9.1 – Rewrite-less changing types of column

CHAR(x) vs. VARCHAR(x) vs. VARCHAR vs. TEXT – UPDATED 2010-03-03

UPDATE

Please read also about this change in Pg 9.1, and this change in Pg 9.2 posts, as they explain that since Pg 9.1 some of the limitations listed in this post are no longer there.

END OF UPDATE

Fight!

But more seriously – people tend to use various data types, and there have been some myths about them, so let's see how it really boils down.

First of all – All those data types are internally saved using the same C data structure – varlena.

Thanks to this we can be nearly sure that there are no performance differences. Are there no performance differences in reality? Let's test.

Continue reading CHAR(x) vs. VARCHAR(x) vs. VARCHAR vs. TEXT – UPDATED 2010-03-03