Max# of tablespaces
Hello, I've checked the docs but cant seem to find if there is a max # of tablespaces allowed - I've come across a 9.5 env with 1600 tablespaces - they want to double that - Oracle's max is 64k, I'm not particularly worried about hitting a wall, if there is one , outside of maintenance issues - any assistance would be greatly appreciated. thanks
RE: Max# of tablespaces
Excellent - thanks for the fast response - it was an oracle dba that set it up initially so that may explain it - Thanks very much -Original Message- From: Tom Lane Sent: Sunday, January 3, 2021 12:27 PM To: Thomas Flatley Cc: pgsql-general@lists.postgresql.org Subject: Re: Max# of tablespaces Thomas Flatley writes: > Hello, I've checked the docs but cant seem to find if there is a max # of > tablespaces allowed - I've come across a 9.5 env with 1600 tablespaces - they > want to double that - Oracle's max is 64k, I'm not particularly worried about > hitting a wall, if there is one , outside of maintenance issues - any > assistance would be greatly appreciated. There's no particular hard limit, though you might start to run into OID-starvation problems at a billion or so tablespaces. On the other hand, it's important to realize that a Postgres tablespace doesn't really *do* anything. It's just a separate subdirectory. The only functional reason to use a tablespace is if you can place it on a separate filesystem. There is certainly value in being able to do that --- but I've never heard of systems having more than a few dozen filesystems mounted. Hence, the above issue sounds suspiciously like somebody is expecting Postgres tablespaces to do something they don't do. (I suppose if you are working on a system that has limits on the number of files per directory, or performance problems with large values of that, then you could use tablespaces as a workaround. But TBH you'd be better off moving onto a more modern platform.) regards, tom lane
RE: Max# of tablespaces
I don’t, but I didn’t set up the env As far as I can tell, each tablespace is a partition, and I assume they felt this was the best way to perform partition maintenance - again, I don’t know , -Original Message- From: Andreas Kretschmer Sent: Sunday, January 3, 2021 11:52 AM To: pgsql-general@lists.postgresql.org; Thomas Flatley ; pgsql-general@lists.postgresql.org Subject: Re: Max# of tablespaces On 3 January 2021 13:59:31 CET, Thomas Flatley wrote: >Hello, I've checked the docs but cant seem to find if there is a max # >of tablespaces allowed - I've come across a 9.5 env with 1600 >tablespaces - they want to double that why on earth do you think you will need so many tablespaces? They have an other meaning than in oracle. -- 2ndQuadrant - The PostgreSQL Support Company
RE: Max# of tablespaces
I agree - it requires a re-think/re-build As for oracle, quite easy to add tablepaces in flight, assuming you don’t hit max db_files I was more curious if there was an actual defined limit - oracle stops at 64K , and their old application release would have 2tbsp per module, and at 400 or so that’s a hassle -Original Message- From: Christophe Pettus Sent: Tuesday, January 5, 2021 5:02 PM To: Thomas Flatley Cc: Andreas Kretschmer ; pgsql-general@lists.postgresql.org Subject: Re: Max# of tablespaces > On Jan 5, 2021, at 13:55, Thomas Flatley wrote: > > As far as I can tell, each tablespace is a partition, and I assume they felt > this was the best way to perform partition maintenance - again, I don’t know > , It's a very common Oracle-ism to have a lot of tablespaces, in part because (IIRC) Oracle makes it an incredible pain in the neck to add tablespaces once the DB is in use. For sharding purposes, you probably want schemas in PostgreSQL instead of tablespaces, although having that many schemas is going to not be optimal, either. -- -- Christophe Pettus x...@thebuild.com
