3 ms·

What are the tradeoffs of making the data denormalization happen in database triggers? Fundamentally, you want to update certain fields whenever certain other f

What are the tradeoffs of making the data denormalization happen in database triggers? Fundamentally, you want to update certain fields whenever certain other fields get changed, and that's exactly what database triggers do. I'm not enough of a database wizard to know if there's good reasons why this isn't more popular, though.

snuxoll 10y ago
Too many people argue "code doesn't belong in the database", but honestly, with everyone circling around to FRP it honestly makes me think maybe stored procedures and database triggers are going to make a comeback again. I never really bought into the whole "but you can't version stored procedures!" bit because that's precisely what schemas and the search path in PostgreSQL are for (triggers are another matter entirely, but I avoid those whenever possible - though it's exactly what I would use for a task like this).
- buckbova 10y ago
  Triggers are synchronous and is not much different than the application updating this value when the underlying data is updated, except for the reliability that always when the data is updated the trigger will for sure fire. Postgres triggers are row by row so depending on your use cases they can be quite slow. Ideally I'd cache all these values and invalidate the global cache through the application. This would require one set of services or each set of services managing their own cache. Anytime a room is updated, boot and reload the cache for that room. Let the db stay normalized.
anonetal 10y ago
It's not very efficient if you have a large number of triggers. There was an early research prototype called Triggerman (http://ieeexplore.ieee.org/document/754942/ http://ieeexplore.ieee.org/document/754942/) which attempted to scale triggers, but most implementations are still clunky (probably because of the transactional guarantees required by triggers). Materialized views (that the article talks about) is a related concept, but only a few databases really support that. Both of these are also related to the work on 'Stream Databases', where the problems of efficient recomputation are central. There is a ton of work on this in academic research (http://www.springer.com/gp/book/9783540286073 http://www.springer.com/gp/book/9783540286073), but it hasn't been able to gain a foothold in the data management market (several of the academic research projects transitioned into startups).
kpil 10y ago
Materialized views where invented for moving computations into the updates. No triggers needed. Not sure what databased that supports it though.

snuxoll 10y ago

Too many people argue "code doesn't belong in the database", but honestly, with everyone circling around to FRP it honestly makes me think maybe stored procedures and database triggers are going to make a comeback again. I never really bought into the whole "but you can't version stored procedures!" bit because that's precisely what schemas and the search path in PostgreSQL are for (triggers are another matter entirely, but I avoid those whenever possible - though it's exactly what I would use for a task like this).

buckbova 10y ago
Triggers are synchronous and is not much different than the application updating this value when the underlying data is updated, except for the reliability that always when the data is updated the trigger will for sure fire. Postgres triggers are row by row so depending on your use cases they can be quite slow. Ideally I'd cache all these values and invalidate the global cache through the application. This would require one set of services or each set of services managing their own cache. Anytime a room is updated, boot and reload the cache for that room. Let the db stay normalized.

anonetal 10y ago

It's not very efficient if you have a large number of triggers. There was an early research prototype called Triggerman (http://ieeexplore.ieee.org/document/754942/ http://ieeexplore.ieee.org/document/754942/) which attempted to scale triggers, but most implementations are still clunky (probably because of the transactional guarantees required by triggers). Materialized views (that the article talks about) is a related concept, but only a few databases really support that. Both of these are also related to the work on 'Stream Databases', where the problems of efficient recomputation are central. There is a ton of work on this in academic research (http://www.springer.com/gp/book/9783540286073 http://www.springer.com/gp/book/9783540286073), but it hasn't been able to gain a foothold in the data management market (several of the academic research projects transitioned into startups).

kpil 10y ago

Materialized views where invented for moving computations into the updates. No triggers needed. Not sure what databased that supports it though.