What is the maximum number of tables that can be in the same pgsql database, while maintaining good performance, given that pgsql stores 1 file in a table in the file system and looks for pg_catalog for each request to schedule requests?
EG: Can pgsql process 1 million tables in one database? Suppose the file system used is ext4, and each table contains very little data, so the size of the excess disk storage is not a problem. The problem is really related to (1) the effect of having 1 million files in the file system and (2) the effect of having 1 million records in pg_catalog.
From this topic (2005), http://postgresql.1045698.n5.nabble.com/GENERAL-Maximum-number-of-tables-per-database-and-slowness-td1853836.html - said below (but I do not know as far as this still applies days):
Benjamin Aray wrote:
What is the maximum number of tables per database? Also, having more tables slows down anyway?
In most cases, the answer is no. However, as soon as you get close to 6 digits of the table, pg_catalog ends up being pretty massive. The problem is that the query planner must check pg_catalog for each query to see what indexes are available, what are the statistics and distribution of values, etc., in order to build an optimal plan. At some point, a really big pg_catalog might start to mess up your system.
...
[ ] > :
:? , , ?. , 6 , pg_catalog .
:
? , , ?
. , 6 , pg_catalog .
, . , , .
. CREATE TABLESPACE . pg_catalog, , , .
CREATE TABLESPACE
( ) pg_catalog.
(, , ) . , .
, question, , .
: " ". "- "? ?
: ? !
: ? .
, , , ( ), db. , , , , , . , , , , , , . .
, ( ) . , pg_class oid relnamespace, relname, . UNIQUE, , , ( ), .
, , . , .
, , .
TL dr: Expect performance issues with lots of tables. Expect them to be creative to resolve.