PostgreSQL with Python: Advanced Query Optimization Techniques

Apr 13, 2025

—

Written by: Rizqi Mulki – Python Backend Developer

PostgreSQL is one of the most powerful open-source relational databases, but without proper optimization, complex queries can slow down your application. As a backend developer working with Python and PostgreSQL, I’ve compiled the most effective query optimization techniques to improve performance in production environments.

Why Optimize PostgreSQL Queries?

Poorly optimized queries can lead to:

❌ Slow API responses
❌ High CPU and memory usage
❌ Database locks and timeouts

By applying these techniques, I’ve achieved 5-10x faster queries in real-world applications.

1. Efficient Indexing Strategies

a) Proper Index Selection

-- ❌ Bad: No index on frequently queried column
SELECT * FROM users WHERE email = 'user@example.com';

-- ✅ Optimized: Create an index
CREATE INDEX idx_users_email ON users(email);

Best Practices:

✔ Use B-tree for equality/range queries
✔ Use GIN for JSON/array columns
✔ Use Partial Indexes for filtered queries

b) Composite Indexes for Multi-Column Queries

-- ❌ Bad: Two separate indexes
CREATE INDEX idx_users_firstname ON users(first_name);
CREATE INDEX idx_users_lastname ON users(last_name);

-- ✅ Optimized: Composite index
CREATE INDEX idx_users_fullname ON users(first_name, last_name);

2. Query Execution Analysis

a) EXPLAIN ANALYZE

Use this to see the query execution plan:

# Python + psycopg2 example
cur.execute("EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123")
print(cur.fetchall())

Key Metrics to Check:

Seq Scan (Full table scan → Bad)
Index Scan (Good)
Actual Rows vs Estimated Rows (Large mismatch means outdated statistics)

b) Enable Query Logging

ALTER SYSTEM SET log_min_duration_statement = 100;  # Log queries >100ms

3. Advanced JOIN Optimization

a) Use INNER JOIN Instead of WHERE

-- ❌ Suboptimal
SELECT u.*, o.* FROM users u, orders o WHERE u.id = o.user_id;

-- ✅ Optimized
SELECT u.*, o.* FROM users u INNER JOIN orders o ON u.id = o.user_id;

b) Avoid Nested Loops for Large Tables

-- Force Hash Join if better for your case
SET enable_nestloop = off;

4. Python-Specific Optimizations

a) Connection Pooling

# Using psycopg2.pool
from psycopg2 import pool

connection_pool = pool.SimpleConnectionPool(
    minconn=1,
    maxconn=10,
    host="localhost",
    database="mydb"
)

b) Batch Inserts

# ❌ Slow: Individual inserts
for user in users:
    cur.execute("INSERT INTO users (name) VALUES (%s)", (user.name,))

# ✅ Fast: Batch insert
cur.executemany(
    "INSERT INTO users (name) VALUES (%s)",
    [(user.name,) for user in users]
)

5. PostgreSQL Configuration Tweaks

a) Adjust work_mem

-- For complex sorts/aggregations
SET work_mem = '64MB';

b) Optimize shared_buffers

-- Typically 25% of available RAM
ALTER SYSTEM SET shared_buffers = '4GB';

Key Takeaways

✔ Index wisely – Avoid over-indexing but cover critical queries
✔ Analyze execution plans – Use EXPLAIN ANALYZE
✔ Optimize JOINs – Prefer INNER JOIN over WHERE
✔ Use connection pooling – Critical for web applications
✔ Tune PostgreSQL config – Especially work_mem and shared_buffers

For Recruiters

I specialize in high-performance Python/PostgreSQL backends. If you need a developer who can optimize database-heavy applications:

📧 Email: rizqimulkisrc@gmail.com
🔗 LinkedIn: linkedin.com/in/rizqi-mulki-261a5372
💻 GitHub: github.com/rizqimulkisrc

What’s your biggest PostgreSQL performance challenge? Let’s discuss in the comments!