Move pending project members to their own table
When somebody requests access to a project/group, the entry is added to the corresponding members table with requested_at
being set to a timestamp. In turn we filter members using where request_at is null
in a whole bunch of places. The column is also indexed so filtering isn't dog slow.
All of this is redundant, and a waste of time, storage space, and overhead when updating indexes. Instead we should use a separate table (one for every members-like table, please don't use poly morphic tables) to store members that requested access. Once they are granted access the entry from this table is removed, and an entry is inserted into the members table.
The performance impact of this is:
- We can remove all instances of
where members.requested_at IS NULL
, subsequently speeding up these queries; though how much depends entirely on the planner, available buffers, etc. - We can remove the index
index_members_on_requested_at
- Code becomes simpler because
members
now only includes actual members, whereas now it includes members and soon-to-be-members-most-likely.
Structure wise the pending members table would look something like this:
- user_id (integer, foreign keyed to
users.id
with a cascading delete) - project_id (integer, foreign keyed to
projects.id
with a cascading delete) - created_at (timestamp mostly used to show when the request was created)
There is no need for a primary key column in this table.
This table will have just 1 index: a UNIQUE index on (project_id, user_id)
. The order is important because this order allows usage of this index when displaying member requests on a per project basis.
When displaying pending members, the application just runs a query such as:
SELECT users.*
FROM member_requests
INNER JOIN users ON users.id = member_requests.user_id
And boom, we're done; no need for additional filtering.
If we want to display all members (both present and pending), we can do something like this:
SELECT *
FROM users
WHERE ( SELECT true FROM member_requests WHERE project_id = our-project-id AND user_id = users.id ) pending_members
OR ( SELECT true FROM members WHERE project_id = our-project-id AND user_id = users.id) members
Note that a UNION may perform much better than the OR, we'd have to measure this to determine what works best. Either way, the end result is the same: you pull the data from both sets using a single query.