optimize SELECT query with CASE statements and JOINs
Moved from a comment because getting lengthy:
I think if your query is executing in only 4ms currently you're fine. The relationship between data growth and execution time is not linear, so if your data even grew 10x what it currently is and you re-ran your query, it'll probably still run in under 10ms, roughly speaking. I don't see much issues in your query itself as long as your indexing everything properly.
The only recommendation I could make is to avoid sub-queries when not necessary, especially as part of predicates, such as in your WHERE clause. This can lead to slight inefficiencies, and there's a better relational alternative by just doing an INNER JOIN to the followers table from your subquery on the user_id = following_user_id field. You may never run into a performance issue as you currently wrote it, but just a heads up a better alternative exists.
Example:
SELECT
  a.post_id::int,
  a.repost_id::int,
  CASE WHEN a.repost_id IS NULL THEN a.text ELSE b.text END,
  CASE WHEN a.repost_id IS NULL THEN a.media ELSE b.media END,
  CASE WHEN a.repost_id IS NULL THEN a.timestamp ELSE b.timestamp END,
  c.username,
  d.username as original_username
FROM
  posts a
INNER JOIN followers f
    ON a.user_id = f.following_user_id
    AND f.user_id = $1
LEFT JOIN
  posts b
      ON a.repost_id = b.post_id
JOIN
  users c
      ON a.user_id = c.user_id
LEFT JOIN
  users d
      ON b.user_id = d.user_id
WHERE
  post_id &lt; $2
ORDER BY
  post_id DESC
LIMIT
  10