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

On 31/01/15 14:55, Roger Pack wrote:
> [...]
>
> 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.
> OK so "system stability" doesn't degrade per se when it wraps [since
> they all use that GetNewOid method or similar [?] good to know.
>
> So basically when it gets near 4B TOAST'ed rows it may have to wrap that
> counter and search for "unused" number, and for each number it's
> querying the TOAST table to see if it's already used, degrading
> performance.

The problem here is that performance degrades exponentially, or
worse. Speaking here from experience, we already tested this for a very
similar case (table creation, where two oids are consumed from a global
sequence when inserting to pg_class). Have a look at
http://www.slideshare.net/nosys/billion-tables-project-nycpug-2013,
slides 43-45. We tested there this scenario and shown that table
creations per second dropped from 10K to a few per second and then to a
few per day. In the graphs you can't even realize there were more tables
been created. At around 8K tables from the theoretical limit of 4B oids
consumed, the process basically stopped (doing more insertions).

Hope that this information helps.

Best regards,

Álvaro

--
Álvaro Hernández Tortosa

-----------
8Kdata