Re: to BLOB or not to BLOB

> I am going  to write an application tht writes big  amounts of plain text
into a database.

 What kind of data are those? Articles? News/mail messages? Other?

> I thought of using  the text type for this, but I don't  know if it has a
maxlenght, and given that these will  be very long texts I started wondered
if these would have to be blobs... but they aren't binary.

 Well,  plain text  is a  `subset' of  `binary,' so  storing it  as a  BLOB
shouldn't be an issue. My home-brewn email archive stores message bodies as
BLOBs with absolutely no problem.
 On length limit of text type field,  for older versions of PGSQL it should
be less  that page size  (default 8 KB,  configurable at compile  time). In
TOASTed PGSQL there's probably no limit set by DBMS itself.

> So, does the text daya type have a maxium lenght? Do BLOBs? What should I
use? For the time being, at least,  I won't be searching these texts with a
search  engine or  anything... but  if I  were to  be, what  considerations
should I take into account when designing the tables?

 As you already figured it, the answer  to your question depends on what do
you want from  your system. Maybe you'll  be OK with BLOBs but  you have to
keep in  mind that  pg_dump couldn't  handle them and  you need  to perform
somewhat tricky things to backup and restore PGSQL DB with BLOBs.
 Another  thing to  consider is  searchability and  indexes. If  you really
don't want (and never going to) search  you big fields, your best way would
be to  store just file names  and to keep  long chunks of text  in external
files. Sure,  you can  store them  in TOASTed fields  if you  need indexes,
but  _IMHO_,  this is  yet  to  be proven  that  simplicity  of design  and
implementation is  worth _possiblie_ preformance degradation.  I believe we
still have to see good and fast index built on a 100 KB text field.
 Maybe what  you need  is FTS, because  IMNSHO, there's no  much use  of an
attribute that can't be indexed  and searched (updated, joined, grouped) on
swiftly. Everything relatively  small and fast to process goes  to DB, huge
portions of not-easily-indexable data should stay outside.
 Actually, FTS is a  last resort, kind of. You may wish  to split your text
into smaller fragments with similar semantical (logical, whatever) load.


--

 ������������������