Trouble are the clients. Currently, Mac OS X's Mail does not support it, although it will in Leopard. And iPhone does not support it, except with Yahoo mail. Booo!
I did find a mail bundle you can install for Mail that's kind of a hack, but works great. It opens up an additional IMAP connection to each of your mail servers and issues an IDLE request, and wakes up Mail proper whenever the server says there's something ready. Check out IMAP-IDLE. I sent myself an email from Gmail to my account at work, hosted on Exchange, and a few seconds later it popped up. Wow! My iPhone however just sat there until I manually hit refresh.
Here's an informative snippet from afp548.com:
The phone supports "push e-mail" which is most likely just IMAP using the idle command (although we'd be happy to be told they mean something else). This works by having the e-mail client, your iPhone in this case, send the idle command to the IMAP server. The client then leaves the IMAP conenction open and will wait until the server receives new mail. At which point the server will notify the client, thorugh the connection that's still open, that e-mail has arrived. The client will then go get the e-mail.
At this time, the only e-mail service that appears to with with push IMAP is Yahoo! The iPhone does not issue an IDLE command when conversing with other IMAP servers.
Update 11/2/2007: Yes, as anticipated, the Mail.app client in Leopard does support Push IMAP. And on the server side so does Googlemail IMAP, making for a nice combination.