On 2/2/15, José Luis Tallón <jltallon(at)adv-solutions(dot)net> wrote:
> On 01/31/2015 12:25 AM, Jim Nasby wrote:
>> [snip]
>> It's a bit more complex than that. First, toast isn't limited to
>> bytea; it holds for ALL varlena fields in a table that are allowed to
>> store externally. Second, the limit is actually per-table: every table
>> gets it's own toast table, and each toast table is limited to 4B
>> unique OIDs. Third, the OID counter is actually global, but the code
>> should handle conflicts by trying to get another OID. See
>> toast_save_datum(), which calls GetNewOidWithIndex().
>>
>> Now, the reality is that GetNewOidWithIndex() is going to keep
>> incrementing the global OID counter until it finds an OID that isn't
>> in the toast table. That means that if you actually get anywhere close
>> to using 4B OIDs you're going to become extremely unhappy with the
>> performance of toasting new data.
>
> Indeed ......
>
>> I don't think it would be horrifically hard to change the way toast
>> OIDs are assigned (I'm thinking we'd basically switch to creating a
>> sequence for every toast table), but I don't think anyone's ever tried
>> to push toast hard enough to hit this kind of limit.
>
> We did. The Billion Table Project, part2 (a.k.a. "when does Postgres'
> OID allocator become a bottleneck").... The allocator becomes
> essentially unusable at about 2.1B OIDs, where it performed very well at
> "quite empty"(< 100M objects) levels.
>
> So yes, using one sequence per TOAST table should help.
> Combined with the new SequenceAMs / sequence implementation being
> proposed (specifically: one file for all sequences in a certain
> tablespace) this should scale much better.
But it wouldn't be perfect, right? I mean if you had multiple
deletion/insertions and pass 4B then the "one sequence per TOAST
table" would still wrap [albeit more slowly], and performance start
degrading the same way. And there would still be the hard 4B limit.
Perhaps the foreign key to the TOAST table could be changed from oid
(32 bits) to something else (64 bits) [as well the sequence] so that
it never wraps? What do you think? And would a more aggressive change
like this have a chance of being accepted into the code base?
Thanks.
-roger-