1

I have a User Relations table, which basically stores all the one-directional relations of users. For example , User A follows user B ; User X blocks User Y

User ids:

the id of users are bigints. It's not like I think that using ints would really end up in a bottleneck(unless my app grows to more than 2 billion users that is :) ), but as far as a I understand, if I integrate social networks signups, Facebook uses bigints for their user ids, hence I have to keep the id of the users as bigint.

Indexing :

At first I thought about making a composite index of both user (the relating and the related), but since both indexes are bigints, meaning 8 bytes each, wouldn't it be a waste to store such a monstrous index? I ended up leaving only one column index, but not sure whether it's the wisest decision in this case.

I also thought of a variant with covering index - but in this case the relation_type(follow, block) will not be part of indexing/filtering, so maybe I should include that inside the composite index, but then I would have 8+8+4 bytes of index. The tradeoffs between space and time are not clear to me in this case. Would appreciate any thoughts on the matter.

First variant (one-column index):

CREATE TABLE user_relations (
    relating_user_id bigint NOT NULL,
    related_user_id bigint NOT NULL,
    relation_type smallint NOT NULL,
    created_at default current_timestamp,

    PRIMARY KEY (relating_user_id),
    FOREIGN KEY (relation_type) references user_relations_types (id)

    -- will having only one index affect the performance really ? 
    -- should I include the type into the composite type ?
);

Second variant (using covering index and omitting PK) :

    CREATE TABLE user_relations (
        relating_user_id bigint NOT NULL,
        related_user_id bigint NOT NULL,
        relation_type smallint NOT NULL,
        created_at default current_timestamp,

        FOREIGN KEY (relation_type) references user_relations_types (id),
        CREATE UNIQUE INDEX relating_user_related_user_relation_type_idx ON user_relations 
                    (relating_user_id, related_user_id) INCLUDE (relation_type);

        -- should I include the type into the composite type ?
    );
  • 1
    PRIMARY KEY (relating_user_id) means each user can only participate once in a relation ship (at least in one direction) - I am pretty sure this is not what you intend. So you can't store x likes y and x blocks z in that table. You have to use both ids as the primary key. An no, that's absolutely not a "monstrous" index – a_horse_with_no_name Oct 14 at 6:07
  • thank you. So is there a general rule for indexes that take too much space? does having two bignts end up really in a 16 bytes index or is there more to that than that? Also, do you think I need to 'INCLUDE' the relation_type? (or perhaps even add to the composite index itself?) – BinaryVeil Oct 14 at 6:10
  • 1
    Define "too much" - if your model requires that primary key, it's absolutely not "too much". If you want to prevent x blocks y to e stored twice, then the relation_type needs to be part of the primary key- So you need a PK over three columns - and no this is still not a monstrous or even a "big" index. – a_horse_with_no_name Oct 14 at 6:12
  • Thank you for the help, it's clearer to me now – BinaryVeil Oct 14 at 6:19
1

Indexes use space, and a 16 byte key is nothing to worry about.

So you should define the primary key on user_relations on (relating_user_id, related_user_id). If you need to search by relation_type, it won't help to put the column into the INCLUDE list, since such columns cannot be used as filter for an index scan.

I see two options:

  1. In addition to the primary key suggested above, have additional indexes on (relating_user_id, relation_type) and (related_user_id, relation_type).

  2. If you want to have fewer indexes, define the primary key on (relating_user_id, relation_type, related_user_id) and define only one additional index. Perhaps you can live with it if the database allows several different relationships between the same users.

The deciding factor which indexes to define should be the queries you will have to serve frequently and efficiently. Design these queries, and the question will become easier.

  • thank you. Just a small follow-up question though - theoretically, if I have a composite index on relating_user & related user, it means there could be only one occurrence of userA:userB, correct? so consequently, there could be only one type relationship between them anyway, even without adding the relation_type into the index, no? because userA:userB - follow, and userA:userB - block would violate the index of (relating_user, related_user) – BinaryVeil Oct 14 at 6:42
  • 1
    Yes. Well, then the second variant is perfect for you. – Laurenz Albe Oct 14 at 6:57

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.