PostDataCache has slowed to the point that hivemind intermittently switches from livesync to massive sync

Problem is likely caused by either full text search change ( @Ickiewicz ) or notifications mentions changes ( @klesniak ). Can you guys figure out which is the issue and see what can be done to speed it up?

Here's a partial log from s17 to see the problem. Hivemind is still running there if you want to see the issue on a live system.

 2025-06-27T18:37:21.926739519Z INFO - hive.indexer.sync:246 - [SINGLE] Attempting to process first block in range: <97155381:97155381>
 2025-06-27T18:37:21.937648795Z INFO - hive.indexer.blocks:180 - #############################################################################
 2025-06-27T18:37:21.937659215Z INFO - hive.indexer.blocks:197 - [PROCESS MULTI] Flushing data in 1 thread
 2025-06-27T18:37:21.967483121Z INFO - hive.indexer.flusher:36 - Posts flush executed in: 0.0298 s
 2025-06-27T18:37:27.843268444Z INFO - hive.indexer.flusher:36 - PostDataCache flush executed in: 5.8755 s
 2025-06-27T18:37:27.862551119Z INFO - hive.indexer.flusher:36 - Votes flush executed in: 0.0194 s
 2025-06-27T18:37:27.862570329Z INFO - hive.indexer.flusher:36 - Follow flush executed in: 0.0000 s
 2025-06-27T18:37:27.862575389Z INFO - hive.indexer.flusher:36 - Reblog flush executed in: 0.0000 s
 2025-06-27T18:37:27.862577819Z INFO - hive.indexer.flusher:36 - Notify flush executed in: 0.0000 s
 2025-06-27T18:37:27.862580730Z INFO - hive.indexer.flusher:36 - Accounts flush executed in: 0.0000 s
 2025-06-27T18:37:27.866983282Z INFO - hive.indexer.flusher:36 - VoteNotifications flush executed in: 0.0044 s
 2025-06-27T18:37:27.869912324Z INFO - hive.indexer.flusher:36 - PostNotifications flush executed in: 0.0029 s
 2025-06-27T18:37:27.869937164Z INFO - hive.indexer.flusher:36 - FollowNotifications flush executed in: 0.0000 s
 2025-06-27T18:37:27.869960395Z INFO - hive.indexer.flusher:36 - ReblogNotifications flush executed in: 0.0000 s
 2025-06-27T18:37:27.869976595Z INFO - hive.indexer.blocks:200 - [PROCESS MULTI] Tables updating in live synchronization
 2025-06-27T18:37:27.872422580Z INFO - hive.indexer.blocks:402 - SELECT hivemind_app.update_hive_posts_children_count(97155381, 97155381) executed in: 0.0024 s
 2025-06-27T18:37:27.873079039Z INFO - hive.indexer.blocks:402 - SELECT hivemind_app.update_hive_posts_root_id(97155381,97155381) executed in: 0.0006 s
 2025-06-27T18:37:27.873444714Z INFO - hive.indexer.blocks:402 - SELECT hivemind_app.update_feed_cache(97155381, 97155381) executed in: 0.0003 s
 2025-06-27T18:37:27.875542914Z INFO - hive.indexer.blocks:402 - SELECT hivemind_app.update_last_completed_block(97155381) executed in: 0.0021 s
 2025-06-27T18:37:27.875940500Z INFO - hive.indexer.blocks:402 - SELECT hivemind_app.prune_notification_cache(97155381) executed in: 0.0004 s
 2025-06-27T18:37:27.875964250Z INFO - hive.utils.timer:18 - on_live_blocks_processed executed in 0.0060 s
 2025-06-27T18:37:27.875986790Z INFO - hive.indexer.blocks:208 - [PROCESS MULTI] 1 blocks in 5.9481s
 2025-06-27T18:37:27.876702611Z INFO - hive.indexer.sync:123 - Last imported block is: 97155381
 2025-06-27T18:37:27.877558823Z INFO - hive.indexer.sync:241 - Next block range from hive.app_next_iteration is: <97155382:97155382>
 2025-06-27T18:37:27.877740845Z INFO - hive.indexer.sync:143 - target_head_block: 97155382
 2025-06-27T18:37:27.877752726Z INFO - hive.indexer.sync:144 - test_max_block: None
 2025-06-27T18:37:27.879881956Z INFO - hive.indexer.sync:184 - [SINGLE] *** SINGLE block processing***
 2025-06-27T18:37:27.879901246Z INFO - hive.indexer.sync:185 - [SINGLE] Current system time: 2025-06-27 18:37:27.879
 2025-06-27T18:37:27.879910356Z INFO - hive.indexer.sync:246 - [SINGLE] Attempting to process first block in range: <97155382:97155382>
 2025-06-27T18:37:27.886570041Z INFO - hive.indexer.blocks:180 - #############################################################################
 2025-06-27T18:37:27.886579541Z INFO - hive.indexer.blocks:197 - [PROCESS MULTI] Flushing data in 1 thread
 2025-06-27T18:37:27.907111404Z INFO - hive.indexer.flusher:36 - Posts flush executed in: 0.0205 s
 2025-06-27T18:38:23.898311560Z INFO - hive.indexer.flusher:36 - PostDataCache flush executed in: 55.9911 s   <<---------------------------------------------- TOO SLOW