title: Performance of postgresql with large numbers of tables (EG: 1 million tables)?
body: <p>What is the largest number of tables that can be within a single pgsql database while still retaining good performance, given that pgsql stores 1 file per table on the filesystem and searches the pg_catalog for every query to do query planning?</p>
  
  <p>EG: Can pgsql deal with 1 million tables within a single database? Assume that the filesystem used is ext4 and each table contained very little data, so the overage disk storage size isn't an issue. The issue really comes from (1) impact of having 1 million files on the filesystem and (2) impact of having 1 million entries in pg_catalog.</p>
  
  <p>From this thread (2005), <a href="http://postgresql.1045698.n5.nabble.com/GENERAL-Maximum-number-of-tables-per-database-and-slowness-td1853836.html" rel="noreferrer">http://postgresql.1045698.n5.nabble.com/GENERAL-Maximum-number-of-tables-per-database-and-slowness-td1853836.html</a> - it is said below (but I do not how much of this is still applicable these days):</p>
  
  <p>Benjamin Arai wrote: </p>
  
  <blockquote>
    <p>What is the current maximum number of tables per database?  Also, does 
    having more tables slow down performance in any way? </p>
  </blockquote>
  
  <p>For most cases, the answer is no. However, once you get near 6 figure 
  table counts, pg_catalog ends up being pretty massive. The problem is 
  that the query planner must check pg_catalog for every query to see what 
  indexes are available, what the statistics &amp; value distributions are, 
  etc. in order to build the optimal plan. At some point, a really large 
  pg_catalog can begin to bog down your system.</p>
  
  <p>...</p>
  
  <p>William Yu &lt;[hidden email]> writes: </p>
  
  <blockquote>
    <p>Benjamin Arai wrote: </p>
    
    <blockquote>
      <p>What is the current maximum number of tables per database?  Also, does 
      having more tables slow down performance in any way? </p>
    </blockquote>
    
    <p>For most cases, the answer is no. However, once you get near 6 figure 
    table counts, pg_catalog ends up being pretty massive. </p>
  </blockquote>
  
  <p>You also have to think about the performance implications of having tens 
  of thousands of files in your database directory.  While some newer 
  filesystems aren't fazed by that particularly, a lot of 'em bog down on 
  lookups when there are more than a few thousand entries in a directory. </p>