I am developing an application which uses a PostgreSQL database which is located in a specific folder of a hard disk. I think that maybe it is a good idea to reorganize the different objects that compose it and change the location of tables, indexes and constraints. For example, tables can be split among different hard disks and apply this same criteria with other indexes and constraints. I am guessing this approach could improve DB functionality but I do not have a solid base for my idea. Lets suppose that, after some time, I decide to move some tables and indexes to another (one or more) storage unit(s). How do I perform this change? Should I create a new DB that reference such new locations (Tablespaces), backup the original one and restore it to the new one? If this is so then it would be a big job to create every object unless there is a tool to generate a script with the necessary SQL commands to build the new DB.
I will appreciate any comment about my issue.
Respectfully,
Jorge Maldonado