Schemas vs partitioning vs multiple databases for archiving - Mailing list pgsql-general

Dear mailing list.

My current application make use of partitioning by creating a new child table which holds transaction records for every month. I’ve notice that after a couple of months depending on the hardware at some of our clients the inserts become very slow. The reason memory. I don’t want to delete old child tables even though they may be queried seldom and we can’t upgrade memory since most clients are far and remote.

I’m in the design faze of a new GUI and DB layout, what are my options.

Create a DB for each month.

Create a Schema for each month. Example

Schema layout

Public.schema (will have all tables and the current months transaction table)

Jan2012.schema (This will just have the archive transaction table for Jan 2012)

Feb2012.schema

Mrt2012.schema

I’ve red a couple of articles regarding data warehousing but they don’t mention schema’s to split large transaction tables.

Will multiple schema’s solve my problem ?

Regards