Postgresql – Find Uncompressed/Compressed Sizes of TimescaleDB Database/Table

In version 10.1, the logs are internally compressed, that means that more information is stored in those files.

But if you want to compress them in order to use less space in your disks, I do not see this like a very good practice, because logs are the only part where the latest data is stored in, and if you want to do a restore, you will have to use the log files, to restore the database with an extra process, and probably, to perform a restore you will need even more space than before (the compressed file + the expanded file).

Presonally, this compress operation adds "noise" to the disaster recovery strategy, how are you going to check if a compressed file is ok or not?

Finally, if you want to do a thing like this, you could use the userexit option in order to implement your own process to take the online logs and pass them as offline logs. In this process you could compress and extract the files.

BTW, why don't you use circular logs? Any enterprise knows the importance of archive logs.

Which db to choose? I would choose PostgreSQL having worked with MySQL and PostgreSQL but it is worth noting how different the databases are. I will say I have frequently been impressed (and only rarely disappointed) by what sort of abuse I can throw at PostgreSQL only to watch things be handled gracefully.

In your specific case, however, there may be reasons to look specifically at PostgreSQL. The major issue is programmability and advanced indexing but there are other reasons too (basically in InnoDB your table is contained in the pkey index, while in PostgreSQL you can do a physical-order scan of the table (and this has advantages when pulling a lot of rows). Major disadvantages might include not liking the binary type interfaces (and often saving/retrieving the values can use more memory than I would like). As for filesystem I know a lot of people who are very happy with XFS and also with ZFS.

Finally one specific tradeoff here is the Pg 8k data page size. It sounds like your time series would be larger than this, and so would be TOASTed (moved to extended storage). This speeds up many queries, but it also slows down retrieving the specific time series slightly because extra physical files must be scanned.

As for programmability, basically you could write functions that can look inside a time series for specific useful information and then you can index the output of this function. Consequently this can be used to speed up searches because the function doesn't have to be run.