DEV Community

Rhaqim
Rhaqim

Posted on

Journal Entry: I Forgot to Clean Up After Myself

#go #postgres #backend #database

TL;DR: Always call defer rows.Close() after querying your database.

Recently, I ran into a silent but critical problem in my Go application backed by PostgreSQL. Everything worked fine on the surface — requests were being handled, responses came back fast… until one day, I hit a wall.

New requests started hanging unexpectedly, and I noticed some routes weren’t returning data at all. But one route continued to work: the one fetching data from the cache instead of hitting the database.

Something wasn’t right.

🔍 The Clue: pg_stat_activity

I ran the following on my Postgres server:

SHOW max_connections;
SELECT COUNT(*) FROM pg_stat_activity;
Enter fullscreen mode Exit fullscreen mode

And here was the result:

  • max_connections was set to 100
  • Active connections in pg_stat_activity? 67 😳

That number seemed way too high. On the client side (Go), I had:

db.SetMaxOpenConns(50)
Enter fullscreen mode Exit fullscreen mode

So what was holding these connections open?

🧠 Aha Moment: I Forgot defer rows.Close()

After inspecting my codebase, I realized I had this kind of pattern:

rows, err := db.Query("SELECT * FROM rows_fetcher($1)", query_key)
if err != nil {
    return nil, err
}

for rows.Next() {
    // scan logic...
}
Enter fullscreen mode Exit fullscreen mode

But no defer rows.Close(). 😱
Without explicitly closing the result set, the connection remains open and eventually floods the pool.

🔬 Digging Deeper: Seeing the Idle State

I ran:

SELECT pid, state, query, backend_start, xact_start, query_start 
FROM pg_stat_activity 
WHERE state = 'idle';
Enter fullscreen mode Exit fullscreen mode

I noticed many of these had timestamps from weeks or even months ago!

🧹 Cleaning Up

I decided to take a few actions:

  1. Update all database queries in my Go code to include: 
defer rows.Close()
Enter fullscreen mode Exit fullscreen mode
  1. Use pg_terminate_backend(pid) to clean up old, stale processes manually.
  2. Add SetConnMaxIdleTime to my DB setup: 
db.SetConnMaxIdleTime(5 * time.Minute)
Enter fullscreen mode Exit fullscreen mode
  1. Consider setting PostgreSQL server config options like: 
idle_in_transaction_session_timeout = 60000       # 1 min
idle_session_timeout = 300000                     # 5 min
Enter fullscreen mode Exit fullscreen mode

✅ The Result After 24 Hours

I checked back the next day and ran:

SELECT COUNT(*) FROM pg_stat_activity;
Enter fullscreen mode Exit fullscreen mode

To my surprise and relief, the number was down to 1.

One. From 67.

💡 Takeaways

  • Always close your rows after querying:
  • Use pg_stat_activity to spot stale connections.
  • Monitor timestamps, old queries might still be holding connections open.
  • Use connection pooling wisely (SetMaxOpenConns, SetConnMaxIdleTime).
  • Set database level timeouts to catch idle sessions that your app misses.

Top comments (0)