Hibernate : Splitting table automatically every month

What about having the data split across several tables like:

• current values (up to a week) - table latest_values
• also older values (up to a year) - table year_values
• all values (complete history) - table all_values

Insert always into the latest_values and have rules push them to the other ones as well.

On a daily or weekly basis you run a "DELETE FROM latest_values WHERE insert_date < CURRENT_DATE - 7". On a monthly basis you run "DELETE FROM year_values WHERE insert_date < CURRENT_DATE - 365" (leap years don't matter here).

This way you keep tables small and can retrieve more current values easily, and still have all values at hand should a query need them.

Splitting every week into a separate table (that's how I understand your idea) would leave you with too many tables and complicated - thus slow - UNION operations if you need data from many weeks.

You can add a new column like period. This will store the year-month row was added.This will act as partitioning.When you run query for recent data you can specify which period (YYYYMM) you are looking for . This will reduce overhead which you are facing right now.

If you actually want to have partitioned DB which is best solution for this problem check out this PostgresSQL DOC