Sadly, a good DBA would be able to give you information on these things, but the DBA like system admins is a fading "not needed" position. I know HN hates stored procedures, but having an application use stored procedures instead of on-the-fly queries allows a team to do the proper optimizations. Databases evolve with use and added records, and few teams devote resources to stay ahead of the changes.
I personally love stored procedures, and Postgres, MSSQL, Sybase and Oracle all provide pretty good languages to implement them in.
In my experience the most reliable and long living projects treat the DB as its own separate product, whose interface to data mutation is via stored procs. The db can then be sanely designed with good data integrity rules (foreign keys, unique indexes and data column constraints).
Then external callers use the stored procs as their API, and then mostly don't have to worry about the internals. You can easily create a web based client, REST calls or a native GUI app that all work directly off the same db.
However they do have to worry about :
-connection timeouts
-query timeouts
-deadlocks
