I spent two weeks optimizing my database queries. The app got slower.

Not marginally slower. Noticeably slower. Users started complaining about page load times that had been perfectly fine before I "fixed" anything.

Here's how I did it, and more importantly, what I learned.

The Setup

We had a Next.js app with Supabase (PostgreSQL) handling our data layer. Average page load: 1.2 seconds. Nothing amazing, but perfectly acceptable for a knowledge base platform.

Then I looked at the query log and saw something that triggered my optimization instincts:

-- The "bad" query that was working fine SELECT * FROM articles WHERE category = 'ai-llm' ORDER BY created_at DESC ;

Enter fullscreen mode

Exit fullscreen mode

Simple. Direct. Returns 47 rows in ~12ms.

But I thought: What if we add an index? What if we paginate? What if we add materialized views?…