Tiling window managers don't maximize the use of small screen area; they ensure you'll almost always be using a fraction of the screen instead of the full screen.
Overlapping windows are the thing that maximized the use of a small screen, because they let you dedicate as much or as little of the screen to any given application when its window is on top.
What you are after, rather, is an efficient way of having the entirety of the windows of multiple apps on screen simultaneously. That's a slightly different problem. I personally get by with a combination of cascaded windows[1] and maximized windows with quick keyboard window switching.
[1] When you cascade windows such that the bottom left corner of each window forms a diagonal line across the bottom left edge of the screen, you can quickly select whatever window you want with a mouse, usually more efficiently than with a task bar, since the application identity is far more obvious when you can see a rectangle of its contents. And with choice of where each window is in the cascade order, you can get usable data out of multiple windows simultaneously.
I have found cascading to be an unhappy medium in the past. In awesome-wm, I'll tap [Win+Space] to toggle between 100% overlapped (fullscreen with unoccluded task bar) and 0% overlapped (multi-column horizontal). [Win+J] and [Win+K] cycle windows. [Win+H] and [Win+L] resize the width of the primary column in a multicolumn layout.
So let's say I have a terminal window with VIM, and a web browser with a tutorial or documentation open up to the left of it. I quickly need to scroll the web page down to continue the example, BUT the VIM window is currently focused. Assuming the web browser can be scrolled using [J] and [K], I'll simply tap [Win-K, J, Win-J] to accomplish this task while avoiding modifying the layout or leaving the home row to use a pointing device. If I don't actually need to display multiple columns at the same time, I just press the change layout key to stop using a multicolumn layout.
So as a VIM user I've been very happy with this setup in awesome-wm for the past 2+ years, but if you wish to recommend an alternative I will definitely give it an install.
edit: I was able to write a new layout function for awesome-wm which implements diagonal cascading in the manner you described and retains my keybindings. I definitely agree that cascading makes it bluntly obvious which window is the currently focused one. I'll have to play around with it more.
Overlapping windows are the thing that maximized the use of a small screen, because they let you dedicate as much or as little of the screen to any given application when its window is on top.
What you are after, rather, is an efficient way of having the entirety of the windows of multiple apps on screen simultaneously. That's a slightly different problem. I personally get by with a combination of cascaded windows[1] and maximized windows with quick keyboard window switching.
[1] When you cascade windows such that the bottom left corner of each window forms a diagonal line across the bottom left edge of the screen, you can quickly select whatever window you want with a mouse, usually more efficiently than with a task bar, since the application identity is far more obvious when you can see a rectangle of its contents. And with choice of where each window is in the cascade order, you can get usable data out of multiple windows simultaneously.