𝗜 𝗔𝗱𝗱𝗲𝗱 𝟮𝟬 𝗜𝗻𝗱𝗲𝘅𝗲𝘀 𝘁𝗼 𝗙𝗶𝘅 𝗦𝗹𝗼𝘄 𝗤𝘂𝗲𝗿𝗶𝗲𝘀 𝗠𝘆 𝗗𝗮𝘁𝗮𝗯𝗮𝘀𝗲 𝗚𝗼𝘁 𝟯𝘅 𝗦𝗹𝗼𝘄𝗲𝗿

𝗜 𝗔𝗱𝗱𝗲𝗱 𝟮𝟬 𝗜𝗻𝗱𝗲𝘅𝗲𝘀 𝘁𝗼 𝗙𝗶𝘅 𝗦𝗹𝗼𝘄 𝗤𝘂𝗲𝗿𝗶𝗲𝘀 𝗠𝘆 𝗗𝗮𝘁𝗮𝗯𝗮𝘀𝗲 𝗚𝗼𝘁 𝟯𝘅 𝗦𝗹𝗼𝘄𝗲𝗿

I took over a PostgreSQL database. API response times hit 8 seconds. Users timed out.

I added 20 indexes across 12 tables. I thought this would fix it.

The database slowed down. Writes crawled. Disk usage spiked.

Many think more indexes mean faster queries. This is a mistake.

PostgreSQL updates every index on every write. Your INSERT and UPDATE tasks get heavier.

I indexed a status column with 4 values. PostgreSQL ignored the index. It used a full table scan. The index was dead weight.

I had redundant indexes. A composite index covers a single column index. I wasted resources.

I used a partial index for pending orders. This small index worked 5x faster than full indexes.

I found 14 unused indexes. I dropped them.

I used pg_stat_statements to find queries burning CPU.

I ran EXPLAIN ANALYZE for every slow query. This shows real timing data.

Results:

• Total indexes: 47 to 18
• Avg query time: 3.2s to 0.4s
• Write latency: 180ms to 25ms
• Disk usage: 12.4GB to 2.1GB

Use EXPLAIN ANALYZE before adding any index. Every index needs a specific purpose. Point to the query it helps.

Indexes are not for emergencies. They are surgical tools. Use them like one.

Source: https://dev.to/kollittle/i-added-20-indexes-to-fix-slow-queries-my-database-got-3x-slower-3ac2