Best practice regarding concurrency for INSERT into a table with composite primary key?
If I wanted to add a user to a team, what's the best option


For just INSERT INTO teams_users ...  and without raising an exception, the answer is option 3: Use Postgres 9.5 Beta for the UPSERT functionality. For a "school project" this is the best option anyway. Your students will want to study the latest version.

INSERT INTO teams_users (team_ID, user_ID)
VALUES (1, 2)
ON CONFLICT ON CONSTRAINT teams_users_pkey DO NOTHING;


Where teams_users_pkey is the actual name of your PK constraint.

This inserts the new row or does nothing if the unique index would raise a duplicate key violation. Designed to be fast and safe against concurrent writes.

Additional question


  Finally, I have no idea if there's a race condition when it comes to
  the 2nd option.


Option 2 being:


  While inserting, use the WHERE EXISTS syntax (2nd example).


That would actually be WHERE NOT EXISTS in your case and yes, there is a race condition. Two parallel transactions could find that a certain combination does not exist yet at virtually the same time and happily try to insert it. The slower one would run into a unique violation and error out. Not much of a problem if your application is prepared for that eventuality.