On 2/3/15 9:01 AM, Tom Lane wrote:
> Matthew Kelly <mkelly(at)tripadvisor(dot)com> writes:
>> However, I do have active databases where the current oid is between 1 billion and 2 billion. They were last dump-restored for a hardware upgrade a couple years ago and were a bit more than half the size. I therefore can imagine that I have tables which are keyed by ~8,000,000 consecutive oids.
>
>> I would argue that when it wraps there will be a single insert that will probably block for 2-5 minutes while it tries to accomplish ~8,000,000 index scans inside of GetNewOidWithIndex. Even partitioning doesn’t protect you from this potential problem.
>
> That may be a hazard, but ...
>
>> That being said I’d be perfectly happy merely giving each TOAST table its own sequence as that almost entire mitigates the risk of an unexpected lock up on reasonably sized tables/partitions, and provides a functional work around for those of us with larger than average installs.
>
> ... this "fix" would actually make things enormously worse. With the
> single counter feeding all tables, you at least have a reasonable
> probability that there are not enormously long runs of consecutive OIDs in
> any one toast table. With a sequence per table, you are nearly guaranteed
> that there are such runs, because inserts into other tables don't create a
> break.
>
> (This effect is also why you're wrong to claim that partitioning can't fix
> it.)
That's assuming that toasting is evenly spread between tables. In my
experience, that's not a great bet...
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com