Efficiently return two aggregated arrays from a m:n table

Question

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

Answer 1

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
  LANGUAGE sql SECURITY DEFINER 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$;

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

• Select columns inside json_agg

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:

• PostgreSQL unnest() with element number

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:

• What is the difference between LATERAL and a subquery in PostgreSQL?
• Optimize GROUP BY query to retrieve latest record per user

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:

• How to use an array as argument to a VARIADIC function in PostgreSQL?

(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:

• Can Postgres use an index-only scan for this query with joined tables?

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:

• Is a composite index also good for queries on the first field?

You can always output text IDs additionally.