Thread: Generic design: char vs varchar primary keys
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 D0EFCC68 D17F PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves
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
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 D0EFCC68 D17F PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves
On 2011-08-03, Raj Mathur (राज माथुर) <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
On 2011-08-04, Raj Mathur (राज माथुर) <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