Re: [GENERAL] 4B row limit for CLOB tables

On 2/3/15 9:01 AM, Tom Lane wrote:
> Matthew Kelly <mkelly@tripadvisor.com> writes:
>> However, I do have active databases where the current oid is between 1 billion and 2 billion.  They were last
dump-restoredfor a hardware upgrade a couple years ago and were a bit more than half the size.  I therefore can imagine
thatI 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
triesto accomplish ~8,000,000 index scans inside of GetNewOidWithIndex.  Even partitioning doesn’t protect you from
thispotential 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
mitigatesthe risk of an unexpected lock up on reasonably sized tables/partitions, and provides a functional work around
forthose 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