Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux