One of the problems people may experience when trying out something like ZODB is trying to use it as they would use a relational database management system rather than as they would manage a normal object structure inside their program. Things like list comprehensions and careful design of the structure of the objects can improve things a lot, but I will agree that sometimes the fact that you can't as easily run ad hoc queries is sad. Also very often you simply won't need to use an index, and for a very large range of problems the graph nature of ZODB (compared with the tabular nature of the RDBMS, where you'll need indices to avoid full-table scans) is liberating.

Certainly there are some types of problems which ZODB won't work well with, but overall I quite enjoyed the experimental project I made early this year in Pyramid with ZODB. Combine it with traversal-based URL generation and you get very interesting results.

I think it's the sort of tool that I'd recommend people at least try; once they've broadened their horizons, then they can go back to using their RDBMS when they wish to. (In this it's just like Pyramid's traversal; many developers have forgotten that pattern matching is not the only good way of routing URLs.)

I've used Durus a lot (a simpler version of ZODB) and indexing was always the biggest issue I ran into. Normal object associations don't need traditional indexing but object lookup based on attribute values gets pretty painful without some indexing support.