Re: PostgreSQL Limits and lack of documentation about them.

On 10/30/18, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> On 26 October 2018 at 11:40, Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>
> wrote:
>> On Fri, Oct 26, 2018 at 9:30 AM David Rowley
>> <david(dot)rowley(at)2ndquadrant(dot)com>
>> wrote:
>>>
>>> For a long time, we documented our table size, max columns, max column
>>> width limits, etc. in https://www.postgresql.org/about/ , but that
>>> information seems to have now been removed. The last version I can
>>> find with the information present is back in April this year. Here's a
>>> link to what we had:
>>>
>>> https://web.archive.org/web/20180413232613/https://www.postgresql.org/about/
>>>
>>> I think it's a bit strange that we don't have this information fairly
>>> early on in the official documentation. I only see a mention of the
>>> 1600 column limit in the create table docs. Nothing central and don't
>>> see mention of 32 TB table size limit.
>>>
>>> I don't have a patch, but I propose we include this information in the
>>> docs, perhaps on a new page in the preface part of the documents.
>>
>>
>> I also try to find such limits of PostgreSQL, but I couldn't find it.
>> +1 to add them to docs.
>
> I've attached a very rough patch which adds a new appendix section
> named "Database Limitations". I've included what was mentioned in [1]
> plus I've added a few other things that I thought should be mentioned.
> I'm sure there will be many more ideas.

David,
Thanks for doing this. I haven't looked at the rendered output yet,
but I have some comments on the content.

+ <entry>Maximum Relation Size</entry>
+ <entry>32 TB</entry>
+ <entry>Limited by 2^32 pages per relation</entry>

I prefer "limited to" or "limited by the max number of pages per
relation, ...". I think pedantically it's 2^32 - 1, since that value
is used for InvalidBlockNumber. More importantly, that seems to be for
8kB pages. I imagine this would go up with a larger page size. Page
size might also be worth mentioning separately. Also max number of
relation file segments, if any.

+ <entry>Maximum Columns per Table</entry>
+ <entry>250 - 1600</entry>
+ <entry>Depending on column types. (More details here)</entry>

Would this also depend on page size? Also, I'd put this entry before this one:

+ <entry>Maximum Row Size</entry>
+ <entry>1600 GB</entry>
+ <entry>Assuming 1600 columns, each 1 GB in size</entry>

A toast pointer is 18 bytes, according to the docs, so I would guess
the number of toasted columns would actually be much less? I'll test
this on my machine sometime (not 1600GB, but the max number of toasted
columns per tuple).

+ <entry>Maximum Identifier Length</entry>
+ <entry>63 characters</entry>
+ <entry></entry>

Can this be increased with recompiling, if not conveniently?

+ <entry>Maximum Indexed Columns</entry>
+ <entry>32</entry>
+ <entry>Can be increased by recompiling
<productname>PostgreSQL</productname></entry>

How about the max number of included columns in a covering index?

> I'm not so sure about detailing limits of GUCs since the limits of
> those are mentioned in pg_settings.

Maybe we could just have a link to that section in the docs.

--
-John Naylor