Re: plpgsql versus domains

On 2/28/15 11:26 PM, Tom Lane wrote:
> Also, instrumenting the code showed that TypeCacheConstrCallback gets
> called quite a lot during the standard regression tests, which is why
> I went out of my way to make it quick. Almost all of those cache flushes
> are from non-domain-related updates on pg_type or pg_constraint, so
> they're not really necessary from a logical perspective, and they're
> surely going to hurt performance for heavy users of domains. I think to
> fix this we'd have to split pg_constraint into two catalogs, one for table
> constraints and one for domain constraints; which would be a good thing
> anyway from a normal-form-theory perspective. And we'd have to get rid of
> pg_type.typnotnull and instead store domain NOT NULL constraints in this
> hypothetical domain constraint catalog. I don't plan to do anything about
> that myself right now, because I'm not sure that production databases
> would have the kind of traffic on pg_type and pg_constraint that the
> regression tests exhibit. But at some point we might have to fix it.

FWIW, my experience running a low-downtime website and supporting DDL
during normal operations (ie: no maintenance windows) is that by far the
biggest concern is acquiring locks. Once you have the locks, taking an
extra second for the actual DDL isn't that big a deal (and I suspect
you'd need to do a LOT of DDL to add up to that).

Likewise, after piling up waiting for a DDL lock to release, I really
doubt the extra sinval workload is going to matter much. If you're
pushing the hardware that hard I doubt you'd be able to do online DDL
for a slew of other reasons.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com