Thread: How to expand existing table-space

How to expand existing table-space

From
Ravi Krishna
Date:
If the FS associated with the existing FS is reaching capacity, and
sys-admins do not want to expand the FS any more and are advising us
to use a new FS, what is the best practice in this case.  Since PG
does not have concept of adding data files to existing tablespace, how
do we add space with no outage.

Thanks.


Re: How to expand existing table-space

From
Igor Neyman
Date:

-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Ravi Krishna
Sent: Tuesday, May 26, 2015 2:53 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] How to expand existing table-space

If the FS associated with the existing FS is reaching capacity, and sys-admins do not want to expand the FS any more
andare advising us to use a new FS, what is the best practice in this case.  Since PG does not have concept of adding
datafiles to existing tablespace, how do we add space with no outage. 

Thanks.


--

I see no other way, but create new TS on the new FS and move objects (all or some of them) from old into new TS.

Regards,
Igor Neyman


Re: How to expand existing table-space

From
Ravi Krishna
Date:
On Tue, May 26, 2015 at 3:03 PM, Igor Neyman <ineyman@perceptron.com> wrote:

> I see no other way, but create new TS on the new FS and move objects (all or some of them)
> from old into new TS.

But isn't moving objects from old to new TS an offline job (the table
is locked).

This leads to obvious conclusion that in PG, no single table should be
allowed to grow beyond a reasonable limit.

Is this being addressed in future versions, like allowing a new
storage path to existing tablespace.


Re: How to expand existing table-space

From
Tom Lane
Date:
Ravi Krishna <sravikrishna3@gmail.com> writes:
> Is this being addressed in future versions, like allowing a new
> storage path to existing tablespace.

As far as PG is concerned, a tablespace is just a directory.  Space
provisioning should be addressed at the level of the filesystem.

            regards, tom lane


Re: How to expand existing table-space

From
Ravi Krishna
Date:
On Tue, May 26, 2015 at 3:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Ravi Krishna <sravikrishna3@gmail.com> writes:
>> Is this being addressed in future versions, like allowing a new
>> storage path to existing tablespace.
>
> As far as PG is concerned, a tablespace is just a directory.  Space
> provisioning should be addressed at the level of the filesystem.

I guess one easy way for large tables is to break it up to small
tables via inheritance and each table assigned to a different
tablespace.


Re: How to expand existing table-space

From
Scott Ribe
Date:
On May 26, 2015, at 1:54 PM, Ravi Krishna <sravikrishna3@gmail.com> wrote:
>
> I guess one easy way for large tables is to break it up to small
> tables via inheritance and each table assigned to a different
> tablespace.

That’s also a way you could migrate your current table without disrupting production.

Partitioning like that is often done as a performance optimization; but it sounds like you’re proposing that a file
systemcannot be expected to handle a large table in piece??? I don’t understand that. 

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice







Re: How to expand existing table-space

From
Ravi Krishna
Date:
> That's also a way you could migrate your current table without disrupting production.
>
> Partitioning like that is often done as a performance optimization; but it sounds like you're proposing that a file
systemcannot be expected to handle a large table in piece??? I don't understand that. 

Let us say that that the sys admins have set a limit of 1TB per FS.
This automatically implies that a single table can not grow more than
1TB, unless it is broken into smaller tables via inheritance and each
child table goes to a separate table-space. I hope I am clearer now.


Re: How to expand existing table-space

From
Scott Ribe
Date:
On May 26, 2015, at 2:33 PM, Ravi Krishna <sravikrishna3@gmail.com> wrote:
>
> Let us say that that the sys admins have set a limit of 1TB per FS.
> This automatically implies that a single table can not grow more than
> 1TB, unless it is broken into smaller tables via inheritance and each
> child table goes to a separate table-space. I hope I am clearer now.

Yes. There’s an externally-imposed limit, and you just have to deal with it, regardless of how idiotic it might be ;-)

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice