Q:

Could Wordpress have lazy-loading queries? Or is it limitation of PHP?

There's an interesting comparison of the functional Clojure language vs Python for lazy-loading and similar functions here. It got me thinking - is there no comparable feature in PHP? For example, when you do a Wordpress query:
$loop = new WP_Query( ... )

You immediately have the full array of posts $loop->posts and (unless I'm mistaken), that array was fully created on WP_Query constructor. In Java, or Python with iterator, you generally would have a database-cursor-iterator that you would call a get-next function to get the next item as needed.
Is the reason Wordpress doesn't do this based on lack of such iterator notion in PHP, or some other reason?

A:

The short answer is that yes you can iterate over a database cursor in PHP. The WP_Query() class is a convenience library to wrap this up and make it easy for you to use the filters/parameters provided in that API. Ultimately WordPress uses the mysql_* extensions (deprecated) on current version and older installs, however when using newer version of PHP it uses mysqli as the underlying database connector.
In regards to WP_Query, its __construct() calls query() which then calls get_posts(). On line 3554 it uses the global $wpdb object to call get_results(). This ultimately calls the query() method, (and _do_query() internally) and finally iterates over the results on line 1615.
You are free to use the WP_Query(), $wpdb, or even mysql_*/mysqli to handle your database code in WordPress, listed from a higher level of abstraction to lower. Things like protection from SQL injections are provided by the higher level classes, but you have more control going to a lower level class.
The possibility of using more efficient yield statements has been discussed here, and apparently part of the resistance is that WP needs to work everywhere even on ancient systems - the same reason it is using jQuery 1.x series that works with very old IE versions.

