TheShiraverseSELECT * TheShiraverseCurriculumPracticeKahaniFAQGet StartedPlaygroundNukteTheShiraverse ↗
Practice › Topic 01

Intro to SQL: practice questions

400 questions in four levels, all on RetailMart, the practice database of this course. Write every query yourself, get it wrong, read the error, fix it. That is how it sticks.

Open the SQL PlaygroundPut RetailMart on your laptop
Answers are coming soon. Post your query in the WhatsApp Community or Discord and we will check it together.

Core syntax, applied directly

DATA, SQL & ROLES

  1. Q1Your friend asks "what's the difference between data and information?" - give a one-line answer with an example.
  2. Q2A non-technical hiring manager asks "what is SQL?" - describe it in 30 seconds.
  3. Q3Explain why Excel is not enough once a company crosses ~1 million rows.
  4. Q4Why is SQL called a "declarative" language? Contrast with imperative code.
  5. Q5A new joiner asks: "do data engineers and data analysts use the same SQL?" - answer.
  6. Q6Name three job roles where SQL is a daily-use skill, and what each uses it for.
  7. Q7In an interview, you're asked: "is SQL still relevant in the AI era?" - argue yes.
  8. Q8Your manager says "we use NoSQL, why should I care about SQL?" - give one counter-argument.
  9. Q9Explain in plain English what a "query" is.
  10. Q10Why do almost all BI tools (Power BI, Tableau, Looker, Metabase) speak SQL under the hood?
  11. Q11Your team lead asks why we structure data into ROWS and COLUMNS instead of free text. Explain.
  12. Q12What does it mean when someone says "SQL is the lingua franca of data"?
  13. Q13A friend is comparing Python pandas vs SQL - name one strength of each.
  14. Q14In a hiring panel, you're asked: "rank these by importance for a data analyst - Excel, SQL, Python, BI tool." Defend your order.
  15. Q15Why does the same SQL query usually run faster on a database than the equivalent loop in Python?
  16. Q16Explain to a junior - what's the difference between "structured" and "unstructured" data, with one example of each?
  17. Q17Your CTO says "every analyst should learn SQL." Give two reasons that justify this rule.
  18. Q18A bootcamp peer says "I'll just use ChatGPT to write SQL" - push back with one strong argument.
  19. Q19Name 4 industries where SQL is core to the daily workflow.
  20. Q20Explain how SQL skills transfer across MySQL, PostgreSQL, BigQuery, Snowflake - what stays constant?
  21. Q21A student asks: "is SQL hard?" - answer honestly, naming the easy and the hard parts.
  22. Q22Why do data scientists who "only care about ML" still need to know SQL?
  23. Q23Your friend wonders why companies don't just dump everything into one giant Excel file. List two real problems with that.
  24. Q24Explain what "data engineering" is in one sentence - and where SQL fits in that role.
  25. Q25Name 5 things that are EASY in SQL but PAINFUL in Excel.

DBMS vs RDBMS & DATABASE TYPES

  1. Q26Your interviewer asks: "what does DBMS stand for, and what does it actually DO?" Answer in 2 sentences.
  2. Q27Explain the R in RDBMS - what does "relational" mean?
  3. Q28Give 3 examples of well-known relational databases used by Indian companies.
  4. Q29Give 3 examples of well-known NON-relational (NoSQL) databases.
  5. Q30A teammate confuses "database" and "DBMS" - explain the difference.
  6. Q31Why does an RDBMS enforce a schema before you can insert data?
  7. Q32Name two scenarios where a NoSQL database is a BETTER choice than an RDBMS.
  8. Q33What does "ACID" stand for, and why does it matter for banking systems?
  9. Q34Your CTO asks "why are we paying for Oracle instead of using free PostgreSQL?" - give one valid reason a big company might choose Oracle.
  10. Q35Explain "concurrency" - why an RDBMS must handle many users writing at once.
  11. Q36What's the difference between a "primary database" (OLTP) and a "data warehouse" (OLAP)?
  12. Q37A junior asks: "why don't we use MongoDB for accounting?" - give one solid reason.
  13. Q38Name 3 cloud-managed RDBMS services (Indian / global).
  14. Q39In an interview, you're asked: "what's the difference between SQL and MySQL?" Answer.
  15. Q40Why is PostgreSQL favored for analytics + data science over MySQL? Name two reasons.
  16. Q41Your team is choosing between SQLite and PostgreSQL for a new app. Pick one and justify in 2 sentences.
  17. Q42Explain "client-server" architecture in databases.
  18. Q43Why can multiple users connect to the same RDBMS at the same time without corrupting data?
  19. Q44Your friend opens an Excel file and asks "is this a database?" - answer with the distinction.
  20. Q45Name 3 ways an RDBMS protects against data loss.
  21. Q46What is "replication" in databases and why do banks use it?
  22. Q47Explain "horizontal scaling" vs "vertical scaling" in your own words.
  23. Q48Why are columnar databases (Snowflake, BigQuery, Redshift) preferred for analytics?
  24. Q49Explain in 1 sentence what a "key-value store" is.
  25. Q50Why are graph databases (Neo4j) useful for social-network and fraud-detection problems?

SQL COMPONENTS - DDL/DML/DCL/TCL

  1. Q51Expand each acronym: DDL, DML, DCL, TCL - and one sentence on what each is FOR.
  2. Q52Classify: CREATE TABLE - DDL, DML, DCL, or TCL?
  3. Q53Classify: INSERT INTO - DDL, DML, DCL, or TCL?
  4. Q54Classify: UPDATE - DDL, DML, DCL, or TCL?
  5. Q55Classify: DELETE - DDL, DML, DCL, or TCL?
  6. Q56Classify: ALTER TABLE - DDL, DML, DCL, or TCL?
  7. Q57Classify: DROP TABLE - DDL, DML, DCL, or TCL?
  8. Q58Classify: TRUNCATE TABLE - DDL, DML, DCL, or TCL?
  9. Q59Classify: GRANT - DDL, DML, DCL, or TCL?
  10. Q60Classify: REVOKE - DDL, DML, DCL, or TCL?
  11. Q61Classify: COMMIT - DDL, DML, DCL, or TCL?
  12. Q62Classify: ROLLBACK - DDL, DML, DCL, or TCL?
  13. Q63Classify: SAVEPOINT - DDL, DML, DCL, or TCL?
  14. Q64Classify: SELECT - DML, DCL, or something else?
  15. Q65Your teammate runs DROP TABLE in production by mistake. Can it be rolled back? Why or why not?
  16. Q66Why is TRUNCATE faster than DELETE on a huge table - but riskier?
  17. Q67What's the difference between DROP TABLE and DELETE FROM TABLE?
  18. Q68In a banking system, why does a money transfer use BEGIN, COMMIT, ROLLBACK?
  19. Q69A junior asks: "do I need to commit after every SELECT?" - answer.
  20. Q70Explain why GRANT and REVOKE exist - what real-world problem do they solve?
  21. Q71Which SQL command would you use to give the analytics team read-only access to a table?
  22. Q72Which SQL command would you use to add a new column to an existing table?
  23. Q73Which SQL command would you use to permanently remove a customer record?
  24. Q74Which SQL command would you use to permanently destroy a whole table including its structure?
  25. Q75Explain the difference between DELETE and TRUNCATE in two sentences.

DATABASE / SCHEMA / TABLE & RETAILMART

  1. Q76Define a "database" in your own words.
  2. Q77Define a "schema" in your own words - and how it differs from a database.
  3. Q78Define a "table" - what is the smallest meaningful unit of data inside it?
  4. Q79Define a "row" and a "column" with a one-line example each.
  5. Q80Your manager says "RetailMart has 16 schemas." What does that mean in practical terms?
  6. Q81Why does RetailMart split data into schemas like sales, customers, products, hr - instead of dumping everything into one schema?
  7. Q82Give two reasons a company would put customer data and HR data in SEPARATE schemas.
  8. Q83Name 5 RetailMart schemas you remember from class.
  9. Q84Why are dimension tables (dim_brand, dim_region, dim_category) separated from fact tables (orders, order_items)?
  10. Q85Explain the difference between a fact table and a dimension table in plain English.
  11. Q86What is a "primary key" - explain by pointing at any RetailMart table.
  12. Q87What is a "foreign key" - explain how sales.orders.cust_id is one.
  13. Q88Explain what "schema-qualified name" means - and why we write customers.customers, not just customers.
  14. Q89Your script breaks because you wrote SELECT * FROM orders. What's missing?
  15. Q90A teammate says "PostgreSQL is case-sensitive". When does that matter for SQL queries?
  16. Q91Why do databases use NULL instead of empty string for missing values?
  17. Q92RetailMart has 55 tables. Name 5 things you can do with that many tables that you can't do in one Excel sheet.
  18. Q93The CTO asks "how many distinct schemas does RetailMart V3 have?" - answer the number and name 5 of them.
  19. Q94Explain in one sentence what an "index" is - pretend you're teaching a class 10 student.
  20. Q95A junior asks "what's the difference between a database and a table?" - answer.
  21. Q96Explain why a query like SELECT * FROM customers might be slow on a table with 50 million rows.
  22. Q97Name 2 reasons to use PostgreSQL 18 over PostgreSQL 12 for this course.
  23. Q98What does "RetailMart V3" mean - what's the difference from V1 or V2?
  24. Q99Your teammate asks "is pgAdmin the database?" - explain what pgAdmin actually is.
  25. Q100By end of class today, what THREE things must you be able to explain about RetailMart to pass a quick verbal check?

Combined ideas, multi-step thinking

SQL'S PLACE IN THE STACK - DEEPER

  1. Q1Compare SQL vs pandas for analyzing a 100 GB dataset - which wins and why? Give 2 reasons per side.
  2. Q2Your manager wants real-time dashboards. Argue for SQL on a database vs Python loading CSVs.
  3. Q3When would you choose a graph database OVER SQL - give a concrete RetailMart-flavoured example.
  4. Q4Defend the statement: "SQL skills are MORE valuable in the LLM era." Three concrete arguments.
  5. Q5A startup founder says "we don't need SQL; MongoDB + ChatGPT can handle everything." Push back with 3 specific gaps.
  6. Q6Explain to a non-technical CEO why analysts shouldn't share Excel files via email. Use the lens of correctness, audit, scale.
  7. Q7Compare a Data Analyst's SQL work to a Data Engineer's SQL work - where do they overlap, where do they diverge?
  8. Q8In an interview you're asked: "tell me about a time SQL beat a Python solution at your job." Make up a plausible scenario.
  9. Q9Walk through what happens when you click "Send" on an order at amazon.in - how many SQL queries (roughly) and why?
  10. Q10Why do some teams prefer DBT over hand-written SQL pipelines - what specific problem does it solve?
  11. Q11Why does the same SQL query give different EXECUTION PLANS on PostgreSQL vs MySQL vs SQL Server?
  12. Q12Compare "Excel for analysis" vs "SQL for analysis" using the four lenses: scale, correctness, repeatability, audit.
  13. Q13A junior asks: "why do interviewers care about SQL when AI can write it?" - three real reasons.
  14. Q14Defend why a data scientist who "only does ML" still must know SQL - three concrete examples from a typical workflow.
  15. Q15List five industries where SQL knowledge translates directly into salary uplift - with one role per industry.
  16. Q16Explain the difference between "data" and "information" using a RetailMart example.
  17. Q17A business stakeholder asks: "give me yesterday's revenue." Walk through what an analyst's SQL workflow looks like end-to-end.
  18. Q18Why is it harder to scale a TEAM of analysts on Excel than on SQL?
  19. Q19Compare SQL declarative style vs Python imperative style with a "find top 10 customers" example.
  20. Q20A teammate proposes storing everything in TEXT columns because "we can parse later." Counter-argue with three problems.
  21. Q21Trace the path of a customer's purchase from app click -> database -> analyst dashboard. Where does SQL appear?
  22. Q22Explain what a "data swamp" is and how disciplined SQL design prevents it.
  23. Q23Why do most SaaS startups eventually consolidate to PostgreSQL even if they started on MongoDB?
  24. Q24Define "OLTP" and "OLAP" - and where in RetailMart V3 are queries of each type likely to run?
  25. Q25The CTO asks: "if we hired ONE engineer who knows EITHER Python OR SQL deeply, which is more valuable for our analyst team?" Defend your answer.

DBMS TRADEOFFS & DATABASE TYPES - DEEPER

  1. Q26Compare PostgreSQL vs MySQL for a fintech startup with strict accounting needs. Pick one and defend it.
  2. Q27When would you choose SQLite over PostgreSQL? Two concrete cases.
  3. Q28Why is Snowflake (columnar warehouse) chosen for analytics even though PostgreSQL also works?
  4. Q29Compare an OLTP DB (handles online orders) vs an OLAP warehouse (handles BI reports). Give two architectural differences.
  5. Q30A startup uses BigQuery for analytics + PostgreSQL for production. Explain why both exist.
  6. Q31Why do banks insist on ACID - what specifically goes wrong if even one of the 4 properties is violated?
  7. Q32Compare "primary database with read replicas" vs "shared-nothing distributed DB" - when does each win?
  8. Q33Argue why Redis is NOT a replacement for PostgreSQL even though Redis is faster.
  9. Q34Explain "eventual consistency" and why most analyst tools can't tolerate it well.
  10. Q35RetailMart V3 has 16 schemas. What if all 55 tables were dumped in one 'public' schema - list 4 things that would get worse.
  11. Q36Why is PostgreSQL preferred over MS SQL Server for an open-source-friendly startup? Three reasons.
  12. Q37Compare row-oriented vs columnar storage with a "monthly revenue per category" query example.
  13. Q38Why does PostgreSQL allow MULTIPLE NULLs in a UNIQUE column but only ONE PRIMARY KEY?
  14. Q39Why do some DBs (Oracle, SQL Server) cost millions while PostgreSQL is free - what do you actually pay for?
  15. Q40Compare DBaaS (AWS RDS) vs self-hosted PostgreSQL on EC2 - three tradeoffs.
  16. Q41Why is "horizontal scaling" hard for relational databases compared to NoSQL ones?
  17. Q42Explain in plain English what a "database transaction" is using a Paytm UPI transfer as the example.
  18. Q43Argue: "All NoSQL adopters end up needing SQL eventually." Defend with two concrete startup case-types.
  19. Q44Why do most analytics SQL queries run on a REPLICA of the OLTP DB instead of the OLTP DB itself?
  20. Q45What is a "database view" - and why is it a useful primitive even though you could just paste the SELECT?
  21. Q46Explain what "MVCC" means in PostgreSQL - a one-paragraph plain-English answer.
  22. Q47Compare "logical replication" vs "physical replication" - when would you pick each?
  23. Q48Why does almost every modern web app use a connection POOL (pgbouncer, RDS Proxy) instead of opening direct DB connections?
  24. Q49A startup chooses NoSQL because "schemas slow them down." Six months later they regret it - what's the typical pain point?
  25. Q50Why do some teams adopt "schema migrations" as a discipline even on small DBs?

SQL DIALECT & COMPONENT TRADEOFFS

  1. Q51Classify and EXPLAIN: which is more dangerous in production - DROP TABLE or TRUNCATE TABLE - and why?
  2. Q52A junior runs DELETE FROM orders without WHERE. Walk through what could/should have stopped them.
  3. Q53Defend the design choice that ALTER TABLE locks the table in older PostgreSQL versions - what tradeoff was being made?
  4. Q54Why is GRANT/REVOKE at the SCHEMA level often more useful than at the TABLE level?
  5. Q55Compare COMMIT and ROLLBACK with an INSERT example - what does each do at the byte level?
  6. Q56Why do some teams BAN truncate in production scripts entirely?
  7. Q57Compare SQL standard ANSI types vs PostgreSQL extensions (TEXT, JSONB, UUID, ARRAY). Which is portable, which is power?
  8. Q58Explain why TRUNCATE doesn't fire row-level triggers but DELETE does.
  9. Q59A team's INSERT is suddenly slow. List 5 things that could be the cause.
  10. Q60Compare auto-commit mode vs explicit BEGIN/COMMIT - when does each surprise junior developers?
  11. Q61Why is INSERT ... ON CONFLICT (upsert) more useful than try-catch-update flow in app code?
  12. Q62Walk through what happens if you forget the WHERE on an UPDATE in a 5-million-row table.
  13. Q63Compare "INSERT INTO ... SELECT" vs "COPY FROM file" for loading 1 million rows - when does each win?
  14. Q64Why is "GRANT ALL TO PUBLIC" considered a security anti-pattern?
  15. Q65Explain what SAVEPOINT does inside a transaction. Give a use case.
  16. Q66Compare implicit (auto-commit) vs explicit transactions. Which is safer for analysts running ad-hoc queries?
  17. Q67Why do schema-prefixed names (sales.orders) become essential as a company's database grows?
  18. Q68A teammate insists on storing dates as VARCHAR. Convince them to use DATE/TIMESTAMP with 3 arguments.
  19. Q69Defend why PostgreSQL allows quoted identifiers like "Customer" - and why most teams ban them anyway.
  20. Q70Explain in plain English why a query that worked yesterday "suddenly" fails today - list 5 plausible causes.
  21. Q71Compare CREATE TABLE AS SELECT vs CREATE TABLE then INSERT - when does each shine?
  22. Q72Why is using a NUMERIC type more "correct" than FLOAT for money, even though FLOAT is faster?
  23. Q73Explain "column ordering" in CREATE TABLE - does the order matter? Defend your answer.
  24. Q74Compare CHECK constraints vs application-side validation. Where should the rule live?
  25. Q75Why do most production DBs add CREATED_AT / UPDATED_AT columns to almost every table?

RETAILMART V3 LEVEL-2

  1. Q76Defend the choice to split sales.orders (fact) from sales.order_items (fact detail) into two tables.
  2. Q77Why is products.products kept separate from core.dim_brand AND core.dim_category? Defend this 3-table model.
  3. Q78The RetailMart designer says "stores.employees + payroll.pay_slips is a deliberate split." What real problem does the split solve?
  4. Q79Why is web_events.page_views in its OWN schema (not customers or sales)? Defend the design.
  5. Q80RetailMart has both sales.payments AND finance.payments - why two tables for "payments"?
  6. Q81customers.customers has a 'tier' column AND there's also loyalty.tiers + loyalty.members. Why both?
  7. Q82Why does customers.addresses have an is_default boolean - what design problem does it solve?
  8. Q83The hr.attendance table has check_in AND check_out as separate columns (not duration). Defend why.
  9. Q84Why is core.dim_date pre-populated as a calendar dimension instead of just using EXTRACT()?
  10. Q85The audit.application_logs table has a 'level' column - argue why this is better than a separate 'severity_id' FK.
  11. Q86Compare denormalized sales.orders.gross_total vs computing it on the fly from order_items. Why does RetailMart keep both?
  12. Q87Why does call_center.transcripts store transcript_text as TEXT - what tradeoff vs JSONB?
  13. Q88Why is supply_chain.shipments a separate schema from sales.shipments? They have similar names - explain the design intent.
  14. Q89Defend the use of TIMESTAMP without timezone in some V3 tables vs TIMESTAMPTZ in others.
  15. Q90Why are dim_* tables (dim_brand, dim_region, dim_date) in 'core' but customers/products/sales aren't?
  16. Q91The PK of supply_chain.inventory_snapshots is (warehouse_id, product_id, snapshot_date) - explain this composite key design.
  17. Q92Why does products.products NOT have a "category" column directly - what's the design tradeoff?
  18. Q93Compare the size of customers.customers (~50K rows) vs sales.orders (~150K rows) - why are they sized this way?
  19. Q94Why does loyalty.members have customer_id AS its primary key, not a separate member_id?
  20. Q95The CHRO asks: "should we add a dept_name VARCHAR to stores.employees?" Defend yes or no.
  21. Q96RetailMart has 16 schemas. Explain why a single "permissions" table doesn't exist - instead, schemas ARE the permission boundary.
  22. Q97Why does audit.api_requests have request_id as TEXT (not INT)?
  23. Q98The data model has multiple "id" columns: cust_id (sales.orders) vs customer_id (customers.customers). Defend (or critique) this inconsistency.
  24. Q99Why is core.dim_category just (category_id, category_name) - only 2 columns?
  25. Q100By the end of your first month, what 5 RetailMart facts should an analyst know cold? Name them.

Interview grade, edge cases

POSTGRESQL INTERNALS DEEP-DIVE

  1. Q1Explain MVCC in PostgreSQL. Why doesn't UPDATE overwrite a row in place?
  2. Q2What are xmin and xmax on every row - and why do they matter for visibility?
  3. Q3Walk through what VACUUM does and why dead tuples accumulate without it.
  4. Q4Difference between VACUUM, VACUUM FULL, and VACUUM ANALYZE - when to use each?
  5. Q5What is the Write-Ahead Log (WAL) and why is it the foundation of durability?
  6. Q6Explain a CHECKPOINT - what's flushed and when does it trigger?
  7. Q7What is the shared_buffers cache vs OS page cache - why does PostgreSQL keep both?
  8. Q8Describe how a backend process handles a single query end-to-end (parse -> plan -> execute).
  9. Q9What does the planner_cost_constant family of GUCs control (seq_page_cost, random_page_cost)?
  10. Q10Explain the difference between a logical replication slot and a physical replication slot.
  11. Q11What is "transaction wraparound" and why is it catastrophic if VACUUM falls behind?
  12. Q12How does HOT (Heap-Only Tuples) update optimization save work?
  13. Q13What does the visibility map track - and how does it help index-only scans?
  14. Q14Explain the role of the autovacuum daemon and when DBAs disable it on hot tables.
  15. Q15What is the planner's "genetic query optimization" and when does it kick in?
  16. Q16Why does ANALYZE matter for query plans - what statistics does it gather?
  17. Q17Explain how pg_stat_statements helps find slow queries in production.
  18. Q18What's the difference between a relfilenode and a relation OID - and when do they diverge?
  19. Q19Describe the role of the wal_level GUC - what does each setting enable?
  20. Q20What is logical decoding - how is it different from streaming replication?
  21. Q21Explain how synchronous_commit interacts with WAL durability guarantees.
  22. Q22What is the role of the postmaster process vs backend processes?
  23. Q23How does PostgreSQL detect and resolve deadlocks (compared to MySQL)?
  24. Q24What is a "bloated" table or index - how do you detect and fix it?
  25. Q25Walk through how a SELECT sees a row inserted by an uncommitted concurrent transaction.

ISOLATION, LOCKING & CONCURRENCY

  1. Q26Define dirty read, non-repeatable read, phantom read - with a 2-transaction example each.
  2. Q27Why is READ UNCOMMITTED treated as READ COMMITTED in PostgreSQL?
  3. Q28Demonstrate a non-repeatable read at READ COMMITTED - and show why REPEATABLE READ fixes it.
  4. Q29Explain why PostgreSQL's REPEATABLE READ is actually "snapshot isolation" - and how it differs from SQL standard.
  5. Q30What is a serialization failure under SERIALIZABLE - give a real example.
  6. Q31Explain Serializable Snapshot Isolation (SSI) and why it's more expensive than REPEATABLE READ.
  7. Q32Walk through a write skew anomaly - show what isolation level prevents it.
  8. Q33What is a "lost update" - and how do SELECT FOR UPDATE and SERIALIZABLE each prevent it?
  9. Q34Difference between SELECT FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, and FOR KEY SHARE.
  10. Q35Explain row-level locks vs predicate locks - why does SSI need predicate locks?
  11. Q36What is a deadlock - show a 2-transaction example that deadlocks on row locks.
  12. Q37How does deadlock_timeout work - and why is the default 1 second?
  13. Q38Explain advisory locks (pg_advisory_lock) - when do you use them over row locks?
  14. Q39What is an "intent lock" and why does PostgreSQL not expose them at the SQL level?
  15. Q40Difference between session-level and transaction-level advisory locks.
  16. Q41What does LOCK TABLE ... IN ACCESS EXCLUSIVE MODE block - give 3 examples.
  17. Q42What is "lock escalation" - and why doesn't PostgreSQL do it (unlike SQL Server)?
  18. Q43Explain "tuple-level lock contention" on a hot row - and how to design around it.
  19. Q44What is a "queue table anti-pattern" - and why do row locks make it perform badly?
  20. Q45How does SKIP LOCKED help build a queue table without contention?
  21. Q46Explain why DDL takes ACCESS EXCLUSIVE - and how to do online schema changes safely.
  22. Q47What is the role of pg_locks and pg_stat_activity in debugging contention?
  23. Q48Walk through how SAVEPOINT interacts with subtransaction visibility.
  24. Q49What is "transaction ID exhaustion" risk in long-running transactions?
  25. Q50Why is two-phase commit (PREPARE TRANSACTION) rarely used outside distributed setups?

STORAGE, CATALOGS, METADATA

  1. Q51Walk through the PostgreSQL page layout (header + line pointers + tuples + special).
  2. Q52What does FILLFACTOR do - and when do you tune it down to 70% or 80%?
  3. Q53Explain TOAST - when does a value get TOASTed and what are the four storage modes?
  4. Q54Difference between EXTERNAL and EXTENDED storage modes on a TEXT column.
  5. Q55What is a "tuple header" and why does every row have at least 23 bytes of overhead?
  6. Q56Explain how COLUMN order affects on-disk size (the alignment padding trick).
  7. Q57What is the difference between pg_catalog and information_schema - and which is portable?
  8. Q58Why is pg_class.reltuples just an estimate - and what updates it?
  9. Q59Walk through pg_attribute - how is it different from information_schema.columns?
  10. Q60What is a "system column" (ctid, xmin, xmax, tableoid) - and when do you query them?
  11. Q61Explain how partitioning is stored - is a partitioned table itself a heap?
  12. Q62Difference between LIST, RANGE, and HASH partitioning - give a RetailMart example for each.
  13. Q63What is a "partition pruning" and how does the planner decide?
  14. Q64Why are global indexes not supported on partitioned tables - what's the workaround?
  15. Q65Explain TABLESPACE - when do you create one (and why most setups don't need them).
  16. Q66What is a foreign table (FDW) - give a real RetailMart use case for postgres_fdw.
  17. Q67Walk through how a unique constraint differs internally from a unique index.
  18. Q68What is an exclusion constraint - give an example (booking system overlap prevention).
  19. Q69Explain partial indexes - give 2 RetailMart cases where they save 80% of index size.
  20. Q70What is an expression index - when would you index lower(email) or date_trunc('day', ts)?
  21. Q71Difference between B-tree, Hash, GiST, GIN, BRIN, SP-GiST - pick the right one for 5 scenarios.
  22. Q72Why is BRIN ideal for time-series tables - and what's its tradeoff?
  23. Q73What does CLUSTER do - and why is it not maintained automatically?
  24. Q74Walk through how DROP TABLE and TRUNCATE differ in WAL volume and lock duration.
  25. Q75Explain what gets written to disk during CREATE INDEX CONCURRENTLY - and why it's slower.

PRODUCTION, HA, SCALING

  1. Q76Compare physical streaming replication vs logical replication - pick the right one for 3 scenarios.
  2. Q77What is a "hot standby" - what queries can run on it and what cannot?
  3. Q78Explain replica lag - what causes it and how do you monitor it?
  4. Q79What is synchronous_standby_names - and what's the durability tradeoff vs async?
  5. Q80Walk through how a failover happens - what tools (Patroni, repmgr) automate it?
  6. Q81What is connection pooling - why is pgBouncer essential at high concurrency?
  7. Q82Difference between session pooling, transaction pooling, statement pooling in pgBouncer.
  8. Q83Why do prepared statements break under transaction-pooling mode - and what's the fix?
  9. Q84What is a "thundering herd" on a connection pool - give a real RetailMart scenario.
  10. Q85Explain horizontal vs vertical scaling - when does sharding actually win?
  11. Q86Walk through the Citus extension's approach to distributed PostgreSQL.
  12. Q87What is the role of read replicas - and the read-after-write consistency trap.
  13. Q88Explain why ORDER BY id DESC LIMIT 1 on a huge table can be slow without the right index.
  14. Q89What is the "N+1 query" problem - give a RetailMart order-display example.
  15. Q90Walk through a backup strategy: pg_dump vs pg_basebackup vs WAL archiving - when each is right.
  16. Q91What is PITR (point-in-time recovery) - and what does it depend on?
  17. Q92Explain why upgrades use pg_upgrade vs pg_dump/restore - speed and downtime tradeoffs.
  18. Q93What is a "rolling upgrade" using logical replication - when is this needed?
  19. Q94Walk through a real production incident: long-running transaction caused 100GB of bloat. How to recover?
  20. Q95What is the "noisy neighbor" problem on a managed Postgres (RDS/Cloud SQL) - how do you detect?
  21. Q96Explain pg_repack and pg_squeeze - when is VACUUM FULL not enough?
  22. Q97What is "index bloat" - how do you measure it and when do you REINDEX CONCURRENTLY?
  23. Q98Walk through why autovacuum can fall behind on tables with very high churn - and what to tune.
  24. Q99Explain "row-level security" (RLS) - give a real multi-tenant RetailMart case.
  25. Q100Design question: RetailMart hits 1B orders/year. What architectural shifts do you make?

Production scenarios, optimisation

DISTRIBUTED, SHARDING, CONSENSUS

  1. Q1Design RetailMart for 100M monthly active users - what becomes the bottleneck first?
  2. Q2Walk through a sharding strategy for sales.orders - pros/cons of customer_id vs order_id vs date as shard key.
  3. Q3Explain Citus's distribution table types: distributed, reference, local - when each.
  4. Q4Why is "cross-shard JOIN" a planning nightmare - and how do you avoid it?
  5. Q5How do you maintain referential integrity across shards (or do you)?
  6. Q6Compare 2-phase commit vs Saga pattern for distributed transactions.
  7. Q7What is "eventual consistency" - give a RetailMart scenario where it's OK.
  8. Q8Explain the CAP theorem in the context of a multi-region Postgres deployment.
  9. Q9When does linearizability matter for sales.orders - checkouts, deductions, audit?
  10. Q10Walk through a "split-brain" scenario during failover - what prevents it?
  11. Q11Explain RAFT consensus - and how Patroni uses it for leader election.
  12. Q12Why is "quorum read/write" the foundation of distributed correctness?
  13. Q13Compare strong vs eventual consistency for a customer's "lifetime spend" counter.
  14. Q14Walk through how Aurora / Spanner / CockroachDB differ from vanilla Postgres replication.
  15. Q15Why is logical replication the right tool for "blue-green" upgrade - but not for HA?
  16. Q16Design a "hot replica routing" strategy: connection pool decides primary vs replica.
  17. Q17Compare statement-level vs read-your-writes consistency for a checkout flow.
  18. Q18What is "fence token" - and where does it appear in distributed locks?
  19. Q19Walk through a "thundering herd" caused by cache eviction in a 4-shard cluster.
  20. Q20Design a cross-region "global" customer table with regional sales.orders.
  21. Q21Compare ISO/SQL standards for distributed semantics vs Postgres-specific guarantees.
  22. Q22How does Vitess (MySQL) compare to Citus (Postgres) for OLTP scaling?
  23. Q23Walk through CRDT-based counters - when can they replace Postgres SUM aggregations?
  24. Q24What is "isolation downgrade" in distributed systems - give a real example.
  25. Q25Design a "global feature flag" service that serves 100k QPS - Postgres backend?

TIME-SERIES AT SCALE

  1. Q26Design ingestion for 1B page_views per day - what's the schema, partitioning, retention?
  2. Q27Walk through TimescaleDB vs vanilla Postgres partitioning - when each pays off.
  3. Q28Why is BRIN ideal for time-series, but useless after VACUUM FULL?
  4. Q29Design a "rolling window" retention policy: keep 90 days hot, 1 year warm, 7 years cold.
  5. Q30Walk through a continuous aggregate (materialized view refreshed incrementally).
  6. Q31Compare downsampling strategies: per-minute -> per-hour -> per-day rollups.
  7. Q32Design a "real-time analytics" pipeline: orders -> Kafka -> ClickHouse, with Postgres as ledger.
  8. Q33Why does ORDER BY ts DESC LIMIT 1 on a TB-scale table need a B-tree even if BRIN exists?
  9. Q34Design index strategy for "hot last 24h" vs "cold last 5y" queries.
  10. Q35Walk through autovacuum tuning for a 50M rows/day inserts table.
  11. Q36Explain why HOT updates are critical for telemetry tables.
  12. Q37Design partition pruning verification - automated test for SLA breach detection.
  13. Q38Walk through "cold partition" archival to S3 via foreign data wrapper.
  14. Q39Why is partition-wise JOIN important - when does PG12+ do it automatically?
  15. Q40Compare "fact table denormalized" vs "star schema" for analytic queries.
  16. Q41Design a "log compaction" job that deduplicates a high-churn event table.
  17. Q42Walk through "Lambda architecture" - batch + stream + serving layers.
  18. Q43Why is timezone-aware time-series tricky - design a single-tz solution.
  19. Q44Design a "time-bucket sliding" query for last 24h, last 7d, last 30d, served in <100ms.
  20. Q45Walk through "data tiering" - automated movement from hot SSD -> warm SSD -> cold S3.
  21. Q46Compare ORC vs Parquet vs Postgres for analytic time-series.
  22. Q47Why is "out-of-order ingest" hard to handle in partitioned time-series?
  23. Q48Design a "late-arriving event" merge into existing partitions.
  24. Q49Walk through "schema evolution" in append-only time-series tables.
  25. Q50Design a "tagged time-series" table for IoT-like events (metric_name, tags, value, ts).

MULTI-TENANT ISOLATION

  1. Q51Compare row-level vs schema-level vs database-level isolation for multi-tenant.
  2. Q52Walk through Postgres RLS (Row-Level Security) - show a complete tenant rule.
  3. Q53Why is "tenant_id column on every table" the standard pattern?
  4. Q54Design noisy-neighbor mitigation when one tenant runs 10x the queries of others.
  5. Q55Walk through per-tenant connection pool exhaustion - how to limit.
  6. Q56Compare statement_timeout vs idle_in_transaction_timeout per role.
  7. Q57Design a "tenant onboarding" flow that creates schema, seeds tables, sets RLS in <1s.
  8. Q58Walk through "tenant exit / GDPR delete" - cascading deletes across 50 tables.
  9. Q59Why is pg_dump --schema=tenant_001 a bad idea at scale?
  10. Q60Design a logical replication setup for cross-region tenant isolation.
  11. Q61Compare workspace isolation in BigQuery vs Postgres schemas.
  12. Q62Walk through "shared schema, separate database" tradeoffs.
  13. Q63Design "noisy tenant detection" - auto-throttle by query count + CPU.
  14. Q64Walk through pg_hint_plan for tenant-specific plan tuning.
  15. Q65Design a billing/usage accounting system based on pg_stat_statements.
  16. Q66Compare RLS vs application-side WHERE - security audit risk.
  17. Q67Walk through column-level access control with grants + views.
  18. Q68Design an "export tenant data" pipeline that hits replicas only.
  19. Q69Walk through "tenant tier sizing" - small/medium/large with different SLAs.
  20. Q70Design a "tenant compaction" job that merges small tenants into shared schema for cost.
  21. Q71Walk through encryption-at-rest options (TDE) - and column-level encryption (pgcrypto).
  22. Q72Design a "tenant audit log" that survives DROP DATABASE.
  23. Q73Walk through "data residency" requirements - design region-pinned tenants.
  24. Q74Compare AWS RDS multi-tenant patterns vs Aurora Serverless.
  25. Q75Design a "tenant migration" plan that moves a tenant from one region to another with zero downtime.

DISASTER, SCALE, COST

  1. Q76Design a backup strategy that supports 5min RPO + 30min RTO.
  2. Q77Walk through PITR drill - recover to "5 minutes before that bug shipped".
  3. Q78Why is "Schrodinger's backup" common - backups exist but recovery untested?
  4. Q79Design a "logical backup + WAL streaming" hybrid.
  5. Q80Walk through a real "100GB bloat from long transaction" recovery.
  6. Q81Compare pg_repack vs VACUUM FULL - production vs midnight tradeoffs.
  7. Q82Design a "schema migration" pipeline using pg_repack to avoid downtime.
  8. Q83Walk through "zero-downtime ADD COLUMN with default" pre-PG11 vs PG11+.
  9. Q84Design a "zero-downtime rename" of a hot column.
  10. Q85Walk through "zero-downtime FK addition" using NOT VALID + VALIDATE.
  11. Q86Walk through dropping a column on a billion-row table without locking.
  12. Q87Compare RDS / Aurora / Cloud SQL / Crunchy / Neon pricing tradeoffs.
  13. Q88Design a "cost-aware" indexing policy: bytes-per-query justified.
  14. Q89Walk through "ghost" indexes - created, never used, but still cost write amplification.
  15. Q90Design an "index audit" job that flags unused indexes weekly.
  16. Q91Walk through "WAL bloat" caused by replication slot held by dead subscriber.
  17. Q92Design "automatic failover with sanity check" - what conditions block promotion?
  18. Q93Walk through "data divergence after failover" - how do you reconcile?
  19. Q94Design an "audit trail" that survives data corruption.
  20. Q95Compare "synchronous_commit = local" vs "remote_apply" for write-heavy workloads.
  21. Q96Walk through a "wal_size_runaway" - when does WAL refuse to recycle?
  22. Q97Design a "checkpoint storm" mitigation strategy.
  23. Q98Walk through "max_wal_senders saturation" - how to detect and fix.
  24. Q99Compare Postgres 16 vs 17 vs 18 release-note highlights you'd care about as a DBA.
  25. Q100Design RetailMart's "next-3-year scale plan": today=10K orders/day, target=10M/day. Specific architectural milestones at 100K, 1M, 10M.