TECH **SQL for Data Analysis: Avoiding SELECT *, Index-Only Scans, and Table Bloat**

SQL for Data Analysis: Avoiding SELECT *, Index-Only Scans, and Table Bloat

A hyper-practical, zero-fluff guide for real-world performance and cost efficiency

1. What This Is & Why It Matters

You’re a data analyst at a mid-sized e-commerce company. Your team runs a daily report that pulls customer orders, product details, and shipping statuses. The query looks like this:

SELECT * FROM orders
JOIN customers USING (customer_id)
JOIN products USING (product_id)
WHERE order_date >= '2024-01-01';

At first, it works fine. But as the company grows, the orders table swells to 50 million rows, and the report starts timing out. Your database admin (DBA) sends you a Slack message:

"Your query is scanning 12GB of data and killing our read replicas. Fix it or we’re throttling your access."

This guide teaches you three critical optimizations to prevent this nightmare: 1. Avoiding SELECT * → Reduces I/O, network traffic, and memory usage.
2. Index-Only Scans → Lets the database fetch data without touching the table (like reading a book’s index instead of flipping every page).
3. Preventing Table Bloat → Stops your tables from ballooning in size due to dead rows, slowing down every query.

Why this matters in production:
- Cost: Cloud databases (e.g., AWS RDS, BigQuery) charge by data scanned. SELECT * on a 100GB table? That’s a $500 bill.
- Performance: A query that takes 30 seconds with SELECT * might take 300ms with proper indexing.
- Reliability: Bloated tables cause lock contention, leading to timeouts and failed transactions.
- Scalability: If your query scans 10x more data than needed, it won’t scale when traffic grows.

Real-world scenario:
You inherit a legacy analytics dashboard that runs SELECT * FROM sales on a 200GB table. The dashboard loads in 15 seconds for you, but times out for users in Europe (higher latency). Your boss wants it fixed today.

2. Core Concepts & Components

1. SELECT * (The Silent Killer)

• Definition: Fetching all columns from a table, even if you only need 2.
• Production insight: SELECT * forces the database to:
• Read every column from disk (even unused ones).
• Transfer all data over the network (slow for remote clients).
• Load unnecessary data into memory (causing cache misses).
• Example: If orders has 50 columns but you only need order_id and total_amount, SELECT * wastes 96% of the I/O.

2. Index-Only Scan (The Superpower)

• Definition: A query that only reads the index, not the table, to satisfy a request.
• Production insight: Indexes are sorted and smaller than tables. If your query can be answered by the index alone, it’s 10–100x faster.
• Example: If you have an index on orders(order_date, customer_id), this query can use an index-only scan: sql SELECT customer_id FROM orders WHERE order_date = '2024-01-01'; The database never touches the table—it just reads the index.

3. Table Bloat (The Silent Performance Killer)

• Definition: When a table accumulates dead rows (deleted or updated rows that haven’t been vacuumed) and wasted space.
• Production insight: Bloat makes tables physically larger, slowing down:
• Full table scans (SELECT *).
• Index scans (more pages to read).
• Vacuum operations (longer maintenance windows).
• Example: A 10GB table with 30% bloat (3GB of dead rows) will perform like a 13GB table—even though only 7GB is "live" data.

4. Covering Index (The Index-Only Scan Enabler)

• Definition: An index that includes all columns needed for a query, so the database never has to read the table.
• Production insight: A covering index turns a 10-second query into a 100ms query.
• Example: If you frequently run: sql SELECT customer_id, order_date, total_amount FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31'; A covering index would be: sql CREATE INDEX idx_orders_covering ON orders(order_date) INCLUDE (customer_id, total_amount);

5. VACUUM (The Bloat Cleaner)

• Definition: A PostgreSQL (and some other DBs) command that reclaims space from dead rows.
• Production insight: Without VACUUM, tables grow indefinitely, even if you delete data.
• Example: Running VACUUM FULL orders might shrink a 50GB table to 30GB if it had 40% bloat.

6. Autovacuum (The Silent Janitor)

• Definition: A background process that automatically runs VACUUM when tables bloat beyond a threshold.
• Production insight: If autovacuum is disabled or misconfigured, tables bloat uncontrollably.
• Example: If autovacuum = off in PostgreSQL, a table with 10M deletes will keep all 10M dead rows until you manually run VACUUM.

7. Heap-Only Tuple (HOT) Updates (The Bloat Reducer)

• Definition: A PostgreSQL optimization where updates reuse the same row if no indexed columns change.
• Production insight: Without HOT updates, every UPDATE creates a new row version, increasing bloat.
• Example: If you update orders.status (not indexed), PostgreSQL can reuse the same row, avoiding bloat.

8. Fillfactor (The Bloat Preventer)

• Definition: A setting that reserves space in table pages for future updates.
• Production insight: A fillfactor = 80 means 20% of each page is left empty for updates, reducing bloat.
• Example: If you frequently update rows, set fillfactor = 70 to minimize row migrations.

3. Step-by-Step Hands-On Section

Prerequisites

• A PostgreSQL database (local or cloud, e.g., AWS RDS, Supabase, or Docker).
• A table with at least 1M rows (we’ll generate one).
• psql or a SQL client (e.g., DBeaver, pgAdmin).

Step 1: Create a Test Table with Bloat

We’ll simulate a real-world scenario where a table grows uncontrollably due to updates and deletes.

-- Create a table with 1M rows
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    notes TEXT
);

-- Insert 1M rows (run in batches if needed)
INSERT INTO orders (customer_id, order_date, total_amount, status, notes)
SELECT
    (random() * 10000)::INT,
    '2023-01-01'::DATE + (random() * 365)::INT,
    (random() * 1000)::DECIMAL(10, 2),
    CASE WHEN random() < 0.5 THEN 'shipped' ELSE 'pending' END,
    md5(random()::TEXT)
FROM generate_series(1, 1000000);

Step 2: Measure Table Bloat

Check how much bloat exists in the table.

-- Check table size and bloat
SELECT
    pg_size_pretty(pg_total_relation_size('orders')) AS total_size,
    pg_size_pretty(pg_table_size('orders')) AS table_size,
    pg_size_pretty(pg_indexes_size('orders')) AS indexes_size,
    n_dead_tup AS dead_rows,
    n_live_tup AS live_rows,
    round(n_dead_tup::numeric / (n_live_tup + n_dead_tup) * 100, 2) AS bloat_percentage
FROM pg_stat_user_tables
WHERE relname = 'orders';

Expected output:

 total_size | table_size | indexes_size | dead_rows | live_rows | bloat_percentage
------------+------------+--------------+-----------+-----------+------------------
 120 MB     | 80 MB      | 40 MB        | 0         | 1000000   | 0.00

(No bloat yet—we’ll create some in the next step.)

Step 3: Simulate Bloat with Updates & Deletes

Run these commands to artificially bloat the table:

-- Update 500K rows (creates dead rows)
UPDATE orders SET status = 'cancelled' WHERE order_id % 2 = 0;

-- Delete 200K rows (creates more dead rows)
DELETE FROM orders WHERE order_id % 5 = 0;

-- Check bloat again
SELECT
    pg_size_pretty(pg_total_relation_size('orders')) AS total_size,
    n_dead_tup AS dead_rows,
    round(n_dead_tup::numeric / (n_live_tup + n_dead_tup) * 100, 2) AS bloat_percentage
FROM pg_stat_user_tables
WHERE relname = 'orders';

Expected output:

 total_size | dead_rows | bloat_percentage
------------+-----------+------------------
 180 MB     | 700000    | 41.18

Now the table is 41% bloat! The physical size grew from 120MB → 180MB, even though we only have 300K live rows (1M - 500K updates - 200K deletes).

Step 4: Fix Bloat with VACUUM

Run VACUUM to reclaim space:

-- Standard VACUUM (reclaims space but doesn't shrink the file)
VACUUM orders;

-- Check bloat again
SELECT
    pg_size_pretty(pg_total_relation_size('orders')) AS total_size,
    n_dead_tup AS dead_rows,
    round(n_dead_tup::numeric / (n_live_tup + n_dead_tup) * 100, 2) AS bloat_percentage
FROM pg_stat_user_tables
WHERE relname = 'orders';

Expected output:

 total_size | dead_rows | bloat_percentage
------------+-----------+------------------
 180 MB     | 0         | 0.00

• Dead rows are gone, but the file size is still 180MB (PostgreSQL doesn’t shrink files by default).
• To physically shrink the table, use VACUUM FULL (but this locks the table):

VACUUM FULL orders;

Now the table size drops to ~60MB (close to the original size).

Step 5: Avoid SELECT * with a Covering Index

Let’s say your team runs this query 100 times a day:

-- Bad: SELECT * (scans the whole table)
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE order_date BETWEEN '2023-06-01' AND '2023-06-30';

Output:

Seq Scan on orders  (cost=0.00..22965.00 rows=83333 width=56) (actual time=0.012..120.456 rows=83333 loops=1)
  Filter: ((order_date >= '2023-06-01'::date) AND (order_date <= '2023-06-30'::date))
Planning Time: 0.123 ms
Execution Time: 150.234 ms

• Scans 1M rows (even though only 83K match).
• Takes 150ms.

Fix: Use a covering index + explicit columns:

-- Create a covering index
CREATE INDEX idx_orders_covering ON orders(order_date)
INCLUDE (customer_id, total_amount, status);

-- Rewrite the query to avoid SELECT *
EXPLAIN ANALYZE
SELECT customer_id, total_amount, status
FROM orders
WHERE order_date BETWEEN '2023-06-01' AND '2023-06-30';

Output:

Index Only Scan using idx_orders_covering on orders  (cost=0.29..2800.29 rows=83333 width=20) (actual time=0.023..10.123 rows=83333 loops=1)
  Index Cond: ((order_date >= '2023-06-01'::date) AND (order_date <= '2023-06-30'::date))
  Heap Fetches: 0
Planning Time: 0.123 ms
Execution Time: 15.456 ms

• Uses the index only (no table access).
• Takes 15ms (10x faster).
• Scans 0 rows from the table (Heap Fetches: 0).

Step 6: Prevent Future Bloat with Fillfactor

Set fillfactor to reduce row migrations during updates:

-- Recreate the table with fillfactor=80 (leaves 20% space for updates)
DROP TABLE orders;
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    notes TEXT
) WITH (fillfactor = 80);

-- Re-insert data
INSERT INTO orders (customer_id, order_date, total_amount, status, notes)
SELECT
    (random() * 10000)::INT,
    '2023-01-01'::DATE + (random() * 365)::INT,
    (random() * 1000)::DECIMAL(10, 2),
    CASE WHEN random() < 0.5 THEN 'shipped' ELSE 'pending' END,
    md5(random()::TEXT)
FROM generate_series(1, 1000000);

-- Update 500K rows again
UPDATE orders SET status = 'cancelled' WHERE order_id % 2 = 0;

-- Check bloat
SELECT
    pg_size_pretty(pg_total_relation_size('orders')) AS total_size,
    n_dead_tup AS dead_rows,
    round(n_dead_tup::numeric / (n_live_tup + n_dead_tup) * 100, 2) AS bloat_percentage
FROM pg_stat_user_tables
WHERE relname = 'orders';

Expected output:

 total_size | dead_rows | bloat_percentage
------------+-----------+------------------
 130 MB     | 500000    | 33.33

• Bloat is now 33% (vs. 41% before) because fillfactor=80 reduced row migrations.

4. ? Production-Ready Best Practices

Avoiding SELECT *

✅ Do:
- Always specify columns in SELECT.
```sql -- Good SELECT order_id, customer_id, total_amount FROM orders;

-- Bad SELECT * FROM orders; - Use `EXPLAIN ANALYZE` to check if your query scans unnecessary data.
- Add `LIMIT` when debugging to avoid fetching millions of rows.sql SELECT * FROM orders LIMIT 10; -- Safe for testing ```

❌ Don’t:
- Use SELECT * in ETL pipelines (you’ll process 10x more data than needed).
- Assume SELECT * is "simpler"—it’s technical debt.

Index-Only Scans

✅ Do:
- Create covering indexes for frequent queries.
sql CREATE INDEX idx_orders_covering ON orders(order_date) INCLUDE (customer_id, total_amount); - Check EXPLAIN for Index Only Scan (means the query never touches the table).
- Use pg_stat_user_indexes to find unused indexes (drop them to reduce write overhead).
sql SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE schemaname = 'public' AND idx_scan < 10;

❌ Don’t:
- Create indexes without INCLUDE if you need extra columns (forces table access).
- Assume all indexes are good—unused indexes slow down INSERT/UPDATE/DELETE.

Preventing Table Bloat

✅ Do:
- Enable autovacuum (PostgreSQL default, but check settings).
sql SHOW autovacuum; -- Should be 'on' - Set fillfactor for write-heavy tables (e.g., fillfactor=70 for tables with frequent updates).
- Monitor bloat with: sql SELECT schemaname, relname, n_dead_tup, n_live_tup,
round(n_dead_tup::numeric / (n_live_tup + n_dead_tup) * 100, 2) AS bloat_pct FROM pg_stat_user_tables WHERE n_dead_tup > 0 ORDER BY bloat_pct DESC; - Schedule VACUUM during low-traffic periods (e.g., nightly).

❌ Don’t:
- Disable autovacuum (tables will bloat uncontrollably).
- Run VACUUM FULL during peak hours (locks the table).
- Ignore bloat—it compounds over time and slows down every query.

Cost Optimization

• Cloud databases charge by data scanned. SELECT * on a 100GB table in BigQuery = $500/month.
• Use columnar storage (e.g., BigQuery, Redshift) for analytics—SELECT * is cheaper there (but still avoid it).
• Partition large tables (e.g., by order_date) to reduce scan size.

Reliability & Maintainability

• Add comments to indexes to explain their purpose.
  sql COMMENT ON INDEX idx_orders_covering IS 'Covering index for order_date range queries';

• Use consistent naming (e.g., idx_table_column for indexes, uq_table_column for unique constraints).