I am looking into ways to save some space with our audit tables, which have accumulated a lot of rows with nearly identical information. We use MySQL and InnoDB tables, and table compression doesn't work very well because it compresses a single row without accounting for duplication between rows.
An ideal solution seems to be a MERGE table with one table that can accept new revisions, and a second compress read only table with the remaining historical data. The compressed table could use compression across rows, resulting in much greater compression.
The problem we have is that MySQL only supports the MERGE table using underlying MyISAM tables. Backing these up requires locking the entire table, which is something that I would like to avoid.
Is there some way that Envers can be configured to query multiple tables, but save new revisions to only one? That way we could have our compressed, readonly tables with the majority of the historical data (which only needs to be backed up once when it is created, say every month or year) and the other tables can be InnoDB with single transaction backups.
So the table structure would look something like:
Employees -> InnoDB, read write with current records
Employees_AUD -> InnoDB, read write with new revisions
Employees_AUD_Frozen -> MyISAM, compressed and read only, used to query old revisions only
Envers would know to map any queries across both Employees_AUD and Employees_AUD_Frozen, but only save new revisions to Employees_AUD.
