pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Maximum Number of Tables in a database
2+ messages / 2 participants
[nested] [flat]

* Maximum Number of Tables in a database
@ 2002-07-25 00:29  Brubaker, Shane <Shane_Brubaker@affymetrix.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Brubaker, Shane @ 2002-07-25 00:29 UTC (permalink / raw)
  To: pgsql-admin; +Cc: Cawley, Simon <Simon_Cawley@affymetrix.com>

On the limitations page it states various maximums for PostgreSQL, but I
don't see an explicit reference to the maximum number of tables, a question
that has come up before.  

I'm guessing that the maximum number of tables is related to how much can be
stored in the pg_ tables which track the names and information of the
various fields in the database.

So, based on that, the maximum number of rows is unlimited and the maximum
size for a table is 64 TB.  So realistically, you would need an enormous
number (trillions) of tables to exceed that limit, and of course the actual
storage of those tables themselves would use up your disk long before you
ran out of room in the pg_ tables.

So I'm assuming there is no practical limit on the number of tables in a
given database.

In looking through the pg_ tables though, I don't see one that specifically
lists table names.  Is there one?  Is some information perhaps stored on
disk in the base, global, or clog areas?

We currently have a system with 100 GB of data and about 1000 tables.  We
are designing it such that there will be multiple machines in parallel, and
then within each machine a given Virtual table is broken down into many
smaller tables, so we will typically have about 1000-10,000 tables per
database.  

We'd be curious to hear of anyone who has pushed these limits further.

 

Thank You,

 

Shane Brubaker & the Transcriptome group

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Maximum Number of Tables in a database
@ 2002-07-25 15:31  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Brubaker, Shane <Shane_Brubaker@affymetrix.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2002-07-25 15:31 UTC (permalink / raw)
  To: Brubaker, Shane <Shane_Brubaker@affymetrix.com>; +Cc: pgsql-admin@postgresql.org, "Cawley, Simon" <Simon_Cawley@affymetrix.com>

"Brubaker, Shane" <Shane_Brubaker@affymetrix.com> writes:
> So I'm assuming there is no practical limit on the number of tables in a
> given database.

The practical limit depends on how well your kernel copes with
directories containing large numbers of files (since PG stores each
table as a file within the directory for the database).  Many Unixen
start to get pretty slow with more than a few thousand files in a
directory, because they do directory lookups by linear search.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2002-07-25 15:31 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-07-25 00:29 Maximum Number of Tables in a database Brubaker, Shane <Shane_Brubaker@affymetrix.com>
2002-07-25 15:31 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox