SQL Querying Best Practices
Query hygiene reduces incidents and makes EXPLAIN-driven tuning possible.
How to Use This List
- Apply in SQL review checklist.
- Enforce via ORM lint rules where possible.
- Revisit when p95 latency regresses.
A - Projection and Shape
- Select explicit columns in application queries. Survives schema growth.
- Paginate large result sets. Keyset over deep OFFSET.
- Return only needed rows. Filter in SQL not in app loops.
B - Joins and Logic
- Prefer EXISTS for existence checks. Avoid IN NULL traps.
- Qualify all column names in multi-table SQL. Prevents ambiguous references.
- Put optional join filters in ON. Preserves OUTER JOIN semantics.
C - Performance Culture
- EXPLAIN before index sprawl. Verify bottleneck.
- Keep statistics fresh after bulk changes. ANALYZE or autovacuum.
- Store SQL in version control. Reviewable diffs.
FAQs
Is SELECT * OK in psql?
Fine for exploration; not for app code.
Related
- SQL Querying Basics - examples
- 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+.