SQL Interview Questions

DDL (Data Definition Language) defines database structure: CREATE, ALTER, DROP, TRUNCATE. DDL statements modify the database schema (catalog) and are typically auto-committed (except in PostgreSQL which supports transactional DDL). DML (Data Manipulation Language) manipulates data within the structure: SELECT, INSERT, UPDATE, DELETE. DML operations are transactional — they can be rolled back. TRUN

Constraints are rules enforced by the database engine: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT. They should be at the DB level because: 1) Multiple applications may access the same database — app-level validation can be bypassed. 2) Database constraints are atomic within transactions — no partial violation. 3) Database constraints are documented in the schema — self-documenting.

DELETE: DML, logged row-by-row, can have WHERE clause, fires triggers, respects foreign keys, can be rolled back, slow for large tables. TRUNCATE: DDL, minimal logging (deallocates data pages), no WHERE clause, does NOT fire per-row triggers (fires statement-level), may not respect foreign keys (check DB), faster (O(1) vs O(n)). PostgreSQL: TRUNCATE CAN be rolled back (transactional DDL). MySQL: T

PostgreSQL approach (zero-downtime): 1) ADD COLUMN with DEFAULT: ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'ACTIVE'. PostgreSQL 11+ writes the default to catalog metadata only (no row rewrite) — instant. 2) Backfill if needed: UPDATE orders SET status = 'ACTIVE' WHERE status IS NULL (do in batches). 3) Add NOT NULL: ALTER TABLE orders ALTER COLUMN status SET NOT NULL (fast if no nul

FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT/OFFSET. This matters practically: 1) You can't use column aliases defined in SELECT inside WHERE (it hasn't run yet). 2) You can't use aggregate functions in WHERE (GROUP BY hasn't happened). 3) You CAN use aggregate functions in HAVING (runs after GROUP BY). 4) You CAN use SELECT aliases in ORDER BY (runs after SELECT). 5) LI

WHERE filters individual rows BEFORE grouping — it reduces the input to GROUP BY. Cannot use aggregate functions. HAVING filters groups AFTER GROUP BY — applied to the result of aggregation. Can use aggregate functions. Performance: WHERE is more efficient because it reduces data before aggregation. Use WHERE whenever possible; only use HAVING for aggregate conditions. Example: to find departments

OFFSET scans and discards n rows before returning results. With OFFSET 100000, the DB reads and throws away 100,000 rows on every page load — O(n) performance. Keyset pagination (cursor-based) is O(log n): SELECT * FROM orders WHERE order_id > last_seen_id ORDER BY order_id LIMIT 20. The application passes the last seen ID. The query uses the primary key index to jump directly. Limitation of keyse

Step-by-step optimization: 1) EXPLAIN ANALYZE — identify the bottleneck (Seq Scan on orders? Sort taking 20s?). 2) Add partial index on frequently filtered column: CREATE INDEX ON orders(status, order_date) WHERE status IN ('COMPLETED', 'PENDING'). 3) Use materialized views: CREATE MATERIALIZED VIEW daily_order_summary AS SELECT date_trunc('day', order_date), COUNT(*), SUM(amount) FROM orders GROU

INNER JOIN returns only rows where the join condition is true for BOTH tables — unmatched rows from either table are excluded. LEFT JOIN returns ALL rows from the left table and matched rows from the right; unmatched right rows are NULL. RIGHT JOIN is the mirror of LEFT JOIN. FULL OUTER JOIN returns all rows from BOTH tables; where no match exists, the other side is NULL. Venn diagram: INNER = int

The most common cause: a WHERE clause filtering on a column from the right table. Example: SELECT * FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id WHERE d.dept_name = 'Engineering'. If dept_name is NULL (no match), the WHERE condition fails → that row is excluded. Solution: move the filter to the ON clause: LEFT JOIN departments d ON e.dept_id = d.dept_id AND d.dept_name = 'Engi

SELF JOIN: use for comparing rows within the same table (finding duplicates, adjacent rows, hierarchies). Example: employees with their managers. Simple, single-level comparisons. CTE with recursive: use for arbitrary-depth hierarchies (org charts, category trees, bill of materials). SELF JOIN can only do 1 level at a time; recursive CTE can traverse unlimited depth. Example: finding all subordina

Use LEFT JOIN with COALESCE: SELECT e.emp_id, e.first_name || ' ' || e.last_name AS name, COALESCE(SUM(o.amount), 0) AS total_orders, COUNT(o.order_id) AS order_count FROM employees e LEFT JOIN orders o ON e.emp_id = o.emp_id AND EXTRACT(YEAR FROM o.order_date) = 2024 GROUP BY e.emp_id, e.first_name, e.last_name ORDER BY total_orders DESC. Key points: 1) LEFT JOIN ensures all employees appear. 2)

Non-correlated subquery: independent of the outer query. It executes ONCE and its result is used by the outer query. Example: WHERE salary > (SELECT AVG(salary) FROM employees) — inner query runs once, returns one value. Correlated subquery: references columns from the outer query. It executes ONCE FOR EACH ROW of the outer query. Example: WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE

IN: evaluates all values in the subquery result set, then checks membership. If subquery returns NULLs: NOT IN returns empty result. Performance depends on list size. EXISTS: evaluates the subquery and returns TRUE on FIRST match (short-circuit). Always safe with NULLs. Generally preferred for large sets or when membership (not the values) is what matters. Use IN when the subquery returns a small,

Option 1 (Correlated Subquery): SELECT * FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e.dept_id). Option 2 (JOIN with derived table): SELECT e.* FROM employees e JOIN (SELECT dept_id, AVG(salary) AS dept_avg FROM employees GROUP BY dept_id) da ON e.dept_id = da.dept_id WHERE e.salary > da.dept_avg. Option 3 (Window Function): SELECT * FROM (SELECT *, AVG

If the subquery in NOT IN returns ANY NULL value, NOT IN returns empty result. Reason: SQL evaluates 'column NOT IN (1, 2, NULL)' as 'column != 1 AND column != 2 AND column != NULL'. The last condition (column != NULL) evaluates to NULL (UNKNOWN), which causes the entire WHERE condition to fail for every row. Fix: 1) Filter NULLs in the subquery: NOT IN (SELECT dept_id FROM employees WHERE dept_id

Clustered index: the table data is physically stored in the order of the index key. One per table. In SQL Server: every table has one clustered index (by default on PRIMARY KEY). In MySQL InnoDB: the primary key IS the clustered index. In PostgreSQL: no native clustered index; CLUSTER command physically reorders data once but doesn't maintain order on DML. Non-clustered index: a separate B-Tree st

A composite index on (col_a, col_b, col_c) can only be used when the query filters include the leftmost columns in order. Supported: WHERE col_a = x (1st column). WHERE col_a = x AND col_b = y (1st and 2nd). WHERE col_a = x AND col_b = y AND col_c = z (all). NOT supported: WHERE col_b = y (skipping col_a). WHERE col_c = z (skipping col_a and col_b). Reason: the B-Tree is sorted first by col_a, the

Use a partial index when: 1) Only a fraction of rows are queried (e.g., WHERE status = 'PENDING' when 90% of orders are 'COMPLETED'). 2) A specific value is queried far more than others. Benefits: smaller index (only indexes matching rows → faster scans, less memory, faster maintenance). Example: CREATE INDEX ON orders(order_date) WHERE status = 'ACTIVE'. This index is 10× smaller than a full inde

Analysis steps: 1) Check selectivity: how many distinct values? status (3-5 values, low selectivity), customer_id (1000 values, high selectivity), order_date (365 days, high selectivity). 2) Identify query pattern: WHERE status = 'PENDING' AND order_date > '2024-01-01' AND customer_id = 42. 3) Composite index design: most selective columns first — BUT consider that equality filters (=) should prec

Atomicity: all statements in a transaction execute completely or none do. If any step fails, all previous steps in the transaction are rolled back. No partial transactions. Consistency: the database moves from one valid state to another valid state. All constraints (PK, FK, CHECK) are satisfied after commit. Isolation: concurrent transactions operate as if they were executed serially. Isolation le

ROLLBACK: undoes ALL changes made in the current transaction since BEGIN. Returns to pre-BEGIN state. Transaction is ended. ROLLBACK TO SAVEPOINT name: undoes changes only SINCE the savepoint was created. The transaction is NOT ended — it remains active and can continue with more statements. The savepoint itself remains (unless RELEASE SAVEPOINT is used). Use case: complex workflows where a sub-st

WAL (Write-Ahead Log): before any data page is modified, a log record is written to WAL. On COMMIT: PostgreSQL flushes WAL records for the transaction to disk (fsync). Returns success to client only after WAL is durable. On crash: PostgreSQL replays WAL from last checkpoint to recover all committed transactions. Changes not in WAL are lost (should only be uncommitted). fsync=on (default): ensures

Without transaction: debit succeeds, credit fails → money disappears (inconsistency). With transaction: BEGIN; UPDATE accounts SET balance = balance - 1000 WHERE id = 1; UPDATE accounts SET balance = balance + 1000 WHERE id = 2; COMMIT. If the second UPDATE fails (e.g., account 2 doesn't exist, CHECK constraint violation, network error): the exception triggers ROLLBACK. The first UPDATE is also un

Dirty Read: reading data that another transaction has written but not yet committed. If that transaction rolls back, you've read data that never officially existed. Prevented at READ COMMITTED. Non-Repeatable Read: within a single transaction, reading the same row twice returns different values because another transaction committed a change between reads. Prevented at REPEATABLE READ. Phantom Read

READ COMMITTED: each SQL statement sees the most recently committed data at statement start. If Transaction B commits between two SELECT statements in Transaction A, Transaction A's second SELECT sees Transaction B's changes. Prevents dirty reads only. REPEATABLE READ: the entire transaction sees data as of the transaction's start time. Transaction A gets a consistent snapshot: even if Transaction

Serialization failures (error code 40001 in PostgreSQL) are expected and retryable — they indicate a conflict that would violate serial execution order. Application handling: 1) Catch the serialization_failure exception. 2) Roll back the current transaction (it's already invalid). 3) Retry the entire transaction from scratch with a brief delay. 4) Implement exponential backoff: retry 1 after 100ms

Use REPEATABLE READ isolation for the report transaction: BEGIN; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- All 10 queries here see the SAME consistent snapshot -- even if other transactions commit changes during the report COMMIT. With REPEATABLE READ: all queries in the transaction see the data as it was at the moment BEGIN was executed. No data changes by concurrent transactions are vi

Pessimistic locking: assumes conflicts are frequent. Locks the resource before accessing it. SQL: SELECT ... FOR UPDATE. Other transactions are BLOCKED until the lock is released. Guarantees exclusive access during the transaction. Good for: high-conflict scenarios, financial transactions where conflicts are expected. Optimistic locking: assumes conflicts are rare. No locks taken. On write: verifi

Deadlock: Transaction A holds Lock 1 and waits for Lock 2. Transaction B holds Lock 2 and waits for Lock 1. Neither can proceed — circular dependency. PostgreSQL detection: periodically checks the wait graph for cycles (deadlock_timeout = 1s by default). When detected: PostgreSQL aborts one of the transactions (the one that's cheaper to restart) with ERROR: deadlock detected (code 40P01). Preventi

SKIP LOCKED: when attempting to lock rows, if a row is already locked by another transaction, SKIP it instead of blocking. Ideal for job queue pattern: multiple workers run simultaneously, each with: SELECT * FROM jobs WHERE status='PENDING' LIMIT 1 FOR UPDATE SKIP LOCKED. Each worker gets a different (unlocked) row. No blocking between workers. Workers process their claimed jobs, then UPDATE stat

Pessimistic locking is appropriate here — high-conflict scenario (1000 users competing for 1 item). Implementation: BEGIN; SELECT * FROM inventory WHERE product_id = 42 FOR UPDATE NOWAIT; -- Check: if 0 stock, ROLLBACK and return 'sold out'; -- If stock > 0: UPDATE inventory SET stock = stock - 1 WHERE product_id = 42; INSERT INTO orders ...; COMMIT; Use NOWAIT to fail fast rather than queuing 999

All three assign numbers to rows within a window, but handle ties differently. ROW_NUMBER(): always unique — assigns sequential 1,2,3... even for tied values. Arbitrary which tie gets lower number (depends on physical order or secondary sort). RANK(): assigns same rank to ties, then SKIPS the next number(s): 1,1,3,4. DENSE_RANK(): assigns same rank to ties but does NOT skip: 1,1,2,3. Example with

Use ROW_NUMBER() with PARTITION BY: SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employees) ranked WHERE rn <= 2. How it works: ROW_NUMBER() assigns 1 to highest salary per department, 2 to second highest, etc. The outer WHERE rn <= 2 keeps only the top 2 per department. Why ROW_NUMBER over RANK: if two employees tie at the same salary, RANK wou

Both access values from other rows within the window without self-joins. LAG(col, n, default): returns the value from n rows BEFORE the current row within the partition. LEAD(col, n, default): returns the value from n rows AFTER the current row. Default: value returned when n goes out of partition bounds (default is NULL). Example: SELECT revenue, LAG(revenue, 1, 0) OVER (ORDER BY month_date) AS p

SELECT sale_id, salesperson_id, region, amount, SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total, AVG(amount) OVER (PARTITION BY DATE_TRUNC('month', sale_date)) AS monthly_avg, RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_in_region FROM sales ORDER BY sale_date, region. Explanation: Running total uses cumulative frame (ROWS U

A CTE (Common Table Expression) is a named temporary result set defined in a WITH clause before the main query. Subquery: anonymous, defined inline (in FROM, WHERE, SELECT). CTE: named, defined once at the top, reusable multiple times in the main query. Readability: CTEs break complex queries into named steps (like functions). A 10-level nested subquery is unreadable; 10 CTEs is manageable. Perfor

A recursive CTE has two parts separated by UNION ALL: 1) Anchor member: non-recursive SELECT that returns the starting rows (e.g., employees with no manager). 2) Recursive member: references the CTE itself; joins to find next-level rows (e.g., employees whose manager_id = cte.emp_id). Execution: anchor runs → produces working table W. Recursive member runs with W as input → produces new rows. Repe

A writeable CTE contains DML (INSERT, UPDATE, DELETE) in the CTE body and uses RETURNING to pass modified data to the main query or other CTEs. Example: WITH updated AS (UPDATE employees SET salary = salary * 1.1 WHERE dept_id = 1 RETURNING emp_id, salary) INSERT INTO audit(emp_id, new_salary) SELECT emp_id, salary FROM updated. Both UPDATE and INSERT execute in a single transaction — atomic. The

WITH RECURSIVE subordinates AS (-- Anchor: start with the target manager SELECT emp_id, first_name, manager_id, 0 AS level FROM employees WHERE emp_id = 1 -- Alice (find all her reports) UNION ALL -- Recursive: find all direct reports of each person in the CTE SELECT e.emp_id, e.first_name, e.manager_id, s.level + 1 FROM employees e JOIN subordinates s ON e.manager_id = s.emp_id WHERE s.level < 10

Key things to examine: 1) Seq Scan on large tables: a Seq Scan on a million-row table means no index is being used. Candidate for indexing. 2) Actual rows vs estimated rows: if actual=10000 and estimated=10, the planner used a bad plan. Run ANALYZE to update statistics. 3) Execution time vs planning time: if planning takes longer than execution, the query is very fast. If execution is long, find t

Partitioning divides a large table into smaller physical sub-tables (partitions) while presenting them as a single table to queries. Types: Range (most common): partition by date, number range. List: partition by discrete values (country, region). Hash: even distribution. When to use: 1) Table > ~100GB AND queries commonly filter by the partition key. 2) Archival: easily drop old partitions (vs sl

Partitioning: logical division within a single database server. Data split into partitions on the same machine. Query planner does partition pruning automatically. Transparent to the application. Sharding: physical distribution across multiple database servers (nodes). Each node stores a subset of rows. Requires application awareness (which shard to query) or a middleware layer. Much higher operat

Step-by-step: 1) Identify the WHERE clause filter columns: WHERE order_date >= '2024-01-01' AND status = 'PENDING'. 2) Create targeted index: CREATE INDEX CONCURRENTLY ON orders(order_date, status) WHERE status IN ('PENDING', 'PROCESSING'). Partial composite index. 3) EXPLAIN ANALYZE again — should show Index Scan or Bitmap Index Scan. 4) If still slow: consider partitioning by order_date (RANGE P

1NF: Every column holds atomic (indivisible) values. No arrays, no comma-separated lists. Each row is unique. Example violation: storing 'Engineering, Marketing' in a single column. 2NF: 1NF + no partial dependencies. Every non-key column must depend on the ENTIRE primary key (relevant when PK is composite). Example violation: storing product_name in order_items when PK is (order_id, product_id) —

Separate Database: one database per tenant. Maximum isolation, easy backup per tenant. Cons: very expensive, hard to manage at 1000 tenants, sharing schema changes across tenants is complex. Separate Schema: one database, one schema per tenant. Good isolation (schemas are separate namespaces). Cons: 1000 tenants = 1000 schemas, connection pool complications. Shared Schema: one database, one schema

When to denormalize: 1) Measured performance bottleneck from JOINs in hot-path queries. 2) Reporting tables that are read-heavy and rarely updated. 3) When the JOIN cost exceeds the storage/sync cost. Methods: add redundant columns (customer_name in orders), materialized views, summary tables, caching layer. Risks: 1) Data inconsistency: redundant data must be kept in sync. Solution: triggers, app

Shared Database, Shared Schema with Row-Level Security for 5000 tenants. Rationale: Separate DB (5000 databases) = impossible to manage, hugely expensive. Separate Schema (5000 schemas) = planner overhead, connection pool complexity. Shared Schema = single manageable database, cost-effective, RLS enforces isolation. Implementation: 1) Add UUID tenant_id to every table. 2) CREATE INDEX on (tenant_i

MVCC (Multi-Version Concurrency Control): PostgreSQL never overwrites rows in place. Instead, each UPDATE creates a NEW version of the row with a new transaction ID (xmin) and marks the old version as dead (xmax = the updating transaction). Every row has xmin (which transaction created it) and xmax (which transaction deleted/updated it, or 0 if current). Read transaction at time T sees rows where

JSON: stored as plain text, preserved exactly as-is (including whitespace and duplicate keys). Validation only on input. Slower reads (must reparse text on every access). No GIN index support. JSONB: stored as a decomposed binary format. Keys are sorted; duplicate keys removed (last one wins). Faster for reads (no reparsing). Supports GIN index for fast key/value containment queries (@>, ?, ?|, ?&

VACUUM removes dead row versions (dead tuples) created by PostgreSQL's MVCC. When you UPDATE or DELETE, old row versions are marked dead but not immediately removed. They accumulate as bloat, wasting disk space and slowing queries. VACUUM (regular): marks dead tuples as reusable, updates Free Space Map and Visibility Map. No exclusive lock. AUTOVACUUM: runs automatically based on n_dead_tup thresh

1) Verify bloat: SELECT relname, n_dead_tup, n_live_tup, ROUND(n_dead_tup * 100.0 / (n_live_tup + n_dead_tup), 2) AS dead_pct FROM pg_stat_user_tables WHERE relname = 'orders'. 2) Check if autovacuum is running: SELECT last_autovacuum, autovacuum_count FROM pg_stat_user_tables WHERE relname = 'orders'. If never/rarely: autovacuum is overwhelmed. 3) Immediate fix: VACUUM ANALYZE orders — removes de

PostgreSQL 11+: ALTER TABLE t ADD COLUMN status VARCHAR(20) DEFAULT 'ACTIVE'. In PostgreSQL 11+, this writes the DEFAULT to catalog metadata only — no row rewrite, O(1). Then: ALTER TABLE t ALTER COLUMN status SET NOT NULL (fast if all rows have the default). For older versions or MySQL: 1) Add column as nullable with default. 2) Backfill in batches (UPDATE ... LIMIT 10000). 3) Add NOT NULL after

Step 1: Identify WHERE clause columns being filtered. Step 2: Check selectivity — is the filter highly selective (< 10% rows)? Step 3: CREATE INDEX CONCURRENTLY ON table(filtered_col). Step 4: EXPLAIN ANALYZE again to verify Index Scan is chosen. Step 5: If still slow: is the filter selective enough? For low selectivity (> 20%), seq scan may be intentionally chosen. Consider: partial index, coveri

OFFSET N scans and discards N rows — O(n). For OFFSET 1,000,000: 1 million rows read and thrown away. Solution: Keyset pagination. Instead of OFFSET: WHERE id > last_seen_id ORDER BY id LIMIT 20. The query uses the primary key index — O(log n). In API: return last_id in response; next request uses it. For complex ORDER BY: use a tuple comparison: WHERE (salary, id) < (last_salary, last_id) ORDER B

If the subquery returns ANY NULL value, NOT IN returns no rows. SQL evaluates 'col NOT IN (1, 2, NULL)' as 'col != 1 AND col != 2 AND col != NULL'. The last condition evaluates to UNKNOWN (NULL), which fails the WHERE for every row. Debug: SELECT * FROM employees WHERE dept_id IS NULL — if any rows exist, this is the cause. Fix option 1: NOT IN (SELECT dept_id FROM depts WHERE dept_id IS NOT NULL)

PostgreSQL logs deadlocks with log_lock_waits = on. Check the log for 'deadlock detected' — it shows the lock cycle. Common cause: Transaction A locks row 1 then row 2; Transaction B locks row 2 then row 1. Fix: enforce consistent lock ordering. For bank transfers: always lock the lower account_id first: SELECT * FROM accounts WHERE id IN (1,2) ORDER BY id FOR UPDATE. Set deadlock_timeout (default

SELECT * FROM (SELECT *, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dr FROM employees) ranked WHERE dr = 3; Why DENSE_RANK: if multiple employees share the same salary rank, DENSE_RANK assigns same rank without skipping. So the '3rd distinct salary' is what we get. Alternative using ROW_NUMBER if you want the 3rd person (not 3rd salary): replace DENSE_RANK with ROW_NUMBER. Al

Range partitioning by date: CREATE TABLE orders (... order_date DATE NOT NULL, ...) PARTITION BY RANGE (order_date). Create monthly partitions: CREATE TABLE orders_2024_01 PARTITION OF orders FOR VALUES FROM ('2024-01-01') TO ('2024-02-01'). Use pg_partman extension for automatic partition management. Result: queries for 'this month' only scan 1/12 of data (partition pruning). Old data: CREATE TAB

SELECT e.emp_id, e.first_name || ' ' || e.last_name AS manager_name, COUNT(r.emp_id) AS direct_reports FROM employees e LEFT JOIN employees r ON r.manager_id = e.emp_id GROUP BY e.emp_id, e.first_name, e.last_name ORDER BY direct_reports DESC; Key points: LEFT JOIN ensures managers with 0 reports appear (COUNT gives 0). SELF JOIN on manager_id = emp_id. GROUP BY on the manager's columns. Alternati

WITH monthly AS (SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue FROM orders WHERE order_date >= NOW() - INTERVAL '13 months' GROUP BY 1) SELECT month, revenue, LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev_month, revenue - LAG(revenue, 1, 0) OVER (ORDER BY month) AS abs_change, ROUND(100.0 * (revenue - LAG(revenue, 1, 1)) OVER (ORDER BY month) / NULLIF(LAG(revenue, 1, 1

Shared Schema + Row-Level Security for 10,000 tenants: 1) Add UUID tenant_id to every table. 2) Composite index: CREATE INDEX ON orders(tenant_id, order_date). 3) RLS: ALTER TABLE orders ENABLE ROW LEVEL SECURITY; CREATE POLICY orders_tenant ON orders USING (tenant_id = current_setting('app.tenant_id')::UUID). 4) Application: SET LOCAL app.tenant_id = ? on every connection checkout. 5) Never expos

Running total: SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). For 10M rows: 1) Index on the ORDER BY column: CREATE INDEX ON orders(order_date, amount) — covering index avoids heap. 2) Partition the table by date — queries on recent data only process recent partition. 3) Incremental materialized view: instead of computing running total on query, maintain a cumul

CREATE TABLE job_queue (id BIGSERIAL PRIMARY KEY, payload JSONB, status VARCHAR(20) DEFAULT 'PENDING', created_at TIMESTAMP DEFAULT NOW(), started_at TIMESTAMP, worker_id TEXT); Worker claim query: BEGIN; SELECT id, payload FROM job_queue WHERE status = 'PENDING' ORDER BY created_at LIMIT 1 FOR UPDATE SKIP LOCKED; -- Only if row found: UPDATE job_queue SET status = 'PROCESSING', started_at = NOW()

DELETE FROM employees WHERE emp_id IN (SELECT emp_id FROM (SELECT emp_id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn FROM employees) ranked WHERE rn > 1); How it works: ROW_NUMBER assigns 1 to the most recent row per email (rn=1), 2+ to older duplicates. DELETE removes all rows where rn > 1 (the duplicates). After deletion: ADD UNIQUE CONSTRAINT to prevent future duplica

WITH RECURSIVE subordinates AS (-- Anchor: direct reports of manager with id=1 SELECT emp_id, first_name, manager_id, 1 AS level FROM employees WHERE manager_id = 1 UNION ALL -- Recursive: find reports of reports SELECT e.emp_id, e.first_name, e.manager_id, s.level + 1 FROM employees e JOIN subordinates s ON e.manager_id = s.emp_id WHERE s.level < 10 -- safety limit) SELECT emp_id, first_name, lev

1) Immediate: VACUUM ANALYZE orders — removes dead tuple pointers, updates statistics, no lock. 2) Verify: SELECT n_dead_tup, n_live_tup FROM pg_stat_user_tables WHERE relname='orders' — should drop. 3) Tune autovacuum: ALTER TABLE orders SET (autovacuum_vacuum_scale_factor=0.01, autovacuum_vacuum_threshold=1000). 4) Find long transactions blocking vacuum: SELECT pid, xact_start, state FROM pg_sta

Option 1 (JSONB — recommended for PostgreSQL): ALTER TABLE products ADD COLUMN specs JSONB. Store laptop: {ram_gb:16, cpu:'i7', storage_tb:1}. Store clothes: {size:'XL', color:'red', material:'cotton'}. Create GIN index: CREATE INDEX ON products USING GIN(specs). Query: WHERE specs @> '{"ram_gb": 16}'. Pros: flexible, queryable, indexed. Option 2 (EAV — avoid): product_attributes(product_id, key,

SELECT month_date, revenue, AVG(revenue) OVER (ORDER BY month_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3m, SUM(revenue) OVER (PARTITION BY DATE_PART('year', month_date) ORDER BY month_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ytd_cumulative FROM monthly_sales ORDER BY month_date; Moving average: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW = current + 2 prior rows (

Systematic approach: 1) EXPLAIN (ANALYZE, BUFFERS) — identify which join/node takes most time. 2) Fix index problems: ensure all JOIN ON columns have indexes (especially FK columns). 3) Check rows estimated vs actual: discrepancy > 10x → ANALYZE the tables; update statistics. 4) Check Hash Batches > 1 → hash join spilling to disk → SET LOCAL work_mem = '512MB'. 5) Join order: start from smallest f

1) Exact match: CREATE UNIQUE INDEX ON users(email) — already unique; B-Tree for =. 2) Name partial match (full-text): CREATE INDEX ON users USING GIN(to_tsvector('english', first_name || ' ' || last_name)). Query: WHERE to_tsvector('english', ...) @@ to_tsquery('john'). 3) OR for simple LIKE prefix: CREATE INDEX ON users(last_name) for WHERE last_name LIKE 'Smith%'. 4) Status filter: low cardinal

JPA @Version annotation: @Entity public class Order { @Id Long id; @Version Integer version; ... }. JPA auto-handles: on UPDATE, includes AND version = :current in the WHERE clause. On 0 rows updated: throws OptimisticLockException (DataAccessException in Spring). SQL generated: UPDATE orders SET ... version = version+1 WHERE id = ? AND version = ?. Spring retry: @Retryable(value=OptimisticLockExc

Use Flyway or Liquibase: 1) Every schema change is a versioned migration file: V2024_01_15__add_status_column.sql. 2) Migrations run automatically on application startup (flyway.baseline-on-migrate=true for existing schemas). 3) Backward-compatible migrations: add nullable column first, deploy app, then add NOT NULL/backfill. 4) Zero-downtime deployment requires schema compatible with both old and

Diagnose: SELECT count(*), state, wait_event_type FROM pg_stat_activity GROUP BY state, wait_event_type — shows connection distribution. Check for idle-in-transaction connections (long-running transactions holding connections). Fix: 1) Connection pooler (PgBouncer in transaction mode): all app threads share a smaller pool of actual DB connections. pool_size=20 DB connections serve 500 app threads.

SELECT generate_series + 1 AS missing_id FROM generate_series(1, (SELECT MAX(id) FROM orders) - 1) gs WHERE gs + 1 NOT IN (SELECT id FROM orders); Or more efficiently: SELECT s.id + 1 AS gap_start FROM orders s WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.id = s.id + 1) AND s.id < (SELECT MAX(id) FROM orders) ORDER BY gap_start; Or with LAG: SELECT prev_id + 1 AS gap_start, curr_id - 1 AS gap_

Trigger-based audit: CREATE TABLE orders_audit (audit_id BIGSERIAL, operation CHAR(1), changed_by TEXT, changed_at TIMESTAMP, old_data JSONB, new_data JSONB); CREATE OR REPLACE FUNCTION log_orders_audit() RETURNS TRIGGER AS [func_body] CREATE TRIGGER orders_audit_trigger AFTER INSERT OR UPDATE OR DELETE ON orders FOR EACH ROW EXECUTE FUNCTION log_orders_audit(). In trigger body: operation = TG_OP[

Method 1 (NOT EXISTS — preferred): SELECT p.* FROM products p WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id); Method 2 (LEFT JOIN anti-join): SELECT p.* FROM products p LEFT JOIN order_items oi ON p.product_id = oi.product_id WHERE oi.order_id IS NULL; Method 3 (NOT IN — avoid NULLs): SELECT p.* FROM products p WHERE p.product_id NOT IN (SELECT product_id FROM o

Check current sequence value: SELECT last_value FROM employees_emp_id_seq; Check max ID in table: SELECT MAX(emp_id) FROM employees; Reset sequence to match: SELECT setval('employees_emp_id_seq', (SELECT MAX(emp_id) FROM employees)); Or to set next value: SELECT setval('employees_emp_id_seq', (SELECT MAX(emp_id) FROM employees), true); -- true = last_value (next INSERT will be max+1). In PostgreSQ

CREATE TABLE transactions (txn_id BIGSERIAL, account_id INT, amount DECIMAL(10,2), -- positive=deposit, negative=withdrawal txn_date TIMESTAMP DEFAULT NOW(), description TEXT); SELECT txn_id, txn_date, amount, SUM(amount) OVER (PARTITION BY account_id ORDER BY txn_date, txn_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_balance FROM transactions WHERE account_id = 42 ORDER BY txn_

PostgreSQL 15+ MERGE statement: MERGE INTO customers AS target USING staging_customers AS source ON target.email = source.email WHEN MATCHED THEN UPDATE SET name=source.name, phone=source.phone, updated_at=NOW() WHEN NOT MATCHED THEN INSERT (email, name, phone, created_at) VALUES (source.email, source.name, source.phone, NOW()); PostgreSQL INSERT ON CONFLICT (all versions): INSERT INTO customers (

Creating index BEFORE bulk load: each INSERT also updates the index. For 10M rows: 10M index insertions (individual B-Tree inserts). Much slower overall. Creating index AFTER bulk load: 1) Load all rows (INSERT or COPY) without index — fast sequential writes. 2) Then CREATE INDEX CONCURRENTLY — reads all rows once, builds sorted index. Total: load time + one full table scan. Orders of magnitude fa

PostgreSQL: SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary FROM employees; Or using window function: SELECT DISTINCT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median FROM employees; Cross-database approach: SELECT AVG(salary) AS median FROM (SELECT salary, ROW_NUMBER() OVER (ORDER BY salary) AS rn, COUNT(*) OVER () AS cnt FROM employees) t WHERE rn IN (FLO

customers(id, email UNIQUE, name, created_at) — email is natural unique key categories(id, name, parent_id NULLABLE FK self-referential for hierarchy) products(id, name, price DECIMAL, sku UNIQUE, category_id FK, created_at) orders(id, customer_id FK, status, total_amount, created_at) — snapshot total at order time order_items(order_id FK, product_id FK, quantity, unit_price, PRIMARY KEY(order_id,

Shared Schema + RLS pattern for scale Every table: tenant_id UUID NOT NULL with composite indexes (tenant_id, ...) tenants(id UUID PK, plan VARCHAR, created_at, max_users INT) Row-Level Security: CREATE POLICY ... USING (tenant_id = current_setting('app.tenant')::UUID) Application: SET LOCAL app.tenant = ? on every connection checkout Partitioning: PARTITION BY HASH (tenant_id) for large tables Se

rooms(id, number, type, price_per_night) bookings(id, room_id FK, guest_id FK, stay_period DATERANGE NOT NULL, total_price, status) EXCLUDE constraint: EXCLUDE USING GIST (room_id WITH =, stay_period WITH &&) WHERE status != 'CANCELLED' GiST index automatically created by EXCLUDE constraint Partial index on active bookings: WHERE status IN ('CONFIRMED', 'PENDING') The EXCLUDE constraint prevents o

users(id BIGSERIAL PK, username UNIQUE, email UNIQUE, created_at) posts(id BIGSERIAL, user_id FK, content TEXT, media_urls JSONB, created_at) — partition by month follows(follower_id FK, following_id FK, created_at, PRIMARY KEY(follower_id, following_id)) likes(user_id FK, post_id FK, created_at, PRIMARY KEY(user_id, post_id)) — no duplicates comments(id, post_id FK, user_id FK, content, parent_id

sensors(id, name, location, type, created_at) readings(sensor_id FK, recorded_at TIMESTAMPTZ NOT NULL, value DECIMAL(10,4), quality INT) Partition by RANGE(recorded_at) — daily or weekly partitions BRIN index on recorded_at (excellent for time-series: tiny index, chronologically ordered data) B-Tree index on (sensor_id, recorded_at DESC) for per-sensor queries TimescaleDB extension: hypertables wi

accounts(id, user_id FK, currency CHAR(3), balance DECIMAL(18,4), version INT) — @Version for optimistic locking transactions(id UUID PK, from_account_id FK, to_account_id FK, amount DECIMAL(18,4), currency, exchange_rate, status, created_at, idempotency_key UUID UNIQUE) transaction_entries(id, transaction_id FK, account_id FK, debit_amount, credit_amount, created_at) — double-entry bookkeeping au

categories(id, name, parent_id FK, attribute_schema JSONB) — defines valid attributes per category products(id, name, sku UNIQUE, price DECIMAL, category_id FK, status, created_at) product_attributes(product_id FK, attributes JSONB, PRIMARY KEY(product_id)) — JSONB for flexibility GIN index: CREATE INDEX ON product_attributes USING GIN(attributes) Query: WHERE attributes @> '{"color": "red", "size

patients(id UUID PK, name_encrypted BYTEA, ssn_encrypted BYTEA, dob DATE, created_at) — encrypt PII at application level doctors(id, license_number UNIQUE, specialty, department_id FK) appointments(id, patient_id FK, doctor_id FK, scheduled_at TIMESTAMPTZ, status, notes_encrypted BYTEA) audit_log: every SELECT/INSERT/UPDATE/DELETE logged with user_id, patient_id, timestamp, action Row-Level Securi

urls(id BIGSERIAL, short_code CHAR(8) UNIQUE, original_url TEXT, user_id FK, created_at, expires_at NULLABLE, is_active BOOLEAN) clicks(id BIGSERIAL, url_id FK, clicked_at TIMESTAMPTZ, ip_address INET, user_agent TEXT, referrer TEXT) Index: CREATE UNIQUE INDEX ON urls(short_code) — primary lookup; must be B-Tree for fast equality Partitioning: clicks PARTITION BY RANGE(clicked_at) — high-volume wr

documents(id, slug UNIQUE, title, status, created_by FK, created_at, updated_at) document_versions(id BIGSERIAL, document_id FK, version_number INT, content TEXT, created_by FK, created_at, PRIMARY KEY(id)) UNIQUE(document_id, version_number) — no duplicate versions per document published_versions(document_id FK PRIMARY KEY, version_id FK UNIQUE) — which version is live tags(id, name UNIQUE), docu

users(id UUID, role CHECK IN ('rider','driver'), name, phone UNIQUE, email UNIQUE) vehicles(id, driver_id FK UNIQUE, plate UNIQUE, model, status) trips(id UUID PK, rider_id FK, driver_id FK NULLABLE, pickup_location POINT, dropoff_location POINT, status, fare DECIMAL, started_at, completed_at) trip_locations(trip_id FK, recorded_at TIMESTAMPTZ, location POINT, speed DECIMAL, PRIMARY KEY(trip_id, r

warehouses(id, name, location, capacity) products(id, sku UNIQUE, name, category, weight_kg DECIMAL) inventory(warehouse_id FK, product_id FK, quantity INT CHECK(quantity >= 0), reserved_qty INT DEFAULT 0, PRIMARY KEY(warehouse_id, product_id)) inventory_transactions(id BIGSERIAL, warehouse_id FK, product_id FK, quantity INT, txn_type VARCHAR, reference_id, created_at, created_by) EXCLUDE or CHECK

users(id, role CHECK IN ('student','instructor','admin'), email UNIQUE, name) courses(id, title, instructor_id FK, status, price DECIMAL, created_at) lessons(id, course_id FK, title, content, order_position INT, duration_seconds INT, UNIQUE(course_id, order_position)) enrollments(student_id FK, course_id FK, enrolled_at, completed_at NULLABLE, progress_pct DECIMAL(5,2), PRIMARY KEY(student_id, cou

customers_history(id BIGSERIAL, customer_id INT, name, email, address, valid_from DATE, valid_to DATE NULLABLE, is_current BOOLEAN) On update: UPDATE old row: valid_to = TODAY, is_current = FALSE; INSERT new row: valid_from = TODAY, valid_to = NULL, is_current = TRUE Partial index on current records: CREATE INDEX ON customers_history(customer_id) WHERE is_current = TRUE Query current: WHERE is_cur

events(id UUID PK, stream_id VARCHAR, event_type VARCHAR, payload JSONB, created_at TIMESTAMPTZ DEFAULT NOW(), global_seq BIGSERIAL UNIQUE) stream_checkpoints(stream_id VARCHAR PK, consumer_group VARCHAR, last_global_seq BIGINT) -- track consumer position dead_letter_queue(id UUID, original_event_id UUID FK, error_message TEXT, retry_count INT, last_retried_at TIMESTAMPTZ) Partitioning: events by

A simple view stores only the query text — every access re-runs the underlying query against current data, so it's always fresh but pays the full query cost every time. A materialized view physically stores the query's result set on disk like a table — fast to read, but only as fresh as its last REFRESH.

A plain REFRESH truncates the existing storage and reruns the defining query from scratch, taking an ACCESS EXCLUSIVE lock for the duration — any query touching the view blocks until it finishes. REFRESH MATERIALIZED VIEW CONCURRENTLY avoids this by computing the new result into a temporary copy and diffing it in, but it requires a unique index on the materialized view first.

Only after it's populated and refreshed — creating it runs the query once to materialize the result, and subsequent reads hit the stored rows directly instead of recomputing. But it does nothing to speed up the underlying query itself; it only avoids re-running that query on every read, at the cost of the data being as stale as the last refresh.

UNION combines both result sets and removes duplicate rows, requiring a sort or hash pass over the entire combined output. UNION ALL combines them with no deduplication at all, which is strictly cheaper. Default to UNION ALL unless you specifically know duplicates can occur and need to be removed.

It returns rows present in A but not in B. Order matters because EXCEPT is not symmetric — B EXCEPT A answers a completely different question (rows in B but not in A), so swapping the operands is a silent logic bug, not something that throws an error.

Both queries must return the same number of columns, in a compatible order and type — SQL matches columns positionally, not by name. The final result's column names come from the first query alone; the second query's aliases are ignored entirely.

Functions execute as part of the calling statement's transaction — they inherit it and cannot start or end one. Procedures are invoked via CALL as their own top-level statement, and are explicitly allowed to issue COMMIT/ROLLBACK inside their own body, useful for long-running batch work that needs incremental commits.

nextval() uses a lightweight increment operation that's never rolled back, even if the surrounding transaction is — a transaction that calls nextval() and then rolls back loses that value forever, since guaranteeing no gaps would require blocking every other concurrent nextval() call until that transaction resolves. Uniqueness under concurrency and gaplessness are fundamentally in tension.

A BEFORE trigger runs before the write happens and its return value actually replaces the row that gets written (or NULL cancels the write entirely) — it can inspect and modify the row. An AFTER trigger runs once the write has already happened, typically for side effects like audit logging, and its return value is ignored since the write can no longer be changed.

NULL represents 'unknown,' not a comparable value — NULL = NULL evaluates to NULL (unknown), not TRUE, because SQL uses three-valued logic (TRUE/FALSE/UNKNOWN) and only rows evaluating to exactly TRUE are kept. The correct form is WHERE column IS NULL, which checks for the unknown state directly rather than comparing against it.

NUMERIC stores digits directly in an exact, arbitrary-precision base-10 representation. REAL/DOUBLE PRECISION store values in IEEE 754 binary floating point, which cannot represent most decimal fractions exactly — 0.10 is actually stored as a close binary approximation. For money, these tiny, silent rounding errors compound across enough transactions into a real, visible discrepancy.

The logical order is FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY, then LIMIT. Since WHERE is evaluated before SELECT has produced any aliases, a SELECT alias doesn't exist yet at the point WHERE runs — it's why the same alias works fine in ORDER BY, which runs after SELECT.