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?
We have a large table (tasks) that keep track of all the tasks that are
created and their statuses. Around 1.4 million tasks per day are created
every day (~15 inserts per second).
One of the columns is int `status` that can be one of (1 - Init, 2 -
InProgress, 3 - Success, 4 - Aborted, 5 - Failure) (Actually, there are
more statuses, but this would give the idea)
On average, a task completes in around a minute with some outliers that can
go as long as a few weeks. There is a periodic heartbeat that updates the
last updated time in the table.
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).
```
\d+ tasks
Table "public.tasks"
Column | Type | Collation |
Nullable | Default | Storage | Compression |
Stats target | Description
-------------------------------+----------------------------+-----------+----------+-----------------------------------+----------+-------------+--------------+-------------
id | bigint | |
not null | nextval('tasks_id_seq'::regclass) | plain | |
|
client_id | bigint | |
not null | | plain | |
|
status | integer | |
not null | | plain | |
|
description | character varying(128) | |
not null | | extended | |
|
current_count | bigint | |
not null | | plain | |
|
target_count | bigint | |
not null | | plain | |
|
status_msg | character varying(4096) | |
| | extended | |
|
blob_key | bigint | |
| | plain | |
|
created | timestamp with time zone | |
not null | | plain | |
|
updated | timestamp with time zone | |
not null | | plain | |
|
idle_time | integer | |
not null | 0 | plain | |
|
started | timestamp with time zone | |
| | plain | |
|
Indexes:
"tasks_pkey" PRIMARY KEY, btree (id)
"tasks_created_idx" btree (created)
"tasks_pending_status_created_idx" btree (status, created) WHERE status
<> ALL (ARRAY[3, 4, 5])
"tasks_client_id_status_created_idx" btree (client_id, status, created
DESC)
"tasks_status_idx" btree (status)
Access method: heap
Options: autovacuum_vacuum_scale_factor=0.02,
autovacuum_analyze_scale_factor=0.02, fillfactor=70
```
Immediately after REINDEX
```
SELECT relname,reltuples,relpages FROM pg_class WHERE relname like
'tasks%idx%';
relname | reltuples | relpages
------------------------------------+----------------+----------
tasks_pending_status_created_idx | 34175 | 171
tasks_created_idx | 5.3920026e+08 | 11288121
tasks_client_id_status_created_idx | 5.3920026e+08 | 7031615
tasks_status_idx | 5.3920026e+08 | 2215403
(9 rows)
```
A couple of days after manual full REINDEX.
```
SELECT relname, relpages, reltuples, relallvisible, relkind, relnatts,
relhassubclass, reloptions, pg_table_size(oid) FROM pg_class WHERE (relname
like 'tasks%idx%' OR relname='tasks');
relname | relpages | reltuples |
relallvisible | relkind | relnatts | relhassubclass | reloptions |
pg_table_size
------------------------------------+----------+----------------+---------------+---------+----------+----------------+------------+---------------
tasks_pending_status_created_idx | 79664 | 201831 |
0 | i | 3 | f | | 652771328
tasks_created_idx | 11384992 | 5.42238e+08 |
0 | i | 1 | f | | 93481443328
tasks_client_id_status_created_idx | 7167147 | 5.42274e+08 |
0 | i | 5 | f | | 58727710720
tasks_status_idx | 2258820 | 5.4223546e+08 |
0 | i | 1 | f | | 18508734464
tasks | 71805187 | 5.171037e+08 |
71740571 | r | 30 | f | | 613282308096
```
