SELECT *

Introduction

SELECT * is a projection shortcut: it asks the database to return every column visible through the query. That is convenient while exploring a schema, but production consumers usually need a smaller, stable shape. Selecting only the required columns limits transfer and deserialization work, makes access patterns clearer, and can preserve index-only scan opportunities.

Mental model: rows are the records you match; columns are the fields you project. SELECT * expands the projection to the whole row, so every schema addition can change the result shape and every unused field travels through the database-to-application path. The cost depends on the storage engine, query plan, row width, and workload, so measure the hot paths.

TL;DR

• SELECT * fetches every column in the row, including columns your application never uses. This adds unnecessary data transfer, deserialization, and memory overhead.
• In column-oriented databases (ClickHouse, Redshift, BigQuery), SELECT * can forfeit much of the benefit of column pruning. You may read many more column files when a query only needs 2 or 3.
• SELECT * can prevent an index-only scan when the index does not cover every projected column, requiring heap or table access for matching rows.
• Schema changes silently expose new columns (including sensitive ones like password hashes) through SELECT * queries, with no code change required.
• Fix: Prefer explicit columns for production query paths. Override ORM defaults with explicit select calls where the full row is not needed.

What It Is

It's 3 a.m. and your on-call engineer gets paged. The dashboard API is timing out. Database CPU is at 94%. The slow query log shows one query repeated 10,000 times per second:

SELECT * FROM users WHERE id = ?

Your users table has 42 columns totalling ~8KB per row: id, email, name, avatar_url, bio, preferences_json, oauth_tokens_json, mfa_secret, password_hash, created_at, updated_at, last_login, stripe_customer_id, billing_address, ...

The dashboard card uses exactly 4 of those 42 columns: id, name, avatar_url, email. At 10,000 req/s, you're transferring 38 unnecessary columns per request. That's ~7.6KB of useless data per request, 76 MB/s of unnecessary network traffic, and 6.5 TB/day of database-to-app traffic providing zero value.

This kind of incident can be quick to fix once the projection is identified, but the outage can last much longer if nobody checks what the query is actually selecting.

Concrete before/after: dashboard user lookup

Before: SELECT * FROM users WHERE id = ? returns the full row, even though the dashboard needs only id, name, avatar_url, and email.

After: SELECT id, name, avatar_url, email FROM users WHERE id = ? makes the response shape explicit. It can reduce transfer and object construction, and it may enable a covering index when the selected columns and visibility rules allow it.

The cascade in numbers

Here's how SELECT * costs compound at different scales. The numbers look small per-request, but they're multiplied across every query, every second, every day.

At 100K req/s, the unnecessary data transfer alone can saturate a 10 Gbps network link. That's before accounting for deserialization cost, garbage collection pressure, or the database buffer pool churn.

Why restarts and scaling don't help

When SELECT * causes DB CPU spikes, the natural reaction is to add read replicas or increase instance size. This treats the symptom, not the cause. You may be paying substantially more database compute to transfer data nobody uses.

Doubling your database budget to handle 76 MB/s of useless data transfers may treat the symptom. In this example, changing the projection is a lower-cost first experiment and could reduce the returned data by about 95%.

The index-only scan problem

If you have CREATE INDEX idx_users_email ON users (email, name) and query:

-- Index-only scan possible: both columns in the index
SELECT email, name FROM users WHERE email = ?;

-- ❌ Index-only scan NOT possible: * requires columns not in the index
SELECT * FROM users WHERE email = ?;

The * version normally needs the actual table heap for each matching row (a "heap access") when the index does not cover all projected columns, which means random I/O on physical disk or cache pressure on SSD. The explicit column version can be satisfied from the compact index structure when the index and visibility rules allow it, reducing I/O.

You can verify this with EXPLAIN ANALYZE:

-- Check if your query uses an index-only scan
EXPLAIN ANALYZE SELECT email, name FROM users WHERE email = 'alice@test.com';
-- Look for "Index Only Scan" in output

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@test.com';
-- You'll see "Index Scan" + "Heap Fetches: 1" instead

The difference matters most on large tables. A covering index scan on a 100 million row table can be materially faster than a heap-fetching scan for the same query, because the index may be much smaller than the table.

Column-store impact

In analytical databases built on columnar storage (ClickHouse, Apache Parquet, BigQuery):

-- SELECT * can read all columns from disk: reduces column pruning
SELECT * FROM events WHERE event_date = '2026-04-04';

-- Reads only 2 columns out of potentially 100: 98% less I/O
SELECT event_type, user_id FROM events WHERE event_date = '2026-04-04';

Columnar storage achieves compression ratios of 5x to 20x in some workloads by storing columns contiguously and applying column-specific encoding. SELECT * can read many or all column files, forfeiting column pruning and much of the I/O reduction even though the selected data may still be compressed.