Re: Can not create more than 32766 databases in ufs file system.

Not sure that this really belongs on pgsql-committers - maybe pgsql-hackers?

No matter what scheme PostgreSQL uses for storing the data, there can be
underlying file system limitations. One solution, for example, would be
to use a file system that does not have a limitation of 32k
subdirectories. Although ext3 and/or ufs has this limit - ext4 has
removed this limited.

There are many ways PostgreSQL could work around this problem - your
suggestion of using sub-directories being one of them - but what happens
if this causes performance degradation for existing users, due to the
extra file system lookups required on every access?

Another solution would be to store everything in the same file.

In any case, I think this would be a significant architecture change for
something that sounds like a bad idea. I would expect having 32k
databases to have significant performance degradations in other ways. In
particular, I am thinking about having to open a file descriptor for
each of these files. What sort of database architecture requires 32k
databases or tables for the same PostgreSQL instance? Have you
considered having an additional field for your primary key and combining
several tables into one?

Cheers,
mark

On 09/12/2009 02:49 PM, fulan Peng wrote:
> Hi, pgsql-committers!
>
> I cannot created more than 32766 databases with freeBSD in one setup,
> not as the document says, "as many as you like".
> I found the problem is that the directory pgsql/data/base cannot hold
> more than 32766 subdirectories.
> I suggest to make 32766 subdirectories in base directory, say /base/0,
> /base/1, .... /base/32765. Then in each subdirectory to put the
> database description.
> This way, we can have 32766x32766 databases. This is kind of "as many
> as you like".
> The ZFS system is not a solution for me. It is snail slow.
>
>

--
Mark Mielke<mark(at)mielke(dot)cc>