0

I'm working on optimizing disk I/O for a PostgreSQL database by distributing data, indexes, WAL files, and logs across separate SSDs. Here's my current disk setup:

  • /dev/sda for data
  • /dev/sdb for WAL and logs
  • /dev/sdc for indexes

Challenges and Questions

  1. Existing Indexes: My database already contains data and indexes. I want to move the existing indexes into a new tablespace located on /dev/sdc without affecting the current data location. What are the steps to achieve this?

  2. Future Indexes: For any new indexes created in the future, how can I ensure they automatically use the new tablespace on /dev/sdc without manual intervention, while keeping table data on /dev/sda?

Additional Considerations

  • What are the potential caveats or issues I might encounter by separating indexes and data onto different disks?
  • Are there specific PostgreSQL settings or best practices I should follow to optimize this configuration effectively?

I'm looking for detailed guidance or examples on setting up this environment, as well as insights into any possible performance implications or maintenance challenges that might arise from such a setup.

Improve this question
1
  • Do the simple and most effective thing to spread the I/O: create a volume that is striped across all three disks and use that for everything. Still, create three file systems: data, WAL and log. That way, they cannot fill each other's disk.
    – Laurenz Albe
    Commented Aug 28 at 8:09

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.