Modelling "Friendships" in PostgreSQL

fiends_not_a_typo.md

For this particular problem, of modelling mutual friendship what we truly need is a "SET" data type. But Postgres doesn't really have a set datatype. However, I realised that tsvectors are sorted sets and can be used for this. So, here's a fun solution with a single row: tsvectors

Our schema:

create table friendships (
  id bigint primary key,
  friends tsvector 
);

-- performance can be tested using either a GIN index or a BTREE index
create index fast_friends_lookup on friendships using btree (friends);

-- add constraint to keep tsvector length to 2
alter table friendships add constraint only_2_friends check (length(friends) = 2);

-- rows to test
insert into friendships values (1, to_tsvector('3 5'));
insert into friendships values (2, to_tsvector('3 7'));

List 3's friends:

select id, friends, tsvector_to_array(friends) from friendships where friends @@ '3'::tsquery;

Are 3 & 6 friends?

select id, friends, tsvector_to_array(friends) from friendships where friends = to_tsvector('3 6');