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 -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
