On 2/3/15 9:01 AM, Tom Lane wrote:
Matthew Kelly <mke...@tripadvisor.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
