Keep redirecting old realurl urls after migrating to TYPO3 9+

I would like to use the realurl memory of expired url to generate 301 for sites upgraded to TYPO3 9+ and avoid 404.

For example, before TYPO3 9, fetching /my-old-page redirected to /my-new-page, because /my-old-page was still in the realurl database table. Now, since the migration to TYPO3 9, fetching /my-old-page throws a 404.

TYPO3 9 ships an upgrade wizard which transforms realurl pagepath/aliases into slugs, but does not transforms realurl's expired pagepath/aliases into sys_redirect.

What would be the best strategy to keep the realurl memory of redirects:

• Migrate all expired url/alias to sys_redirect? This can leads to a big sys_redirect table, with performance issues
• Run a middleware after the RedirectHandler that searches for expired url and triggers a 301 if found? This will make an extra db query for each request.
• Create a PageNotFoundHandler which searches for expired url if page is not found? TYPO3 allows only one ErrorHandler per status code so it can be an issue
• List the redirects in the .htaccess

By "best strategy" I mean :

• the performance could be important (I have more than 10,000 expired urls)
• if possible the redirects should be maintainable by an editor (like sys_redirect)

Thanks for your insights!

My second solution (which I am using - slightly modified - in production) is with TYPO3:

• create a page error handler based on PageErrorHandlerInterface for 404. Check in the realurl table for the URL. If you have a hit, redirect to the new URL.
• if there is no hit, fall back to what you would usually do, e.g. display error page.

This has the following advantages (to TYPO3 redirects extension):

• It is only fired up on 404, not on every page.
• also, you don't have to migrate your redirects to sys_redirects, you can use the old realurl table as is.

Repository\PathMappingRepository:

  public function findPageidForPathFromRealurl(string $path, int $languageId) : int
  {
        $path = ltrim($path, '/');

        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tx_realurl_pathdata');
        $uid = $queryBuilder->select('tx_realurl_pathdata.page_id')
            ->from('tx_realurl_pathdata')
            ->join(
                'tx_realurl_pathdata',
                'pages',
                'p',
                $queryBuilder->expr()->eq('tx_realurl_pathdata.page_id',$queryBuilder->quoteIdentifier('p.uid'))
            )
            ->where(
                $queryBuilder->expr()->like('tx_realurl_pathdata.pagepath', $queryBuilder->createNamedParameter($path)),
                $queryBuilder->expr()->eq('tx_realurl_pathdata.language_id', $queryBuilder->createNamedParameter($languageId, \PDO::PARAM_INT)),
                $queryBuilder->expr()->eq('p.sys_language_uid', $queryBuilder->createNamedParameter($languageId, \PDO::PARAM_INT))
            )
            ->orderBy('tx_realurl_pathdata.uid', 'DESC')
            ->execute()
            ->fetchColumn(0);
        $this->logger->debug("findPageidForPathFromRealurl: path=$path language=$languageId returns $uid");
        return (int)$uid;
  }