Обсуждение: limits?

On Jun 23, 2008, at 10:56 AM, Kynn Jones wrote:

>
>
>
>
> How can I find the limits (if any) on things such as the maximum
> number of tables, views, indices, columns-per-table, size of
> database, etc.?
>
> (At the moment I'm particularly interested any limits that my exist
> on the numbers of tables and views that may exist in any one
> database.)

http://www.postgresql.org/about/ has some of the theoretical limits.

In real use you're unlikely to hit any limits, theoretical or
practical, but if you start to use a silly number of tables and so on
you're likely to hit performance issues eventually. I'm not sure where
that threshold would be, but it's higher than "thousands".

Cheers,
   Steve

http://www.postgresql.org/about/

On Mon, 2008-06-23 at 13:56 -0400, Kynn Jones wrote:
>
>
>
>
>
>
>
> How can I find the limits (if any) on things such as the maximum
> number of tables, views, indices, columns-per-table, size of database,
> etc.?
>
>
> (At the moment I'm particularly interested any limits that my exist on
> the numbers of tables and views that may exist in any one database.)
>
>
> TIA!
>
>
> Kynn
>

On Mon, 2008-06-23 at 11:21 -0700, Steve Atkins wrote:
>
>
> http://www.postgresql.org/about/ has some of the theoretical limits.
>
> In real use you're unlikely to hit any limits, theoretical or
> practical, but if you start to use a silly number of tables and so
> on
> you're likely to hit performance issues eventually. I'm not sure
> where
> that threshold would be, but it's higher than "thousands".
>
> Cheers,
>    Steve

I'm just a developer (my DBA would have more details on the settings
that needed adjusting), but I will attest to it being reasonably easy to
hit database limits.

For us, it was related to having a reasonably small number of (large)
aggregate tables that are partitioned (inherited with check constraints)
by week.

The real problem wasn't the absolute limit of tables (IIRC) as much as
accessing the data in a parallel manner from the parent tables in bulk
update transactions (the parallel was probably what pushed it over the
top).

The limit is absurdly high, and by the time you hit it, you'll probably
have a really good idea of how to overcome it.  Really, there's lots of
"challenges" you'll overcome before that time (IMO).

Of course all this is anecdotal, and you should take it with a grain of
salt. :)

-Mark

"Kynn Jones" <kynnjo@gmail.com> writes:
> Actually, the DB I have in mind would certainly be approaching "silly
> territory."  I'm looking at a schema with around 10 thousand tables (or
> views).  Unfortunately, as far as I can tell,
> http://www.postgresql.org/about/ says nothing about maximum number of
> tables.

There is no hard limit (at least not till you hit the 16TB size limit on
pg_class or pg_attribute...).  In practice this number tends to be
constrained by the operating system, not Postgres.  How well does your
OS do with tens of thousands of entries in a single directory?

Generally, though, schema designs like this amount to manually replacing
leading columns of an index key with separate tables, and they're
usually bad style and a PITA to work with.  If you have a desperate need
to partition the data then you might have to go that way, but you should
think twice.

            regards, tom lane

On Mon, Jun 23, 2008 at 2:45 PM, Kynn Jones <kynnjo@gmail.com> wrote:
> Actually, the DB I have in mind would certainly be approaching "silly
> territory."  I'm looking at a schema with around 10 thousand tables (or
> views).

What kind of app would require such a schema? Just curious...

Steve Atkins wrote on 23.06.2008 20:21:
  > In real use you're unlikely to hit any limits, theoretical or practical,

I imagine that the 1GB column-value limit is something that could be reached
though. Especially for BLOB (aka bytea) or CLOB (aka text) columns.

Thomas

Thomas Kellerer wrote:
> Steve Atkins wrote on 23.06.2008 20:21:
>  > In real use you're unlikely to hit any limits, theoretical or practical,
>
> I imagine that the 1GB column-value limit is something that could be
> reached though. Especially for BLOB (aka bytea) or CLOB (aka text) columns.

No, since they are stored out of band (toast tables). For *lob there is
a lob type which stored the data completely separate, not to be confused
with bytea and text (varchar).

Tino.

On Monday 23 June 2008 15:45:22 Kynn Jones wrote:
> On Mon, Jun 23, 2008 at 2:21 PM, Steve Atkins <steve@blighty.com> wrote:
> > In real use you're unlikely to hit any limits, theoretical or practical,
> > but if you start to use a silly number of tables and so on you're likely
> > to hit performance issues eventually. I'm not sure where that threshold
> > would be, but it's higher than "thousands".
>
> Actually, the DB I have in mind would certainly be approaching "silly
> territory."  I'm looking at a schema with around 10 thousand tables (or
> views).  Unfortunately, as far as I can tell,
> http://www.postgresql.org/about/ says nothing about maximum number of
> tables.  I suppose I could always find what this limit is "the hard way",
> by writing a script that just keeps creating empty tables and see where
> that goes, but I'd prefer not to do something like this...
>

http://people.planetpostgresql.org/greg/index.php?/archives/37-The-million-table-challenge.html

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL