Re: Large objects oids

David Wall <d(dot)wall(at)computer(dot)org> writes:
> Tom Lane wrote:
>> Yup, and in practice you'd better have a lot less than that or assigning
>> a new OID might take a long time.

> What's a rough estimate of "a lot less"? Are we talking 2 billion, 3
> billion, 1 billion?

It's difficult to say --- the assignment problem occurs if there's a
very long run of consecutive OIDs in use in the table beginning right
at the spot where the OID counter currently is (having wrapped around
one or more times since those objects were created). So the question is
what is the probability of that. The fact that the same OID counter
feeds everything (system objects, TOAST objects, and LOs) means that any
given catalog probably hasn't got tremendously long runs, but it could
happen.

Personally I'd feel uncomfortable designing a system that expected to
have more than a few million LOs per database, but that's just a guess.

>> They are, but this isn't relevant to large objects. The uniqueness
>> requirement is only per-catalog.
>>
> Isn't there just one catalog per postmaster instance (pg_catalog)?

Sorry, by "catalog" I meant "any table with a unique OID column".
For purposes of this discussion you could consider each database's
pg_largeobject table to be one such catalog.

> The
> issue we have is that one postmaster runs a large number of databases
> (let's say 100 for easy calculations), so even with the max 4 billion
> potential OIDs, that would drop each DB to 40 million each.

No, because the OIDs needn't be unique across databases. The situation
you describe would actually be best-case as long as the LO creation
activity is well-intermixed across the databases, because that would
minimize the probability of a long run of consecutive OIDs being
assigned to LOs within any one database. If it was really well-mixed
you could probably approach a billion LOs per DB without noticing any
problems.

regards, tom lane