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

Ah, thanks a lot for the Select .. For Update clue/reminder! The partitions
are actually going to arrive from different systems (lab reports coming
from lab equipment and nurses entering bedside data at the same time etc).
So I'll have to be defensive about concurrency.

Best regards
Seref

On Tue, Aug 28, 2012 at 2:14 PM, Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> wrote:

> 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
>