Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1miL4w-0008L3-Ti for pgsql-docs@arkaria.postgresql.org; Wed, 03 Nov 2021 18:33:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1miL4v-0003kx-U7 for pgsql-docs@arkaria.postgresql.org; Wed, 03 Nov 2021 18:33:41 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1miKhb-00072L-5Y for pgsql-docs@lists.postgresql.org; Wed, 03 Nov 2021 18:09:35 +0000 Received: from m-out.actual-it.si ([195.250.58.25]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1miKhX-0003V5-9l for pgsql-docs@lists.postgresql.org; Wed, 03 Nov 2021 18:09:34 +0000 Received: from ACEXC1.actual.local (10.108.6.2) by ACEXC1.actual.local (10.108.6.2) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.2308.14; Wed, 3 Nov 2021 19:09:28 +0100 Received: from ACEXC1.actual.local ([fe80::6d5b:82be:eb03:1416]) by ACEXC1.actual.local ([fe80::6d5b:82be:eb03:1416%14]) with mapi id 15.01.2308.014; Wed, 3 Nov 2021 19:09:28 +0100 From: Grega Jesih To: Tom Lane CC: "David G. Johnston" , "grega.jesih@gmail.com" , Pg Docs Subject: Re: text fields and performance for ETL Thread-Topic: text fields and performance for ETL Thread-Index: AQHX0LbtwJI9OtT9g0OmU8QZraFd6KvxzrGAgAAeh5CAABOHAIAAFRQt Date: Wed, 3 Nov 2021 18:09:28 +0000 Message-ID: References: <163594615980.678.1904941309603792278@wrigleys.postgresql.org> <686716cb5c4242c28e5967d9b6fcfc75@actual-it.si>,<137264.1635961048@sss.pgh.pa.us> In-Reply-To: <137264.1635961048@sss.pgh.pa.us> Accept-Language: sl-SI, en-US Content-Language: sl-SI X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.108.10.17] x-c2processedorg: d07a8c1a-8772-405d-8b3f-3e485b5946e7 Content-Type: multipart/alternative; boundary="_000_c57e7a702932465ca9962d4f5d332c3aactualitsi_" MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_c57e7a702932465ca9962d4f5d332c3aactualitsi_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Dear Tom and David, >I'll just point out in passing, though, that "fixed record size" for text >strings is an illusion as soon as you have to deal with non-ASCII data. >So I'm skeptical that such optimizations are worth much anymore. It doesn't matter is it ascii or not. A string is a string. An UTF-8 is al= so one, just differently coded. It matters a lot. It means time saving. Plenty of time. So we're talking pe= rformance. Not postgres performance, interface performance. 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. But what I am trying to achieve is that you describe a view local to databa= se itself and and broader view, integration. There is also the inner aspect of a database model where for a currency of = true size 3 you choose char(3) instead of text. You prevent a dummy insert on a database level. So no text len>3 may enter = this field. BR Grega ________________________________ Od: Tom Lane Poslano: 3. november 2021 18:37:28 Za: Grega Jesih Kp: David G. Johnston; grega.jesih@gmail.com; Pg Docs Zadeva: Re: text fields and performance for ETL Grega Jesih writes: >> The goal in our docs is to point out that using an arbitrary length spec= ification is not required in PostgreSQL. > Well perhaps yours. But there are pro-tools (I refer to SSIS in this cont= ext) that provide a very fast dataflow in case there is a known record size= . That's a matter for those tools to document, not us, because it's their performance characteristics that you are talking about, not ours. I'll just point out in passing, though, that "fixed record size" for text strings is an illusion as soon as you have to deal with non-ASCII data. So I'm skeptical that such optimizations are worth much anymore. regards, tom lane NOTICE - NOT TO BE REMOVED. This e-mail and any attachments are confidential and may contain legally pr= ivileged information and/or copyright material of Actual I.T. or third part= ies. 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 e= mail or any attachments and you should destroy all copies of them. --_000_c57e7a702932465ca9962d4f5d332c3aactualitsi_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable


Dear Tom and David, 

>I'll just point out in passing, though, that "= ;fixed record size" for text
>strings is an illusion as soon as you have to deal= with non-ASCII data.
>So I'm skeptical that such optimizations are worth= much anymore.

It doesn't matter is it ascii or not.  A string is a string. An UTF-8 = is also one, just differently coded. 

It matters a lot. It means time saving. Plenty of time. So we're talking perf= ormance. Not postgres performance, interface performance. 


The new architectures include more and more data exchange among databases.&nbs= p;
Now when you deal with bigger data sizes that go from millions to billions,= this fixed size vs of text - undefined size becomes very  releva= nt. 

But what I am trying to achieve is that you describe a view local to dat= abase itself and and broader view, integration.  =


There is also the inner aspect of a database model where for a currenc= y of true size 3 you choose char(3) instead of text. 

You prevent a dummy insert on a database level. So no text len>3 may enter = this field. 


BR
Grega



Od: Tom Lane <tgl@sss.= pgh.pa.us>
Poslano: 3. november 2021 18:37:28
Za: Grega Jesih
Kp: David G. Johnston; grega.jesih@gmail.com; Pg Docs
Zadeva: Re: text fields and performance for ETL
 
Grega Jesih <Grega.Jesih@actual-it.si> write= s:
>> The goal in our docs is to point out that using an arbitrary lengt= h specification is not required in PostgreSQL.

> Well perhaps yours. But there are pro-tools (I refer to SSIS in this c= ontext) that provide a very fast dataflow in case there is a known record s= ize.

That's a matter for those tools to document, not us, because it's their
performance characteristics that you are talking about, not ours.

I'll just point out in passing, though, that "fixed record size" = for text
strings is an illusion as soon as you have to deal with non-ASCII data.
So I'm skeptical that such optimizations are worth much anymore.

            &nb= sp;           regards, to= m lane

NOTICE - NOT TO BE REMO= VED.
This e-mail and any attachments are confidential and may contain legally pr= ivileged information and/or copyright material of Actual I.T. or third part= ies. 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 e= mail or any attachments and you should destroy all copies of them. --_000_c57e7a702932465ca9962d4f5d332c3aactualitsi_--