On Thu, 2009-10-22 at 03:13 -0700, Mike Christensen wrote:
> It has occurred to me that there might be some advantages of creating
> a separate table called "OrderArchive" which would be used to store
> order states 3 or 4. This would allow me to get rid of an index on
> order state as well as probably use different caching techniques on
> each table. It would also keep the Orders table super light and fast
> with only very volatile information.
The lightweight solution is to make your indexes partial indexes that
omit order states 3 and 4. If most of your queries use indexes, this
might be sufficient. The more complex solution is partitioning, which,
as you have found out, requires some manual labor and setting up some
triggers. But those are pretty much the standard solutions.