🧠 Optimizing PostgreSQL Queries in a Full-Stack App: A Developer’s Playbook

🔍 1. Use Indexing Wisely — Not Blindly

✅ What It Does:

Indexes speed up data retrieval by creating a quick lookup structure. Perfect for WHERE clauses, JOIN conditions, and ORDER BY.

🧪 Example:

-- Create an index on frequently filtered column CREATE INDEX idx_users_email ON users(email); 

⚠️ Common Mistake:

Over-indexing! Every insert/update operation becomes slower.

🛠️ Best Practices:

• Use pg_stat_user_indexes to find unused indexes.
• Composite indexes help with multiple conditions:

CREATE INDEX idx_orders_customer_status ON orders(customer_id, status); 

🧠 2. Decode Queries with EXPLAIN ANALYZE

Before optimizing, you need to understand what your query actually does.

🧪 Example:

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42; 

This tells you if it’s doing a sequential scan (slow) vs an index scan (fast).

⚠️ Common Mistake:

Ignoring cost and actual execution time. Always look at actual time instead of just cost.

✅ Best Practice:

• Use tools like PgHero or auto_explain in dev/staging.
• Log slow queries for analysis.

🪓 3. Eliminate the N+1 Query Problem

This common ORM issue causes 1 parent + N child queries, hammering performance.

🧪 Example (Sequelize):

// BAD: Causes N+1 const posts = await Post.findAll(); for (const post of posts) { const comments = await post.getComments(); } 

✅ Fix It:

// GOOD: Eager load const posts = await Post.findAll({ include: [{ model: Comment }] }); 

⚠️ Mistake:

Failing to use eager/lazy loading appropriately.

🧬 4. Join Smartly (and with Purpose)

Joins are powerful — but misuse can slow things down.

🧪 Example:

SELECT o.id, c.name FROM orders o JOIN customers c ON o.customer_id = c.id; 

⚠️ Mistake:

Joining large tables unnecessarily or without indexing the join key.

✅ Best Practices:

• Use INNER JOIN instead of LEFT JOIN when possible.
• Always index foreign keys.

🧰 5. Sequelize Performance Tips

🔹 Use attributes to fetch only necessary fields:

User.findAll({ attributes: ['id', 'name'] }); 

🔹 Disable logging in production:

const sequelize = new Sequelize(..., { logging: false }); 

🔹 Use raw queries when you hit ORM limits:

await sequelize.query("SELECT COUNT(*) FROM users WHERE active = true", { type: QueryTypes.SELECT }); 

📦 6. Stored Procedures & Functions = Reusable Logic + Speed

✅ Use-case:

Move complex business logic to the DB for faster execution.

🧪 Example:

CREATE OR REPLACE FUNCTION active_user_count() RETURNS INT AS $$ BEGIN RETURN (SELECT COUNT(*) FROM users WHERE active = true); END; $$ LANGUAGE plpgsql; 

Call from Sequelize:

await sequelize.query("SELECT active_user_count()"); 

⚠️ Caveat:

Use stored procedures sparingly; they’re harder to debug and test.

🖼️ 7. Data Views — Precomputed Results for Fast Reads

🧪 Example:

CREATE VIEW top_customers AS SELECT customer_id, SUM(amount) AS total_spent FROM orders GROUP BY customer_id HAVING SUM(amount) > 10000; 

Use it like a regular table:

SELECT * FROM top_customers; 

✅ Benefits:

• Simplifies frontend queries.
• Improves performance for complex analytics dashboards.

🧹 8. Vacuum & Analyze — Housekeeping You Shouldn’t Skip

PostgreSQL doesn’t auto-clean up dead tuples.

✅ Command:

VACUUM ANALYZE; 

Or use auto-vacuum — but monitor it.

⚠️ Mistake:

Ignoring bloat in high-transaction tables like logs or sessions.

✅ Best Practices:

• Schedule VACUUM FULL for rarely updated large tables.
• Monitor with pg_stat_user_tables.

🏁 Final Thoughts

Database optimization isn’t just the DBA’s job anymore. As a full-stack developer, you own performance too.

🔑 Golden Rule: Don’t optimize blindly. Measure first, optimize second.

📚 TL;DR Cheat Sheet