Frank,
> >Otherwise, TEXT and VARCHAR perform the same in PostgreSQL.
>
> Almost the same? I read recently that VARCHAR(n) has a minor
> overhead on
> UPDATE to enforce the max length.
Hmmm ... yes, probably. I was comparing with SQL-Server, where large
text fields are stored as attached files and thus have vastly slower
performance than VARCHAR. I suppose that it would be an amusing
turnabout if, thanks to TOAST, TEXT was marginally faster than VARCHAR
for INSERT & UPDATE in Postgres.
Warning, though; despite TOAST, you will still see a significant
degradation is database performance with very large text fields if
your machine has limited disk access (such as an IDE drive).
Basically, if the size of your text file exceeds the buffer size
you've set in postgresql.conf (default is 8k I think) then you'll see
queries slow down 10-fold as the database engine shuttles your data to
and from the swap files on disk. You can fix this by increasing the
buffer (which may require buying more memory for the computer!) and/or
adding more/faster disks to the computer.
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
