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

* 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).
>
> I had a look at our FAQ about Postgres limitations and I don't see
> anything that needs changing:
>
>     https://wiki.postgresql.org/wiki/FAQ#What_is_the_maximum_size_for_a_row.2C_a_table.2C_and_a_database.3F
>
>     Maximum size for a database? unlimited (32 TB databases exist)
>     Maximum size for a table? 32 TB
>     Maximum size for a row? 400 GB
>     Maximum size for a field? 1 GB
>     Maximum number of rows in a table? unlimited
>     Maximum number of columns in a table? 250-1600 depending on column types
>     Maximum number of indexes on a table? unlimited
>
> 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...).

As for the other discussion on the thread, having a per-table sequence
would be far better as it'd reduce the wrap-around risk quite a bit and,
further, we should be able to provide that without breaking the on-disk
format.  What would be really nice is a way to expand the size of the
key when needed- in other words, instead of wrapping around, if we
actually hit 4B toasted values in a table then stick a flag somewhere
for the next toasted value that says "this value is in the second toast
table/fork" and then go up to 4B on that one, etc.  That allows us to
support more than 4B toasted values and doesn't require searching for
holes in the values assigned.

If we end up with empty toast tables eventually, then allow reusing
them.  Perhaps vacuum can even be used to make a note somewhere saying
"this toast table is now empty and can be reused".

In the end, I'd like to think we can do better here than having a hard
limit at 4B when it comes to how many values over a few KB we can store.
As mentioned, that isn't all that much these days.  I'm not saying that
my proposal or what's been proposed upthread is an answer, but I've
certainly build PG systems which store over 4B rows and it's not hard to
imagine cases where I might have wanted a toasted value for each of
those rows.
Thanks,
    Stephen