I have a huge transaction table which I need to split. Right now am thinking on monthly basis. But I want this to be handled automatically by grails or by hibernate. It is similar to Sharding where a new table would be created and data belonging to a month will be inserted in that months table. whenever I give a select query with a date range, the appropriate months are queried and data is retrieved. It would differ from the classical Sharding in that there is no limit on the data in each table and separation is based only on the month index. I saw that there is a grails Sharding plugin, but it looks like it is used for something different?
Right now I have created this structure of 12 tables manually and for every select I get the date range and get a list of month domains to query on and retrieve individually and then again combine this data..
I want to know if I am not using something out of the box instead of doing the whole implementation manually
Thanks in advance Priyank
You don't mention which database you're using.
Sounds like a lot like table partitioning, supported by databases like Oracle and Postgres.
This kind of feature is best implemented within the database and hidden from the application. For example test systems rarely need partitioned tables.
Update:
Googling also found a sharding plugin for grails, with article describing its use.