Postgres Pro
Downloads
Home   >   mailing lists

Thread: Maximum Number of Tables in a database

Maximum Number of Tables in a database

From
"Brubaker, Shane"
Date:

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

Re: Maximum Number of Tables in a database

From
Tom Lane
Date:
"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

PL/pgSQL

From
Heni Lolov
Date:
Hi there
I have a problem that I can not solve.
So is there any way to return a couple of rows from a PL/pgSQL function. I need
this because I have to execute a query with undefined depth of subqueryes (not
more than 10). The searched table represents a tree. And I have to find all sub
branches og a given branch.
here is the table:

CREATE SEQUENCE grp_id_seq;
CREATE TABLE grp (
    id INT PRIMARY KEY NOT NULL DEFAULT nextval('grp_id_seq'),
    name VARCHAR NOT NULL,
    info VARCHAR,
    type_id INT DEFAULT NULL REFERENCES grp_types(id),
    mastergrp_id INT REFERENCES grp(id)
);

I need it to use it in a query like this:

SELECT foo FROM footable WHERE groups IN sub_grp(some_grp_id);

Any idea how to make it?

Thanks in advance!

Hal

__________________________________________________
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.com