What is the difference between implicit and explicit type conversion, and why does implicit conversion hurt performance?

Explicit conversion is when you use CAST/CONVERT to deliberately change a type. Implicit conversion is when the database auto-converts types to make a comparison work (e.g., WHERE varchar_col = 123). Implicit conversion in a WHERE predicate forces the database to apply the conversion to every row in the table, which invalidates the index — turning an index seek into a full table scan. On a 5M-row table this can mean 8,000ms instead of 80ms.

Mention EXPLAIN ANALYZE as the tool to catch this in production.

Why should you never use FLOAT for monetary values?

FLOAT uses binary (base-2) representation, which cannot exactly represent most base-10 decimals. 0.1 in binary is a repeating fraction, just like 1/3 in decimal. This causes accumulation of rounding errors: 0.1 + 0.2 = 0.30000000000000004. For money use DECIMAL(p,s) — it stores exact decimal values. A common choice is DECIMAL(19,4) for currencies.

Bonus: mention that FLOAT is 2–3× faster — so it's fine for scientific data where approximation is acceptable.

What is TIMESTAMPTZ and why should global applications use it?

TIMESTAMPTZ (PostgreSQL) is a timestamp that is always stored as UTC internally, then converted to the session timezone on read. TIMESTAMP (or DATETIME) stores a "wall clock" time with no timezone context — ambiguous when servers span regions or when DST transitions occur. Global apps should always store in UTC (TIMESTAMPTZ or DATETIME with UTC convention) and convert to local time at display.

Distinguish named timezones (America/New_York, auto-handles DST) from fixed offsets (-05:00, does not).

What is TRY_CAST and when would you use it over CAST?

TRY_CAST attempts a type conversion and returns NULL on failure instead of throwing an error. Use it whenever data quality is uncertain: ETL pipelines ingesting raw CSVs, API input validation, staging tables before cleansing. CAST is fine when you control the data or have already validated it. Combine TRY_CAST with CASE to classify rows as valid / invalid / partial for downstream processing.

How does PostgreSQL JSONB differ from plain JSON, and when would you choose each?

JSON stores the raw text (preserving whitespace, key order). JSONB stores a binary decomposition: deduplicated keys, sorted, indexed. JSONB is 3–5× faster to read, supports GIN indexes for containment queries (@>), and enables path operators (->>, ->). The tradeoff is a slightly slower write (binary serialization). Use JSONB for anything you query; JSON only if you need to preserve exact text representation (rare).

A colleague suggests using VARCHAR(255) for every string column "just to be safe." What is wrong with this?

VARCHAR(255) allocates up to 256 bytes of metadata overhead in some engines even when storing short values, and wastes space in sort buffers and temporary tables. Right-sizing columns improves buffer cache efficiency, reduces sort memory usage, and makes schema intent clear. More practically: some engines create MEMORY temporary tables with fixed-width rows for sorting — VARCHAR(255) reserves 255 bytes per row there regardless of actual content, degrading sort performance on large result sets.

SQL Server creates in-memory temp tables with the full declared width; MySQL (MEMORY engine) does the same.

You inherit a query: `WHERE created_at = '2024-01-15'`. The created_at column is TIMESTAMP. EXPLAIN shows a full scan. How do you fix it?

-- Problem: '2024-01-15' is a DATE, created_at is TIMESTAMP.

-- Implicit conversion applies CAST to every row → no index.

-- Fix 1: Range comparison (works on any engine)

WHERE created_at >= '2024-01-15 00:00:00'

AND created_at < '2024-01-16 00:00:00'

-- Fix 2: Cast the literal explicitly to TIMESTAMP

WHERE created_at = CAST('2024-01-15' AS TIMESTAMP)

-- Note: this only finds exact midnight; use range for all-day

-- Fix 3 (PostgreSQL only): Date trunc — but this still hits index

-- only if the index is on DATE_TRUNC('day', created_at), not created_at

WHERE DATE_TRUNC('day', created_at) = '2024-01-15'::DATE -- only if functional index exists

-- VERIFY with EXPLAIN:

EXPLAIN SELECT * FROM orders

WHERE created_at >= '2024-01-15' AND created_at < '2024-01-16';

-- Should show: Index Scan on idx_created_at

Fix the implicit conversion: use a range predicate so the index on created_at (TIMESTAMP) can be used as an index scan, or cast the literal to TIMESTAMP. Never wrap the column in a function — that defeats the index.