Re: Performance impact of record sizes - Mailing list pgsql-admin

I see in the data types section for character types this text:

     There are no performance differences between these three types,
     apart from the increased storage size when using the blank-padded
     type.

I can't improve on that.

---------------------------------------------------------------------------

Tom Lane wrote:
> John Moore <postgres@tinyvital.com> writes:
> > So I *suspect* I want to keep the data in the physical row, rather than
> > using TEXT and having it stored separately from the record.
>
> You seem to be reading something into the TEXT type that's not there;
> perhaps you are carrying over associations from some other DBMS?
> Here's how it works in Postgres:
>
> The *only* performance difference between TEXT and VARCHAR(n) is that
> for a VARCHAR(n) column, on insert or update there's an extra function
> call applied to the new value to verify that its length is within N.
> TEXT makes no such check; so TEXT has a small performance advantage if
> there are lots of updates.  On the SELECT side there's no difference.
>
> Both TEXT and VARCHAR(n) data is stored using TOAST (automatic
> compression or out-of-line storage) if the row gets too big.  There
> is no difference between the two datatypes on this score.
>
> For the particular application you describe I think TOAST storage will
> be ideal, since the overhead of compression or out-of-line storage is
> only paid on the few rows where it's needed.
>
> What you really ought to be asking yourself is whether (for this
> application) a hard limit on the field width makes sense at all.
> IMHO you should only use VARCHAR(n) if you can make a defensible
> argument for the particular value of N you are using.  If you can't
> explain why you are setting the field width limit, then you probably
> do not need a specific limit at all, and should be using TEXT.
>
> > A related question: is it more expensive to use varchar than fixed char
> > fields?
>
> There is no savings from using CHAR(n) --- most of the time it's a
> substantial loss, because of the extra I/O costs associated with the
> space used by all those padding blanks.  I can think of very very few
> applications where CHAR(n) is really a sensible choice over VARCHAR(n).
> US state postal codes (CHAR(2)) are an example, but beyond that the
> valid use cases are mighty thin on the ground.
>
> Bruce, it occurs to me that the "Performance Tips" section of the user's
> guide ought to have an explanation of the performance implications of
> TOAST.  We have some material in the FAQ but there's precious little
> mention in the manual.  Thoughts?
>
>             regards, tom lane
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026