This message was deleted.

that’s big afaik. Would expect pg_repack to be taking care of the bloat though

yeah that's another issue 😄 I've ~36GB free disk space, not enough for pg_repack to finish

sounds like the same issue 🙂

next week I can extend the VM with more storage, hopefully pg_repack can free up some space afterwards

you could drop and recreate the index which will shrink it and doesn’t require extra space

Already had that in mind 😄

the index should never be bigger than the table so at a minimum you’re talking about getting 47 GB back

you mean dead tuples or tuples Puppet somehow didn't delete?

of tuples or dead tuples?

I did

select count(*) from resource_params

maybe, I don’t remember

we were able to reduce this by roughly 50% diskspace with

REINDEX TABLE resource_params

. that requires a write-lock, so maybe don't do that during a huge DB load

yea that’s why we use pg_repack

I didnt have enough disk space for pg_repack :D

In my experience the answer is add more RAM. If any of the most used queries need to read anything off disk that’s going to cost you alot. You should ideally be able to fit all of the database other than the reports and resource_events tables in memory

mhm, that would require +50gb of memory I think. need to check tomorrow

reaching back in my brain I think you can turn on something that will cause query plans to to get logged for longer than x seconds. Once you have that you can use pg_badger to see your slowest queries and look at the query plans ( shown right in the pg_badger html ) to see if they are spending time reading from disk

ah good idea. I think I already enabled slow query log