Vacuums extremely slow for HNSW indices? about pgvector

[ankane]

Hi @williamhakim10, autovacuum_vacuum_cost_limit and autovacuum_vacuum_cost_delay control the vacuum speed (docs). You can set these on individual tables if needed.

Parallel vacuum isn't currently supported, but would be nice to add at some point (added to #27).

[jkatz]

@williamhakim10 Curious -- do you frequently updated rows and/or vectors in the table where vacuum was stuck?

[williamhakim10]

Hi @williamhakim10, autovacuum_vacuum_cost_limit and autovacuum_vacuum_cost_delay control the vacuum speed (docs). You can set these on individual tables if needed.

Parallel vacuum isn't currently supported, but would be nice to add at some point (added to #27).

It feels like this shouldn't matter because I'm manually triggering the vacuum? Unless I misunderstand what these settings do.

@jkatz there would have been only an extremely small number of writes to that table while vacuum was happening, and no updates or deletes as far as I know.

Edit: it's possible there might be a few updates as well, because the insert query we run has an ON CONFLICT ... DO UPDATE clause. But it certainly wouldn't be a large number relative to the size of the table.

[ankane]

My bad, thought you were talking about autovacuum for some reason.

Vacuum should max out a single CPU. Is that what you're seeing?

In general, vacuuming will be slower than building the index, since the index build can happen in-memory and then write to disk, while vacuuming needs to persist changes to disk for every tuple (which for live tuples, requires updating many pages).

[ankane]

Also, just looked into parallel vacuuming, and it looks like it only supports a single worker per index (so won't really help here).

[williamhakim10]

I don't believe Cloud SQL allows you to break out CPU utilization per-core, but generally yes we're seeing total CPU utilization consistent with one core being maxed out over the many hours we attempted to vacuum.

FWIW, rebuilding the index (concurrently) took about 20 minutes after setting maintenance_work_mem to 35GB, on 0.5.2 (so no parallel index builds).

[ankane]

Unfortunately, the time to vacuum will likely be closer to the time to build the index with a very small value of maintenance_work_mem (where the build happens mostly on-disk instead of in-memory, which is significantly slower).

Going back to your original question, I'm not sure there's anything you can do to speed it up right now.

[williamhakim10]

OK cool, this is good to know. Perhaps this might merit a comment in the README; feels like other folks may unexpectedly encounter this because the size of the HNSW indices can get pretty large without that many vectors, such that large deletes may lead to a situation in which autovacuum can't complete (and even manual vacuum can't run in a reasonable amount of time) leading to unexpected drops in recall? Happy to propose something in a PR if you think it's valuable?

[ankane]

Spoke too soon. It looks like rebuilding the index with REINDEX INDEX CONCURRENTLY before vacuuming will make it much faster (since it can rebuild in-memory with maintenance_work_mem and parallel workers when available).

REINDEX INDEX CONCURRENTLY index_name;
VACUUM table_name;

Will add a note to the readme.

[williamhakim10]

Awesome. Appreciate you guys walking this through with me!

[ankane]

Thanks for reporting!

[hlinnaka]

We are pretty aggressive in the RepairGraph phase, to add replacement edges for the removed ones, to keep the index in good order. I'm not sure that's a good tradeoff. It would probably be better to more lazy. For example, only call HnswFindElementNeighbors if more than 10% of an element's neighbors have been deleted. Otherwise just remove the deleted neighbors and leave some empty slots in it.

[ankane]

It's an interesting idea. Would need to understand the impact on recall and vacuum speed to have a better idea of if it's (subjectively) worth the trade-off.