Re: Performance issues of one vs. two split tables.

On May 15, 2007, at 10:35 AM, Bill Moseley wrote:

>> For some value of "large", is there a time when one might consider
>> using a single column in the user or user_prefs table to represent
>> their color choices instead of a link table?

We use bitfields on our large user table. It is becoming unworkable
to scan for matches, since overall most people have very few
selections made.

We are moving it to a model like your favorite_colors table which
just links the option and the user.

We find that doing joins on large tables which can be indexed to
avoid full table scans are very fast in postgres, since the index can
do much of your culling of potential matching rows. With bitfields,
you are more or less forced into doing a sequence scan to find
everyone who likes the color red.

Of course, if you're playing with only a few thousand users, either
approach works well.