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:
- Dropping old data by configuring a retention policy.
- Find the indexes that take tons of space but are not actually used (I’m not super sure how to do this)
- Compressing the table as well as the index (
account_eventis a hypertable
)
What would you do? Thanks
Annex
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?