Skip site navigation (1) Skip section navigation (2)

Re: Domain vs table

From: decibel <decibel(at)decibel(dot)org>
To: Merlin Moncure <mmoncure(at)gmail(dot)com>
Cc: Michal Szymanski <mich20061(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Domain vs table
Date: 2009-10-25 20:43:56
Message-ID: A8A45685-9A41-48DF-AFF1-1BFE2DCFDCCB@decibel.org (view raw or flat)
Thread:
Lists: pgsql-performance
On Oct 20, 2009, at 6:55 AM, Merlin Moncure wrote:
> On Sun, Oct 11, 2009 at 11:31 AM, Michal Szymanski  
> <mich20061(at)gmail(dot)com> wrote:
>> I think I've found answer to my question
>> http://www.commandprompt.com/blogs/joshua_drake/2009/01/ 
>> fk_check_enum_or_domain_that_is_the_question/
>>
>
> I mostly agree with the comments on the blog but let me throw a couple
> more points out there:
>
> *) It is possible (although not necessarily advised) to manipulate
> enums via direct manipulation of pg_enum
> *) enums are the best solution if you need natural ordering properties
> for indexing purposes
> *) domains can't be used in arrays
> *) foreign key is obviously preferred if you need store more related
> properties than the value itself
> *) if the constraint is complicated (not just a list of values), maybe
> domain/check constraint is preferred, possibly hooked to immutable
> function


Also, if the base table will have a very large number of rows  
(probably at least 10M), the overhead of a text datatype over a  
smallint or int/oid gets to be very large.
-- 
Decibel!, aka Jim C. Nasby, Database Architect  decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828



In response to

pgsql-performance by date

Next:From: Marc MaminDate: 2009-10-26 09:39:48
Subject: Re: optimizing query with multiple aggregates
Previous:From: Greg SmithDate: 2009-10-25 00:16:46
Subject: Re: Table Clustering & Time Range Queries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group