Your user-auth service runs on PostgreSQL 15 (and has a legacy MySQL 8 shard) with a 50-million-row `users` table (`phone_number VARCHAR(20)` with a B-Tree index `idx_users_phone`). After a Java/Python API refactor, `SELECT id, password_hash FROM users WHERE phone_number = ?` jumps from **2 ms** to **18 seconds**, spiking DB CPU to 100% and disk read IOPS to 40,000. Diagnose why the B-Tree index is ignored and deploy a zero-downtime fix.
From patterns to decisions.
Your user-auth service runs on PostgreSQL 15 (and has a legacy MySQL 8 shard) with a 50-million-row `users` table (`phone_number VARCHAR(20)` with a B-Tree index `idx_users_phone`). After a Java/Python API refactor, `SELECT id, password_hash FROM users WHERE phone_number = ?` jumps from **2 ms** to **18 seconds**, spiking DB CPU to 100% and disk read IOPS to 40,000. Diagnose why the B-Tree index is ignored and deploy a zero-downtime fix.
A VARCHAR B-Tree is sorted.
A VARCHAR B-Tree is sorted lexicographically, whereas numeric comparison treats '09876543210' and '9876543210' as equal. The database must cast every row's column value, invalidating the index.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | PostgreSQL throws an operator error on `varchar = bigint`, while MySQL silently degrades to a full table scan. |
Step 1
Run EXPLAIN (ANALYZE, BUFFERS) (PostgreSQL) or EXPLAIN FORMAT=JSON (MySQL).
Look for `Seq Scan` / `type
ALL and CAST(phone_number AS ...)` in the plan.
Alternative 1
Check pg_stat_statements for queries with sudden spikes in shared_blks_read.
Failure Mode 1
Testing on small staging tables hides the Seq Scan until production hits 50M rows.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, password_hash FROM users WHERE phone_number = '9876543210';
-- Expect: Index Scan using idx_users_phone (actual time=0.04..0.05 ms)
Bind phone_number as String in.
Bind phone_number as String in Java/Python to match VARCHAR(20), and optionally build a concurrent expression index if an app hotfix cannot ship immediately.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Fixing the bind type costs 0 extra storage and avoids write amplification on `users`. |
Step 1
Change DTO/parameter type back to String and use ps.setString(1, phoneNumber).
Step 2
Verify EXPLAIN shows Index Scan using idx_users_phone.
Alternative 1
Emergency DB bridge: CREATE INDEX CONCURRENTLY idx_users_phone_num ON users ((phone_number::bigint)) WHERE phone_number ~ '^[0-9]+$';.
Failure Mode 1
Leaving an emergency expression index forever wastes GBs of RAM and slows INSERT/UPDATE.
String sql = "SELECT id, password_hash FROM users WHERE phone_number = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, phoneNumber); // Matches VARCHAR(20) -> SARGable
try (ResultSet rs = ps.executeQuery()) {
// < 2 ms B-Tree Index Scan
}
}
Implementation & Guardrails (3).
No—wrapping the column in CAST(phone_number AS BIGINT) still prevents idx_users_phone from being used. You must cast the parameter (CAST(? AS VARCHAR)), or better, bind a String.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Casting the parameter is SARGable, though it still fails if leading zeros (`'09876543210'`) were stripped by the integer. |
Step 1
Keep the indexed column bare on the left-hand side: WHERE phone_number = CAST(? AS VARCHAR).
Step 2
Verify idx_users_phone is selected in EXPLAIN.
Alternative 1
Bind as String in application code so leading zeros and + prefixes are preserved.
Failure Mode 1
Integer binding silently strips leading zeros (09876543210 -> '9876543210'), causing missed user logins.
-- Non-SARGable (ignores idx_users_phone):
SELECT id FROM users WHERE CAST(phone_number AS BIGINT) = 9876543210;
-- SARGable (uses idx_users_phone):
SELECT id FROM users WHERE phone_number = CAST(9876543210 AS VARCHAR);
Implementation & Guardrails (4).
Mismatched character sets or collations (e.g., utf8mb4_unicode_ci vs utf8mb4_0900_ai_ci or latin1) force MySQL/PostgreSQL to convert one column per row, invalidating the B-Tree index.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Changing collation on a 50M-row table requires rebuilding indexes via `pt-online-schema-change` or `gh-ost`. |
Step 1
Inspect SHOW CREATE TABLE users and SHOW CREATE TABLE profiles for COLLATE differences.
Step 2
Align both columns to the identical charset and collation.
Alternative 1
Use COLLATE on the smaller table's join key if immediate alignment isn't possible.
Failure Mode 1
Legacy tables created before an upgrade often retain latin1 or utf8mb3 collations.
SELECT table_name, column_name, data_type, character_set_name, collation_name
FROM information_schema.columns
WHERE table_name IN ('users', 'profiles') AND column_name = 'phone_number';
Run integration tests against a.
Run integration tests against a seeded PostgreSQL/MySQL container with enable_seqscan = off or assert EXPLAIN (FORMAT JSON) shows Index Scan on all critical repository queries.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Note that tiny test tables (<100 rows) may still choose `Seq Scan`; set `SET LOCAL enable_seqscan = off` or seed 10k rows in CI. |
Step 1
Parse EXPLAIN (FORMAT JSON) in a JUnit/pytest test and fail if Node Type == 'Seq Scan' on large tables.
Step 2
Enable log_min_duration_statement = 200 in staging load tests.
Alternative 1
Use PostgreSQL auto_explain in staging with auto_explain.log_min_duration = '50ms'.
Failure Mode 1
Testing against an empty table never catches index invalidation.
cur.execute("EXPLAIN (FORMAT JSON) SELECT id FROM users WHERE phone_number = %s", ("9876543210",))
plan = cur.fetchone()[0][0]["Plan"]
assert plan["Node Type"] != "Seq Scan", f"Non-SARGable query plan: {plan}"
Theory & Tips and Tricks
Practical heuristics & interview traps
- 01Interview anchor: Ensure strict type parity between ORM/JDBC bind parameters and column types (making predicates SARGable), and use a concurrent expression index as an immediate bridge.
- 02Trade-off tip: Casting the parameter is SARGable, though it still fails if leading zeros (
'09876543210') were stripped by the integer. - 03Watch out for: Legacy tables created before an upgrade often retain
latin1orutf8mb3collations.
B Tree Index Invalidation Type Coercion — Normal → Load / Fault → Recovery
Ensure strict type parity between ORM/JDBC bind parameters and column types (making predicates SARGable), and use a concurrent expression index as an immediate bridge.
Putting Why Implicit Type Coercion Breaks B-Tree Indexes Together
Ensure strict type parity between ORM/JDBC bind parameters and column types (making predicates SARGable), and use a concurrent expression index as an immediate bridge.
Putting Why Implicit Type Coercion Breaks B-Tree Indexes Together
- Change DTO/parameter type back to
Stringand useps.setString(1, phoneNumber). - Verify
EXPLAINshowsIndex Scan using idx_users_phone.
Interview Readiness Check
Mismatched character sets or collations (e.g., utf8mb4_unicode_ci vs utf8mb4_0900_ai_ci or latin1) force MySQL/PostgreSQL to convert one column per row, invalidating the B-Tree index.
Walk through bottlenecks, back-of-the-envelope math, and failure recovery.
Make the trade-off yourself.
Practice Check
Test your understanding
When is ACID Transaction Fundamentals the right default for a production team?
Why Implicit Type Coercion Breaks B-Tree Indexes
Ensure strict type parity between ORM/JDBC bind parameters and column types (making predicates SARGable), and use a concurrent expression index as an immediate bridge.
