... make use of an iterator or tied array and avoid accumulating, but I would also like to keep the current user interface of simply iteratoring over the results with a for loop. Is this possible?

You could use a tied array in a for loop if the number of items is known in advance (FETCHSIZE is called; see also my reply to the relatively recent thread How can I create an iterator that works inside foreach()). Otherwise I recommend you bite the bullet and switch to while, as that's generally the way to work with iterators in Perl, and iterators are incredibly powerful, as HOP taught us. Such a while can often also be expressed as a C-style for(;;), but IMHO that doesn't always look as elegant as while and foreach.

Update: Another alternative is to provide a custom API for looping with a callback, for example like Mojo::Collection's ->each. I think it would be less performant than a while, though.