When should you create an index

You should create an index when queries on a table are unacceptably slow and you have identified specific columns frequently used for filtering, joining, or sorting.

🟢 Junior Level

You should create an index when queries on a table are unacceptably slow and you have identified specific columns frequently used for filtering, joining, or sorting.

Simple Analogy

If you frequently look up contacts in a directory by phone number instead of person name, it makes sense to add a dedicated “reverse lookup by phone number” index. But adding an index for shoe size or eye color would be a waste of paper, ink, and storage space.

Core Principle

Indexes accelerate reads (SELECT) but penalize writes (INSERT, UPDATE, DELETE).
Never create indexes “just in case” without verifying production query workloads and table access patterns.
An index is justified only when the query performance gain outweighs the storage and write overhead.

-- Without an index: Reads 1,000,000 rows sequentially (~350 ms)
SELECT * FROM users WHERE email = 'alex@example.com';

-- Create an index on the highly selective column
CREATE INDEX idx_users_email ON users(email);

-- With an index: Traverses B-tree directly to the target row (~0.05 ms)
SELECT * FROM users WHERE email = 'alex@example.com';

When to Create an Index:

High Selectivity Filters: Columns in WHERE clauses matching a small percentage of total rows (< 5–10%, e.g., email, uuid, username).
Foreign Keys: Columns referencing parent tables in relationships (user_id in orders).
Join Predicates: Columns frequently used in JOIN ... ON conditions.
Sorting & Pagination: Columns used in ORDER BY with LIMIT on large tables.
Significant Table Size: Tables with more than a few thousand rows.

🟡 Middle Level

Identifying Indexing Candidates from Workload Metrics

Never guess where an index is needed. Use PostgreSQL’s built-in performance telemetry:

1. Analyze Slow Query Logs

# postgresql.conf
log_min_duration_statement = 250  # Logs queries executing longer than 250 ms
log_line_prefix = '%m [%p] %q%u@%d '

2. Analyze Workload with `pg_stat_statements`

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Identify top queries consuming the most cumulative database execution time
SELECT 
    query, 
    calls,
    round(mean_exec_time::numeric, 2) AS avg_time_ms,
    round(total_exec_time::numeric, 2) AS total_time_ms,
    shared_blks_read,   -- Disk reads (high ratio indicates missing indexes)
    shared_blks_hit     -- Cache hits
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

The 4 Major Architectural Indexing Triggers

1. Foreign Key Columns (Crucial for Locking & Cascades)

PostgreSQL does NOT automatically create indexes on Foreign Keys:

CREATE TABLE orders (
    order_id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT REFERENCES customers(customer_id) -- ⚠️ Not indexed by default!
);

-- ✅ Always index foreign keys in OLTP systems:
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Why? When a parent row is deleted or updated in customers, PostgreSQL must verify referential integrity on orders. Without an index on customer_id, PostgreSQL is forced to perform a full Seq Scan on orders, taking row-share table locks that stall concurrent writes.

2. High Selectivity Filters in `WHERE`

-- ✅ High selectivity (each value matches 1 row) -> Ideal index
CREATE INDEX idx_users_ssn ON users(ssn);

-- ❌ Low selectivity (values match 50% of the table) -> Poor index
CREATE INDEX idx_users_gender ON users(gender); -- 'M' / 'F'

3. Pre-sorting for `ORDER BY` with `LIMIT`

CREATE INDEX idx_audit_created ON audit_logs(created_at DESC);

-- Executes instantly via Index Backward Scan without an in-memory Sort node
SELECT * FROM audit_logs ORDER BY created_at DESC LIMIT 20;

4. Covering Indexes for Hot Read Paths

When a query runs millions of times per hour, use the INCLUDE clause to achieve zero-heap Index Only Scans:

CREATE INDEX idx_users_lookup ON users(email) INCLUDE (user_id, status);

🔴 Senior Level

Testing Virtual Indexes with `HypoPG`

Before allocating tens of gigabytes of disk space and risking production write locks, test index efficacy in memory using the HypoPG extension:

CREATE EXTENSION IF NOT EXISTS hypopg;

-- 1. Create a hypothetical in-memory index (consumes 0 disk space, 0 WAL)
SELECT * FROM hypopg_create_index(
    'CREATE INDEX idx_orders_customer_date ON orders(customer_id, created_at DESC)'
);

-- 2. Run EXPLAIN to verify if PostgreSQL's cost-based optimizer would choose it
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10;
-- Output will demonstrate: Index Scan using <hypo_index_oid>...

-- 3. Remove the hypothetical index
SELECT * FROM hypopg_reset();

The Write Tax: Write Amplification & Replication Lag

Every additional index incurs a substantial operational penalty:

INSERT Cost: Every new row must be inserted into the heap and into every single index on the table.
UPDATE Cost: Modifying an indexed column breaks HOT (Heap-Only Tuple) optimization, forcing index updates across all table indexes and generating heavy WAL traffic.
Replication Lag: High WAL write volumes cause physical streaming replication delays across read replicas.

Number of Indexes	Relative INSERT Overhead	Write-Ahead Log (WAL) Multiplier
1–2	Baseline (+5–10%)	1.0x
5–7	+50–100%	2.5x–3.0x
10–15+	+200–500%	6.0x–10.0x

When NOT to Create an Index

Small Lookup Tables (< 1,000 rows): Sequential scans in memory are faster than traversing B-tree blocks plus reading the heap.
High-Throughput Append-Only Tables: Ingestion pipelines, time-series telemetry, and audit sinks where write throughput is prioritized over read queries.
Low-Cardinality Columns Without Partial Filtering: Standard B-trees on booleans or enums waste space and are ignored by the planner.
Columns with High Update Frequency: If a column like last_heartbeat_at is updated every 5 seconds, an index will cause catastrophic page splits and bloat.

Bulk Data Loading Strategy

When inserting millions of records into a table, maintaining indexes on-the-fly degrades throughput by 5x–10x:

-- 1. Drop secondary indexes before bulk loading
DROP INDEX idx_orders_customer_id;

-- 2. Stream data directly to the heap
COPY orders FROM '/data/orders_dump.csv' WITH (FORMAT csv);

-- 3. Recreate the index in parallel
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders(customer_id);

4 Tricky Questions

1. Why does PostgreSQL NOT automatically create indexes on Foreign Key columns?

Answer:
The SQL standard and database architecture do not mandate automatic FK indexing because in certain workloads (e.g., append-only child tables where parent rows are never updated or deleted), an FK index is a pure write tax with zero read benefits.
However, in production OLTP systems, omitting an FK index is a critical performance hazard: if a transaction executes DELETE FROM parent WHERE id = ?, PostgreSQL must verify that no child rows reference that ID. Without an index on child.parent_id, PostgreSQL must perform a full Seq Scan on the child table while holding a ROW SHARE lock, which can cascade into severe production lock contention.

2. How should you index a boolean column `is_processed` where 99% of rows are `true` and 1% are `false`?

Answer:
Creating a standard B-tree index on is_processed is an anti-pattern:

For WHERE is_processed = true, the planner ignores the index because fetching 99% of the table is cheaper with a Seq Scan.
The index consumes gigabytes of wasted storage and incurs write penalties on every update.
Correct Solution: Create a Partial Index:
```
CREATE INDEX idx_unprocessed_orders ON orders(order_id) 
WHERE is_processed = false;
```
This index covers only the 1% active queue rows. It occupies megabytes instead of gigabytes, resides permanently in the RAM buffer cache, and executes in microseconds.

3. How do you identify completely unused indexes that should be dropped?

Answer:
Query the pg_stat_user_indexes catalog for indexes with zero scans:

SELECT 
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS wasted_size,
    idx_scan
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0
  AND NOT indisunique       -- Do NOT drop unique constraints!
  AND NOT indisprimary      -- Do NOT drop primary keys!
ORDER BY pg_relation_size(indexrelid) DESC;

Caveat: Always verify the server uptime and ensure the statistics have not been recently cleared (pg_stat_reset()). Also, ensure the index is not required by infrequent monthly or year-end financial reporting queries.

4. Why might an index created on a `VARCHAR` column be ignored when queried with a parameter like `WHERE code = 100`?

Answer:
Because of Data Type Precedence and Implicit Casting.
When comparing a VARCHAR column with an unquoted integer literal 100, PostgreSQL’s type resolution rules give precedence to INTEGER. The query planner rewrites the predicate internally as:

WHERE CAST(code AS integer) = 100

Wrapping the column in a cast function prevents the standard B-tree index on code from being used, triggering an unexpected Seq Scan. The query must pass matching string types (WHERE code = '100') or use an expression index.

🎯 Interview Cheat Sheet

Core Decision Framework

Index When: High selectivity filters in WHERE (< 5–10%), Foreign Keys, frequent JOIN columns, and ORDER BY ... LIMIT queries on large tables (> 10,000 rows).
Do NOT Index: Small lookup tables (< 1,000 rows), low-cardinality columns, write-heavy audit streams, or frequently updated columns that break HOT updates.
Foreign Keys Are Not Automatic: Always explicitly index Foreign Key columns in OLTP databases to prevent full table scans and locking on parent deletions.

Diagnostic & Optimization Tools

Telemetry: Use pg_stat_statements to find queries with high cumulative time and high shared_blks_read / calls ratios.
Simulation: Use the HypoPG extension to create in-memory virtual indexes and verify optimizer plan changes before building on disk.
Production Safety: Always build production indexes using CREATE INDEX CONCURRENTLY.
Bulk Ingestion: Drop non-essential indexes before massive COPY ingestion, then rebuild in parallel.

Red Flags (What NOT to Say)

❌ “Postgres automatically indexes foreign keys just like primary keys.” (Foreign keys are NOT indexed automatically; you must create them manually).
❌ “We should index every column to cover all possible search queries.” (Massive write amplification, WAL generation, and replica lag).
❌ “If an index has idx_scan = 0, it is safe to drop immediately.” (Must check statistics uptime; might be used by monthly or quarterly billing jobs).

What are indexes and why are they needed — Index fundamentals and scan types
What are the disadvantages of indexes — Write tax and maintenance penalties
What is a composite index — Multi-column indexing and prefix rules
What is index cardinality — Selectivity thresholds and optimizer decisions