Reindexer

[chaporgin]

Hi! Thank you for the library. I see that Reindexer is disabled. Can you please let me know what the plans are about the Reindexer and how the fact that Reindexer is doing nothing ATM because of IndexNames being empty by default affects the production systems?

[bgentry]

@chaporgin hi there, the reindexer was disabled before release because we felt it needed more testing before being enabled by default. Although the maintenance process is still active, the overhead is very low: on the single leader node it will run its loop daily at midnight, looping through the 0 indexes in that default list before sleeping again.

The downside of not reindexing periodically is your indexes can get quite bloated and take up significantly more space than necessary, and the bloat can cause less efficient index operations. For example I just checked on the demo app's database which hasn't been reindexed in a very long time, and discovered that the args index had grown to 4 GB:

riverdemo=# SELECT
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes;
               index_name               | index_size 
----------------------------------------+------------
 river_job_prioritized_fetching_index   | 80 MB
 river_job_args_index                   | 4013 MB
 river_job_metadata_index               | 645 MB
...

After reindexing these 3 rows, the size was much smaller:

riverdemo=# SELECT                                    
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes;
               index_name               | index_size 
----------------------------------------+------------
 river_job_prioritized_fetching_index   | 38 MB
 river_job_args_index                   | 35 MB
 river_job_metadata_index               | 5656 kB
...

It's probably past time for us to take a serious look at turning this functionality on by default and seeing if we can address the potential downsides that led us to disable it before initial release. cc @brandur

[chaporgin]

Thank you for the asnwer! But was is the downside of having that process do the actual job? The REINDEX blocks the writes, right?

[chaporgin]

the args index had grown to 4 GB

That's huge. May I know how many jobs were scheduled from the beginning of time?

[chaporgin]

If REINDEX blocks the writes, it probably makes sense to do something like the following?

CREATE INDEX CONCURRENTLY index_name_{current_suffix} ON ...;
DROP INDEX CONCURRENTLY index_name_{prev_suffix};

[chaporgin]

My app has the following:

> SELECT
     indexrelname AS index_name,
     pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
 FROM pg_stat_user_indexes WHERE indexrelname LIKE 'river%';
+----------------------------------------+------------+
| index_name                             | index_size |
|----------------------------------------+------------|
| river_job_pkey                         | 43 MB      |
| river_job_kind                         | 12 MB      |
| river_job_state_and_finalized_at_index | 38 MB      |
| river_job_prioritized_fetching_index   | 107 MB     |
| river_job_args_index                   | 107 MB     |
| river_job_metadata_index               | 5704 kB    |
| river_leader_pkey                      | 16 kB      |
| river_queue_pkey                       | 16 kB      |
| river_migration_pkey1                  | 8192 bytes |
| river_client_pkey                      | 8192 bytes |
| river_client_queue_pkey                | 8192 bytes |
| river_job_unique_idx                   | 120 kB     |
+----------------------------------------+------------+
SELECT 12
Time: 0.031s
> SELECT COUNT(*) FROM river_job;
+--------+
| count  |
|--------|
| 887694 |
+--------+
SELECT 1
Time: 1.677s (1 second), executed in: 1.666s (1 second)
>

[bgentry]

The demo app does a little under a million jobs a day (I believe it has the default 24 hour retention). Multiply that by many months and that’s a lot of jobs!

The reindexer uses REINDEX INDEX CONCURRENTLY which is available in newer Postgres versions so it does not block writes. I believe there was some concern about needing more knobs for controlling the timeout for these queries, and also wanting to make sure we don’t have multiple reindexing attempts going on the same index at one time. They’re solvable problems that we just ran out of time to sort out before release and haven’t yet come back around to.