SQL Interview Questions

Short answers for common database interview questions. Answers describe PostgreSQL; MySQL (InnoDB) differences are noted where they matter. SQL snippets run on the sample database.

SQL basics

1. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping; HAVING filters groups after aggregation and can use aggregate functions.

SELECT dept_id, count(*) AS headcount
FROM employees
WHERE salary > 90000
GROUP BY dept_id
HAVING count(*) >= 2
ORDER BY dept_id;

 dept_id | headcount 
---------+-----------
       1 |         5
(1 row)

2. What is the logical order of execution of a SELECT?

FROM/JOIN, WHERE, GROUP BY, HAVING, window functions, SELECT, DISTINCT, ORDER BY, LIMIT. This is why a SELECT alias can be used in ORDER BY but not in WHERE.

3. What is the difference between DELETE, TRUNCATE, and DROP?

In PostgreSQL TRUNCATE is transactional. In MySQL it is DDL and commits implicitly.

BEGIN;
TRUNCATE order_items;
SELECT count(*) AS inside_tx FROM order_items;
ROLLBACK;
SELECT count(*) AS after_rollback FROM order_items;

 inside_tx 
-----------
         0
(1 row)

 after_rollback 
----------------
             13
(1 row)

4. What is the difference between UNION and UNION ALL?

UNION removes duplicate rows (requires a sort or hash); UNION ALL keeps all rows and is faster.

SELECT city FROM customers WHERE city = 'Mumbai'
UNION
SELECT city FROM customers WHERE city = 'Mumbai';

SELECT city FROM customers WHERE city = 'Mumbai'
UNION ALL
SELECT city FROM customers WHERE city = 'Mumbai';

  city  
--------
 Mumbai
(1 row)

  city  
--------
 Mumbai
 Mumbai
 Mumbai
 Mumbai
(4 rows)

5. What is NULL, and how does it behave in comparisons?

NULL means unknown. Any comparison with NULL (including NULL = NULL) yields NULL, which WHERE treats as not true. Use IS NULL, IS NOT NULL, or IS NOT DISTINCT FROM. See NULL handling.

6. What is the difference between COUNT(*), COUNT(col), and COUNT(DISTINCT col)?

COUNT(*) counts rows, COUNT(col) counts rows where col is not NULL, and COUNT(DISTINCT col) counts distinct non-NULL values.

SELECT count(*) AS all_rows, count(dept_id) AS with_dept, count(DISTINCT dept_id) AS distinct_depts
FROM employees;

 all_rows | with_dept | distinct_depts 
----------+-----------+----------------
       12 |        11 |              4
(1 row)

7. What is the difference between CHAR, VARCHAR, and TEXT?

CHAR(n) pads to a fixed length; VARCHAR(n) limits length; TEXT is unlimited. In PostgreSQL all three are stored the same way (varlena) with no performance difference, so text is the usual choice. Oracle treats an empty string as NULL; PostgreSQL and MySQL do not.

8. Why not use float for money?

Binary floating point cannot represent most decimal fractions exactly. Use numeric(p,s) (PostgreSQL) or DECIMAL (MySQL), or store integer minor units such as paise.

9. What is the difference between timestamp and timestamptz?

timestamptz stores an absolute instant (normalized to UTC) and converts to the session time zone on display. timestamp stores a wall-clock value with no zone. Use timestamptz for events. MySQL's TIMESTAMP is UTC-based; DATETIME has no zone.

10. What is the difference between RANK, DENSE_RANK, and ROW_NUMBER?

ROW_NUMBER gives unique sequential numbers; RANK gives ties the same rank and leaves gaps; DENSE_RANK gives ties the same rank with no gaps. See window functions.

11. What is the difference between a view and a materialized view?

A view stores a query and runs it each time. A materialized view stores the result and must be refreshed (REFRESH MATERIALIZED VIEW). MySQL has no native materialized views. See views and routines.

12. What is a CTE, and is it always materialized?

A CTE (WITH) names a subquery. Since PostgreSQL 12, non-recursive CTEs referenced once are inlined; MATERIALIZED and NOT MATERIALIZED force the behavior. Before 12 they always acted as optimization fences. See set operations and CTEs.

Joins and queries

13. What are the join types?

INNER (matches only), LEFT/RIGHT (all rows from one side), FULL (all rows from both), CROSS (Cartesian product), and self joins. MySQL has no FULL JOIN; emulate it with LEFT JOIN ... UNION ... RIGHT JOIN. See joins.

14. What is the difference between LEFT JOIN with a WHERE filter and with an ON filter?

A filter on the right table in WHERE removes the NULL-extended rows and turns the query into an inner join. The same filter in ON restricts which right rows match and keeps all left rows.

SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'placed'
ORDER BY c.id;

  name  | order_id 
--------+----------
 Aarav  |         
 Isha   |         
 Karan  |         
 Lata   |        7
 Manish |        9
 Nisha  |         
(6 rows)

15. How do you find rows in one table with no match in another?

Use an anti-join: LEFT JOIN ... WHERE right.key IS NULL, or NOT EXISTS.

SELECT c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

 name  
-------
 Nisha
(1 row)

16. What is the difference between IN and EXISTS?

EXISTS checks whether a correlated subquery returns any row and stops at the first. IN compares against a set of values. PostgreSQL usually plans both as semi-joins. NOT IN returns no rows if the subquery yields any NULL, so prefer NOT EXISTS. See subqueries and EXISTS.

17. What is a correlated subquery?

A subquery that references columns of the outer query, conceptually evaluated once per outer row. The optimizer often rewrites it into a join.

18. How do you delete duplicate rows?

Number duplicates with ROW_NUMBER() OVER (PARTITION BY key_columns ORDER BY id) and delete rows with number greater than 1, or delete using ctid/id comparisons with a self join. Then add a unique constraint to prevent recurrence.

19. How do you get the Nth highest salary?

Use DENSE_RANK() OVER (ORDER BY salary DESC) and filter on the rank, which handles ties. LIMIT 1 OFFSET N-1 on SELECT DISTINCT salary also works. More in classic interview problems.

20. What is the difference between GROUP BY and window functions?

GROUP BY collapses rows into one row per group. A window function keeps every row and adds a value computed over a set of related rows.

21. How does pagination work, and why is OFFSET slow?

OFFSET n makes the database produce and discard n rows, so cost grows with page depth. Keyset pagination filters on the last seen sort key and uses an index.

SELECT id, order_date
FROM orders
WHERE (order_date, id) > ('2024-06-15', 2)
ORDER BY order_date, id
LIMIT 3;

 id | order_date 
----+------------
  5 | 2024-06-20
  4 | 2024-07-01
  6 | 2024-07-05
(3 rows)

22. What is the N+1 query problem?

Running one query for a list and then one query per row for related data (common with ORMs). Fix it with a join, WHERE id = ANY(...)/IN batch loading, or the ORM's eager loading.

Schema design and normalization

23. What is a primary key versus a unique key?

PostgreSQL and MySQL allow multiple NULLs in a unique column; SQL Server allows only one.

24. What is a foreign key, and does it create an index?

A foreign key enforces that a value exists in the referenced table's key. PostgreSQL does not index the referencing column automatically; MySQL InnoDB does. Index it manually in PostgreSQL to speed joins and parent deletes.

25. What are the normal forms?

See normalization.

26. What is denormalization, and when is it used?

Deliberately storing redundant data (copied columns, precomputed aggregates) to avoid joins on read-heavy paths. The cost is extra write work and the risk of inconsistent copies.

27. Surrogate key or natural key?

A surrogate key (identity or UUID) never changes and is compact. A natural key (email, ISBN) carries meaning but can change. Common practice is a surrogate primary key plus a unique constraint on the natural key.

28. bigint identity or UUID as primary key?

bigint is 8 bytes and inserts in order. Random UUIDv4 is 16 bytes and inserts scatter across the index, causing more page splits and cache misses. Time-ordered UUIDs (v7) reduce this.

29. What constraints exist, and why use them?

PRIMARY KEY, UNIQUE, FOREIGN KEY, NOT NULL, CHECK, and exclusion constraints (PostgreSQL). They enforce integrity in the database regardless of which application writes. See data types and constraints.

30. What is the difference between one-to-many and many-to-many, and how is each modeled?

One-to-many: a foreign key on the "many" side. Many-to-many: a junction table with foreign keys to both sides, as order_items does for orders and products.

31. When is JSONB appropriate?

For sparse or variable attributes that are not joined or constrained heavily. Core, frequently filtered fields belong in columns. JSONB supports GIN indexes.

32. What is soft delete, and what are its drawbacks?

A deleted_at flag instead of removing the row. Every query must filter it, unique constraints need partial indexes (WHERE deleted_at IS NULL), and tables grow.

Indexes

33. How does a B-tree index work?

A balanced tree of sorted keys; lookups take O(log n) page reads, and leaf pages are linked for range scans. PostgreSQL's default index type is B-tree. See how indexes work.

34. What is the difference between clustered and non-clustered indexes?

A clustered index defines the physical order of the table rows (the table is the index). InnoDB always clusters on the primary key, and secondary indexes store the primary key value. PostgreSQL tables are heaps: every index is non-clustered, and CLUSTER reorders the table once without maintaining the order. SQL Server allows one clustered index per table.

35. Why is an index not used?

Common causes: a function or cast on the column, a leading wildcard in LIKE '%x', a type mismatch, low selectivity (the planner prefers a sequential scan), stale statistics, or a composite index whose leading column is not filtered. The planner may also be correct that a scan is cheaper.

CREATE TABLE users AS
SELECT g AS id, 'User' || g || '@example.com' AS email, g % 100 AS grp
FROM generate_series(1, 100000) g;
CREATE INDEX users_email_idx ON users (email);
ANALYZE users;

EXPLAIN (COSTS OFF) SELECT * FROM users WHERE lower(email) = 'user500@example.com';

CREATE INDEX users_lower_email_idx ON users (lower(email));
ANALYZE users;

EXPLAIN (COSTS OFF) SELECT * FROM users WHERE lower(email) = 'user500@example.com';

                       QUERY PLAN                       
--------------------------------------------------------
 Seq Scan on users
   Filter: (lower(email) = 'user500@example.com'::text)
(2 rows)

                         QUERY PLAN                         
------------------------------------------------------------
 Index Scan using users_lower_email_idx on users
   Index Cond: (lower(email) = 'user500@example.com'::text)
(2 rows)

An expression index matches the function call and is used.

36. What is a covering index?

An index that contains every column a query needs, so the query is answered without visiting the table (an index-only scan in PostgreSQL). INCLUDE adds non-key columns. PostgreSQL index-only scans also require the visibility map to be current, which VACUUM maintains.

CREATE INDEX users_grp_cover_idx ON users (grp) INCLUDE (id);
VACUUM ANALYZE users;

EXPLAIN (COSTS OFF) SELECT id FROM users WHERE grp = 7;

                     QUERY PLAN                     
----------------------------------------------------
 Index Only Scan using users_grp_cover_idx on users
   Index Cond: (grp = 7)
(2 rows)

37. How does column order in a composite index matter?

The index sorts by the first column, then the second within it. It serves filters on a leftmost prefix (a, or a and b) but not efficiently on b alone. Put equality columns first, then range or sort columns; high selectivity alone does not decide the order.

38. What is selectivity and cardinality?

Cardinality is the number of distinct values (or rows). Selectivity is the fraction of rows a predicate matches. Indexes help when selectivity is high (few rows match).

39. What is a partial index?

An index over a subset of rows defined by a WHERE clause, for example only active rows. It is smaller and cheaper to maintain. MySQL does not support partial indexes.

40. What other index types exist in PostgreSQL?

41. What is the cost of indexes?

Each index takes disk space and must be updated on every insert, delete, and non-HOT update, so write throughput drops. Create indexes for real query patterns and drop unused ones (pg_stat_user_indexes).

42. How do you create an index without blocking writes?

CREATE INDEX CONCURRENTLY in PostgreSQL (slower, cannot run inside a transaction block). MySQL InnoDB builds most indexes online by default.

Transactions and isolation

43. What is ACID?

See transactions and ACID.

44. What are the isolation anomalies?

Dirty read (reading uncommitted data), non-repeatable read (a row changes between two reads), phantom read (a new row matches a repeated query), lost update (two writers overwrite each other), and write skew (two transactions read overlapping data and write disjoint rows, breaking an invariant).

45. What are the isolation levels and what does each prevent?

SQL standard minimums:

PostgreSQL treats Read Uncommitted as Read Committed, and its Repeatable Read (snapshot isolation) also prevents phantoms but allows write skew. See isolation levels.

46. What is the default isolation level?

Read Committed in PostgreSQL, Oracle, and SQL Server; Repeatable Read in MySQL InnoDB.

47. How does PostgreSQL implement Serializable?

Serializable Snapshot Isolation (SSI): snapshot reads plus detection of dangerous read-write dependency cycles. A transaction that would cause an anomaly fails with a serialization error (SQLSTATE 40001) and must be retried.

48. What is a savepoint?

A named point inside a transaction to which you can roll back without aborting the whole transaction (SAVEPOINT s; ... ROLLBACK TO s;).

49. What happens if a statement fails inside a PostgreSQL transaction?

The transaction enters an aborted state and rejects further commands until ROLLBACK (or ROLLBACK TO a savepoint). MySQL keeps the transaction usable after a statement error.

50. What is autocommit?

Each statement runs in its own transaction and commits immediately unless an explicit BEGIN/START TRANSACTION is open.

51. Why are long transactions harmful?

They hold locks, keep old row versions from being cleaned up (bloat in PostgreSQL, long undo history in MySQL), and delay vacuum and replication apply.

52. What is two-phase commit?

A protocol for atomic commit across multiple resources: a prepare phase where each participant promises it can commit, then a commit phase. PostgreSQL exposes it as PREPARE TRANSACTION. It blocks if the coordinator fails, which is why distributed systems often use sagas instead.

Locking and concurrency

53. What is a deadlock, and how is it handled?

Two or more transactions each hold a lock the other needs. The database detects the cycle and aborts one transaction: PostgreSQL checks after deadlock_timeout (default 1 s) and raises SQLSTATE 40P01; InnoDB detects immediately and rolls back the smaller transaction. The application retries. See locking and deadlocks.

54. How do you prevent deadlocks?

Acquire locks in a consistent order, keep transactions short, lock the rows needed up front (SELECT ... FOR UPDATE), and retry on failure.

55. What is the difference between optimistic and pessimistic locking?

Pessimistic locking takes a lock before changing data (SELECT ... FOR UPDATE). Optimistic locking takes no lock and checks at write time that the row is unchanged, typically with a version column.

UPDATE accounts
SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = 7;
-- 0 rows affected means another writer got there first: reload and retry

56. What does SELECT ... FOR UPDATE do, and what are NOWAIT and SKIP LOCKED?

It locks the selected rows against other writers until the transaction ends. NOWAIT errors instead of waiting; SKIP LOCKED skips locked rows, which makes it suitable for job queues.

57. Does a read block a write in PostgreSQL?

No. Under MVCC, plain reads take no row locks, so readers do not block writers and writers do not block readers. Writers block other writers on the same row.

58. What are shared and exclusive locks?

Shared locks allow concurrent readers and block writers; exclusive locks block everything else. PostgreSQL table lock modes (for example ACCESS SHARE, ROW EXCLUSIVE, ACCESS EXCLUSIVE) follow a conflict matrix; ALTER TABLE typically takes ACCESS EXCLUSIVE.

59. What is a gap lock?

An InnoDB lock on the gap between index records, used at Repeatable Read to prevent phantoms. PostgreSQL has no gap locks.

60. How do you find who is blocking a query?

In PostgreSQL, query pg_stat_activity and pg_blocking_pids(pid); pg_locks shows lock details. In MySQL, use performance_schema.data_lock_waits or SHOW ENGINE INNODB STATUS.

61. How do you make writes idempotent?

Give each operation a unique key (an idempotency key or natural key) and enforce it with a unique constraint, so a retry does nothing.

CREATE TEMP TABLE payments (idempotency_key text PRIMARY KEY, amount int NOT NULL);

INSERT INTO payments VALUES ('req-1', 500) ON CONFLICT (idempotency_key) DO NOTHING;
INSERT INTO payments VALUES ('req-1', 500) ON CONFLICT (idempotency_key) DO NOTHING;

SELECT * FROM payments;

 idempotency_key | amount 
-----------------+--------
 req-1           |    500
(1 row)

MySQL equivalents are INSERT IGNORE and ON DUPLICATE KEY UPDATE.

Internals: storage, WAL, MVCC, buffer pool, optimizer

62. How is data stored on disk?

In fixed-size pages (8 KB in PostgreSQL, 16 KB in InnoDB) holding rows. PostgreSQL stores table rows in heap files unordered; InnoDB stores rows in the primary-key B-tree. See storage and pages.

63. What is the buffer pool?

A shared memory cache of pages (shared_buffers in PostgreSQL, the InnoDB buffer pool). Reads check it first; modified pages are marked dirty and written later. PostgreSQL also benefits from the OS page cache.

64. How does the WAL provide durability?

Changes are first appended to a sequential write-ahead log and flushed to disk at commit; data pages are written later. After a crash, recovery replays the log from the last checkpoint to redo committed changes. See WAL and crash recovery.

65. Why is WAL faster than writing data pages at commit?

Log writes are sequential and small; data page writes are random and larger. Commit needs only one log flush, and page writes are batched.

66. What is a checkpoint?

A point at which all dirty pages up to a given WAL position are flushed to disk, so recovery only replays WAL after it. In PostgreSQL it runs every checkpoint_timeout (default 5 min) or when WAL reaches max_wal_size.

67. What is synchronous_commit?

A PostgreSQL setting (default on) making commit wait until WAL is flushed to disk. Setting it off reports success earlier and can lose the last few transactions on a crash, but does not corrupt data.

68. What is MVCC?

Multi-version concurrency control keeps multiple versions of a row so each transaction reads a consistent snapshot without blocking writers. PostgreSQL stores old versions in the table (with xmin/xmax transaction ids); InnoDB keeps them in undo logs. See MVCC and vacuum.

69. What does VACUUM do?

It removes dead row versions no transaction can see, updates the visibility map and statistics, and freezes old transaction ids to avoid wraparound. Autovacuum runs it automatically. VACUUM FULL rewrites the table and takes an exclusive lock.

70. Why does UPDATE create bloat in PostgreSQL?

An update writes a new row version and leaves the old one until vacuum reclaims it. Heap-only tuple (HOT) updates avoid new index entries when no indexed column changes and the page has free space.

71. Why can COUNT(*) be slow in PostgreSQL?

Visibility is per row version, so the table (or a whole index) must be scanned to count rows visible to the snapshot. Use an approximate count (pg_class.reltuples) or a maintained counter when an estimate is enough.

72. What does the query optimizer do, and what does it use?

It rewrites the parsed query and chooses the cheapest plan by estimated cost, using table statistics (row counts, distinct values, histograms, most common values, gathered by ANALYZE), available indexes, and cost settings. See query execution and the optimizer.

73. What are the join algorithms and when is each used?

74. How do you read an execution plan?

Read from the innermost, most-indented node outward. EXPLAIN shows the estimated plan; EXPLAIN (ANALYZE, BUFFERS) runs the query and adds actual rows, timing, and buffer hits. Large gaps between estimated and actual rows indicate bad statistics. See query plans.

EXPLAIN (COSTS OFF)
SELECT e.name, d.name
FROM employees e JOIN departments d ON d.id = e.dept_id
WHERE d.name = 'Sales';

                             QUERY PLAN                             
--------------------------------------------------------------------
 Hash Join
   Hash Cond: (e.dept_id = d.id)
   ->  Seq Scan on employees e
   ->  Hash
         ->  Index Scan using departments_name_key on departments d
               Index Cond: (name = 'Sales'::text)
(6 rows)

75. What is a B-tree versus an LSM tree?

A B-tree updates pages in place: fast reads, random writes. An LSM tree buffers writes in a memtable, flushes sorted immutable files (SSTables), and merges them by compaction: fast sequential writes, but reads may check several files (reduced by Bloom filters) and compaction adds write amplification. LSM is used by RocksDB, Cassandra, and LevelDB. See LSM trees vs B-trees.

Scaling: replication, sharding, partitioning, CAP

76. What is the CAP theorem?

During a network partition, a distributed system must choose between consistency (every read sees the latest write) and availability (every request gets a response). Partition tolerance is not optional in a distributed system, so the real choice is CP or AP. PACELC adds that even without a partition there is a latency versus consistency trade-off. See replication and CAP.

77. What is the difference between synchronous and asynchronous replication?

Synchronous: the primary waits for a standby to confirm before acknowledging commit, so no acknowledged data is lost on failover, at a latency cost. Asynchronous: the primary acknowledges immediately; standbys lag and can lose recent commits on failover, and replica reads can be stale.

78. What is the difference between physical and logical replication?

Physical (streaming) replication ships WAL bytes and produces an identical copy of the whole cluster. Logical replication ships row changes per table, allowing selective tables, different major versions, and different schemas at the target.

79. What is the difference between partitioning and sharding?

Partitioning splits one table into pieces inside one database server (by range, list, or hash); the planner prunes partitions. Sharding splits data across multiple servers, each holding a subset. See partitioning and sharding.

80. How do you choose a shard key?

Pick a key with high cardinality and even distribution that most queries include, so requests hit one shard. A poor key causes hot shards and cross-shard queries. Cross-shard joins and transactions are expensive or unsupported.

81. What is consistent hashing?

Keys and nodes are hashed onto a ring; a key belongs to the next node clockwise. Adding or removing a node moves only about 1/N of the keys instead of nearly all, as happens with hash(key) % N. Virtual nodes even out the load.

82. How do read replicas scale a system, and what is the catch?

They add read capacity by serving queries from standbys. Writes still go to one primary, and replication lag means a user may not see their own write; route those reads to the primary or wait for the replica to catch up.

83. What is split brain?

Two nodes both believe they are the primary and accept writes, causing divergent data. Prevented with quorum-based leader election, fencing, or a single external coordinator.

SQL vs NoSQL

84. When would you choose a relational database?

When data is structured with relationships, needs multi-row transactions, ad hoc queries, and strong integrity constraints.

85. When would you choose NoSQL?

When the access pattern is simple and known (key lookups), the schema varies, or write volume and horizontal scale exceed a single node. Types: key-value (Redis), document (MongoDB), wide-column (Cassandra), graph (Neo4j). See SQL vs NoSQL.

86. Is NoSQL always eventually consistent?

No. Consistency is per system and often configurable. MongoDB offers majority read and write concerns; Cassandra has tunable consistency levels; DynamoDB offers strongly consistent reads. Relational systems can also be eventually consistent on replicas.

87. What is BASE?

Basically Available, Soft state, Eventually consistent: the design stance of many NoSQL systems, contrasted with ACID.

Practical and troubleshooting

88. How do you find and fix a slow query?

Identify it (pg_stat_statements, log_min_duration_statement, MySQL slow query log), run EXPLAIN (ANALYZE, BUFFERS), find the node with the largest actual time or row-estimate error, then fix: add or adjust an index, rewrite the predicate so it can use the index, refresh statistics with ANALYZE, reduce returned columns and rows, or batch queries. See fixing slow queries.

89. What is SQL injection, and how do you prevent it?

Untrusted input concatenated into SQL text changes the query's structure. Prevention: parameterized (prepared) statements, which send the SQL and the values separately so values are never parsed as SQL. Identifiers and keywords cannot be parameters; validate them against an allowlist or quote them with format('%I', name).

PREPARE orders_by_status (text) AS
SELECT id, customer_id FROM orders WHERE status = $1 ORDER BY id;

EXECUTE orders_by_status('delivered');
EXECUTE orders_by_status('x'' OR ''1''=''1');

 id | customer_id 
----+-------------
  1 |           1
  2 |           1
  3 |           2
  5 |           3
  8 |           3
(5 rows)

 id | customer_id 
----+-------------
(0 rows)

The second call treats the whole string as one value and matches nothing. In application code, use the driver's placeholders, for example cursor.execute("SELECT ... WHERE status = %s", (status,)) in psycopg.

90. Do stored procedures prevent SQL injection?

Only if they use parameters. A procedure that builds dynamic SQL by string concatenation is as vulnerable as application code.

91. Stored procedures versus an ORM?

Stored procedures keep logic next to the data, cut round trips, and permit permission-based access, but are harder to version, test, and port. ORMs speed development and map objects to rows, but can generate inefficient queries (N+1) and hide SQL. Many systems use an ORM for CRUD and raw SQL for hot or complex queries. See views and routines.

92. What is connection pooling and why is it needed?

Opening a connection is costly, and PostgreSQL uses one OS process per connection, so thousands of connections waste memory and slow scheduling. A pooler (PgBouncer, or an application-level pool such as HikariCP) reuses a small set of connections. In PgBouncer transaction mode, session state (session-level SET, advisory locks, and, in older versions, protocol-level prepared statements) does not persist across transactions.

93. How do you back up a database, and what is PITR?

Logical backups (pg_dump, mysqldump) export SQL or data and are portable. Physical backups (pg_basebackup, Percona XtraBackup) copy data files and are faster to restore. Point-in-time recovery restores a base backup and replays archived WAL (PostgreSQL) or binary logs (MySQL) up to a chosen time (recovery_target_time). A backup is only proven by a test restore.

94. How do you change a schema on a large live table?

Use changes that avoid long exclusive locks: add nullable columns (a constant default is metadata-only since PostgreSQL 11), backfill in batches, build indexes with CREATE INDEX CONCURRENTLY, add constraints as NOT VALID then VALIDATE CONSTRAINT, and set lock_timeout so a blocked ALTER does not queue other queries behind it.

95. A query is fast in testing and slow in production. Why?

Typical causes: different data volume or distribution (a different plan), stale statistics, lock waits, cache state, bloat, missing indexes, parameter-dependent plans, or resource contention. Compare EXPLAIN (ANALYZE, BUFFERS) in both environments.

96. How do you monitor a database?

Track connections, transactions per second, query latency percentiles, replication lag, cache hit ratio, lock waits, dead tuples and autovacuum activity, disk and WAL growth, and checkpoint frequency, using pg_stat_* views and an external metrics system.

Summary: topics and lessons