[ Is there a limitation on the number of tables a PostgreSQL database can have? ]
I have created a database in PostgreSQL, let's call it testdb.
I have a generic set of tables inside this database, xxx_table_one, xxx_table_two and xxx_table_three.
Now, I have Python code where I want to dynamically create and remove "sets" of these 3 tables to my database with a unique identifier in the table name distinguishing different "sets" from each other, e.g.
The reason I want to do it this way is to keep multiple LARGE data collections of related data separate from each other. I need to regularly overwrite individual data collections, and it's easy if we can just drop the data collections table and recreate a complete new set of tables. Also, I have to mention, the different data collections fit into the same schemas, so I could save all the data collections in 1 set of tables using an identifier to distinguish data collections instead of separating them by using different tables.
I want to know, a few things
- Does PostgreSQL limit the number of tables per database?
- What is the effect on performance, if any, of having a large number of tables in 1 database?
- What is the effect on performance of saving the data collections in different sets of tables compared to saving them all in the same set, e.g. I guess would need to write more queries if I want to query multiple data collections at once when the data is spread accross tables as compared to just 1 set of tables.
PostgreSQL doesn't have many limits, your hardware is much more limited, that's where you encounter most problems. http://www.postgresql.org/about/
You can have 2^32 tables in a single database, just over 4 billion.
- PostgreSQL doesn't impose a direct limit on this, your OS does (it depends on maximum directory size)
- This may depend on your OS as well. Some filesystems get slower with large directories.
- PostgreSQL won't be able to optimize queries if they're across different tables. So using less tables (or a single table) should be more efficient
If your data were not related, I think your tables could be in different schema, and then you would use
SET search_path TO schema1, public for example, this way you wouldn't have to dynamically generate table names in your queries. I am planning to try this structure on a large database which stores logs and other tracking information.
You can also change your
tablespace if your os has a limit or suffers from large directory size.