Enforcing a constraint that a user may vote on each post at most once

Question

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.

My questions:

1. 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)?

2. 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?

Answer 1

which one is more efficient

Almost surely the unique index will be. Querying back and forth between the server and database is very expensive, and the query that you would have to write to check the table beforehand would do the same thing as the unique constraint - just far less efficiently. The best case is that you'd have to still add an index to the database to get vaguely similar performance, and the connection round-trip expense between the server and database would then be the limiting factor.

All of that said: you aren't asking the more important question, which is

which method gives me a better guarantee of correctness?

Under most circumstances, it's useless to do performance tuning when your data are wrong. The database can guarantee that the data remain valid if you give it a constraint to enforce. If the server is responsible for enforcing the constraint, and it has a race condition or other nasty concurrency edge case, you're setting yourself up for a very difficult debugging session trying to find why duplicates are getting into your database.