Sven Willenberger <sven@dmv.com> writes:
> 3) Each month:
> CREATE newmonth_dynamically_named_table (like mastertable) INHERITS
> (mastertable);
> modify the copy.sql script to copy newmonth_dynamically_named_table;
> pg_dump 3monthsago_dynamically_named_table for archiving;
> drop table 3monthsago_dynamically_named_table;
A number of people use the above approach. It's got some limitations,
mainly that the planner isn't super bright about what you are doing
--- in particular, joins involving such a table may work slowly.
On the whole I'd probably go with the other approach (one big table).
A possible win is to use CLUSTER rather than VACUUM ANALYZE to recover
space after your big deletes; however this assumes that you can schedule
downtime to do the CLUSTERs in.
regards, tom lane