Advanced SQL Best Practices
Advanced SQL pays off when readable and measured.
How to Use This List
- Require EXPLAIN for new CTE/window/lateral queries.
- Store complex SQL in repo, not hidden ORM strings.
A - Clarity
- Name CTEs by business step. Not cte1.
- Comment invariant assumptions. Status values documented.
- Limit dynamic SQL to proven cases. Injection and plan risk.
B - Performance
- EXPLAIN ANALYZE before prod. Especially window and sort nodes.
- Index PARTITION BY and JOIN keys. Match window ORDER BY.
- Materialize expensive CTE once when reused. AS MATERIALIZED.
C - Safety
- Guard recursive CTE depth. Cycle detection.
- Test NULL partition rows. Window frames with sparse data.
- Version SQL with migrations. Reviewable diffs.
FAQs
Should ORMs own advanced SQL?
Let ORM do CRUD; raw SQL files for reporting and complex pipelines.
Related
- Window Functions - analytics
- EXPLAIN Basics - plans
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.