On 24/04/15 05:24, Tom Lane wrote:
Stephen Frost <sfr...@snowman.net> writes:
* Bruce Momjian (br...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
