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.