Reindexing to reduce bloat - postgresql

postgres=# create table demo1 as select * from generate_series(1,50000) as id;

SELECT 50000

postgres=# create index demo1_idx on demo1(id);

CREATE INDEX

postgres=# \dt

         List of relations

 Schema | Name  | Type  |  Owner

--------+-------+-------+----------

 public | demo1 | table | postgres

 public | ridx1 | table | postgres

(2 rows)

postgres=# \dt+

                                    List of relations

 Schema | Name  | Type  |  Owner   | Persistence | Access method |  Size   | Description

--------+-------+-------+----------+-------------+---------------+---------+-------------

 public | demo1 | table | postgres | permanent   | heap          | 1808 kB |

 public | ridx1 | table | postgres | permanent   | heap          | 43 MB   |

(2 rows)

postgres=# \di+

                                           List of relations

 Schema |    Name     | Type  |  Owner   | Table | Persistence | Access method |  Size   | Description

--------+-------------+-------+----------+-------+-------------+---------------+---------+-------------

 public | demo1_idx   | index | postgres | demo1 | permanent   | btree         | 1112 kB |

 public | ridx1_pk    | index | postgres | ridx1 | permanent   | btree         | 14 MB   |

 public | ridx1_x_idx | index | postgres | ridx1 | permanent   | btree         | 14 MB   |

(3 rows)

postgres=# delete from demo1 where id % 2 = 0;

DELETE 25000

postgres=# select pg_size_pretty(pg_relation_size('demo1')) as table_size, pg_size_pretty(pg_relation_size('demo1_idx')) as index_size, 100 - (pgstatindex('demo1_idx')).avg_leaf_density as bloat_ratio;

 table_size | index_size | bloat_ratio

------------+------------+-------------

 1776 kB    | 1112 kB    |       54.94

(1 row)

postgres=# reindex index demo1_idx;

REINDEX

postgres=# select pg_size_pretty(pg_relation_size('demo1')) as table_size, pg_size_pretty(pg_relation_size('demo1_idx')) as index_size, 100 - (pgstatindex('demo1_idx')).avg_leaf_density as bloat_ratio;

 table_size | index_size |    bloat_ratio

------------+------------+--------------------

 1776 kB    | 568 kB     | 10.819999999999993

(1 row)