-
* write vs read tables: some people have tables that are 99% write activity (log tables for example) rather than your traditional read/write oltp types access patterns. in these cases it often makes sense to push these tables on to thier own tablespace if you can dedicate a tablespace for it. This is especially true if you have a situation like the next item…
* uneven performance in disk speeds: in many cases, when you add a second set of disks, it often is not the same hardware as the first set. in most cases there will be a difference in size, but quite often also a difference in speed, and also a difference in the ability to handle concurrent read/write activity. If your in a situation where you have a set of disks that may only be good at reads or writes, but not when done concurrently, it often makes sense to optimize your workload around that limitation.
* uneven needed response time in table access: some tables might need fast queries for quick user response, other tables may get larger/slower reporting/aggregation queries that aren’t expected to be fast. in both cases you may have similar numbers looking at tuples even though one may need more optimization than the other.
* uneven index usage patterns: index_tup_fetch is the combined access for all indexes on a table, however some usage patterns include heavily access indexes (like an index on a primary key) and then occasionally accessed indexes on another field (for example a timestamp column index that is only needed for occasional ad-hoc queries). if you think you might have a situation like this, and think splitting up your indexes might be beneficial, you can get information on specific index usage patterns by looking at pg_stat_all_indexes.
Tablespace Management Variables
I was catching up on my blog reading this morning and ran across a very nice article from depesz on [http://www.depesz.com/index.php/2007/09/30/finding-optimum-tables-placement-in-2-tablespace-situation/ balancing workloads across tablespaces]. It’s actually a very good article, if you haven’t read it I recommend it. One thing that stuck out to me was that he seemed to have a well defined, and very straight-forward, use case and needs assesment. In my experience, you can’t always count on things being that nice, so I have some additional thoughts on things you should keep in mind when you start looking at tablespaces and begin to determine how best to use them.