2

I have a TimescaleDB database running on Docker (which is running on Ubuntu). As you can guess, I store a lot of time-series data in it and to save disk space I activated a compression policy:

ALTER TABLE measurements SET (
  timescaledb.compress,
  timescaledb.compress_orderby = 'time DESC',
  timescaledb.compress_segmentby = 'device'
)

SELECT add_compression_policy('measurements', INTERVAL '2 weeks')

and if I check the compression stats i get the result I expect:

  • before compression --> 81GB
  • after compression --> 3900MB

Until here it's all fine, except it does not free up actual disk space and the Docker folder that contains the TimescaleDB files is growing as if compression was never activated in the first place.

VACUUMing the whole DB has no effect, except for taking up an additional 1GB of disk space.

EDIT:

by disk space I mean the specific fs directory where the timescaleDB volume is mounted, where the database files are stored on the host machine.

morphineglelly
  • 107
  • 7
  • 1
    did you do a `VACUUM FULL`? A normal vacuum does not move data around to shrink files it just makes sure space occupied by old rows can be reused. – Eelke Mar 30 '22 at 08:33
  • [Related question](https://stackoverflow.com/questions/5327964/free-space-after-massive-postgres-delete) that maybe can help you on this. – jonatasdp Mar 30 '22 at 12:31
  • @Eelke Yes we ran both VACUUM and VACUUM FULL but all we got was the DB down for a few hours and no space reclaimed, it looks like the table takes a huge amount of space even if compressed. – morphineglelly Mar 30 '22 at 12:59
  • Look to see where the space is being used. Maybe your WAL files are not getting cleaned up. Maybe you have gratuitously large human-readable log files that never get cleaned up. Maybe you have been hacked and are now storing the hackers collection of internet cat videos. The possibilities are endless, and you need to narrow them down as we don't have access to your system. – jjanes Mar 30 '22 at 14:46
  • mybe docker is the problem: e.g check this https://stackoverflow.com/questions/36969395/docker-container-keeps-growing and also consider, that there may be bugs like this: [Windows 10: Docker does not release disk space after deleting all images and containers #244](https://github.com/docker/for-win/issues/244). – TmTron Mar 31 '22 at 06:17

1 Answers1

2

It turns out that everything worked fine, I was just tricked by what the compression_stats were telling me.

In the end the compression was active but the chunks were not old enough so they weren't compressed yet, just pending to be compressed.

We noticed it by manually checking chunks status.

Thanks to everyone who commented.

morphineglelly
  • 107
  • 7