| Best performing query is the query that's not executed. You have discussed multiple ideas that fit for different use cases. SQL is for transactional type of workload that's where it's best so data returned need to be less. In first part you are talking about not making any changes to the program when a new column is added. Now a days we are talking about column masking. So querying all columns is a no. Otherwise there needn't be a columnar DBs. Seeing a generalized rest APIs(selecting many Columns than required) are also no but architects are recommending it nowadays. Here unnecessarily you are retreiving lots of data than required and filtering in frontend after retrieval (you have already done the heavy duty work). Firebase is a special case, UX designer basically dictates the database schema rather than architects or dba. When you hit the node in firebase you retreive all the data in that node. Here there has to be duplication of data as it's designed based on UX no foreign keys or checks. To be efficient if UX changes you will have to change schema. If you are not changing UX but just adding more info or taking out things you can easily add more fields, no disruption. What you do with firebase cant be doing with RDBMS, adding a column needs to be planned and also theres boyce codd normal form to avoid duplication and repeated groups. Again there is a reason why they had to build firestore when they have firebase. Also one api call or three api calls again that depends on use case and performance testing. Three separate calls to get data from three separate tables or join table query again it depends in use case. Don't do this,
"Do a SELECT *, iterate over all the columns, and copy them to the JSON that will be sent back to the frontend." As the data grows you will end up thrashing memory and unnecessary network data transfers. Basically do work where there is lots of memory. |