Thread: Multiple Tablespaces with single mount point?
Hello,
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
Thank you.
-CuTech
Hello,I want to know what is the general practice when only one mount point is available.1. Use default tablespace2. Use Default + User Define one tablespace3. Use Default + User Defined Data + User Defined Index tablespaces
1.
Using non-default tablespace adds backup/restore complications. Don't do that without a demonstrable benefit. You will not be able to demonstrate a benefit when you only have a single filesystem.
David J.
Thank you. I was referring to this link and that's what made me think, what if in the future scenario.
On Fri, Aug 12, 2016 at 2:07 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
Hello,I want to know what is the general practice when only one mount point is available.1. Use default tablespace2. Use Default + User Define one tablespace3. Use Default + User Defined Data + User Defined Index tablespaces1.Using non-default tablespace adds backup/restore complications. Don't do that without a demonstrable benefit. You will not be able to demonstrate a benefit when you only have a single filesystem.David J.
Even otherwise, is the general recommendation is to store the data and xlog on different volumes a preferred way?
Thank you.
On Fri, Aug 12, 2016 at 2:14 PM, Curious Tech <cutech777@gmail.com> wrote:
Thank you. I was referring to this link and that's what made me think, what if in the future scenario.On Fri, Aug 12, 2016 at 2:07 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:Hello,I want to know what is the general practice when only one mount point is available.1. Use default tablespace2. Use Default + User Define one tablespace3. Use Default + User Defined Data + User Defined Index tablespaces1.Using non-default tablespace adds backup/restore complications. Don't do that without a demonstrable benefit. You will not be able to demonstrate a benefit when you only have a single filesystem.David J.
Even otherwise, is the general recommendation is to store the data and xlog on different volumes a preferred way?
I'd say the general recommendation is to understand your needs and your options and make an informed decision. While I have an idea of the choices available I have no clue your experience with them nor do I have any comprehension of your needs.
David J.
Thank you David.
On Fri, Aug 12, 2016 at 2:48 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
Even otherwise, is the general recommendation is to store the data and xlog on different volumes a preferred way?I'd say the general recommendation is to understand your needs and your options and make an informed decision. While I have an idea of the choices available I have no clue your experience with them nor do I have any comprehension of your needs.David J.
On Sat, Aug 13, 2016 at 6:59 AM, Curious Tech <cutech777@gmail.com> wrote:
Hello,I want to know what is the general practice when only one mount point is available.1. Use default tablespace2. Use Default + User Define one tablespace3. 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
performance benefits.
Hope that helps !
Regards,
Venkata B N
Fujitsu Australia