For example, I store interests for Joe as chemistry and classical-music (as two separate values) , and you can query the table like where interests in ('chemistry', 'biochem') and it will use the index to efficiently find people such as Joe.
Does PostgreSQL have such a datatype that can index multiple values to allow advanced queries? What are the performance advantages for queriues? And what are the important caveats?
If you use an array datatype, it can be indexed and the index can be used, check this topic: Can PostgreSQL index array columns?
A better datamodel might be a better idea, but that's up to you.
You could use the hstore datatype for this. You would only store keys, not values. A hstore column can be indexed and testing for the presence of a key is really fast.
Something like:
create table person
(
person_id integer not null primary key,
name text not null,
interests hstore
);
insert into person (person_id, name, interests)
values
(1, 'Joe', 'chemistry => NULL, biochem => null'::hstore);
Then to find a person with a specific interest you can use:
select *
from person
where interests ? 'chemistry'
or interests ? 'biochem';
It's a bit of a hack because the hstore datatype is intended to store key/value pairs and in this case you only need the keys.