Common PostgreSQL Mistakes Developers Make

PostgreSQL is a very forgiving database right up until your application becomes busy enough that bad habits start compounding.

That is when the same small mistakes show up again and again:

• queries that read far too much data
• indexes that do not match the real workload
• transactions that stay open too long
• schema shortcuts that looked convenient early on
• and migrations that were safe on a laptop but risky in production

The frustrating part is that many of these mistakes are not obvious at first.

A system can feel fine with:

• a few thousand rows
• a handful of users
• and low concurrency

Then traffic grows, data grows, the ORM starts generating more SQL than expected, and suddenly PostgreSQL is being blamed for decisions the application made months ago.

This guide covers the most common PostgreSQL mistakes developers make and, more importantly, what to do instead.

1. Designing Tables Without Starting From Real Query Patterns

A lot of developers design schemas from a model diagram first and the application queries second.

That is backwards.

A schema is not only a representation of the domain. It is also the structure through which the application reads and writes data every day.

If you do not understand the core query patterns early, you usually end up with:

• missing indexes
• awkward joins
• oversized rows
• poor pagination
• and expensive filtering on the wrong columns

What to do instead

Before finalizing a table, write down the 3 to 5 most important production queries it will serve.

For example:

select id, status, created_at
from orders
where account_id = $1
order by created_at desc
limit 20;

That single query already tells you a lot:

• account_id matters
• created_at matters
• result shape matters
• and a supporting multicolumn index probably matters

A schema that ignores query shape usually becomes a performance problem later.

2. Using select * in Production Code

select * is convenient during exploration.

It is lazy in real application code.

The problem is not only bandwidth. It is also:

• wasted I/O
• wider rows pulled into memory
• more work for the network and serializer
• stronger coupling to table changes
• fewer opportunities for leaner indexes and smaller result sets

Bad

select *
from users
where id = $1;

Better

select id, email, created_at, status
from users
where id = $1;

When a table grows from 6 columns to 24 columns over time, select * quietly gets worse while the handler code still looks innocent.

3. Not Learning EXPLAIN ANALYZE

A surprising number of developers try to fix PostgreSQL performance by instinct.

That usually leads to:

• random indexes
• ORM rewrites without evidence
• and lots of time lost guessing

EXPLAIN shows the plan PostgreSQL expects to run. EXPLAIN ANALYZE runs the query and shows what actually happened.

That difference matters.

Example

explain analyze
select *
from users
where lower(email) = lower($1);

Even basic plan reading can tell you:

• whether PostgreSQL used an index
• whether it scanned too many rows
• whether it sorted unnecessarily
• whether row estimates were badly wrong
• and where most of the time was spent

You do not need to become a planner expert. But you do need to stop treating query behavior like a mystery.

4. Creating Indexes by Guesswork

Indexes are useful because they speed up reads. They are dangerous because they also increase storage cost and write overhead.

That means more indexes is not automatically better.

Common indexing mistakes include:

• indexing columns because they “seem important”
• indexing every foreign key plus several near-duplicate combinations
• adding indexes without checking the actual query shape
• building single-column indexes when the real workload needs a multicolumn index
• overindexing write-heavy tables

Example mistake

The real query is:

select *
from orders
where account_id = $1
order by created_at desc
limit 20;

But the developer creates:

create index idx_orders_account_id on orders (account_id);
create index idx_orders_created_at on orders (created_at);

A much stronger index is usually:

create index idx_orders_account_created
on orders (account_id, created_at desc);

The planner does not reward wishful thinking. Indexes need to match query shape.

5. Forgetting That Multicolumn Index Order Matters

Developers often know they need a composite index, then put the columns in the wrong order.

That weakens the whole point of the index.

In practice, good multicolumn B-tree indexes usually place:

• equality-filter columns first
• then sort or range columns

That is why this:

(account_id, created_at desc)

is often much more useful than this:

(created_at desc, account_id)

for tenant-scoped activity queries.

If the index order does not match how the application filters and sorts, the index may still exist and still disappoint.

6. Leaving N+1 Queries Unchecked

N+1 problems are still one of the most common application-side PostgreSQL mistakes.

The usual pattern:

1. fetch a list of parent rows
2. loop in application code
3. fetch each related row set individually

It looks fine at low scale. It gets ugly under concurrency.

Example

• fetch 100 posts
• fetch author for each post
• fetch comments for each post
• fetch tags for each post

That turns one request into hundreds of queries.

What to do instead

Use:

• joins where appropriate
• batched lookups
• preload or eager-load features in the ORM
• request-level query counting in development

A lot of “PostgreSQL is slow” complaints are really “the app issued 240 queries for one page.”

7. Holding Transactions Open Too Long

A transaction is not just a logical unit of work. It also affects concurrency, locking behavior, and cleanup timing.

Developers often make transactions too large by:

• opening them too early
• doing network calls inside them
• reading too much before writing
• bundling unrelated operations together
• leaving them open across slow application logic

That leads to:

• more contention
• longer lock lifetimes
• harder-to-debug production issues
• slower cleanup of dead tuples
• and a system that behaves worse under load than it does in development

Better pattern

Do non-database work first when possible. Then:

1. open transaction
2. do minimal read/write set
3. commit quickly

The shorter the transaction, the easier PostgreSQL can keep moving.

8. Using the Wrong Isolation Expectations

Some developers assume PostgreSQL behaves like a simple shared-memory store with one obvious “current” version of the data.

That is not how it works.

PostgreSQL uses MVCC, and the default isolation level is READ COMMITTED.

That means concurrency behavior, repeat reads, retries, and race conditions need to be understood at the application level too.

A common mistake is assuming:

• one read early in a transaction
• guarantees the same result later in that same transaction

That is not always true at the default isolation level.

What to do instead

Know your transaction expectations:

• what must be serialized
• what can tolerate ordinary concurrent change
• when optimistic conflict handling is good enough
• when retries need to be explicit

A lot of subtle bugs in inventory, balances, quotas, and state machines come from treating transaction isolation like magic instead of something you deliberately design around.

9. Overusing JSONB to Avoid Schema Design

jsonb is one of PostgreSQL’s best features.

It is also one of the most abused.

Developers often use it because:

• it feels flexible
• it avoids migrations
• it makes early iteration easy
• it reduces design friction

Those are real advantages.

But the mistake is using JSONB for fields that are actually:

• core query filters
• frequent join keys
• strict domain values
• or important business invariants

Good JSONB use

• audit payloads
• event metadata
• flexible option sets
• sparse semi-structured attributes

Bad JSONB use

Putting important application fields like:

• email
• status
• tenant_id
• created_at
• plan_type

inside a big document blob and then trying to query around it later.

Use JSONB where it helps. Do not let it replace relational thinking where relational structure is clearly the better fit.

10. Picking Bad Data Types by Habit

Developers often choose PostgreSQL data types by cargo cult.

Common examples:

• bigint everywhere even when integer is enough
• numeric for values that are really simple integers
• varchar(n) everywhere because it “feels stricter”
• timestamps where dates are enough
• strings where booleans or enums would be clearer

These choices affect:

• row width
• index size
• memory use
• sort cost
• and long-term clarity

A good rule is:

Choose the narrowest correct type, not the widest “just in case” type.

Most performance-friendly schemas are also semantically cleaner.

11. Treating PostgreSQL Like a Dumb Persistence Layer

A lot of application teams think about PostgreSQL only as:

• where rows go
• after the real work has already been done in the app

That mindset causes trouble.

PostgreSQL is not only storage. It is also:

• a query engine
• a constraint engine
• a concurrency system
• an indexing system
• and a planner that is constantly reacting to your query shapes

If you ignore that, you usually underuse important capabilities like:

• returning
• upserts with on conflict
• constraints
• partial indexes
• expression indexes
• proper transactional grouping

The result is often more application code, more race conditions, and worse performance.

12. Ignoring RETURNING

A lot of code still does this:

1. insert a row
2. run another query to fetch the inserted data

Or:

1. update a row
2. run another query to see the new state

That is often unnecessary.

PostgreSQL’s RETURNING support is extremely useful for backend code because it lets you get back:

• generated IDs
• computed defaults
• updated values
• deleted rows
• and more

Example

insert into users (email, display_name)
values ($1, $2)
returning id, email, display_name, created_at;

Using RETURNING can reduce round trips and simplify handlers.

13. Reimplementing Upsert Logic in the App

A classic developer mistake is:

1. check whether row exists
2. if yes, update
3. if no, insert

That creates race conditions and extra queries.

PostgreSQL already provides INSERT ... ON CONFLICT.

Example

insert into feature_flags (account_id, key, enabled)
values ($1, $2, $3)
on conflict (account_id, key)
do update set enabled = excluded.enabled
returning *;

This is usually cleaner and safer than hand-rolled read-then-write logic.

The mistake is not learning it early enough.

14. Forgetting That Autovacuum and Statistics Matter

Some developers assume PostgreSQL will stay healthy on its own as long as queries are okay.

But routine maintenance matters.

If autovacuum falls behind, or statistics go stale, the application can suffer from:

• worse plans
• bloat
• unnecessary scans
• and inconsistent latency

You do not have to become the database operator for every system you touch. But you do need to recognize when a performance problem is not caused by the SQL alone.

At minimum, backend developers should understand:

• autovacuum is not optional background trivia
• analyze statistics influence plan choice
• heavy write tables need more attention than static tables
• “nothing changed in code” does not mean nothing changed in database behavior

15. Writing Unsafe Migrations

This is one of the most dangerous mistakes because it often shows up only in production.

A migration that runs instantly on a local database can become disruptive when:

• the table is large
• traffic is live
• writes are constant
• and locks matter

Common migration mistakes:

• adding heavy defaults carelessly
• bundling multiple risky changes into one release
• creating indexes without considering concurrent builds
• rewriting hot tables without understanding impact
• assuming rollback is trivial

Better migration habits

• split large changes into steps
• backfill in batches when needed
• prefer safer rollout sequencing
• think about locks before shipping DDL
• test migrations against realistic table sizes if possible

A migration is not only “valid SQL.” It is operational behavior.

16. Using OFFSET Pagination Too Long

Offset pagination is easy to start with:

select *
from posts
order by created_at desc
offset 5000
limit 20;

It is also one of the most common scaling regrets.

As offsets grow, PostgreSQL still has to walk through a lot of rows to reach the page. That makes deep pagination expensive.

Better option

For large feeds and active tables, keyset pagination is usually better:

select id, created_at, title
from posts
where created_at < $1
order by created_at desc
limit 20;

Developers often keep offset pagination far longer than they should because it looks simple in the ORM.

17. Not Using Connection Pooling Correctly

PostgreSQL is not built for every request to open a fresh connection.

This becomes a real problem when:

• application instances scale out
• serverless traffic spikes
• multiple services talk to the same database
• idle connection counts quietly grow

A common mistake is focusing only on query speed while ignoring connection behavior.

Better habits

• use sensible application pooling
• do not allow unbounded connection growth
• keep transactions short so connections return quickly
• understand framework defaults
• consider PgBouncer or similar approaches when concurrency grows

Connection problems often feel like “database slowness” even when the real issue is application-side connection behavior.

18. Letting the ORM Hide the SQL Too Much

ORMs are useful. They are also dangerous when developers stop reading the SQL they generate.

Common ORM-related mistakes:

• loading too many columns
• accidental N+1s
• filters applied too late
• inefficient joins
• duplicate query patterns
• using ORM convenience for endpoints that should have hand-tuned SQL

The mistake is not using an ORM. The mistake is assuming the ORM is always producing sensible SQL for hot paths.

Better approach

Use the ORM where it helps productivity. Drop down to explicit SQL where performance and clarity matter more.

That is not failure. That is good engineering judgment.

19. Not Measuring Query Count and Query Time Per Endpoint

Many teams watch only:

• endpoint latency
• CPU
• and maybe database load

That is not enough.

A very useful backend habit is to track per-request:

• query count
• total DB time
• slowest query
• repeated query patterns

This helps reveal:

• N+1 issues
• chatty handlers
• poor preload logic
• hidden ORM cost
• endpoints that look fine in code but are noisy in practice

If you never measure how much SQL an endpoint actually emits, you are debugging half blind.

20. Optimizing Too Late

One of the most common developer mistakes is waiting until PostgreSQL becomes a visible problem before building basic database-aware habits.

That delay creates technical debt in:

• schema shape
• query style
• indexing strategy
• migration safety
• transaction boundaries
• and monitoring

The better approach is not premature optimization. It is early correctness in the parts that are hard to unwind later.

That means:

• reasonable data types
• explicit result columns
• clean indexes
• short transactions
• safe migrations
• and real visibility into what the app is doing

Those are not advanced tricks. They are the baseline that keeps PostgreSQL pleasant as the system grows.

How to Avoid Most PostgreSQL Mistakes

If you want a simple checklist, use this:

1. Design tables from real query patterns
2. Avoid select * in production code
3. Learn EXPLAIN ANALYZE
4. Index for repeated query shapes, not for guesswork
5. Watch for N+1 queries
6. Keep transactions short
7. Use JSONB deliberately
8. Choose correct data types
9. Use RETURNING and ON CONFLICT where appropriate
10. Treat migrations as operational events, not just code changes
11. Respect autovacuum and statistics
12. Measure query count and DB time per endpoint

That small set of habits prevents a surprising percentage of PostgreSQL pain.

FAQ

What is the most common PostgreSQL mistake developers make?

One of the most common mistakes is building schemas and queries without thinking about real production access patterns. That leads to bad indexes, unnecessary query volume, weak pagination, and avoidable performance problems.

Is JSONB overuse a real PostgreSQL problem?

Yes. JSONB is excellent for flexible data, but it becomes a problem when developers use it to avoid proper relational design for fields that should really be indexed, constrained, filtered, or joined normally.

Why are long transactions bad in PostgreSQL?

Long transactions increase contention, hold locks and resources longer, delay cleanup work, and usually make concurrency problems worse under load.

Should developers use EXPLAIN ANALYZE regularly?

Yes. It is one of the most practical PostgreSQL habits because it shows whether a query is scanning too much data, sorting unnecessarily, or missing an index strategy.

Conclusion

The most common PostgreSQL mistakes developers make are not exotic.

They are repeatable, understandable, and fixable:

• weak schema choices
• careless indexes
• N+1 queries
• long transactions
• JSONB misuse
• bad migrations
• connection mistakes
• and lack of visibility into what the application is really asking PostgreSQL to do

That is actually good news.

Because once you stop making those mistakes, PostgreSQL usually gets much easier to work with.

The goal is not to become a database wizard overnight.

The goal is to stop shipping avoidable database problems as application code.