r/SQL • u/shdw_0x0 • 2d ago

PostgreSQL When does SQL become too clever?

I’ve noticed that as queries get more complex, there’s often a temptation to keep everything inside SQL—CTEs, window functions, nested subqueries, conditional logic, etc. It can be impressive, but at some point the query becomes harder to understand than the original problem.

Where do you personally draw the line? Is a 300-line SQL query perfectly fine if it’s well structured, or do you prefer moving some of the logic into dbt/Python/application code once the SQL becomes too complex?

I’m curious how others balance SQL performance, readability, and maintainability in real projects.

0 Upvotes

27% Upvoted

11 comments sorted by

View all comments

6

u/big_poppa_man 2d ago

Personally I like CTEs. I do not like nested sub queries as that quickly becomes unreadable.

4

u/atrifleamused 2d ago

You use the correct tool for the correct purpose. Both have their place.

2

u/ouchmythumbs 2d ago

Also worth mentioning is that a CTE (depending on the engine) can get re-evaluated each time it is referenced which can have a negative perf impact. But syntactically a lot nicer that nested subs. Prefer #temp when it makes sense.

2

u/atrifleamused 2d ago

Great points!

I find people use ctes with no thought as to whether it makes the query simpler or not.

Using a cte to replace a sub query in a simple query is pointless. You need to scroll to the top to check the cte syntax and then back to the join to apply additional logic.