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

(removed -committers)

Mark,

* Mark Mielke (mark(at)mark(dot)mielke(dot)cc) wrote:
> No matter what scheme PostgreSQL uses for storing the data, there can be
> underlying file system limitations.

This is true, but there's a reason we only create 1GB files too. I
wouldn't be against a scheme such as described to minimize the impact to
PG of these limitations.

> 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?

Ehhh, it's likely to be cached.. Sounds like a stretch to me that this
would actually be a performance hit. If it turns out to really be one,
we could just wait to move to subdirectories until some threshold (eg-
30k) is hit.

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

eh?

> 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.

Actually, I think some of the changes to remove flatfiles might improve
our performance with large numbers of databases. I also don't see how
this would be a significant architecture change at all. If there are
still issues that make having lots of databases slow, we might want to
look into fixing those issues rather than saying "well, just don't do
that".

> 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?

I've got a ton of instances that have >32K tables. My approach is
generally to keep the number of databases low, while having lots of
schemas, but there are distinct downsides to that (specifically related
to hiding information.. something alot of people care about, but
thankfully I don't have to).

thanks,

Stephen