SELECT * as an Anti-Pattern

~10 min · operations, queries

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Why production code shouldn't say SELECT *

SELECT * is fine in psql for exploration. In production code it's a bug waiting to happen:

Pulls every column over the network — wasted bandwidth.
Defeats covering indexes — the planner can't pick an index-only scan when it has to fetch all columns.
Breaks subtly when columns are added or reordered — code that assumed the third column was X now reads Y.
Obscures intent — the reader can't tell which fields the query depends on.

The rule

Production code lists explicit columns. Migrations and one-shot scripts can use SELECT *. ORMs that select all columns by default are sometimes acceptable because the mapping is type-checked — but if the table has a 4MB JSONB column you read once a month, that's now in every fetch.

Code

Bad and good·sql

-- Bad
SELECT * FROM users WHERE id = ?;

-- Good
SELECT id, name, email, role FROM users WHERE id = ?;

ORMs and the SELECT * trap·python

# SQLAlchemy: by default, fetches every column
users = session.query(User).all()

# When you have huge JSONB or BYTEA columns, defer them
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(Text)
    profile_json = Column(JSONB, deferred=True)  # only loaded on access

External links

Why SELECT * is bad

Exercise

Grep your codebase for SELECT * in production paths. For each, decide: needed? If not, replace with explicit columns. Note any place where this also enables a covering index that wasn't possible before.