I want to know what is the general practice when only one mount point is available.
1. Use default tablespace
2. Use Default + User Define one tablespace
3. Use Default + User Defined Data + User Defined Index tablespaces
To make a decision on tablespaces, lot of factors need to be taken into consideration. The general recommendation is to store tables and indexes into separate tablespaces. Since you have only one mount point, based on your database and application behaviour, it may or may not be much beneficial from performance perspective. Below factors play an important role -
- Memory, Number of CPU cores and type of the disks
- Concurrent connections and queries
- Application behaviour is vital. If you have too many concurrent connections performing read/write operations on different
data sets at the same time, then, database tuning would not add much. It is important that, the application connections are streamlined and benchmarked
- If the database encounters high volume of transactions, then, pg_xlog on a separate mount point would fetch lot of