Thread: 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
On 3 January 2021 13:59:31 CET, Thomas Flatley <FLATLEYT@outlook.com> 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
Thomas Flatley <FLATLEYT@outlook.com> 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.5env with 1600 tablespaces - they want to double that - Oracle's max is 64k, I'm not particularly worried about
hittinga 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
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 <tgl@sss.pgh.pa.us>
Sent: Sunday, January 3, 2021 12:27 PM
To: Thomas Flatley <FLATLEYT@outlook.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Max# of tablespaces
Thomas Flatley <FLATLEYT@outlook.com> 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.5env with 1600 tablespaces - they want to double that - Oracle's max is 64k, I'm not particularly worried about
hittinga 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
separatesubdirectory.
The only functional reason to use a tablespace is if you can place it on a separate filesystem. There is certainly
valuein 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
withlarge 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
On Sun, Jan 3, 2021 at 05:37:52PM +0000, Thomas Flatley wrote: > Excellent - thanks for the fast response - it was an oracle dba that set it up initially so that may explain it - Agreed. It was probably done that way for an invalid reason and should be cleaned up. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
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 <andreas@a-kretschmer.de> Sent: Sunday, January 3, 2021 11:52 AM To: pgsql-general@lists.postgresql.org; Thomas Flatley <FLATLEYT@outlook.com>; pgsql-general@lists.postgresql.org Subject: Re: Max# of tablespaces On 3 January 2021 13:59:31 CET, Thomas Flatley <FLATLEYT@outlook.com> 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
> On Jan 5, 2021, at 13:55, Thomas Flatley <FLATLEYT@outlook.com> wrote: > > As far as I can tell, each tablespace is a partition, and I assume they felt this was the best way to perform partitionmaintenance - 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 inthe neck to add tablespaces once the DB is in use. For sharding purposes, you probably want schemas in PostgreSQL insteadof tablespaces, although having that many schemas is going to not be optimal, either. -- -- Christophe Pettus xof@thebuild.com
It's certainly the "I only know bash" method of determining which partition growing the fastest. On 1/5/21 3:55 PM, Thomas Flatley wrote: > 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 partitionmaintenance - again, I don’t know , > > -----Original Message----- > From: Andreas Kretschmer <andreas@a-kretschmer.de> > Sent: Sunday, January 3, 2021 11:52 AM > To: pgsql-general@lists.postgresql.org; Thomas Flatley <FLATLEYT@outlook.com>; pgsql-general@lists.postgresql.org > Subject: Re: Max# of tablespaces > > On 3 January 2021 13:59:31 CET, Thomas Flatley <FLATLEYT@outlook.com> 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. -- Angular momentum makes the world go 'round.
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 have2tbsp per module, and at 400 or so that’s a hassle -----Original Message----- From: Christophe Pettus <xof@thebuild.com> Sent: Tuesday, January 5, 2021 5:02 PM To: Thomas Flatley <FLATLEYT@outlook.com> Cc: Andreas Kretschmer <andreas@a-kretschmer.de>; pgsql-general@lists.postgresql.org Subject: Re: Max# of tablespaces > On Jan 5, 2021, at 13:55, Thomas Flatley <FLATLEYT@outlook.com> wrote: > > As far as I can tell, each tablespace is a partition, and I assume they felt this was the best way to perform partitionmaintenance - 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 inthe neck to add tablespaces once the DB is in use. For sharding purposes, you probably want schemas in PostgreSQL insteadof tablespaces, although having that many schemas is going to not be optimal, either. -- -- Christophe Pettus xof@thebuild.com