Postgresql Primary Key Index Maintenance

Question

I refresh my indexes in my Postgresql database with the "reindex index concurrently" command. However, I never renewed my primary key indexes because it might damage my primary key indexes or cause problems. My question is: Is there any harm in renewing the primary key indexes? When refreshing the indexes, I use the hours when my database is least busy, but I am hesitant to renew the primary keys. Thanks in advance

Answer 1

I wonder what database systems you are used to, if you are worried that rebuilding the primary key index could cause damage. It cannot cause damage.

On the other hand, it is only rarely necessary to rebuild an index. Only do that if you know it is necessary — for example, after checking for bloat with the pgstatindex() function.

Answer 2

Because the response time in seeking into the values of an index is a logarithmic factor in relation to the number of pages used, even 50% fragmentation will have very limited or no impact on search time.

On the other hand, the fragmentation of an index significantly increases the volume of data and therefore overloads the data cache. 50% fragmentation means that the index uses double the memory space compared to a fully defragmented version. It is therefore only in the case of scanning the index values that this is of any importance...