Thread: varchar vs Text & TOAST

Hi,

I'm a (more than a) bit confuse as to the diference between TEXT and
varchar data-types.

AFAIK, varchar will have a max limit char of, if not mistaken ~65k? But
for TEXT, it's more like a BLOB and there's supposed to be no limit?

Anyhow, searching the archives (in my mail client - no internet at the
moment), I see references that when I use TEXT, I will create TOAST
tables which will have them lie _outside_ of my main data table.

I'm not sure what is the implications or etc. I'm wondering if I should
change my current usage of TEXT to varchar.

the columns will have data like

NULL:NULL:FLE:NULL:FLE:NULL:FLE:FLE

I initially chose TEXT, (for X reasons) and now since I found out about
the TOAST tables, (oh.. i didn't like the TOAST tables cos it's created
_not_ in my usual raidspace, but in my OS drive), I'm trying to
determine if I need to change the data-type.

comments? Advise?

Ow Mun Heng wrote:

> Anyhow, searching the archives (in my mail client - no internet at the
> moment), I see references that when I use TEXT, I will create TOAST
> tables which will have them lie _outside_ of my main data table.

The same is true of varchar, and quite a few other data types. There's
more detail about which data types are and are not toastable, and how,
in the PostgreSQL documentation.

 > oh.. i didn't like the TOAST tables cos it's created
> _not_ in my usual raidspace, but in my OS drive

My understanding was that toast tables were by default created in the
same tablespace as their owning tables, but I could be wrong there.
Again, the documentation will probably tell you for sure.

Note that if you are relying on RAID to protect your database but are
not storing pg_clog, pg_xlog, etc on your RAID volume then you are
experiencing a false sense of security. You must make sure to protect
the whole cluster, including transaction logs etc. Similarly, if you're
using volume-level snapshots rather than pg_dump or Pg's host cluster
copy support to take backups you need to be able to get a consistent
snapshot across ALL of the cluster.

If you want the database to live on the RAID volume, consider moving the
whole cluster there.

--
Craig Ringer