Re: text fields and performance for ETL

On Fri, Nov  5, 2021 at 03:15:35PM +0100, Laurenz Albe wrote:
> On Fri, 2021-11-05 at 09:51 +0000, Grega Jesih wrote:
> > Suggested current text addendum:
> > 
> > But, if you consider doing ETL from Postgres database to some outer target
> > environment and you seek performance in such interfaces, follow the logic
> > of limited size (varchar or char) data types in your database model.
> > Because if you make a dataflow of known size types, interface code can take
> > a big block of data while for text fields you need to check each record.
> 
> I am opposed to that.
> 
> It is not our business to discuss the limitations of a certain third-party software product.
> If that were something wide-spread, perhaps.  But I myself have never seen a problem
> with "text", as long as the actual size of the data is moderate.

Agreed.

> > Optional additional remark:
> > 
> > Another good aspect of known data sizes is easier understanding of field
> > content and implicit data (length) control.
> 
> Something like that makes more sense to me.
> 
> Perhaps, right before the tip you quoted, something like that:
> 
>   If your use case requires a length limit on character data, or compliance
>   with the SQL standard is important, use "character varying".
>   Otherwise, you are usually better off with "text".

I can support that if others think it is valuable.

-- 
  Bruce Momjian          https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.