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

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.

My 2c.

Regards,

/ J.L.