Message-ID: <b288134ae2290d5abc2a2d516a8d58892a656f30.camel@cybertec.at>
Subject: Re: text fields and performance for ETL
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Grega Jesih <Grega.Jesih@actual-it.si>, "David G. Johnston"
	 <david.g.johnston@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Pg Docs <pgsql-docs@lists.postgresql.org>
Date: Fri, 05 Nov 2021 15:15:35 +0100
In-Reply-To: <516dc58b80fa418091f5262879019213@actual-it.si>
References: <163594615980.678.1904941309603792278@wrigleys.postgresql.org>
	 <CAKFQuwa_02fEtL8zJMt7MR=xvrp-De1pQtHFtg7fSHhSm1ajnA@mail.gmail.com>
	 <686716cb5c4242c28e5967d9b6fcfc75@actual-it.si>
	 <137264.1635961048@sss.pgh.pa.us>
	 <c57e7a702932465ca9962d4f5d332c3a@actual-it.si>
	 <CAKFQuwYFfESp=xUpktYBbW8TxO8k=+uWN8F2G5Ehis2cpYuDnw@mail.gmail.com>
	 <70cf9fe110c246afb4eab054caab0c04@actual-it.si>
	 <CAKFQuwbX37AwNcKHMeaOq+LS_nhw_6sHwaUK1+UsQ8Nuo7bcfg@mail.gmail.com>
	 <516dc58b80fa418091f5262879019213@actual-it.si>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.40.4 (3.40.4-2.fc34) 
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/b288134ae2290d5abc2a2d516a8d58892a656f30.camel%40cybertec.at>
Precedence: bulk

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.
 
> 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".

Yours,
Laurenz Albe