pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedprimary keys as TEXT
8+ messages / 4 participants
[nested] [flat]
* primary keys as TEXT
@ 2006-07-28 08:37 Manlio Perillo <manlio_perillo@libero.it>
0 siblings, 3 replies; 8+ messages in thread
From: Manlio Perillo @ 2006-07-28 08:37 UTC (permalink / raw)
To: pgsql-sql
Hi.
There can be performancs problems in having primary keys of type TEXT?
What about having a primary key of 3 columns (all of type TEXT)?
Regards Manlio Perillo
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: primary keys as TEXT
@ 2006-07-28 10:21 Michael Glaesemann <grzm@seespotcode.net>
parent: Manlio Perillo <manlio_perillo@libero.it>
2 siblings, 1 reply; 8+ messages in thread
From: Michael Glaesemann @ 2006-07-28 10:21 UTC (permalink / raw)
To: Manlio Perillo <manlio_perillo@libero.it>; +Cc: pgsql-sql
On Jul 28, 2006, at 17:37 , Manlio Perillo wrote:
> There can be performancs problems in having primary keys of type TEXT?
> What about having a primary key of 3 columns (all of type TEXT)?
What defines a problem in terms of performance is heavily dependent
on your particular needs and requirements. What are your
requirements? What profiling have you done to see where your
performance bottlenecks may be?
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: primary keys as TEXT
@ 2006-07-28 19:28 Aaron Bono <postgresql@aranya.com>
parent: Manlio Perillo <manlio_perillo@libero.it>
2 siblings, 0 replies; 8+ messages in thread
From: Aaron Bono @ 2006-07-28 19:28 UTC (permalink / raw)
To: Manlio Perillo <manlio_perillo@libero.it>; +Cc: pgsql-sql
On 7/28/06, Manlio Perillo <manlio_perillo@libero.it> wrote:
>
> Hi.
>
> There can be performancs problems in having primary keys of type TEXT?
> What about having a primary key of 3 columns (all of type TEXT)?
If you are really worried about it, why not just use surrogate keys? They
are very easy to use. Then your problem is solved.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: primary keys as TEXT
@ 2006-07-28 19:34 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Manlio Perillo <manlio_perillo@libero.it>
2 siblings, 0 replies; 8+ messages in thread
From: Scott Marlowe @ 2006-07-28 19:34 UTC (permalink / raw)
To: Manlio Perillo <manlio_perillo@libero.it>; +Cc: pgsql-sql
On Fri, 2006-07-28 at 03:37, Manlio Perillo wrote:
> Hi.
>
> There can be performancs problems in having primary keys of type TEXT?
> What about having a primary key of 3 columns (all of type TEXT)?
The biggest problem with using text as a primary key or foreign key is
that text types are locale dependent, so that you might get one
behaviour on one server and another behaviour on another, depending on
configuration of the locale and the locale support on that machine.
For instance, if you have a locale that says that E and e are
equivalent, and another locale that says they aren't...
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: primary keys as TEXT
@ 2006-07-28 20:06 Manlio Perillo <manlio_perillo@libero.it>
parent: Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 1 reply; 8+ messages in thread
From: Manlio Perillo @ 2006-07-28 20:06 UTC (permalink / raw)
To: Michael Glaesemann <grzm@seespotcode.net>; +Cc: pgsql-sql
Michael Glaesemann ha scritto:
>
> On Jul 28, 2006, at 17:37 , Manlio Perillo wrote:
>
>> There can be performancs problems in having primary keys of type TEXT?
>> What about having a primary key of 3 columns (all of type TEXT)?
>
> What defines a problem in terms of performance is heavily dependent on
> your particular needs and requirements. What are your requirements? What
> profiling have you done to see where your performance bottlenecks may be?
>
I still don't have done profiling.
Simply in the first version of my schema I used serial keys but the
result is ugly and it force me to do a lot of joins.
Thanks and regards Manlio Perillo
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: primary keys as TEXT
@ 2006-07-30 20:28 Aaron Bono <postgresql@aranya.com>
parent: Manlio Perillo <manlio_perillo@libero.it>
0 siblings, 1 reply; 8+ messages in thread
From: Aaron Bono @ 2006-07-30 20:28 UTC (permalink / raw)
To: Manlio Perillo <manlio_perillo@libero.it>; +Cc: Michael Glaesemann <grzm@seespotcode.net>; pgsql-sql
On 7/28/06, Manlio Perillo <manlio_perillo@libero.it> wrote:
>
> Michael Glaesemann ha scritto:
> >
> > On Jul 28, 2006, at 17:37 , Manlio Perillo wrote:
> >
> >> There can be performancs problems in having primary keys of type TEXT?
> >> What about having a primary key of 3 columns (all of type TEXT)?
> >
> > What defines a problem in terms of performance is heavily dependent on
> > your particular needs and requirements. What are your requirements? What
> > profiling have you done to see where your performance bottlenecks may
> be?
> >
>
> I still don't have done profiling.
>
> Simply in the first version of my schema I used serial keys but the
> result is ugly and it force me to do a lot of joins.
Ugly? Not sure what you mean by that.
I do understand the problem with so many joins. I use views so that the
joins are only delt with once (in the database) and then all my applications
run off the views. That way, the applications use very simple queries. The
views also allow me to change the table structure (column names, more table
normalization, etc.) without having to make changes to the application.
I am even getting ready to start using updatable views so my applications
never touch the tables directly - it ads another layer of abstraction
between the tables and the application. But that may be more complicated
than you are ready for - I have yet to decide if it will be worth the effort
but I won't know until I experiment with it more.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: primary keys as TEXT
@ 2006-07-31 08:57 Manlio Perillo <manlio_perillo@libero.it>
parent: Aaron Bono <postgresql@aranya.com>
0 siblings, 1 reply; 8+ messages in thread
From: Manlio Perillo @ 2006-07-31 08:57 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: Michael Glaesemann <grzm@seespotcode.net>; pgsql-sql
Aaron Bono ha scritto:
> On 7/28/06, *Manlio Perillo* <manlio_perillo@libero.it
> <mailto:manlio_perillo@libero.it>> wrote:
>
> Michael Glaesemann ha scritto:
> >
> > On Jul 28, 2006, at 17:37 , Manlio Perillo wrote:
> >
> >> There can be performancs problems in having primary keys of type
> TEXT?
> >> What about having a primary key of 3 columns (all of type TEXT)?
> >
> > What defines a problem in terms of performance is heavily dependent on
> > your particular needs and requirements. What are your
> requirements? What
> > profiling have you done to see where your performance bottlenecks
> may be?
> >
>
> I still don't have done profiling.
>
> Simply in the first version of my schema I used serial keys but the
> result is ugly and it force me to do a lot of joins.
>
>
>
> Ugly? Not sure what you mean by that.
>
Because serial ids are only surrogate keys.
My tables have well definited primary keys, the only problem is that
they are of type TEXT (and spawn up to 3 columns).
My concern is: how bad can be performance?
> I do understand the problem with so many joins. I use views so that the
> joins are only delt with once (in the database) and then all my
> applications run off the views. That way, the applications use very
> simple queries. The views also allow me to change the table structure
> (column names, more table normalization, etc.) without having to make
> changes to the application.
>
View are a good idea, thanks.
Regards Manlio Perillo
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: primary keys as TEXT
@ 2006-07-31 19:34 Aaron Bono <postgresql@aranya.com>
parent: Manlio Perillo <manlio_perillo@libero.it>
0 siblings, 0 replies; 8+ messages in thread
From: Aaron Bono @ 2006-07-31 19:34 UTC (permalink / raw)
To: Manlio Perillo <manlio_perillo@libero.it>; +Cc: Michael Glaesemann <grzm@seespotcode.net>; pgsql-sql
On 7/31/06, Manlio Perillo <manlio_perillo@libero.it> wrote:
>
>
> Because serial ids are only surrogate keys.
> My tables have well definited primary keys, the only problem is that
> they are of type TEXT (and spawn up to 3 columns).
>
> My concern is: how bad can be performance?
My guess is that the performance difference is not going to be noticable
unless you are dealing with huge amounts of data and even then may account
for such a small hit that there will be other issues that are more pressing
like writing better queries, creating a data warehouse or adding proper
indexes and keeping the indexes well maintained.
Anyone care to disagree?
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2006-07-31 19:34 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-28 08:37 primary keys as TEXT Manlio Perillo <manlio_perillo@libero.it>
2006-07-28 10:21 ` Michael Glaesemann <grzm@seespotcode.net>
2006-07-28 20:06 ` Manlio Perillo <manlio_perillo@libero.it>
2006-07-30 20:28 ` Aaron Bono <postgresql@aranya.com>
2006-07-31 08:57 ` Manlio Perillo <manlio_perillo@libero.it>
2006-07-31 19:34 ` Aaron Bono <postgresql@aranya.com>
2006-07-28 19:28 ` Aaron Bono <postgresql@aranya.com>
2006-07-28 19:34 ` Scott Marlowe <smarlowe@g2switchworks.com>
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