Re: Is it nonsense (read: stupid) to keep count of child entries via triggers and a custom table?

On 08/28/2012 08:56 PM, Seref Arikan wrote:
> Can I simply adopt the naive approach of updating an EHR metadata table
> within a transaction in every partition addition/deletion operation?

Absolutely. That's a classic trade-off; pay the cost of maintaining a
materialized view at INSERT/UPDATE/DELETE time, in exchange for faster
access in frequent queries that're otherwise unacceptably expensive.

It *is* a trade-off, like any performance choice. Careful work is also
required to handle concurrency issues correctly.

I do the same thing in much smaller (tiny, even) databases where I have
expensive queries I want to respond before the user noticed they were
waiting. For example, in a parent->child relationship I sometimes
maintain a summary table with a 1:1 relationship with the parent that
summarizes the children.

It's usually a good idea to keep your summary tables clearly separate as
trigger-maintained materialized views, rather than updating "real"
entities with summary info too. You avoid churn on your "real" tables,
avoid some interesting lock ordering issues, etc.

Some explicit locking with `SELECT ... FOR UPDATE` can be important to
avoid unexpected concurrency issues.

--
Craig Ringer