I was making a new Postgres database table and I decided to do UUID primary keys instead of auto-incremented integers as I had done in the past. There are some interesting discussions around why UUIDs are a good idea in spite of their increased size.
When I wrote the query for insertion, I defaulted to letting Postgres do the work for UUID creation.
I didn’t have a good reason for doing this other than the habit of letting the database handle the primary key creation. But, the more I thought about it, the more it made sense to generate the UUID in Node (this was a Node.JS, Postgres stack). I wouldn’t have to wait for the Postgres
INSERT query to complete to use the generated UUID elsewhere. If I needed this UUID in multiple places, I could fire off several queries at once or more easily build out a transaction. But, is there a performance difference between the two? I’m not a great Postgres guy, but with the help from Stack Overflow, I cobbled together this method.
PERFORM just executes this function, but discards the result. Oh, and I had to activate
\timing to show the duration. So I ran this a few times, and my results:
Seems slow. If anyone has another way of testing this on Postgres please let me know. On to Node.
I’m using the uuid npm module. The
Date objects and comparing. Anywho, the results:
Dang, we’re talking 10x faster! Granted, this isn’t a perfect test. I ran this on my Mac laptop which isn’t going to have the same hardware as a server. But, it’s hard to imagine the disparity being that much different on different hardware. Also, my project will probably never involve cranking out rapid fire UUIDs at this rate, but it’s fascinating nonetheless. Time to create some UUIDs in Node!
Jason Owen wrote a very nice follow up that includes timing UUID generation from the pgcrypto extension which I was unaware of. He also made some good points on the RETURNING clause, and pre-optimization. Go read it!