I've found surprisingly little information/opinion on this.
Is serialization preferable to happen directly on the sql (in this case postgresql) layer (selecting specific columns) or is it perfectly fine in terms of performance to first fetch the whole record (all columns) and then format the data later in the application layer?
In a NodeJS typescript scenario I'd personally love to serialize on the application layer via simple functional util functions, with certain TS interfaces defining exactly what the final data will look like. This approach would be pretty lightweight and modular in my opinion, however, I'm wondering if there are scenarios where this is not advisable?
Would it matter if we fetch 100 records? Would it matter if perhaps there are 100 columns per row? My understanding is that the main burden for a db is the actual fetching of data, the querying and joining based on complex filters, or does it also make a noticeable difference how many columns you query per record?
To give a graspable code example:
const user = await User.findOne({ where: { email }, attributes: ['email', 'username', 'id'] });
// which will be converted to:
SELECT email, username, id FROM users WHERE email = 'some email';
vs.
const user = await User.findOne({ where: { email } });
// which will be converted to:
SELECT * FROM users WHERE email = 'some email';
// serialization happens afterwards on the backend app layer
const serializedUser = serializeUser(user);
I guess you mean "project" when you say "serialize".
Database performance is negatively affected if you always fetch all columns instead of only the ones you need. This has several reasons:
more data has to be transferred across the network
PostgreSQL has to disassemble ("deform") the whole table rows and cannot stop after the last required column
PostgreSQL cannot skip de-toasting large attributes that are compressed or stored out of line
PostgreSQL has no chance to eliminate unnecessary joins (if you have any in your queries)