From: Grega Jesih <Grega.Jesih@actual-it.si>
To: 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: Thu, 4 Nov 2021 07:15:49 +0000
Message-ID: <70cf9fe110c246afb4eab054caab0c04@actual-it.si> (raw)
In-Reply-To: <CAKFQuwYFfESp=xUpktYBbW8TxO8k=+uWN8F2G5Ehis2cpYuDnw@mail.gmail.com>
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>
David,
> Can you demonstrate, with actual numbers, using today's implementation, a situation where defining a column as char(3) or varchar(3) instead of text has a significant performance improvement?
Sure I can.
But first, what am I to prove ? A performance in dataflow from server A to server B.
What is that performance change based on ? It is based on a fact that the tool that pumps the data may calculate block size and thus work with several
rows as a time instead of a row at a time.
Is this Postgres performance related ? NO. It is model design related.
So why would it then be published ? Because inexperienced programmers take your statement that "it's the same performance" from a wrong perspective, so it would be fair to note, that the remark "it's the same performance" is meant "within any operation inside Postgres database". In the moment we want to take this data elsewhere,
the problematic lack of model design comes out.
Now for the demo: I can make a video to see it, but here is a column in context that talks about the thing:
https://dba.stackexchange.com/questions/102830/avoiding-row-by-row-fetch-method-when-dealing-with-so...
If this contribution from stackexchange isn't enough, let me know.
BR
Grega
From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Wednesday, November 3, 2021 7:40 PM
To: Grega Jesih <Grega.Jesih@actual-it.si>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; grega.jesih@gmail.com; Pg Docs <pgsql-docs@lists.postgresql.org>
Subject: Re: text fields and performance for ETL
On Wed, Nov 3, 2021 at 11:09 AM Grega Jesih <Grega.Jesih@actual-it.si<mailto:Grega.Jesih@actual-it.si>> wrote:
The new architectures include more and more data exchange among databases.
Now when you deal with bigger data sizes that go from millions to billions, this fixed size vs of text - undefined size becomes very relevant.
Can you demonstrate, with actual numbers, using today's implementation, a situation where defining a column as char(3) or varchar(3) instead of text has a significant performance improvement? Without a concrete example to examine I'm unable to be convinced to move away from the status quo.
You also need to convince me as to why constraints are an insufficient feature. i.e., why is char(3) better than (check length(val) = 3)?
Even with all that I'd probably still not do anything beyond reviewing a proposed patch (i.e, I wouldn't try to write one myself from scratch...I don't have authority to commit regardless).
David J.
NOTICE - NOT TO BE REMOVED.
This e-mail and any attachments are confidential and may contain legally privileged information and/or copyright material of Actual I.T. or third parties. If you are not an authorised recipient of this e-mail, please contact Actual I.T. immediately by return email or by telephone or facsimile on the above numbers.
You should not read, print, re-transmit, store or act in reliance on this email or any attachments and you should destroy all copies of them.
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: 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: <70cf9fe110c246afb4eab054caab0c04@actual-it.si>
* 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