🚨 3 PostgreSQL Anti-Patterns That Are Silently Killing Your App's Performance

PostgreSQL is one of the most powerful relational databases on the planet. Out of the box, it can handle massive workloads. But as your application scales, the way you write queries and structure your schema matters more than the database engine itself.

If your app is starting to feel sluggish, it might not be a lack of resources. You might be falling into one of these three common PostgreSQL anti-patterns.

Here is how to spot them and how to fix them.

1. The SELECT * Trap (and why it ruins memory)

When we are iterating quickly, it is incredibly tempting to just write SELECT * FROM users and let the backend filter out the fields it doesn't need.

Why it’s an anti-pattern:
PostgreSQL has to read the data from the disk, load it into memory, and send it over the network to your application. If your users table has 30 columns (including heavy JSONB blobs or large TEXT fields) and you only need the id and email, you are forcing the database to do 10x the I/O work for no reason.

Furthermore, SELECT * breaks index-only scans. If you have an index on email, a query like SELECT email FROM users WHERE email = 'x' can be resolved purely from the index without even touching the main table. SELECT * forces Postgres to fetch the whole row.

The Fix:
Always explicitly define your columns, even if you are using an ORM.

-- ❌ Bad
SELECT * FROM orders WHERE status = 'pending';

-- ✅ Good
SELECT id, customer_id, total_amount FROM orders WHERE status = 'pending';

2. Over-Indexing (The "Just Add an Index" Fallacy)

When a query is slow, the immediate reaction is usually: "Let's just throw an index on that column!"

Why it’s an anti-pattern:
Indexes are not free. Every time you INSERT, UPDATE, or DELETE a row, PostgreSQL has to update the main table and every single index associated with that table. If you have a write-heavy table (like an event logger or analytics tracker) with 10 different indexes, your write latency will skyrocket.

Additionally, Postgres query planners are smart. If an index isn't highly selective (e.g., a boolean column like is_active where 95% of users are active), Postgres will likely ignore the index entirely and do a sequential scan anyway. You are paying the write penalty for an index that never gets used!

The Fix:

1. Periodically check for unused indexes. Postgres tracks this for you! You can run this query to find indexes that the database is ignoring:

SELECT relname, indexrelname, idx_scan 
FROM pg_catalog.pg_stat_user_indexes 
WHERE idx_scan = 0;

1. Drop unused indexes and lean into composite indexes for queries that frequently filter by the same multiple columns.

3. The ORM N+1 Query Disaster

If you are using Prisma, TypeORM, Hibernate, or Eloquent, you have probably written this exact bug without realizing it.

Why it’s an anti-pattern:
The N+1 problem occurs when your code fetches a list of records, and then loops through that list to fetch related data for each record.

// ❌ The N+1 Disaster in action
const users = await db.users.findMany(); // 1 query
for (const user of users) {
  // This runs a NEW query for every single user! (N queries)
  const posts = await db.posts.find({ authorId: user.id }); 
}

If you have 1,000 users, you just hit your database 1,001 times over the network for something that should have been a single round trip. This is the #1 cause of API latency.

The Fix:
Use JOINs or rely on your ORM's eager-loading capabilities to fetch everything in a single optimized query.

// ✅ Good: Fetches users and their posts in a single round trip
const usersWithPosts = await db.users.findMany({
  include: { posts: true }
});

Note: Under the hood, this translates to either a LEFT JOIN or exactly two queries (one for users, one for all posts matching those user IDs via an IN clause).

Summary

Scaling a database isn't just about throwing more RAM and CPU at your cloud provider. It is about respecting the network boundary, understanding your indexes, and keeping a close eye on the SQL your ORM is actually generating.

---

What is the worst database performance bug you've ever had to debug? Let me know in the comments! 👇