index hell: 210 GB of space for indexes that the logs say are never used...

[tempcke]

we had a lot of used space in our database and rather than increase the size we are paying for we decided to use AI to look for ways to trim things that are not needed and it found a number of indexes that are never used ... many of them from river.  it reported that these indexes existed and were using a lot of space but were never used.  My boss actually found this and just deleted the indexes so I can't look at them anymore but thought I should report it here in case there is some optimization that could be made in river.  right now I'm being scolded for using river ...

• river_job_args_index (plus 30+ duplicate indexes _ccnew1 through _ccnew31): Size = 6.8 GB each (Totaling ~210 GB!), scans = 0

We re-deployed after they were deleted as I assumed the indexes would only come from migrations to confirm that they won't come back and they didn't however when I rgrep the code for 'ccnew' it doesn't look like these come from migrations and so I'm concerned. If these indexes come back I'm going to have a problem.

210 GB of space on indexes that the logs indicate are never used...

[bgentry]

Those _ccnewN indexes are PostgreSQL artifacts from failed REINDEX INDEX CONCURRENTLY attempts. They aren’t River migrations, and River won’t recreate indexes with those names directly.

River does include river_job_args_index in its default reindexer, but the default per-index timeout is 1 minute. A 6.8 GB GIN index probably won’t rebuild in that window, so repeated timeout/cancelled reindex attempts can leave invalid _ccnewN artifacts behind. PostgreSQL appends numbers like _ccnew1, _ccnew2, etc. when prior artifacts already exist.

To clean up, first inspect the artifacts:

SELECT
  n.nspname AS schema,
  c.relname AS index_name,
  pg_size_pretty(pg_relation_size(c.oid)) AS size,
  i.indisvalid,
  i.indisready
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_index i ON i.indexrelid = c.oid
WHERE c.relkind = 'i'
  AND c.relname ~ '^river_job_(args|metadata)_index_cc(new|old)[0-9]*$'
ORDER BY pg_relation_size(c.oid) DESC;

Invalid _ccnew* / _ccold* artifacts can be dropped. In psql, this will generate and execute the drops:

SELECT format('DROP INDEX CONCURRENTLY IF EXISTS %I.%I;', n.nspname, c.relname)
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_index i ON i.indexrelid = c.oid
WHERE c.relkind = 'i'
  AND NOT i.indisvalid
  AND c.relname ~ '^river_job_(args|metadata)_index_cc(new|old)[0-9]*$';
\gexec

DROP INDEX CONCURRENTLY can’t run inside a transaction block, so don’t wrap that in BEGIN/COMMIT.

To make future reindexes succeed, either raise River’s ReindexerTimeout substantially, or temporarily disable River’s automatic reindexer and run the reindex manually during a quiet period:

SET statement_timeout = 0;
REINDEX INDEX CONCURRENTLY river_job_args_index;

As for why it's unused, the args index was added to facilitate riverui arg searches, though we haven't added those yet. You're of course free to remove the index if you want (it's your database), but be aware that you may experience degenerate performance with such features once they're added.

[bgentry]

Also @brandur I think our failed reindex detection is only checking exact _ccnew / _ccold artifact names, but Postgres can append numeric suffixes like _ccnew1, _ccnew2, etc. once prior artifacts exist. This report’s _ccnew1 through _ccnew31 likely slipped past our current check and allowed repeated failed REINDEX CONCURRENTLY attempts to accumulate artifacts.

[brandur]

@tempcke Can you check your River version? The incomplete index stuff was added in 0.23.0, so make sure you're on at least that.

Also @brandur I think our failed reindex detection is only checking exact _ccnew / _ccold artifact names, but Postgres can append numeric suffixes like _ccnew1, _ccnew2, etc. once prior artifacts exist. This report’s _ccnew1 through _ccnew31 likely slipped past our current check and allowed repeated failed REINDEX CONCURRENTLY attempts to accumulate artifacts.

Yeah, the idea here is that if you've got any incomplete indexes, you should have that first one, but can't hurt to make sure to check the whole progression.

[brandur]

We also cut v0.40.0 which includes further improvements to pause the reindexer in case any pattern of partial indexes is discovered.

[tempcke]

@brandur

Regarding version .. we update dependencies every few months or so

$ cat go.mod | grep river
	github.com/riverqueue/river v0.39.0
	github.com/riverqueue/river/riverdriver/riverpgxv5 v0.39.0
	github.com/riverqueue/river/rivershared v0.39.0
	github.com/riverqueue/river/rivertype v0.39.0
	riverqueue.com/riverui v0.16.0
	github.com/riverqueue/apiframe v0.0.0-20260609005548-64f48e0e6228 // indirect
	github.com/riverqueue/river/riverdriver v0.39.0 // indirect

You're of course free to remove the index if you want (it's your database), but be aware that you may experience degenerate performance with such features once they're added.

we are talking about 210GB of space used to index tables which if I cleared them all ... the system would self heal rapidly. from my perspective the indexes were causing a performance issues ....

I added river-ui during development .. I assumed it would be cool, maybe help give visibility into things etc ... I've never looked at it after that first week of adding it ... no one has. It is a cool interface, but at the end of the day I just need to make sure jobs can be queued and worked by a distributed system efficiently ... that's all that matters. I found that I had done a similar pattern to solve this problem like three times and I needed it again but my solution was not generic enough to be extracted into a shared library so I was re-doing it every time which caused me to search for something community supported which solved this problem and I found river. I love it, TYVM for this ... but for what it's real purpose is ... it shouldn't need very many indexes .. and those indexes shouldn't need to be so large ... most of the data that passes through river AFAIK only lasts a day or two at most.

what I need to know, for the health of the system, is now that we have deleted these indexes ... do I need to fear them or other indexes growing out of control like this? If so, what configuration must I set to ensure that does not happen? River's really good at scheduling jobs, can it not on some interval check all of it's indexes to ensure they are not growing out of control?

If it happens again ... my boss is 100% going to make me remove river .. which means I'll have to invest time into creating my own solution for this which though I love to code .. I don't want to do lol ... so please tell me what must be done to ensure river related indexes don't grow out of control again.

[brandur]

Something odd definitely happened in your case. Either those index artifacts were added before detection for them shipped in 0.23.0 a year ago, or one of the _ccnew indexes was manually removed while the other ones weren't so River was unable to detect the outstanding artifacts (or something of that nature).

Recommendation is to upgrade to 0.40.0 as alluded to above. Even if the identical situation were to arise again (and it shouldn't), River will now be able to detect index artifacts no matter how they're named.

Also, after verifying that all artifacts are removed, you may also want to consider dramatically increasing ReindexerTimeout. That way, you may be able to get a rebuild that actually works and get somewhat better fetching performance.