PostgreSQL Database Troubleshooting

Project Overview

This project demonstrates how to investigate and resolve database performance issues in PostgreSQL.

A slow query was analyzed using EXPLAIN ANALYZE and optimized by creating an index.

The following query was performing poorly because PostgreSQL had to scan the entire table.

SELECT * FROM customers WHERE city = 'abc';

Execution Plan: Sequential Scan

This indicates PostgreSQL scanned the full table, which is inefficient for large datasets.

To inspect running queries we used:

SELECT pid, usename, datname, state, query
FROM pg_stat_activity;

This helps database engineers monitor active sessions and identify problematic queries.

To optimize the query we created an index on the city column.

CREATE INDEX idx_city ON customers(city);

After indexing, PostgreSQL used an Index Scan instead of a sequential scan, significantly improving performance.