Hibernate version: 3.2.0beta9a

Name and version of the database you are using: PostgreSQL 8.X

I'm trying to solve a problem that centers around the issue of data size. I have a side database which I am using to store what we call "transaction" information. Each transaction has two things associated with it, a chunk of data and a version. Each of these is a separate table.

Right now we have about 1500 "shares" that transactions occur on. There can be thousands of chunks and version for a single transaction and a share can have multiple transactions per day. We are trying to keep a set (the amount directly depends on the data size) of this transaction information so that we can analyze their characteristics etc. What this all adds up to is approximately 8 million rows of Chunk information a day (and 8 million of the Version too) across all 1500 shares. We are expecting growth in our business and therefore the number of shares will continue to increase, and along with it the data we're trying to save. with our estimates of needing a minimum of 22 days of history to properly analyze this information we're looking at the chunk table containing approximately 180 million rows.

This will impact performance greatly. I've come up with one solution and am looking for advice on that solution, and any advice as to another solution that I could try.

1: Using Postgres' ability to Partition database tables into a series of smaller tables divide up the data on a per-costumer basis. This way the table size is limited. The constraints placed on the tables will handle the proper insertions etc so that Hibernate doesn't really see what's going on under the hood. This seems great, and the only hurdle I see is the creation of new tables when a customer is first encountered so that the data has someplace to go.

Does anyone have any advice as to how to use hibernate/jdbc to dynamically create a table that inherits from the main table in Postgres?

This above is my first solution and seems like a good approach, the only hard part is the above question.

2: Is there a way to dynamically create a table via hibernate so that I can avoid using database partitioning? If these doesn't seem like a good idea that is fine, I just have been unable to uncover anything in the hibernate documentation/forums that indicates hibernate has the ability to dynamically create tables during runtime.

Please any advice would be appreciated as to what kind of a solution might be best in the situation described above.