From: Bruce Momjian <bruce@momjian.us>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Grega Jesih <Grega.Jesih@actual-it.si>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Pg Docs <pgsql-docs@lists.postgresql.org>
Subject: Re: text fields and performance for ETL
Date: Fri, 5 Nov 2021 10:17:12 -0400
Message-ID: <20211105141712.GA19812@momjian.us> (raw)
In-Reply-To: <b288134ae2290d5abc2a2d516a8d58892a656f30.camel@cybertec.at>
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>
<b288134ae2290d5abc2a2d516a8d58892a656f30.camel@cybertec.at>
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 <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: bruce@momjian.us, laurenz.albe@cybertec.at, Grega.Jesih@actual-it.si, david.g.johnston@gmail.com, tgl@sss.pgh.pa.us, pgsql-docs@lists.postgresql.org
Subject: Re: text fields and performance for ETL
In-Reply-To: <20211105141712.GA19812@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox