Is it better to generate "UUID" and "TIMESTAMP" within the NodeJS application or using the database built-in functions?

best_of_man

2/6/24, 8:57 PM

I am writing a TypeScript-NodeJS application and want to handle object ids and created_at TIMESTAMP within the NodeJS application, instead of using MySQL or Cassandra built-in UUID or TIMESTAMP generator.

1- First of all I like to know is this a good idea to generate id and created_at values within a web server application instead of letting databases generate them?

2- Secondly, I want to know if I use database's built-in functions like uuid() or toTimestamp(now()) in Cassandra and UUID_TO_BIN(UUID()) in MySQL will this add more overhead/latency to my application compare to using NodeJS uuid() library?

315

1 + 0

mysql

resource-management

uuid

cassandra

node.js

Score:3

Server

Rick James

2/6/24, 10:18 PM

Re MySQL or MariaDB:

Mostly the performance difference is negligible. In general, fetching a row is much more costly than any 'standard' function being used in it.

If you do need the value in both places (client and server), then it would be more convenient for you to compute it in one place.

MySQL 8.0 and MariaDB 10.7 have an optimization on UUID that is probably not available in any client -- this rearranges the bits in UUID to make them work somewhat like TIMESTAMP, thereby improving "locality of reference" for a variety of actions. Without this, UUIDs are bad for performance in huge tables. Since the optimization only works for Type 1, it is probably not available to the client.

TIMESTAMPs cannot be trusted to be unique. Hence, they should not be used [alone] in a PRIMARY KEY.

UUIDs are useful when you have multiple clients independently creating ids. Even so, usually, an auto_inc is often just as easy.

+ 6

best_of_man

2/6/24, 10:27 PM

So you say if we have big tables with millions of rows, it's better to use simple auto increment integer value for the `id` column that is also the tables primary key?

Rick James

2/6/24, 11:38 PM

@best_of_man - If you need to fabricate a PK, then auto_increment is better in many ways. If you already have a unique column (or combination of columns), that is often even better. If you provide more details about the table and its usage, we might be able to advise in more detail.

best_of_man

2/7/24, 12:03 AM

I have a table like `CREATE TABLE IF NOT EXISTS auth ( id INT AUTO_INCREMENT UNIQUE PRIMARY KEY, email VARCHAR(64) UNIQUE NOT NULL, password VARCHAR(64) NOT NULL, username VARCHAR(64) UNIQUE NOT NULL, admin INT NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); `

Rick James

2/7/24, 1:41 AM

@best_of_man - You won't be accessing that table a hundred times a second, so I am not worrying about performance. It is a toss-up of whether to have `id` or use `username` as the PK. Do other tables `JOIN` to this table?

best_of_man

2/7/24, 3:03 AM

What if this is a table for a big app like Instagram? I think with billions of user they try to reach out this table hundred of times per second. (At the moment I am not sure about the join statement. I am not that experienced professional programmer and am working on my self startup and try to learn as much as I can and do my best during my first application creation).

Rick James

2/7/24, 5:06 AM

A platform like that has thousands of servers sharing the load. Do plan on revising the schema, the code, and everything else, before you get your millionth user.

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: Is it better to generate "UUID" and "TIMESTAMP" within the NodeJS application or using the database built-in functions?

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.