PostgreSQL: What is the maximum number of tables can store in postgreSQL database?

Question

Q1: What is the maximum number of tables can store in database?

Q2: What is the maximum number of tables can union in view?

Answer 1

Q1: There's no explicit limit in the docs. In practice, some operations are O(n) on number of tables; expect planning times to increase, and problems with things like autovacuum as you get to many thousands or tens of thousands of tables in a database.

Q2: It depends on the query. Generally, huge unions are a bad idea. Table inheritance will work a little better, but if you're using constraint_exclusion will result in greatly increased planning times.

Both these questions suggest an underlying problem with your design. You shouldn't need massive numbers of tables, and giant unions.

Going by the comment in the other answer, you should really just be creating a few tables. You seem to want to create one table per phone number, which is nonsensical, and to create views per number on top of that. Do not do this, it's mismodelling the data and will make it harder, not easier, to work with. Indexes, where clauses, and joins will allow you to use the data more effectively when it's logically structured into a few tables. I suggest studying basic relational modelling.

If you run into scalability issues later, you can look at partitioning, but you won't need thousands of tables for that.

Answer 2

Both are, in a practical sense, without limit.

The number of tables a database can hold is restricted by the space on your disk system. However, having a database with more than a few thousand tables is probably more an expression of an incorrect analysis of your application domain. Same goes for unions: if you have to union more than a handful of tables you probably should look at your table structure.

One practical scenario where this can happen is with Postgis: having many tables with similar attributes that could be joined in a single view (this is a flaw in the design of Postgis IMHO), but that would typically be handled at the application side (e.g. a GIS).

Can you explain your scenario where you would need a very large number of tables that need to be queried in one sweep?