Issue Bug Resolved

#109334ReferenceIndex::updateIndex() causes OOM on large tables due to unbounded SELECT *

Mikel Wohlschlegel 03/24/26, 08:30 updated 08/31/26, 13:00

Description

ReferenceIndex::updateIndex() loads all records of a table in a single unbounded query (SELECT * FROM {table} ORDER BY uid) and iterates over them via fetchAssociative(). While fetchAssociative() fetches one row at a time from the PHP side, PDO with MySQL/MariaDB uses buffered queries by default, meaning the entire result set is loaded into PHP memory when executeQuery() is called.

For installations with large tables, this causes fatal Out-of-Memory errors during referenceindex:update.

Reproduction

Any TYPO3 installation with a large tt_content table will trigger this when running:

typo3 referenceindex:update

Memory calculation

The tt_content table in TYPO3 v13 has a large amount of columns, including several TEXT, MEDIUMTEXT, and LONGTEXT fields (bodytext, pi_flexform, l18n_diffsource, etc.).

With realistic average data per row, each record consumes approximately ~20 KB of PHP memory as an associative array (due to PHP’s hashtable overhead per key-value pair plus string storage).

tt_content records PHP memory (result set only)
10,000 ~200 MB
50,000 ~1,000 MB (1 GB)
90,000 ~1,800 MB (1.8 GB)

This is only the buffered query result — it does not include memory used by updateRefIndexTable() processing, the existing reference index data, TYPO3 framework overhead, or other tables processed in the same run.

The default PHP memory_limit is 256M (also TYPO3’s minimum requirement). Even with a generous 1G or 2G limit, installations with 50,000+ content elements will fail. Our production instance with ~90,000 tt_content records consistently crashes with OOM.

Multi-site TYPO3 installations, university portals, and large corporate setups commonly reach these record counts — this is not an edge case.

Solution

Replace the single unbounded query with keyset pagination

Why keyset pagination over OFFSET/LIMIT:
  • Uses the primary key index — no performance degradation at high offsets
  • Deterministic: no skipped/duplicate records even with concurrent writes
  • $queryResult->free() releases memory between chunks
Impact:
  • Peak memory: ~5,000 rows × ~20 KB = ~100 MB (constant, regardless of table size)
  • No behavioral change: workspace handling, progress listener, and error reporting remain identical
  • Minimal performance overhead: a few additional indexed queries vs. one massive unbounded query

Reviews 3