Thread: NAMEDATALEN and performance

NAMEDATALEN and performance

From
Alessandro Baretta
Date:
Gentlemen,


I use a modeling language which compiles down to a fairly verbose SQL DDL. If I
use semantically transparent identifiers in the modeling language, the compiler
easily generates identifiers much longer than the default value of NAMEDATALEN.
I am considering the possibility of rebuilding the server with NAMEDATALEN equal
to 256. I have seen an interesting thread [1] about the performance impact of
raising NAMEDATALEN, but it did not seem conclusive. Are there commonly accepted
correlations between NAMEDATALEN and server performance? How would raising its
value impact disk usage?


[1] http://archives.postgresql.org/pgsql-hackers/2002-04/msg01253.php

--
*********************************************************************

Ing. Alessandro Baretta

Studio Baretta
http://studio.baretta.com/

Consulenza Tecnologica e Ingegneria Industriale
Technological Consulting and Industrial Engineering

Headquarters
tel. +39 02 370 111 55
fax. +39 02 370 111 54

Lab
tel. +39 02 9880 271
fax. +39 02 9828 0296

Re: NAMEDATALEN and performance

From
Tom Lane
Date:
Alessandro Baretta <a.baretta@studio.baretta.com> writes:
> I am considering the possibility of rebuilding the server with
> NAMEDATALEN equal to 256. I have seen an interesting thread [1] about
> the performance impact of raising NAMEDATALEN, but it did not seem
> conclusive.

More to the point, tests done on 7.2-era code shouldn't be assumed to be
relevant to modern PG releases.  I think you'll need to do your own
benchmarking if you want to find out the costs of doing this.

            regards, tom lane

Re: NAMEDATALEN and performance

From
Alessandro Baretta
Date:
Tom Lane wrote:
> Alessandro Baretta <a.baretta@studio.baretta.com> writes:
>> I am considering the possibility of rebuilding the server with
>> NAMEDATALEN equal to 256. I have seen an interesting thread [1] about
>> the performance impact of raising NAMEDATALEN, but it did not seem
>> conclusive.
>
> More to the point, tests done on 7.2-era code shouldn't be assumed to be
> relevant to modern PG releases.  I think you'll need to do your own
> benchmarking if you want to find out the costs of doing this.
>
>             regards, tom lane
>

That's sensible. Now, performance in my case is much less critical than the
robustness and scalability of the application, so I guess I could just leave it
to that and go with raising namedatalen. Yet, I would like to receive some
insight on the implications of such a choice. Beside the fact that the parser
has more work to do to decipher queries and whatnot, what other parts of the
server would be stressed by a verbose naming scheme? Where should I expect the
bottlenecks to be?

Also, I could imagine a solution where I split the names in a schema part and a
local name, thereby refactoring my namespace. I'd get the approximate effect of
doubling namedatalen, but at the expense of having a much wider searchpath.
Based on your experience, which of two possible strategies is more prone to
cause trouble?

Alex


--
*********************************************************************

Ing. Alessandro Baretta

Studio Baretta
http://studio.baretta.com/

Consulenza Tecnologica e Ingegneria Industriale
Technological Consulting and Industrial Engineering

Headquarters
tel. +39 02 370 111 55
fax. +39 02 370 111 54

Lab
tel. +39 02 9880 271
fax. +39 02 9828 0296

Re: NAMEDATALEN and performance

From
Tom Lane
Date:
Alessandro Baretta <a.baretta@studio.baretta.com> writes:
> ... I would like to receive some
> insight on the implications of such a choice. Beside the fact that the parser
> has more work to do to decipher queries and whatnot, what other parts of the
> server would be stressed by a verbose naming scheme? Where should I expect the
> bottlenecks to be?

I suppose the thing that would be notable is bloat in the size of the
system catalogs and particularly their indexes; hence extra I/O.

> Also, I could imagine a solution where I split the names in a schema part and a
> local name, thereby refactoring my namespace. I'd get the approximate effect of
> doubling namedatalen, but at the expense of having a much wider searchpath.

This might be worth thinking about, simply because it'd avoid the need
for custom executables.

            regards, tom lane