It was launch day. Traffic was high. Everything looked good.
Then… within 10 minutes, the site went down.
The root cause?
A single SQL query:
<pre><code class="language-sql">SELECT * FROM orders WHERE status = &#x27;pending&#x27;;
</code></pre>
In development, this query felt harmless. I only had ~500 test records. Response times were instant.
But in production, things looked very different:
<ul>
<li>60,000+ orders in the table</li>
<li>Each row = ~12KB (id, customer info, shipping, items, metadata, logs)</li>
<li>Total payload = ~720MB pulled into memory</li>
</ul>
That one query triggered a cascade of problems:
<ol>
<li>Memory pressure → Database engine tried to hold 700MB+ in RAM.</li>
<li>Network bottleneck → Transferring hundreds of MB across the wire = 30–40s delays.</li>
<li>Connection lock → Each slow query kept connections busy for too long.</li>
<li>Concurrency disaster → 100 users × 700MB queries = DB server saturated, new requests timed out.</li>
</ol>
All because of a single <code>*</code>.
<hr>
<h3>Why <code>SELECT *</code> is dangerous in production</h3>
<ul>
<li>Column bloat: You pull back data you’ll never use (images, descriptions, logs).</li>
<li>Unstable schemas: If new columns are added later, they get fetched automatically → bloated queries without you noticing.</li>
<li>Index inefficiency: Query planner may not optimize properly when fetching wide rows.</li>
<li>Bandwidth + memory overhead: Every extra KB wastes CPU cycles, RAM, and network.</li>
<li>Concurrency collapse: Under load, these inefficiencies multiply exponentially.</li>
</ul>
<hr>
<h3>The better approach</h3>
<ol>
<li>Select only what you need</li>
</ol>
<pre><code class="language-sql">SELECT id, customer_id, total_price 
FROM orders 
WHERE status = &#x27;pending&#x27; 
LIMIT 50 OFFSET 0;
</code></pre>
<ol start="2">
<li>
Paginate aggressively
Never load 10k+ rows in one go. Use <code>LIMIT</code> + <code>OFFSET</code> or keyset pagination.
</li>
<li>
Cache hot data
Frequent queries (e.g., active products, pending orders) → push to Redis/Memcached.
</li>
<li>
Use EXPLAIN
Check how your queries behave on large datasets. Don’t assume dev ≈ prod.
</li>
<li>
Monitor slow queries
Enable MySQL/Postgres slow query logs. Anything &gt;200ms deserves investigation.
</li>
<li>
Test with production-like data
Your dev DB with 500 records hides problems. Mirror production scale locally or in staging.
</li>
<li>
Set query timeouts
Never let a single bad query hog resources forever.
</li>
</ol>
<hr>
✅ Before pushing a query to production — check these 5 things:
1️⃣ Columns → Are you selecting only what you actually need? 
2️⃣ Pagination → Are you limiting rows (LIMIT/OFFSET or keyset pagination)? 
3️⃣ Indexes → Does your WHERE/JOIN condition hit an index? 
4️⃣ Scale test → Have you tested with production-size data, not just dev seed data? 
5️⃣ Monitoring → Is slow query logging + timeout enabled?
<hr>
🔒 Bonus: Cache hot queries (Redis/Memcached) → cut DB load by 80%+
<hr>
🔑 Takeaway:
In production, every byte counts. 
A single <code>SELECT *</code> might seem innocent in dev… 
…but at scale, it can cripple your database and take your system offline.
Always code with production scale in mind. 🚀

Jobs is a daily dev squad dedicated to sharing job opportunities, career advice, and community-driven insights for developers across all tech stacks, helping them grow and succeed in the industry.

Jobs

A production outage caused by a seemingly innocent `SELECT *` query demonstrates how database queries that work fine in development can fail catastrophically at scale. The query pulled 720MB of data from 60,000+ records, causing memory pressure, network bottlenecks, and connection timeouts. The post provides practical solutions including selecting specific columns, implementing pagination, caching hot data, using EXPLAIN plans, monitoring slow queries, and testing with production-scale data.

🚨How one tiny SQL query nearly killed our production system 🚨

Never <code>SELECT *</code> - It’s elementary, my dear Watson.

SELECT * feels harmless… until it meets production. Noted🫡

SELECT * never hits an index, it’s an automatic Table SCAN every time.