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?

Answer 1

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.

Answer 2

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)