pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGeneric design: char vs varchar primary keys
5+ messages / 3 participants
[nested] [flat]
* Generic design: char vs varchar primary keys
@ 2011-08-03 15:10 Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
2011-08-03 22:24 ` Re: Generic design: char vs varchar primary keys Josh Kupershmidt <schmiddy@gmail.com>
2011-08-05 12:40 ` Re: Generic design: char vs varchar primary keys Jasen Betts <jasen@xnet.co.nz>
0 siblings, 2 replies; 5+ messages in thread
From: Raj Mathur (राज माथुर) @ 2011-08-03 15:10 UTC (permalink / raw)
To: pgsql-sql
Hi,
Can you point me to any pages that explain the difference between using,
say CHAR(8) vs VARCHAR(8) as the primary key for a table? Is there any
impact on the database in terms of:
- Speed of indexed retrieval
- Speed of join from a referencing table
- Storage (I presume VARHAR(8) would have a slight edge, in general)
- Any other issue
Regards,
-- Raj
--
Raj Mathur raju@kandalaya.org http://kandalaya.org/
GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F
PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Generic design: char vs varchar primary keys
2011-08-03 15:10 Generic design: char vs varchar primary keys Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
@ 2011-08-03 22:24 ` Josh Kupershmidt <schmiddy@gmail.com>
2011-08-04 01:58 ` Re: Generic design: char vs varchar primary keys Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
1 sibling, 1 reply; 5+ messages in thread
From: Josh Kupershmidt @ 2011-08-03 22:24 UTC (permalink / raw)
To: raju@linux-delhi.org; +Cc: pgsql-sql
2011/8/3 Raj Mathur (राज माथुर) <raju@linux-delhi.org>:
> Hi,
>
> Can you point me to any pages that explain the difference between using,
> say CHAR(8) vs VARCHAR(8) as the primary key for a table? Is there any
> impact on the database in terms of:
>
> - Speed of indexed retrieval
> - Speed of join from a referencing table
> - Storage (I presume VARHAR(8) would have a slight edge, in general)
> - Any other issue
The docs have good info about the differences between these types:
http://www.postgresql.org/docs/current/static/datatype-character.html
I suspect the tiny size differences between char(8) and varchar(8) are
going to be negligible. In fact, this post talks precisely about this
concern, and more:
http://www.depesz.com/index.php/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/
Personally I'd be most worried about the different semantics of the
types (i.e. treatment of trailing spaces), and perhaps the ease of
expanding the length constraint in the future.
Josh
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Generic design: char vs varchar primary keys
2011-08-03 15:10 Generic design: char vs varchar primary keys Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
2011-08-03 22:24 ` Re: Generic design: char vs varchar primary keys Josh Kupershmidt <schmiddy@gmail.com>
@ 2011-08-04 01:58 ` Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
2011-08-05 12:43 ` Re: Generic design: char vs varchar primary keys Jasen Betts <jasen@xnet.co.nz>
0 siblings, 1 reply; 5+ messages in thread
From: Raj Mathur (राज माथुर) @ 2011-08-04 01:58 UTC (permalink / raw)
To: pgsql-sql
On Thursday 04 Aug 2011, Josh Kupershmidt wrote:
> 2011/8/3 Raj Mathur (राज माथुर) <raju@linux-delhi.org>:
> > Can you point me to any pages that explain the difference between
> > using, say CHAR(8) vs VARCHAR(8) as the primary key for a table?
> > Is there any impact on the database in terms of:
> >
> > - Speed of indexed retrieval
> > - Speed of join from a referencing table
> > - Storage (I presume VARHAR(8) would have a slight edge, in
> > general) - Any other issue
> I suspect the tiny size differences between char(8) and varchar(8)
> are going to be negligible. In fact, this post talks precisely about
> this concern, and more:
>
> http://www.depesz.com/index.php/2010/03/02/charx-vs-varcharx-vs-varc
> har-vs-text/
>
> Personally I'd be most worried about the different semantics of the
> types (i.e. treatment of trailing spaces), and perhaps the ease of
> expanding the length constraint in the future.
Thanks, that's useful for benchmarking the various textual data types.
Anything specific about using CHAR vs VARCHAR for primary keys that are
going to be referenced from multiple tables that comes to mind?
Regards,
-- Raj
--
Raj Mathur raju@kandalaya.org http://kandalaya.org/
GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F
PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Generic design: char vs varchar primary keys
2011-08-03 15:10 Generic design: char vs varchar primary keys Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
2011-08-03 22:24 ` Re: Generic design: char vs varchar primary keys Josh Kupershmidt <schmiddy@gmail.com>
2011-08-04 01:58 ` Re: Generic design: char vs varchar primary keys Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
@ 2011-08-05 12:43 ` Jasen Betts <jasen@xnet.co.nz>
0 siblings, 0 replies; 5+ messages in thread
From: Jasen Betts @ 2011-08-05 12:43 UTC (permalink / raw)
To: pgsql-sql
On 2011-08-04, Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org> wrote:
> Thanks, that's useful for benchmarking the various textual data types.
> Anything specific about using CHAR vs VARCHAR for primary keys that are
> going to be referenced from multiple tables that comes to mind?
that page he pointed you to says they are all stored the same.
(excepte char(8) is padded and that may bloat the index a bit)
--
⚂⚃ 100% natural
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Generic design: char vs varchar primary keys
2011-08-03 15:10 Generic design: char vs varchar primary keys Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
@ 2011-08-05 12:40 ` Jasen Betts <jasen@xnet.co.nz>
1 sibling, 0 replies; 5+ messages in thread
From: Jasen Betts @ 2011-08-05 12:40 UTC (permalink / raw)
To: pgsql-sql
On 2011-08-03, Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org> wrote:
> Hi,
>
> Can you point me to any pages that explain the difference between using,
> say CHAR(8) vs VARCHAR(8) as the primary key for a table? Is there any
> impact on the database in terms of:
look in the data types chapter of the manual
> - Speed of indexed retrieval
> - Speed of join from a referencing table
> - Storage (I presume VARHAR(8) would have a slight edge, in general)
> - Any other issue
Unless you need the padding, and/or the length constraints use text.
("usr/share/doc/postgresql-doc-8.4/html/datatype-character.html")
. Tip: There is no performance difference among these three types,
. apart from increased storage space when using the blank-padded type,
. and a few extra CPU cycles to check the length when storing into a
. length-constrained column. While character(n) has performance
. advantages in some other database systems, there is no such advantage
. in PostgreSQL; in fact character(n) is usually the slowest of the
. three because of its additional storage costs. In most situations text
. or character varying should be used instead.
--
⚂⚃ 100% natural
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-08-05 12:43 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-08-03 15:10 Generic design: char vs varchar primary keys Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
2011-08-03 22:24 ` Josh Kupershmidt <schmiddy@gmail.com>
2011-08-04 01:58 ` Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
2011-08-05 12:43 ` Jasen Betts <jasen@xnet.co.nz>
2011-08-05 12:40 ` Jasen Betts <jasen@xnet.co.nz>
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