Обсуждение: Index bloat and REINDEX/VACUUM optimization for partial index

Hi,

TL;DR: 
Observations:

1. REINDEX requires a full table scan
   • Roughly create a new index, rename index, drop old index.
     
   • REINDEX is not incremental. running reindex frequently does not reduce the future reindex time.
2. REINDEX does not use the index itself
   
3. VACUUM does not clean up the indices. (relpages >> reltuples) I understand, vacuum is supposed to remove pages only if there are no live tuples in the page, but somehow, even immediately after vacuum, I see relpages significantly greater than reltuples. I would have assumed, relpages <= reltuples 
4. Query Planner does not consider index bloat, so uses highly bloated partial index that is terribly slow over other index

Question: Is there a way to optimize postgres vacuum/reindex when using partial indexes?

```
SELECT [columns list]

  FROM tasks

  WHERE status NOT IN (3,4,5) AND created > NOW() - INTERVAL '30 days' AND updated < NOW() - interval '30 minutes'
```

Since we are only interested in the pending tasks, I created a partial index
 `"tasks_pending_status_created_type_idx" btree (status, created, task_type) WHERE status <> ALL (ARRAY[3, 4, 5])`.

At any moment, there are around 1000-1500 tasks in pending statuses (Init + InProgress) out of around 500 million tasks. 

Now, we have a task monitoring query that will look for all pending tasks that have not received any update in the last n minutes.

```
SELECT [columns list]

  FROM tasks

  WHERE status NOT IN (3,4,5) AND created > NOW() - INTERVAL '30 days' AND updated < NOW() - interval '30 minutes'
```

Since we are only interested in the pending tasks, I created a partial index
 `"tasks_pending_status_created_type_idx" btree (status, created, task_type) WHERE status <> ALL (ARRAY[3, 4, 5])`.

This worked great initially, however this started to get bloated very very quickly because, every task starts in pending state, gets multiple updates (and many of them are not HOT updates, working on optimizing fill factor now), and eventually gets deleted from the index (as status changes to success).

On Tue, Aug 29, 2023, 12:43 PM Jeff Janes <jeff.janes@gmail.com> wrote:

On Mon, Aug 28, 2023 at 8:33 PM jayaprabhakar k <jayaprabhakar@gmail.com> wrote:

Since we are only interested in the pending tasks, I created a partial index
 `"tasks_pending_status_created_type_idx" btree (status, created, task_type) WHERE status <> ALL (ARRAY[3, 4, 5])`.

This looks like a poorly designed index.  Since the status condition exactly matches the index where clause, there is no residual point in having "status" be the first column in the index, it can only get in the way (for this particular query).  Move it to the end, or remove it altogether.

Interesting. I don't understand why it will get in the way. Unfortunately we have a few other cases where status is used in filter. That said, I will consider how to get this to work. 

Would removing status from the index column, improve HOT updates %? For example, changing status from 1->2, doesn't change anything on the index (assuming other criteria for HOT updates are met), but I am not sure how the implementation is.

Within the tuples which pass the status check, which inequality is more selective, the "created" one or "updated" one?

Obviously updated time is more selective (after status), and the created time is included only to exclude some bugs in our system that had left some old tasks stuck in progress (and for sorting). We do try to clean up occasionally, but not each time.

 
However we cannot add an index on `updated` column because that timestamp gets updated over 10x on average for each task. Since if a single index use a column, then the update will not be HOT, and every index needs to be updated. That will clearly add a bloat to every index. Did I miss something?

With the default value of autovacuum_vacuum_scale_factor (The default is 0.2 (20% of table size).) index will collect like 100M outdated/dead index entries before autovacuum kicks in and cleans them all (in a worst case),  and of course it will lead to huge index bloat and awful performance.

Even if you scale down autovacuum_vacuum_scale_factor to some unreasonable low value like 0.01, the index still bloats to the 5M dead entries before autovacuum run, and constant vacuuming of a huge 500M table will put a huge load on the database server.





Unfortunately there is no easy way out of this situation from database side, in general I recommend not trying to implement a fast pacing queue like load inside of a huge and constantly growing table, it never works well because you cannot keep up partial efficient indexes for the queue in a clean/non-bloated state.

In my opinion the best solution is to keep list of entries to process ("around 1000-1500 tasks in pending statuses") duplicated in the separate tiny table (via triggers or implement it on the application level), in that case autovacuum will be able quickly clean dead entries from the index.

But anyway, PostgreSQL has features to prevent the index bloat from becoming too severe of a problem, and you should figure out why they are not working for you.  The most common ones I know of are 1) long open snapshots preventing clean up, 2) all index scans being bitmap index scans, which don't to micro-vacuuming/index hinting the way ordinary btree index scans do, and 3) running the queries on a hot-standby, where index hint bits must be ignored.  If you could identify and solve this issue, then you wouldn't need to twist yourself into knots avoiding non-HOT updates.