I have the following table in a PostgreSQL database:
creator uuid not null, post uuid not null, type text not null, content text not null, constraint check_type check (type = 'comment' or (type = 'vote' and (content = 'up' or content = 'down')))
In order to enforce the constraint that there is no duplicate pair of (creator, post, type) where
type = 'vote' (ie. a specific user can only vote on a specific post once) I can either create a unique index like so:
create unique index interactions_index on posts.interactions (creator, post, type, content) where type = 'vote';
Or I can query the table on in the server code and do a check there.
which one is more efficient, or should I include both for safeguards? How much will having a unique index impact performance (say 100,000 posts @ 1000 likes/post = 100,000,000 database lines)?
I am using this to store "interactions" with a post (ie. likes, comments), is this a good/sustainable way to approach this, or should I be doing something different?