Re: File count restriction of directory limits number of relations inside a database.

Hi,

On 4.4.2015 21:24, sudalai wrote:
> Hi,
> We have a requirement to have multiple schemas in a database.Each 
> schema will have all application tables and hold a set of users
> data. We have segmented data across different schemas. We
> dynamically increase the schemas as in when user signs up. So the
> table files created across all schemas resides in a single folder
> under data/base/16546/***. We can't find any restriction to number of
> relation(table,index) on a database in postgresql.

PostgreSQL is using 32-bit object IDs, so technically you can have up to
2^32 objects (tables, indexes). You'll certainly run into other issues
before hitting this limit, though.

> But Number of files in a folder in linux is restricted in our file
> system what we use currently 32k/64K (ext2 / ext4). To overcome this
> we create a folders inside a database folder and associate as table
> space for each schema but it breaks the streaming replication in
> standby server which we use currently.

I'm pretty sure ext4 allows more directory entries. For example I just
did this on my ext4 partition, within a new directory:
 $ for i in `seq 1 1000000`; do touch $i.file; done

and guess what, I do have 1M files there:
 $ ls | wc -l 1000000

So either you're using some old version of ext4, or you've used some
strange parameters when creating the filesystem.

> To overcome this We changed the postgresql-9.4.0 source code to
> create a sub directory "my_<oid of tablespace>" inside the location
> given in create tablespace statement and take that location as
> tablespace location. Now we specify one common location to create
> multiple tablespaces, on both slave and master because postgresql
> creates a tablespace on subdirectory(my_<oid of tablespace>).
>
> Since I'm not an expert in postgresql, i can't assure that it will 
> not affect other functionality of postgres. Please help me, i
> changed the method named "create_tablespace_directories" in a file
> "tablespace.c".

This seems extremely foolish, IMNSHO.

Firstly, I'm not convinced there actually is a problem - you haven't
posted any error messages demonstrating the existence of such ext4
limits, you only claimed they exist. I've created a directory on ext4
with 1M files in it, without any problem.

Secondly, even if there is such problem on your system, it's most likely
by using improper options when creating the ext4 filesystem. Solving
this at the PostgreSQL level is a poor solution.

And finally - no one is going to confirm that your changes are safe,
without a detailed review of your patch. But that would take a lot of
time, and it seems you're solving an artificial problem, so I'd be
surprised if anyone invests the time into reviewing this.

In other words, you should probably fix the filesystem configuration,
not PostgreSQL code.

-- 
Tomas Vondra                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services