I use DBI with PostgreSQL for some years. I think, that your question is too general. My experience is shrinkable to following points:
1/ Imagine your final sql queries. And resolve, what should be in perl and what in db (as view, for instance). Observe db-related performance optimization dependencies.
2/ Think about dynamic and static part of queries
3/ Develop own helper functions for building queries on the fly - parameters binding is strongly recommended.
4/ Think about how many rows is the real maximum of query.
5/ If the result of query is too long, think about user interface - how to list.
6/ Play with sql parts limiting result rows (offset and limit in PostgreSQL) and with pulling datas from db (1 row, n rows, all rows) into perl and, potentially, with attributes of $sth - but be aware, some of them are db dependent. Typically, I use offset, but not limit in sql-string. But I do not pull all rows from db, only actually needed, and something as $sth->{ROWS} says me count of all rows.
Thats all, I am sorry for that so general answer and my english, too :)