Postgresql Performance Tweak – Immutable

In PostgreSQL, every function has one of the possible volatility classifications: volatile, static and immutable. When it is not specified, PostgreSQL assumes that it is a volatile function, which is not always the case.

functions with side-effects.
Immutable: pure functional functions.
Stable: smth in between. (wow, such explanation, very clear *applause*)

Yes, IMHO stable is utterly error-prone and you need to consult to the documentation. Sorry that TL;DR was inadequate 😦 ..(but.. it is supposed to be inadequate by definition right?? whatever..)

The moment of discovery of immutable

So, I’d like to draw your attention to immutable functions before this post gets too long.  When declaring a function, using the immutable keyword actually implies the following:

  • I solemnly swear that my function does not modify the db.
  • It does not do any database lookups.
  • Given the same arguments, it ALWAYS returns the same output.

BEWARE: As stated in the PostgreSQL documentation, your function should be volatile if you use anything like random(), currval(), timeofday(), current_timestamp(), etc. in your function.

Reader: “So, like.. when shall I use immutable functions? “

Recently, we had to implement a hash function and it was much more appropriate to do the job in PL / pgSQL. So it was all sunshine and lollipops, the hash function came out well etc. But unfortunately it was a tad bit slow. To fix this issue, we just altered the volatility classification of some helper functions. For example, a function that converts characters into ascii values was perfectly suitable to use the immutable keyword!

Point of Story Points

*Reader: ughh the obligatory ‘I hate agile, it is useless’ post again..*

Sooooo, it’s been like a year and a half since I have started working in a professional working environment and I have yet to understand what story points stand for.

“It’s not about the time, it is about the effort!” Yeah, I have been warned about this many times already. And as team members, we do not necessarily have to agree on the effort of a story either.

The other day I was trying to load 700k records from a database using a tool called datatables and ajax server-side processing. For the record, I am eager to learn new technologies but my learning curve is kinda pathetic. I am just a junior and I have been hired as a C++ developer. Whatever, I took the task with the hopes of learning something new..

*3 days later*
*Meanwhile, we put cute little dots on post-its..*

“It’s not about the time, it is about the effort!”

“Sooo.. what have we done concerning this task?”
Me: “Well..erm.. I am done with loading the records, I have managed to fix that bug bla bla. But we can’t insert/delete/search yet. But the rest should be easier as I kinda understood how datatables works.”
PM: “*not pleased* hmm.. ok, but we need to finish and deploy it asap. It is a 5-point task, can you add insertion/deletion mechanisms today?”….

Then he begins to creep on my screen every now and then..
I’m sorry, wut?  – _ –

OK, I get it the team gave that task 5 points in the meeting, and I respect that ofc. But instead of a much more experienced team member, if I take that task, it will naturally take much more TIME & EFFORT. Considering every one of us is a unique being, what is the point of story points? Is it merely a tool for managers?