09 January 2009

Database Trigger vs. ActiveRecord Callback

Out of curiosity, I wanted to compare the performance of an ActiveRecord "after_save" callback versus a PostgreSQL "AFTER INSERT" trigger.

I prototyped the functionality in Rails. I got the logic clean and simple. Next, I ported that logic to PL/pgSQL. I wrote a few time-related functions to keep the code clean but it was identical in flow and logic to the Rails code.

Lastly, I ran the the callback and trigger forms of the business logic.
The measurement here is the time it took for the POST action to complete. I know that it is imperfect but, in this case, its a good proxy because, ultimately, the point of this is to enhance responsiveness to the end-user. All other things are equal except for how this one chunk of business logic is implemented.
  • ActiveRecord callback- 181750ms
  • Database Trigger - 93729ms
So, that works out to a 93% decrease in execution time of the Ruby on Rails action when implemented with a database trigger. Honestly, for no particular reason I expected the trigger form of the logic to blow away the ActiveRecord form. 93% is whopping but I was unrealistically expecting something much faster (illogical, I know).

1 comment:

  1. 93% is a huge difference, but you went from 18 to 9 which is only half. Measuring the POST time here does not make sense because you should be concerned with measuring only the thing that is changed -- the business logic encapsulated by the callback or trigger. Before changing the mechanism, however, you often want to make sure your algorithm is correct, i.e. no n*m joins and using distinct to remove duplicates, etc. Clean and simple may not matter if your queries need to do full table scans.