𝗜 𝗔𝗱𝗱𝗲𝗱 𝟮𝟬 𝗜𝗻𝗱𝗲𝘅𝗲𝘀 𝗔𝗻𝗱 𝗠𝘆 𝗗𝗕 𝗚𝗼𝘁 𝟯𝘅 𝗦𝗹𝗼𝘄𝗲𝗿

𝗜 𝗔𝗱𝗱𝗲𝗱 𝟮𝟬 𝗜𝗻𝗱𝗲𝘅𝗲𝘀 𝗔𝗻𝗱 𝗠𝘆 𝗗𝗕 𝗚𝗼𝘁 𝟯𝘅 𝗦𝗹𝗼𝘄𝗲𝗿

I started working with a PostgreSQL database. API response times were 8 seconds. Users timed out.

I added 20 indexes to fix it. I thought more indexes meant more speed. I was wrong.

The database slowed down. Writes crawled. Disk space filled up.

Here is why it happened.

PostgreSQL updates every index during every write. Every INSERT or UPDATE takes longer. Too many indexes kill write speed.

Some indexes are dead weight. I indexed a status column with 4 values. The database ignored the index. It used a full table scan instead.

I also had redundant indexes. I had one for email and one for email and name. The second one already covered the first.

Here is how I fixed it.

• I found indexes with zero scans.
• I dropped 14 unused indexes.
• I used pg_stat_statements to find slow queries.
• I used EXPLAIN ANALYZE to see the execution plan.

I switched to partial indexes for specific statuses. These are smaller and faster.

The results.

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

My rules for indexes.

• Never add an index without EXPLAIN ANALYZE.
• Every index must support a specific query.
• No unnecessary indexes.

Indexes are surgical tools. Use them with care.

Source: https://dev.to/kollittle/i-added-20-indexes-to-fix-slow-queries-my-database-got-3x-slower-3ac2 Optional learning community: https://t.me/GyaanSetuAi