Is there a performance gain in concatenating strings directly into SQL?

Question

Actually, I have a code kind of like this:

const users = await query(`
  SELECT "id", "username", "email" FROM "users";
`)
  .then((users) => users.map((user) => {
    user.id = btoa(`user:${user.id}`);
    return user;
  });

The goal of this is to convert all sequential and numerical Ids (which I need in the database) into Ids that contain the entity name in a string, so that they can be consumed by the client, so that there are no collisions in the users cache.

In this context, I recently wondered if I could make this type of operation more performative, since it should be done at all times that any entity, of any table, is sent to the customer.

Another alternative would be to perform this encoding directly through the SELECT, sort of like this:

SELECT
  encode(concat('user:', "id")::bytea, 'base64') as "id",
  "username",
  "email"
FROM "users";

This type of operation would be more performative than doing it directly on Node.js?

Answer 1

First I have to say that these things are not easy to define like this, it is not something that can be easily inferred, just testing to know.

Second, you need to see if you really need this performance, doing something that doesn’t bring clear advantages isn’t worth the effort. This seems to be a minimal change that will change almost nothing.

Third, I think that if there are other solutions that can give a much better result, for example keeping data cache in the client, or even not using web that limits what can be done or even because the chosen language is not ideal for performance. And I say this because changing language will make an absurdly greater impact than making that change, just to give a parameter of how irrelevant where the data is concatenating is. What’s more, if this level of gain is necessary the code should be less abstract and use pure mechanisms that allow a better performance than it is using. In general it makes no difference, but it does more than change the concatenation of place.

It would be different if you were dealing a lot of information to the customer and a lot of stuff would be discarded.

Have you tested that await Are you helping or disturbing? I see a lot of people start using this because they read somewhere that’s cool and I’ve seen a lot of people get worse results because contrary to popular belief it’s slower than synchronous, the difference is that it doesn’t block the application when it’s running something asynchronous, although the await, as the name says, stands by. There are gains in some cases not at all.

In such an operation there is a huge amount of computational activity involving even much slower hardware, so the concatenation is "cosquinha" in the whole, mainly the way it is written.

I would guess that for all this you will have a tiny gain if you do in the database, but it depends on a lot of things, even it can go better in one scenario and not in another, or change in the future according to the version of Node or Postgresql you are using. In the end only testing in your real scenario, nor is it worth doing a test in a different environment than it will actually run (same load and data including).

There are some types of operation that the database can be much more performatic. There are some rare cases that make in the client can be faster, although making in the client can be more useful to reduce complexity of query, since she doesn’t traffic data anymore because of this.