I Optimized My Database Queries Into a Corner — The Performance Trap Nobody Warns You About

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;

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? What if—

You see where this is going.

The Over-Optimization Spiral

Phase 1: Index Everything

I added indexes on category, created_at, and even a composite index on (category, created_at). Then I added a partial index for published articles only.

Query time went from 12ms to 8ms. Great! 33% improvement!

Except write operations (new articles, updates) went from ~15ms to ~45ms because now PostgreSQL had to maintain four indexes instead of one.

Phase 2: The N+1 "Fix"

I noticed our article detail page was making 3 separate queries:

1. Get the article
2. Get the author info
3. Get related articles

"Classic N+1 problem!" I said. Let me combine them with JOINs!

-- The "optimized" mega-query
SELECT a.*, u.name, u.avatar,
  (SELECT json_agg(r.*) FROM (
    SELECT id, title, excerpt FROM articles 
    WHERE category = a.category AND id != a.id 
    ORDER BY created_at DESC LIMIT 3
  ) r) as related
FROM articles a
JOIN users u ON a.author_id = u.id
WHERE a.slug = $1;

Beautiful, right? One query to rule them all!

Except the query planner decided this was a great opportunity to do a sequential scan on the entire articles table. That 3-query approach that took ~25ms combined? My "optimized" single query took ~180ms.

Phase 3: Materialized Views

"This needs materialized views," I declared. I created refresh-on-schedule materialized views for the homepage, category pages, and search results.

The reads were blazing fast. Like, 2ms fast. I felt like a genius.

Then someone published an article and asked why it didn't show up.

Right. Materialized views need refreshing. So I set up a cron job to refresh every 5 minutes.

Then someone asked why their edit didn't show up.

I reduced the refresh interval to 30 seconds.

Then the database CPU spiked every 30 seconds because refreshing materialized views isn't free.

The Real Problem

I was optimizing for benchmark numbers, not user experience.

The original queries were fine because:

• The dataset was small (under 200 articles)
• PostgreSQL's query planner is smart
• The app had proper caching at the application layer
• The database had plenty of headroom

My optimizations introduced:

• Write amplification from excessive indexes
• Query complexity that confused the planner
• Staleness from materialized views
• Operational overhead from refresh jobs

How I Caught Myself

A colleague asked a simple question during code review: "What's the actual bottleneck?"

I didn't know. I had never profiled the actual user-facing latency. I was optimizing based on individual query times, not end-to-end response times.

So I ran real measurements:

Metric	Before	After My "Fix"	After Revert
Homepage P95	1.2s	1.8s	1.1s
Article page P95	0.8s	1.4s	0.7s
Write latency	15ms	45ms	15ms
DB CPU avg	12%	38%	11%

The revert took 20 minutes. The "optimization" took 2 weeks.

The Framework I Now Use

Before optimizing any database query, I ask:

1. Is there an actual user-facing problem? (Not just "the query could be faster")
2. Have I measured the current baseline? (End-to-end, not isolated query time)
3. Will this optimization help at our current scale? (Or is it premature?)
4. What's the cost of this optimization? (Write penalties, complexity, maintenance)
5. Can I solve this at the application layer instead? (Caching, batching, denormalization)

The Takeaway

The best optimization is the one you don't need to make.

PostgreSQL is incredibly well-optimized for common workloads. At startup/side-project scale, your queries are probably fine. Your bottleneck is almost certainly somewhere else (network latency, unoptimized images, too many API calls, missing application-level caching).

I'm not saying "never optimize." I'm saying measure first, optimize second, and always know what problem you're actually solving.

Two weeks of my life, gone. But now I know: the most dangerous query is the one you optimize without understanding why it needs optimizing.

---

If you're building developer tools or knowledge platforms, I share lessons like this regularly. Check out what I'm working on — always happy to connect with fellow developers navigating these same traps.