Performance Tuning Checklists: Optimize PostgreSQL Databases

Previously in this course, we explored advanced window functions to handle complex analytical queries. Now that your store database is growing, it’s time to shift from writing queries to ensuring they run efficiently. This lesson provides a practitioner’s checklist for ongoing database health.

Performance tuning isn't about guessing; it's about observation and systematic refinement. Whether you're interested in optimizing SQL queries or general database maintenance, the process follows the same rigorous path.

The Performance Audit Framework

When an application slows down, don't just add more indexes. Instead, treat your database like a machine that needs routine inspection. We follow a three-step cycle: Observe, Analyze, and Prune.

1. Audit Your Indexes

Indexes are double-edged swords. While they speed up SELECT queries, they slow down INSERT, UPDATE, and DELETE operations because the database must update the index structure every time the data changes.

To see which indexes are actually being used, query the pg_stat_user_indexes view. If an index has a high number of idx_scan (scans performed), it’s earning its keep. If it’s near zero, it’s likely overhead.

SQL
SELECT
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS number_of_scans
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan ASC;

2. Review Query Plans

Before changing anything, use EXPLAIN ANALYZE to see how PostgreSQL executes your slowest queries. As we discussed when analyzing query plans, the goal is to look for "Seq Scans" (sequential scans) on large tables. If you see a sequential scan on a table with thousands of rows, you’ve likely found a missing index.

3. Remove Unused Indexes

If an index has zero or near-zero scans over a long period (weeks or months in production), remove it.

Warning: Before dropping an index, ensure you have a backup of the CREATE INDEX statement so you can restore it if you discover a monthly report you forgot about suddenly slows to a crawl.

SQL
-- Example: Removing an index that is never used
DROP INDEX IF EXISTS idx_products_category_unused;

Worked Example: Optimizing the Store Checkout

Let's say our store's orders table has become slow. We suspect the index on customer_id is redundant because we already have an index on (customer_id, created_at).

1. Check for redundancy: We see idx_orders_customer_id has very few scans compared to our composite index.
2. Verify the plan: We run:

   SQL
   EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;
   

3. The Result: We observe that PostgreSQL uses the composite index (customer_id, created_at) even for queries filtering only by customer_id.
4. Action: We drop the redundant, narrower index to reduce write overhead.

Hands-on Exercise

1. Run the pg_stat_user_indexes query provided above on your store database.
2. Identify one index with the lowest idx_scan count.
3. Does this table have other, broader indexes that cover the same column? If so, drop the unused one to clean up your schema.

Common Pitfalls

• Premature Optimization: Don't delete an index just because it hasn't been used in a week. Some reports run quarterly. Ensure your "uptime" for tracking usage is representative of your business cycle.
• Indexing Everything: Adding an index to every column will make your database look like it's performing well on reads, but your write speed will plummet.
• Ignoring Foreign Keys: Remember that implementing foreign keys often requires an index on the foreign key column to keep joins efficient. Don't drop these!

FAQ

Q: How do I know if an index is "big" enough to care about? A: Use pg_relation_size('index_name'). If the index is taking up gigabytes of disk space but has zero scans, it is a prime candidate for removal.

Q: Can I drop an index while the app is running? A: Yes, DROP INDEX is a standard DDL operation. However, in very high-traffic production environments, consider DROP INDEX CONCURRENTLY to avoid locking the table.

Recap

Performance tuning is a continuous process of auditing, planning, and pruning. By monitoring your idx_scan counts and using EXPLAIN ANALYZE to validate your assumptions, you ensure your database remains lean and fast as your store grows.

Up next: We will learn how to automate complex tasks by building our first stored procedures.