I've found that CTEs help ameliorate this quite a bit, and it's especially exciting that pg will soon be removing the mandatory performance implications of CTEs

I've found that CTEs help ameliorate this quite a bit, and it's especially exciting that pg will soon be removing the mandatory performance implications of CTEs in pg12 (fencing via CTEs will remain optional via a keyword switch, but in pg<12 CTE fencing is impossible to disable). CTEs seems like a very nice middle ground for code reuse - enshrining them in views is nice for somethings but keeping them raw and composable is to my preference since application developers aren't losing any visibility of logic occurring within the composed in blocks... this is definitely not a simple problem to solve though and is a constant balancing act so there is some clear room for improvement.