Re: Time to move table to new tablespace

On 7/01/2012 10:52 PM, Jason Buberel wrote:
> I'm considering the migration of an existing large (2.3TB) table to a
> new tablespace. The table size, according to the '\dt+' command:
>
> public | city_summary | table | altosresearch | 2345 GB |
>
> Are there any considerations - besides the usual disk and network IO
> constraints - that I need to take into account when estimating the
> amount of time that would be required for the following commands to
> complete?
>
> psql> create tablespace 'newstorage' location '/some/new/path';
> psql> alter table city_summary set tablespace = 'newstorage';
>
> Any other recommendations are welcome, such as "You are an idiot for
> not using partitioning in the first place" :)

Maybe you should create a set of partitioned tables in the new
tablespace, copy your data over, then drop the old table and rename the
partition to the old table's name instead? If the big table keeps on
accumulating data (via INSERTs) you can add a trigger that mirrors all
updates to the partition while the copy runs.

This won't work so well if the big table has UPDATEs and DELETEs too,
since you can't delete or update records from a trigger before they've
been copied to the new table and committed.

--
Craig Ringer