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: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.
Can I simply adopt the naive approach of updating an EHR metadata table
within a transaction in every partition addition/deletion operation?
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
