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

On 24/04/15 05:24, Tom Lane wrote:
> Stephen Frost <sfrost@snowman.net> writes:
>> * Bruce Momjian (bruce@momjian.us) wrote:
>>> On Sun, Feb  1, 2015 at 03:54:03PM +0100, Álvaro Hernández Tortosa wrote:
>>>> 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).
>>> We don't report the maximum number of tables per database, or the
>>> maximum number of TOAST values.  Agreed?
>> For my 2c, this limitation is a surprise to users and therefore we
>> should add documentation to point out that it exists, unless we're going
>> to actually fix it (which is certainly what I'd prefer to see...).
> TBH, I've got very little enthusiasm for fixing this given the number
> of reports of trouble from the field, which so far as I recall is zero.
> Álvaro's case came up through intentionally trying to create an
> unreasonable number of tables, not from real usage.  This thread likewise
> appears to contain lots of speculation and no reports of anyone hitting
> a problem in practice.
    It is certainly true that this was a very synthetic case. I 
envision, however, certain use cases where we may hit a very large 
number of tables:

- Massive multitenancy
- Aggressive partitioning
- Massive multitenancy with aggressive partitioning
- Software dynamically generated tables, like those created by ToroDB 
(https://github.com/torodb/torodb). In ToroDB we generate tables 
depending only on the input data, so we may end up having as many as 
required by the datasource. For example, a "general purpose" json 
datastore may generate several tables per document inserted.

>
> Certainly this is likely to become an issue at some point in the future,
> but I'm not finding it very compelling to worry about now.  By the time
> it does become an issue, we may have additional considerations or use
> cases that should inform a solution; which seems to me to be a good
> argument not to try to fix it in advance of real problems.  Perhaps,
    I understand this argument, and it makes sense. However, on the 
other side, given the long time it may take from patch to commit and 
then release version to companies finally using it in production, I'd 
rather try to fix it soon, as there are already reports and use cases 
that may hit it, rather than wait three years until it explodes in our 
faces. After all, 640Kb RAM is enough, right? So maybe 2B tables is not 
that far in the horizon. Who knows.
    Regards,
    Álvaro


-- 
Álvaro Hernández Tortosa


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