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