1

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 detail:
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?

Improve this question

1 Answer 1

Reset to default
1

This question maybe will be more appropriate to be asked here, but let me try to suggest different approach:

Instead of implement compressing which will consume CPU power for compression/decompression why do not add ordinary (magnetic) disks, create filesystem, mount it. Then create new table in this mountpoint and move the old data in this table. The speed will not be the same as SSD but as I understand you do not access often this data.

Or as suggested here create new tablepsace with specific parameters and partition the data so old data is in this new tablepsace.

Improve this answer
1
  • 1
    Thanks for the ideas, upvoted. I was not aware of the dba section, so I may move this question there. Regarding the CPU, as it is a bare metal server, having 32 CPU cores (if I remember correctly), and the application is not so CPU intensive, it is not an issue. Regarding extending with spinning disks, maybe it is not that easy in this case, simpler hw change may be needed (I will not go into details, too difficult to explain). The second link, however, made me aware of TimescaleDB, which seem to support compression, so I am going to explore that option first.
    – Attila123
    Jul 13, 2022 at 18:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.