In short: compress multiple historical DB records to bigger, compressed records and temporarily uncompress it to the original records for easier querying, and clean that up when querying is done. Wondering of implementing a SW layer for that. I tend to stick to postgres and not introduce another system. However, I know it would require to implement a software layer to manage this, and I also have a feeling that I may be reinventing some existing wheel and may cause our team significant implementation effort by doing so.
In our application there is a postres DB table, which stores some historical measurement information of some "gadgets", so some timestamp, the id of the gadget, and the measurement data. The assumption is that it is nicely compressible.
According to my measurement, 1 record takes about 500 bytes of disk space in average, and roughly 2/3 of that space is spent on the indexes.
This was the result of about 80 million records. So currently it uses about 40 GiB, but we need to prepare for bigger deployments, for which the estimated size of this table may be around 1700 GiB.
The server is bare metal, so the naive/brute force solution would be to add a 2 TB SSD, and that would work.
However, we may need to use a smaller SSD and be more efficient with space. Also, I think that storing all of these historical data uncompressed when it is very seldom queried is not efficient / wasteful on space. Without trying to go to some other DB etc I thought why not just compress this historical measurement data in e.g. per day and per "gadget". (I read there is some support for that in postgres, or just use some readily available standard compression algorithm.)
When the user would like to see this measurement data for gadgets x, y, z, from date1 to date2, a software layer would fetch the compressed data from the DB and put it to some (temporary) table for easy querying (SQL) - it could take advantage of the already existing DAO code, which queries the table.
When the user finished looking at the data, the uncompressed records may be deleted to free up the space.
I wonder whether implementing such a "caching layer" would be reasonable?
Or is there some readily available free to use component which would be better suited to be used for this use case?