Difference between array column type and separate table

From: Mike Christensen <mike(at)kitchenpc(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Difference between array column type and separate table
Date: 2009-05-02 07:33:59
Message-ID: 7aa638e00905020033y7847632fw8e439f417c482f@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Let's say you have a table called Threads, and each thread can have zero or
more "tags" associated with it. A tag is just a byte which maps to some
enum somewhere.
There's two ways I can think of to do this. The first would be to have:

create table Threads (
Id uuid not null,
Posted timestamp not null,
Subject varchar(255) not null,
Replies int4 not null,
PosterId uuid not null,
primary key (Id)
);

and a separate table for each tag on a thread:

create table ThreadTags (
Id uuid not null,
Tag int2 not null,
ThreadId uuid not null,
primary key (Id)
)

I can query for all threads with a certain tag using a join:

select * from Threads T
inner join ThreadTags tag ON tag.ThreadId = T.Id AND tag.Tag = 5;

This should work fine. However, recently I was digging through Postgres
manuals and found that you can store arrays of stuff in a column. Using
this, I could completely get rid of ThreadTags and have a table like this:

create table Threads (
Id uuid not null,
Posted timestamp not null,
Subject varchar(255) not null,
Replies int4 not null,
PosterId uuid not null,
Tags int2[],
primary key (Id)
);

and then find threads using the ANY function:

select * from Threads where 5 = ANY (Tags);

To me this seems cleaner, but I'm wondering about performance. If I had
millions of threads, is a JOIN going to be faster? I guess what I'm asking
about is the underlying implementation of ANY. Is it doing
a sequential search? Can I index Tags and will ANY() then use that index?
Any other opinions on what option is better?

One thing about the array approach is I'm using NHibernate which doesn't
really seem to want to support Postgres arrays without a whole bunch of
custom driver code and IUserTypes and junk, so I'd like to make sure this
architecture is best before I commit to it. Thanks!!

Mike

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Daniel Verite 2009-05-02 09:26:28 Re: Re: Mapping output from a SEQUENCE into something non-repeating/colliding but random-looking?
Previous Message Mike Christensen 2009-05-02 06:40:53 Difference between array column type and separate table