postgresql/sql - Improve query where same sub select used for IN & NOT IN select

Using a common table expression will help:

WITH users_cte AS (
  SELECT followed_id
  FROM relationships
  WHERE follower_id = 1 AND blocked = false)
SELECT "activities.*"
FROM "activities"
WHERE user_id IN (SELECT followed_id FROM users_cte)
  AND targeted_user_id NOT IN (SELECT followed_id FROM users_cte)

I would phrase the query using exists:

SELECT a.* 
FROM activities a
WHERE EXISTS (SELECT 1
              FROM relationships r
              WHERE r.followed_id = a.user_id AND
                    r.follower_id = 1 and
                    r.blocked = false
             ) AND
      NOT EXISTS (SELECT 1
                  FROM relationships r
                  WHERE r.followed_id = a.targeted_user_id AND
                        r.follower_id = 1 and
                        r.blocked = false
                 );

Then, I would create an index on relationships(followed_id, follower_id, blocked):

create index idx_relationships_3 on relationships(followed_id, follower_id, blocked);

From a performance perspective, the index should be much better than using a CTE (if you are really using Postgres, MySQL doesn't support CTEs).

In addition to indexes you could try rewriting the query as follows:

SELECT distinct a.*
  FROM activities a
  join relationships x
    on a.user_id = x.followed_id
  left join relationships r
    on a.targeted_user_id = r. followed_id
   and r.follower_id = 1
   and r.blocked = false
 where r.followed_id is null
   and x.follower_id = 1
   and x.blocked = false

If the inner join to relationships (x) above does not result in repeated rows of activities, you can get rid of the DISTINCT.