SQL Best Practices for Modern Application Developers

Introduction

In modern application development, Object-Relational Mappers (ORMs) like Prisma, Hibernate, or ActiveRecord make it incredibly easy to interact with databases. However, this convenience often hides the underlying queries, resulting in sub-optimal database performance. Understanding raw SQL best practices is crucial for keeping your database fast, responsive, and cost-effective.

---

1. Indexing Fundamentals

An index is a structure that allows the database engine to locate records without scanning the entire table. Think of it as a book index rather than flipping through every single page.

Primary and Unique Indexes: Standard lookups like `WHERE id = 5` are auto-indexed by primary keys.
Foreign Keys: Always index foreign key columns (e.g. `tenant_id`, `user_id`) as they are commonly used in `JOIN` operations.
Composite Indexes: When querying on multiple columns (e.g. `WHERE status = 'active' AND created_at > NOW()`), create a composite index on both columns. The order of columns in the index matters: place the column with the highest filter selectivity (or equality filters) first!

sql

-- Creating an optimized composite index CREATE INDEX idx_orders_status_date ON orders (status, order_date DESC);

---

2. Stop Using SELECT *

It is tempting to write SELECT * FROM users during prototyping. But in production, fetching all columns can severely degrade performance.

The Cost of SELECT * 1. Network Overhead: Transferring useless large columns (like text descriptions, avatar images, or JSON logs) increases network payload size. 2. Memory Footprint: The application server must allocate memory to parse and serialize columns that aren't even used in the UI. 3. Preventing Covered Indexes: If all required columns are in an index, the database can fetch data directly from the index without doing a secondary lookup on the main table heap. Using `*` instantly breaks this optimization.

sql

-- Bad: Pulling entire row structure

-- Good: Fetching only the exact required columns SELECT user_id, username, status FROM users WHERE email = 'dev@sqlbeast.dev';

---

3. Reusable CTEs vs Subqueries

Common Table Expressions (CTEs) make your SQL code readable, modular, and easy to debug. They act like local temporary variables/tables within a single query execution.

Why Choose CTEs? - Readability: CTEs flow sequentially from top to bottom, making it easy for another developer to understand your logic. Subqueries require reading from the inside out. - Optimization (PostgreSQL 12+): PostgreSQL allows CTEs to be merged into the main query plan or materialized, giving the database engine great flexibility.

sql

-- modular, highly readable CTE structure WITH active_users AS ( SELECT user_id, username FROM users WHERE status = 'active' ), monthly_purchases AS ( SELECT user_id, SUM(amount) AS total_spent FROM transactions WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE) GROUP BY user_id ) SELECT u.username, COALESCE(p.total_spent, 0) AS monthly_bill FROM active_users u LEFT JOIN monthly_purchases p ON u.user_id = p.user_id ORDER BY monthly_bill DESC;

---

Conclusion

Tuning your SQL queries isn't black magic. By applying disciplined indexing, fetching only the required column projections, and wrapping complex procedures in readable CTEs, you can scale your database performance to handle millions of transactions with ease. Happy querying!