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?

  • \$\begingroup\$ What is the difference between type='like' and type='vote'? \$\endgroup\$ Sep 6 '19 at 5:04
  • \$\begingroup\$ none. it's a typo :) \$\endgroup\$
    – Mark Dodds
    Sep 6 '19 at 22:35

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.

  • \$\begingroup\$ a followup, in the case where you try to insert a duplicate, is it better/more efficient to query the database and block it if it exists, or try to insert right away and let the unique index throw an error? \$\endgroup\$
    – Mark Dodds
    Sep 3 '19 at 3:42
  • \$\begingroup\$ Let the database index return a unique constraint violation. Think about the number of trips to the database and back. In the first case, there's one request and one response. In the second case, there are two requests and two responses. \$\endgroup\$
    – Reinderien
    Sep 3 '19 at 3:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.