Postgresql: Storing a table index on a separate disk to the data

Question

I have development a postgresql 9.5 database which I originally set up on an SSD. However, as this project will grow to ~200TB (and likely more), I have moved my current test of ~200GB on to a HDD, which will better represent the hardware that will likely be used in the real database, when it is built.

The main tables that are queried are currently 110 million and 785 million rows, and almost all the time is spent on table index scans. Therefore, I would like to know if it is possible (and if so how) to store all the table indexes on an SSD, and the actual raw data on the HDD? I believe this would allow me to keep the improved read performance of the SSD on the index scans, and be able to keep the large volume of data on cheaper HDDs.

Answer 1

You can change the tablespace of the index explicitly with alter index command.

CREATE TABLESPACE superfastssdtablespace LOCATION '/path/to/super/fast/ssds';
ALTER INDEX name_of_the_index SET TABLESPACE superfastssdtablespace;

Before you do that you may (also) want to look at partitioning your table or using BRIN indexes depending on the use case.