2

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,

  1. 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.
  2. 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?
  3. any solution, comment, approach, suggestion are welcome and appreciated.
András Váczi
  • 31,778
  • 13
  • 102
  • 151
Gnanam
  • 987
  • 2
  • 9
  • 16

2 Answers2

5

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.

3

I think that in this case you are better off optimizing I/O generally, and trying to ensure you have a lot of RAM in your server than moving the tables onto another tablespace. If the tables are more frequently accessed and there is enough memory they will likely mostly be kept in memory and the WAL is the only file flushed to disk on commit anyway.

More memory will make the most difference. The frequently accessed pages will be more likely to be in the queue and so disk I/O will be reduced. That's the big one.

Creating additional tablespaces can also create other problems. If these are on other RAID arrays, you are dividing up your total throughpout between both arrays, and so everything will be slower. On the other hand, if you add RAM there is less disk I/O in the first place.

Chris Travers
  • 13,112
  • 51
  • 95