Dropping a bulky hypertable's index

Hello there,

Backups can’t get through to S3 anymore because my database is too heavy; so I’ve decided to take a good look into it and…

parent pg_tablespace_name row_estimate total index toast table percent
account_event default 5.137211e+08 301 GB 248 GB 256 kB 53 GB 79.74630004379325
account default 1.207806e+07 37 GB 4286 MB 13 GB 19 GB 9.779042242253839
order_progress default 4.301289e+08 28 GB 3269 MB 208 kB 25 GB 7.361404196596714
account_login default 1.006458e+07 4311 MB 1299 MB 104 kB 3011 MB 1.116048584065062

As you can see, I have tons of index data!

I’m a bit of a newbie when it comes to DBAdmin, so I’d like to know what an expert would do in this case?

Here’s what I’m thinking about:

  1. Dropping old data by configuring a retention policy.
  2. Find the indexes that take tons of space but are not actually used (I’m not super sure how to do this)
  3. Compressing the table as well as the index (account_event is a hypertable :tada:)

What would you do? Thanks


Some info about my indexes

I’ve used this query to compute a list of unused indexes with their sizes (I’ve put data amounts in MB for convenience).

schemaname tablename indexname index_size_mb
_timescaledb_internal _hyper_4_477_chunk _hyper_4_477_chunk_js_account_has_event 3614
_timescaledb_internal _hyper_4_473_chunk _hyper_4_473_chunk_js_account_has_event 3572
_timescaledb_internal _hyper_4_485_chunk _hyper_4_485_chunk_js_account_has_event 3558
_timescaledb_internal _hyper_4_481_chunk _hyper_4_481_chunk_js_account_has_event 3541
_timescaledb_internal _hyper_4_4046_chunk _hyper_4_4046_chunk_account_event_time_idx 3260
_timescaledb_internal _hyper_4_58_chunk _hyper_4_58_chunk_account_event_uri_time_idx 3124
_timescaledb_internal _hyper_4_470_chunk _hyper_4_470_chunk_js_account_has_event 3069
_timescaledb_internal _hyper_4_477_chunk _hyper_4_477_chunk_account_event_uri_time_idx 2828
_timescaledb_internal _hyper_4_473_chunk _hyper_4_473_chunk_account_event_uri_time_idx 2799
_timescaledb_internal _hyper_4_485_chunk _hyper_4_485_chunk_account_event_uri_time_idx 2782
_timescaledb_internal _hyper_4_481_chunk _hyper_4_481_chunk_account_event_uri_time_idx 2774
_timescaledb_internal _hyper_4_4040_chunk _hyper_4_4040_chunk_account_event_time_idx 2758
_timescaledb_internal _hyper_4_53_chunk _hyper_4_53_chunk_account_event_uri_time_idx 2648
_timescaledb_internal _hyper_4_470_chunk _hyper_4_470_chunk_account_event_uri_time_idx 2504
_timescaledb_internal _hyper_4_55_chunk _hyper_4_55_chunk_account_event_uri_time_idx 2308

It looks like I have many unused indexes which take tons of space, but I’m unsure how to drop them without setting my database on fire? Should I just do it?