How to minimize backend memory load when reading data from the database, grouping, and sending a file to the frontend?

To minimize the load on backend memory when reading data from the database, grouping, and sending files to the frontend, the following approaches can be used:

1. Pagination — do not load all data at once, but retrieve it in portions.
2. Streaming — read and process data in parts without loading the entire set into memory.
3. Database-level grouping — perform aggregations and groupings in SQL queries to reduce the amount of data transmitted.
4. On-the-fly file generation — generate the file in parts and send it to the client via stream, without saving the entire file in memory.

Example in Java using JDBC and streaming CSV output:

try (Connection conn = dataSource.getConnection();
     PreparedStatement ps = conn.prepareStatement("SELECT category, COUNT(*) FROM items GROUP BY category");
     ResultSet rs = ps.executeQuery();
     ServletOutputStream out = response.getOutputStream();
     BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(out))) {

    response.setContentType("text/csv");
    response.setHeader("Content-Disposition", "attachment; filename=report.csv");

    writer.write("Category,Count\n");
    while (rs.next()) {
        String category = rs.getString(1);
        int count = rs.getInt(2);
        writer.write(category + "," + count + "\n");
        writer.flush(); // send data in parts
    }
}

This approach allows not to keep the entire result and file in memory, reducing memory load.