Move data to a different tablespace in PRODUCTION environment

[neiljo10]

Hi,

Is it feasible to move columns to a tablespace ?

We generally move the LOB columns to a different tablespace. 

What are your thoughts on it ?

[slonkar]

Hi @neiljo10,

In Postgres, you can keep the tables and indexes to different tablespaces and not the particular column.

The better way is to have the heavily used indexes, in your case LOB column indexes to separate tablespace which will be on fast SSD drives.

Regards,

Sudhir

[neiljo10]

Hi @slonkar 

In Oracle the query is something like,

alter table TBL1 move lob (BLOB column) store as (tablespace TBSP1);

I hope I am correct when stating that this command is moving a table's column to a tablespace.

I want something like this to happen in PostGres, is there any query or method to do it ?

[slonkar]

Hi @neiljo10,

We cannot move single column to the tablespace, its only applicable to tables and indexes. We can only move the tables and indexes on the tablespaces.

Regards,

Sudhir

[BrandonBrooks]

---

@Dissertation wrote:

A database's data is collectively stored in the datafiles that constitute each tablespace of the database.  need help  for example, the simplest Oracle and sap  database would have one tablespace and one datafile. Another database can have three tablespaces, each consisting of two datafiles in better ways  

---

Thanks for tips.