PostgreSQL index bloat ratio more than table bloat ratio and autovacuum_vacuum_scale_factor

Index bloats are reaching 57%, while table bloat is 9% only and autovacuum_vacuum_Scale_factor is 10% only.

what is more surprising is even primary key is having bloat of 57%. My understanding is since my primary key is auto incrementing and single column key only so after 10% of table dead tuples, primary key index should also have 10% dead tuples.

Now when autovacuum will run at 10% of dead tuples , it will clean dead tuples. The dead tuple space now becomes bloat and this should be reused by new updates, insert. But this isn't happening in my database, here bloat size keeps on increasing.

FYI:

Index Bloat:

    current_database | schemaname |       tblname        |                         idxname                          | real_size  | extra_size |   extra_ratio    | fillfactor | bloat_size |    bloat_ratio    
    | is_na 
    ------------------+------------+----------------------+----------------------------------------------------------+------------+------------+------------------+------------+------------+-------------------
    +-------
 stackdb       | public     | data_entity | data_entity_pkey                              | 2766848000 | 1704222720 | 61.5943745373797 |         90 | 1585192960 |  57.2923760177646

Table Bloat:

current_database | schemaname |            tblname             |  real_size  | extra_size |   extra_ratio    | fillfactor | bloat_size |   bloat_ratio    | is_na 
 stackdb       | public     | data_entity           | 10106732544 | 1007288320 | 9.96650812332014 |        100 | 1007288320 | 9.96650812332014 | f

Autovacuum Settings:

stackdb=> show autovacuum_vacuum_scale_factor;
 autovacuum_vacuum_scale_factor 
--------------------------------
 0.1
(1 row)
stackdb=> show autovacuum_vacuum_threshold;
 autovacuum_vacuum_threshold 
-----------------------------
 50
(1 row)

Note:

autovacuum is on

autovacuum is running successfully at defined intervals.

postgreSQL is running version 10.6. Same issue has been found with version 12.x

Solution

First: an index bloat of 57% is totally healthy. Don't worry.

Indexes become more bloated than tables, because the empty space cannot be reused as freely as it can be in a table. The table, also known al “heap”, has no predetermined ordering: if a new row is written as the result of an INSERT or UPDATE, it ends up in the first page that has enough free space, so it is easy to keep bloat low if VACUUM does its job.

B-tree indexes are different: their entries have a certain ordering, so the database is not free to choose where to put the new row. So you may have to put it into a page that is already full, causing a page split, while elsewhere in the index there are pages that are almost empty.