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.)
regards, tom lane