2

I have a table for a many-to-many relationship over a users table to represent a follow relationship between users:

CREATE TABLE users (
    id text PRIMARY KEY,
    username text NOT NULL
);

CREATE TABLE followers (
    userid text,
    followid text,
    PRIMARY KEY (userid, followid),
    CONSTRAINT followers_userid_fk   FOREIGN KEY (userid)   REFERENCES users (id),
    CONSTRAINT followers_followid_fk FOREIGN KEY (followid) REFERENCES users (id)
);

CREATE INDEX followers_followid_idx ON followers (followid);

I have two scenarios when I would like to create a JSON response with data related to a user:

  • Request an individual user by id,
  • Request an array of user objects by a list of ids

The user data object should contain two arrays of user ids, one of the users they are following and one of the users that follow them. To create those two fields I used the following SELECT statement.

DECLARE follows RECORD;
SELECT  array (select followid FROM followers where userid = Puserid) AS following, 
    array (select userid FROM followers where followid = Puserid) AS followers 
INTO follows;

When the request is for a list of users, I want to create these two fields for each user object returned in the JSON list of users.

I chose to implement the follows relationship as a many to many table so that I don’t have to search and remove ids from arrays contained in a user (or user profile) table and also in the future I might add meta data about a follow relationship (maybe notification settings or block user or such.

However, I am beginning to doubt the efficiency of this decision, especially in the case where many requests are made for 200 users which I assume would run the above SELECT query for each of hte ids in the list. Will this be hugely inefficient?

I do have indexes on both columns (as the primary key index is not useful for searching followid) but I was contemplating creating a view that contained an array_agg of the followid column:

SELECT userid, array_agg(followid) as following
FROM followers
GROUP BY userid;

But to get a view of following and followers I would need something along the lines of:

SELECT f1.userid, array_agg(f1.followid) as following, 
    f2.followers FROM followers AS f1 INNER JOIN
    (select followid AS userid, array_agg(userid) as followers
from ks.followers
    group by followid) AS f2 ON f1.userid = f2.userid group by f1.userid, f2.followers;

which can’t be a good idea, right?

Am I taking the wrong approach to modelling this relationship between users?

I made two attempts at this, both of which take around 600ms for a short list of 18 ids:

Attempt 1

CREATE OR REPLACE VIEW follow_following AS
    select f1.userid, array_agg(f1.followid) as following, 
    f2.followers FROM followers AS f1 INNER JOIN
    (select followid AS userid, array_agg(userid) as followers
    from followers
    group by followid) AS f2 ON f1.userid = f2.userid group by f1.userid, f2.followers;

CREATE OR REPLACE FUNCTION get_users_by_ids(Puserids TEXT[])
    RETURNS JSON AS $$
    DECLARE rjson JSON;
    BEGIN
        CREATE TEMP TABLE getusers ON COMMIT DROP AS
        SELECT u.id, u.username, p.bio, p.avatar, f.followers, f.following
        FROM users u
        INNER JOIN profiles p
        ON u.id = p.userid
        LEFT OUTER JOIN follow_following f 
        ON u.id = f.userid
        WHERE u.id = ANY(Puserids);

        SELECT INTO rjson json_agg (
            json_build_object (
                'data',json_build_object (
                    'id',getusers.id,
                    'username',getusers.username,
                    'bio',getusers.bio,
                    'avatar',getusers.avatar,
                    'following', getusers.following,
                    'followers', getusers.followers
                )
            )
        ) FROM getusers;
        return rjson;
    END;
$$ LANGUAGE plpgsql SECURITY DEFINER

Attempt 2

This functions runs about the same (650ms) on an array of 17 ids, when I loop the array instead of use a view:

CREATE OR REPLACE FUNCTION get_users_by_ids(Puserids TEXT[])
RETURNS JSON AS $$
DECLARE 
    rjson JSON;
    uid   TEXT;
BEGIN
    CREATE TEMP TABLE getusers (
        userid text,
        username text,
        following text[],
        followers text[]
    ) ON COMMIT DROP;

    FOREACH uid IN ARRAY Puserids
    LOOP
        INSERT INTO getusers (userid, username, followers, following)
        SELECT u.id, u.username,
            array (select userid FROM followers where followid = uid) AS followers,
            array (select followid FROM followers where userid = uid) AS following
        FROM ks.users u
        WHERE u.id = uid;
    END LOOP;

    SELECT INTO rjson json_agg (
        json_build_object (
            'id',getusers.userid,
            'username',getusers.username,
            'following', getusers.following,
            'followers', getusers.followers
        )
    ) FROM getusers;

    return json_build_object ('data', rjson);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER
  • 2
    So performance is the issue. I see room for improvement. For starters: Why are your userid and followid (and users.id) data type text? Any reason these can't be integer? – Erwin Brandstetter Apr 23 '18 at 15:59
  • I am migrating data from a mongo instance and need to return ids to clients that will still look up data in mongo using mongo ids which are text strings. – NectarSoft Apr 23 '18 at 16:17
  • What is the precise objective? A JSON document with complete followers and following arrays for all existing users? Just for those who have any followers / followings? Or just for one or more given users? Please sharpen your question and trim some noise. – Erwin Brandstetter Apr 23 '18 at 17:45
  • Ok, I have attempted to be a bit clearer but maybe I should take out my attempts, although I guess they could serve as examples of over engineering a simple problem. – NectarSoft Apr 23 '18 at 20:06
  • Your attempts are good. Always good to show what you tried. – Erwin Brandstetter Apr 23 '18 at 20:27
4

Query

Creating a temporary table and looping are expensive overkill for the purpose. You don't even need plpgsql in the first place - though it may be slightly faster for repeated calls in the same session. Radically simplify:

CREATE OR REPLACE FUNCTION get_users_by_ids(_uids text[])
  RETURNS JSON AS
$func$
   SELECT json_agg(sub)
   FROM (
      SELECT u.id, u.username
           , ARRAY (SELECT followid FROM followers WHERE userid   = u.id) AS following
           , ARRAY (SELECT userid   FROM followers WHERE followid = u.id) AS followers
      FROM   users u
      WHERE  u.id = ANY (_uids)
      ) sub
$func$  LANGUAGE sql SECURITY DEFINER; 

I use a json_agg() on a subquery instead of json_build_object(). Should be a bit faster, yet. Related:

And it conveniently allows cheap ordering of array elements if you should need that: add ORDER BY in the subquery. You might want to preserve original order of elements. See:

If you need SECURITY DEFINER (do you really?), make sure it cannot be abused. See this Postgres Wiki page:

A Guide to CVE-2018-1058: Protect Your Search Path

Correlated subqueries should be fastest here; you get NULL for following and followers if none are found. Alternatively, a LATERAL join might serve. Related:

If you need to nest everything in a 'data' key, you can add that easily, but that seems to be just noise.

A VARIADIC parameter for _uids may be convenient:

(But the list input only allows up to 100 parameters. You can still pass arrays of any length.)

Index

To allow index-only scans make secondary index followers_followid_idx on (followid, userid) instead of just (followid). Related:

DB design

The normalized design is a good idea. It helps write speed a lot and prevents extensive table bloat and locking contention when working on followings. And it is superior in a number of other respects.

I would strongly suggest to work with integer IDs, though. Smaller, faster. Optimum size for your indexes. Related:

You can always output text IDs additionally.

  • Thank you for the detailed answer and the additional reading. I was just thinking about adding the text ids as an additional property after your original comment. Wrapping the return in a data property is an internal implementation detail. I'll reassess the security needs on this function (I also revoke all privileges from public) and I have updated the index. – NectarSoft Apr 23 '18 at 20:55
  • 1
    OK, will do. FYI the query runs for 2 seconds with an array of 500 ids, so clearly network latency is a big factor. Thanks again for your help – NectarSoft Apr 23 '18 at 21:12
  • 1
    I ran the query against a postgres instance in a vagrant box on my local machine using an array of 500 ids and got a JSON string returned that pretty prints over 8000 lines. It ran in 320 msec. Thanks again! – NectarSoft Apr 23 '18 at 21:30
  • 1
    150 msec for the same array with the new version, the json_agg() over a subquery, Twice as fast! I added 1 msec by wrapping it in a data property :P – NectarSoft Apr 23 '18 at 21:42
  • 1
    Additional note: After adding bigint ids and moving the mongo text ids to another column the user ids query came back in 56 ms from the vagrant box (locally, so no network latency). So, don't use text ids! – NectarSoft May 9 '18 at 16: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.