Обсуждение: alter table set tablespace

I want to move one table from one disk to another.

In order to do this I wanted to create a new tablespace on the new disks and
issue the command alter table tablename set tablespace tablespacename;

The question is, will this in any way affect the database which is in heavily
use?

The table in question is the largest one approximately 90GB.

Thanks

Leif Gunnar Erlandsen

On Tue, 2010-07-27 at 20:38 +0000, Leif Gunnar Erlandsen wrote:
> I want to move one table from one disk to another.
>
> In order to do this I wanted to create a new tablespace on the new disks and
> issue the command alter table tablename set tablespace tablespacename;
>
> The question is, will this in any way affect the database which is in heavily
> use?
>
> The table in question is the largest one approximately 90GB.

Yes, it will have a performance impact. The table will be locked while
it's being moved to the new tablespace, preventing both reads and writes
until the operation is complete.

Regards,
    Jeff Davis

Jeff,

One way to address the indefinite locking due to an ALTER TABLE
statement for PostgreSQL is to use ChronicDB. It allows you to apply
such a schema change live, without bringing down the database.

The space requirements for applying the live schema change would be to
have at least twice as much space available on disk as the existing
database.


On Tue, 2010-07-27 at 14:19 -0700, Jeff Davis wrote:
> On Tue, 2010-07-27 at 20:38 +0000, Leif Gunnar Erlandsen wrote:
> > I want to move one table from one disk to another.
> >
> > In order to do this I wanted to create a new tablespace on the new disks and
> > issue the command alter table tablename set tablespace tablespacename;
> >
> > The question is, will this in any way affect the database which is in heavily
> > use?
> >
> > The table in question is the largest one approximately 90GB.
>
> Yes, it will have a performance impact. The table will be locked while
> it's being moved to the new tablespace, preventing both reads and writes
> until the operation is complete.
>
> Regards,
>     Jeff Davis
>
>