Good question! It depends on a lot of factors. The low-fee demand pooled in mempools is purged by most individual nodes all the time, but some nodes, even official mempool.space nodes, set their mempool RAM usage rather high. I think this storage is essentially limited only by the choices and RAM available to node runners, so in some more charitable nodes with beefy RAM allocation, super low-fee transactions will reside almost indefinitely, ready to propagate into block templates of other nodes whenever timechain space gets unusually low. This ensures not only that low fee, low time preference but important (to the owner of the UTXOs) transactions have a high chance of getting through after sufficiently long wait times, but also that demand for timechain block space, though very elastic and volatile, will be essentially ever-present to reward miners.