Pros and cons of database triggers vs Rails ActiveRecord callbacks?

Question

I am writing a program using Ruby on Rails and PostgreSQL. The system generates alot of reports which are frequently updated and frequently accessed by users. I am torn between whether I should use Postgres triggers to create the report tables (like Oracle materialized views) or the Rails built in ActiveRecord callbacks. Has anyone got any thoughts or experiences on this?

score 17 · Accepted Answer · answered Sep 08 '11 at 09:55

17

Callback are useful in the following cases:

Combine all business logic in Rails models which ease maintainability.
Make use of existing Rails model code
Easy to debug
Ruby code is easier to be written/read than sql "maintainability"

Triggers are useful in the following cases:

Performance is a big concern. It is faster than callbacks.

If your concern is ease and clean then use callbacks. If your concern is performance then use triggers.

answered Sep 08 '11 at 09:55

Mahmoud Khaled

6,226
6
37
42

What is the reason for the performance increase when using triggers? – yazz.com Sep 08 '11 at 14:41
1

Because in callback, you will connect to DB, however in triggers you won't need to connect to db you are already in db layer – Mahmoud Khaled Sep 08 '11 at 15:31
4

And very important, db triggers allow you to circumvent some concurrency issues related with conditions. Take for instance, the case where you are updating some column if some conditions. Considering you are not using these two conditions in the same transaction (which is very easy to do in rails), there is no guarantee your condition still holds when actually updating the column. – ChuckE May 10 '13 at 11:17
a disadvantage of using callbacks is that if it for some reason, you can't use your client to insert your record in the database, you might have an inconsistent record if you forgot to apply manually the required changes, I saw a lot of these problems in real-life systems – Abdel P. May 08 '20 at 16:20

score 9 · Answer 2 · answered Jan 15 '16 at 10:31

We had the same problem, and since this is an interesting topic, I'll elaborate based on our choice/experience.

I think the concept is more complex than what highlighted in the current answer.

Since we're talking about reports, I assume that the use case is updating of data warehousing tables - not a "generic" application (this assumption/distinction is crucial).

Firstly, the "easy to debug" idea is not [necessarily] true. In our case, it's actually counterproductive to think so.

In sufficiently complex applications, some types of callbacks (data warehousing updates/millions of lines of code/mid (or more) sized team) are simply impossible to maintain, because there are so many places/ways the database will be updated, that it will be practically impossible to debug missed callbacks.

Triggers don't have to be necessarily designed as the "complex and fast" logic. Specifically, triggers may also works as low-level callback logic, therefore being simple and lean: they would simply forward the update events back to the rails code.

To wrap up, in the use case mentioned, rails callbacks should be avoided like the plague.

An efficient and effective design is to have RDBMS triggers adding records to a queue table, and a rails-side queueing system, which acts upon them.

(Since this post is old, I'm curious about which has been the experience of the OP)

Pros and cons of database triggers vs Rails ActiveRecord callbacks?

2 Answers2