What could cause a giant search index table?

[deviantintegral]

My miniflux instance crashed after running out of disk space. Looking at the database, it appears that the document_vectors_idx index is huge - 9.7GB for 74MB of entries!

    schema_name     |                relname                |    size    | table_size
--------------------+---------------------------------------+------------+-------------
 public             | document_vectors_idx                  | 9787 MB    | 10262618112
 public             | entries                               | 74 MB      |    77758464
 public             | entries_id_user_status_idx            | 21 MB      |    22192128
 public             | entries_user_status_changed_idx       | 20 MB      |    21266432
 public             | entries_user_id_status_starred_idx    | 15 MB      |    15548416
 public             | entries_user_feed_idx                 | 14 MB      |    15040512
 public             | entries_user_status_idx               | 13 MB      |    13869056
 public             | entries_user_status_feed_idx          | 13 MB      |    13418496
 public             | enclosures_pkey                       | 12 MB      |    12738560
 public             | entries_feed_url_idx                  | 8336 kB    |     8536064
 public             | entries_feed_id_status_hash_idx       | 4816 kB    |     4931584
 public             | entries_feed_id_hash_key              | 4224 kB    |     4325376
 public             | entries_pkey                          | 1888 kB    |     1933312

I had 9398 read entries in my history, which I flushed. That didn't reduce the size of the index at all.

Any ideas on how to get this under control, and how to prevent it from happening in the future?

[deviantintegral]

I was able to at least temporarily fix this by exporting the database with pg_dump, dropping, and re-importing with pg_sql. The index is now ~10MB which seems much more reasonable. I'll need to monitor this to see if it grows.

[fguillot]

You may have to vacuum the tables and indexes: https://www.postgresql.org/docs/current/sql-vacuum.html

[deviantintegral]

Thanks. I did try a regular VACUUM with no real change, but I missed there were further options. If this comes up again I’ll dig more deeply.