This proposal outlines the necessary changes to optimize the project list endpoint. By refining the query made to the database, we aim to reduce the amount of information returned, enabling the application server to respond faster and reducing the load on the backend.
The current project list endpoint (/api/v1/projects) returns a comprehensive list of projects, including more information than needed to populate the mobile project list view. This excess data is causing the backend application to crash due to high resource consumption when executing the SQL query and rendering the results.
The objective is to minimize the number of attributes returned by the endpoint, thereby simplifying the SQL query. This reduction in complexity will prevent server crashes when the endpoint is accessed and improve overall performance.
- Reduce the attributes being returned: Limit the attributes returned by the endpoint to only those required by the mobile view.
- Re-write the database query: Modify the query to return only the necessary fields for the mobile view.
- Update the project model: Add a count of the project participants to the project model to simplify participant counting in the query.
- Update Views: Change the response to include only the fields required by the frontend.
- Testing: Write and run tests to ensure the changes are functioning correctly.
- Documentation: Update the documentation to reflect the changes.
Implement a counter cache attribute for the project users on the project model. Whenever a new user is associated with the project, this attribute will automatically get incremented, and whenever a user is deleted, it will get decremented automatically.
class Project < ApplicationRecord
has_many :project_users, counter_cache: true, dependent: :destroy
end- Return essential project attributes such as name, banner, created_at, etc.
- Return a count of the number of project participants instead of a list of all participants.
- Remove all users from the response, thus, not returning user avatars
- Remove all information related to project conversations.
- Remove the project phase attribute.
- Remove the project type attribute.
- Exclude attachments from the response.
- Remove all requests for conversations.
- Maintain a counter of project participants in the projects table.
- Exclude the project users from the request.
- Exclude the project phase request.
- Exclude the project type request.
- Exclude attachments from the query.
Benefits:
- Improves the response time of the endpoint.
- Enhances resource utilization, preventing application crashes.
Potential Risks:
- Requires careful testing to ensure the functionality is implemented as expected.
Optimizing the project list query will significantly improve the performance and stability of the backend application by reducing the data load and simplifying the SQL query.