Ours is a web-based application (built on multi-tenant architecture) running PostgreSQL v9.1.3. There are about 450 tables in our application, out of which 2-3 tables, specific to a module in the application, have huge volume of records stored compared to other remaining tables and are heavily accessed (both read and write operations) by application Users.
To give a picture/statistics on the volume of records available, currently there are 8 million records in one table and 3 million records in another table. We're expecting a immediate increase/traffic in the volume of transaction (again both read and write transactions) to these tables in the near future, since we're coming up with some exciting features within this specific module.
My question with requirements are,
- because we're expecting a huge traffic to this specific module, we don't want to let down the Users accessing other modules in the application to be affected because of any performance issues that it may cause, though.
- separating/isolating heavily accessed tables is one solution that came off the top of my head. Is it a good idea to separate/move to a different database? What are the pros and cons of this approach?
- any solution, comment, approach, suggestion are welcome and appreciated.
Best Answer
You will gain a lot more with regards to performance if you move the table to a different harddisk. As long as the "busy" table and the rest are located on the same disk, moving that table into a different "file" (by moving it into a different database) won't change anything with regards to (I/O) performance.
Distributing the I/O load to a different harddisk (and a different harddisk controller) will most probably give you a better performance for the remaining data because it is not affected by the I/O done on the busy table.
To move the table to a different harddisk, you need to create a new tablespace (which of course is located on that disk) and then move that table to that tablespace.