Is making 2 sequential request to server for every webpage better than implementing server to make subqueries with every request?



  • Design A: Make server do more work

    Given a client url in such format www.mywebsite.com/profile/[username], its page generates a number of components that further requests data from the server to fill the page with content. Some of these components relies on this [username] as part of its parameter for example api/posts?filterBy=[username] returns posts for the page, and api/users/[username]/comments returns profile comments for the page.

    Switching our perspective to the database backend, username is not actually a primary key in the user table but one with a unique index constraint. Instead, I have a userid as a surrogate primary key with uuid type. A lot of table references userid as FK.

    As you can imagine above, with such structure I now forced myself to make sub-queries on the database end to every entity that has a userid as FK. Like given username, I have to find corresponding userid, only then I can filter something such as post table by where post.userid = userid This design makes a tradeoff for usability on client side. As a novice, I don't know whether this cost is large or small as it scale. But since it affects any tables that require filter by userId, which is likely used heavily, I imagine it is a bad design.

    Design B: Slow-down user pageload instead

    The alternative strategy I thought of is to create a once and for all subsequent request. On page load of www.mywebsite.com/profile/[username], the client first calls the endpoint api/users/[username] to get user info including attribute of userid. This userid trickles to the components to make calls to api/posts?filterBy=[userId] or api/users/[userId]/comments - emphasis on the [userid] is no longer [username] - which makes the backend less workload without the need to make subquery to get userId.

    In scenario A, we tax the backend for a blanket effect of subqueries in order to provide abstraction one front end. While scenario B for the same abstraction, we instead are blocking and gated by the first query to carry through to make the subsequent queries (Is this bad design hindering loading speeds).


    Now the whole point of making www.mywebsite.com/profile/[username] not [userid] is for a user experience perspective. If i could make www.mywebsite.com/profile/[userId] then certainly it would make things easier, but that is not an option.

    I assume the two given scenarios are trade-offs, but I hope theres is a third or forth strategy that I've missed where someone can enlighten me.

    now...there is a 3rd design I can conjure, I may implement a coarse grain request like /profile/[username]?includes='userid, posts, comments' that will return data to hydrate the page's components. This will give the client userId and some component data, while specific endpoints can remain and engage if needed further filtering i.e. api/posts?filterBy=[userId]&orderBy=[views]. This seem like a workable solution, however, the lost of separation of concern will increase chance of bugs and maintainability as the application scale with more 'components' and endpoints.

    Additional Notes: I'm designing frontend and backend for my application with the following stack: SPA/Next.js, Node.js + Postgresql


    Edit: I just thought of a 4th hybrid approach:

    Introduce a query parameter for id: as such https://mywebsite.com/profile/[username]?id=[userId]

    if exists, my components will use that id. id exists by way of hyperlinks. i.e. user navigating a profile icon from explore page -> user profile. With that my endpoints api/posts/[userid] can work.

    else if, id param has no value; return to design B, fetch api/users/[username] get userid, append userid to id param on url.



  • If your unique index has only the username column (not compose index), a query to username should be very efficient (at least when compared with query to your other components). If you really need to squeeze any performance, you may keep userid somewhere for reuse (as in Design B).

    In your designs, you considered to keep userid in URL only. Some other possible location are HTTP request body, cookie, app server session variable...

    Note that if you keep userid and username in URL, end user can easily change one of them to make it inconsistent (keeping in other location make changing them a bit more difficult). So you need to be decide which to believe. For example, in the URL for this question, stackexchange believe 308264 and basically discard anything after the slash.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2