SQL Interview Knowledge & Query Lab

A useful logical model is FROM and JOINs first, then WHERE, GROUP BY, aggregate calculation, HAVING, window functions, SELECT, DISTINCT, ORDER BY, and finally row limiting. Engines can physically reorder work when equivalence is preserved. The model explains scope rules—for example, a SELECT alias usually does not exist when WHERE is evaluated—and helps predict where rows disappear or multiply.

WHERE removes input rows before grouping; HAVING removes groups after aggregate values exist. Put nonaggregate predicates in WHERE when semantics allow so less data reaches grouping. HAVING is required for predicates such as HAVING SUM(amount) > 1000. Moving a predicate between them can change the answer, so this is a semantics decision before it is an optimization.

COUNT(*) counts rows. COUNT(column) counts rows where that expression is not NULL. COUNT(DISTINCT column) counts distinct non-NULL values. Performance depends on plan and storage engine, not a universal COUNT(1) trick. Choose the form that matches the business question and watch for join multiplication before counting.

WHERE is logically evaluated before SELECT creates the output alias, so the alias is normally not in scope. ORDER BY is later and commonly can use it. Repeat the expression, or place it in a derived table or CTE when reuse improves clarity. Exact alias rules vary by engine, so avoid relying on a permissive dialect in portable interview SQL.

INNER JOIN returns matching combinations only. LEFT JOIN preserves every left row and fills right-side columns with NULL when no match exists. Start by stating the required output grain and preservation rule. Performance is workload- and plan-dependent; rewriting a LEFT JOIN as INNER JOIN is valid only when unmatched left rows are not part of the required result.

After a LEFT JOIN, unmatched rows have NULLs on the right. A WHERE condition such as WHERE payments.status = 'settled' evaluates UNKNOWN for those rows and removes them, leaving only matches. Put the predicate in ON when it defines which right rows qualify while all left rows must remain. Keep it in WHERE when unmatched rows should be removed.

A join returns every matching pair. A one-to-many relationship therefore repeats the one-side columns, and incomplete join keys can create unintended many-to-many multiplication. Define the desired grain, inspect key uniqueness on both inputs, and aggregate or select one child row deliberately. Adding DISTINCT can hide the symptom while preserving wrong totals and unnecessary work.

EXISTS expresses a semi-join: return an outer row if at least one related row qualifies, without multiplying it by the number of matches. JOIN is appropriate when columns or multiplicity from both relations are needed. Modern optimizers can transform equivalent forms, so choose the clearest semantics and inspect the plan rather than claiming EXISTS is always faster.

CROSS JOIN deliberately produces every pair and fits combinations, calendars, and small parameter grids. A self join relates rows in one table through aliases, such as employees to managers. RIGHT JOIN is the mirrored preservation rule of LEFT JOIN and is often rewritten as LEFT JOIN for reading flow. FULL OUTER JOIN preserves unmatched rows from both sides and is useful for reconciliation; MySQL has no native FULL OUTER JOIN, so emulate it carefully with left/right anti-matched branches and UNION ALL. In every case, state the output grain and expected cardinality before choosing syntax.

One row per qualifying customer. Use NOT EXISTS when the business question is about the absence of a matching row. Begin with active customers. Reject a customer only when a succeeded payment exists. Do not join and deduplicate when no payment columns are needed.

One row per order. Two one-to-many joins create an item-by-payment cross product; aggregate each child to order grain first. Items and payments are independently one-to-many from orders. Joining both raw children produces 2 × 2 rows for order 1. Aggregate each child to order_id before joining.

Conditional aggregation places a CASE expression inside SUM, COUNT, or another aggregate—for example SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END). It can produce multiple metrics per group in one query. Be explicit about ELSE and NULL behavior, avoid mixing incompatible grains, and verify whether one wider aggregation is actually cheaper than separate selective queries.

Compute the finer grain first—such as one row per customer and month—in a CTE or derived table, then aggregate or compare those rows at customer grain. Joining raw orders directly to a customer-level average can weight busy months incorrectly. State each intermediate grain aloud; most aggregate-of-aggregate mistakes are really grain mistakes.

COUNT(DISTINCT value) is semantically correct when unique non-NULL values are the metric. It may require a large hash set or sort, especially per group. First check whether an earlier join multiplied rows unnecessarily or whether the model can supply an already-unique relation. Then inspect memory, spill behavior, cardinality, and engine-specific approximate options only when the product tolerates approximation.

One row per region. Start from the complete region set, constrain orders in the join, and aggregate once per region. Drive from regions and LEFT JOIN matching orders. Put status and half-open date bounds in the ON clause. COUNT the nullable order key and coalesce SUM for empty groups.

One row per signup cohort month. Assign one cohort per user, reduce activity to user-month, calculate month offsets, then pivot counts. Derive each user's signup month exactly once. Reduce repeated events to one user-month. Convert year and month differences into an integer period.

A correlated subquery references the current outer row; a join combines relations directly. Correlation is not automatically slow—optimizers can decorrelate many EXISTS and aggregate subqueries—but some shapes execute repeated work. Use the clearest correct form, inspect the physical plan and row counts, and rewrite only when evidence shows repeated scans or poor estimates.

No. For equivalent non-NULL semantics, modern optimizers often transform IN and EXISTS into the same semi-join. Choose IN for membership and EXISTS for correlated existence when that reads more clearly. The important exception is negative membership: NOT IN can become UNKNOWN when the subquery contains NULL, while NOT EXISTS usually expresses the intended anti-join safely.

Both can express an intermediate relation. A CTE often improves naming and decomposition and can be referenced more clearly; a derived table is local to one FROM position. Neither is universally faster. Engines may inline, materialize, or otherwise transform a CTE depending on version, reuse, recursion, and hints, so separate maintainability value from runtime evidence.

A recursive CTE has an anchor query and a recursive member that reads the rows produced so far to find the next level. It is useful for org charts, categories, dependencies, and bounded graph traversal. Define termination, maximum depth, path representation, duplicate policy, and cycle handling; exact recursion limits and cycle syntax differ by engine.

One row per customer-month above that customer's monthly average. Aggregate raw orders to the monthly grain before comparing each month with a customer-level window average. Collapse paid orders to one row per customer-month. Compute AVG(monthly_spend) within each customer partition. Window results are not available to the same SELECT's WHERE clause.

One row per reachable employee. Anchor at the requested employee, recursively join children, and carry state needed for depth and cycle detection. Seed the CTE with employee 1 at depth zero. Join employee.manager_id to the current employee. Append IDs to an array and refuse IDs already in the path.

ROW_NUMBER assigns every row a unique sequence, so include a stable tiebreaker when the chosen row matters. RANK gives ties the same rank and leaves gaps; DENSE_RANK gives ties the same rank without gaps. Use ROW_NUMBER to select exactly one row, RANK for competition-style placement, and DENSE_RANK to rank distinct value groups.

GROUP BY collapses input rows to one row per group. A window function computes over a partition and ordering but preserves each row, which supports comparisons to group totals, running values, rankings, and previous-row logic. If a window result must be filtered, place it in a CTE or derived table unless the engine supports a suitable later-stage clause.

PARTITION BY defines eligible rows, ORDER BY defines sequence, and the frame defines which ordered rows contribute to the current calculation. Defaults vary by function and engine context and can include peer rows or stop at the current row, surprising LAST_VALUE and running totals. State ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW—or the full partition—when correctness depends on it.

LAG reads a previous row's value and LEAD reads a following row within the window order. They are useful for deltas, state transitions, time gaps, churn, and sequence detection. Partition by the entity, order by a deterministic event key, and define the first/last-row default and duplicate timestamp policy explicitly.

One row per qualifying category-product. Aggregate revenue first, then rank products inside each category with DENSE_RANK. Group line items at category-product grain. DENSE_RANK keeps ties and produces no rank gaps. Filter the derived rank in an outer query.

One row per account with at least one state event. Rank state events newest-first with a stable secondary key, then keep row one. Restart numbering for every account. Sort by changed_at and event_id descending. Keep rn = 1 after the window is evaluated.

One row per calendar day in the requested range. Classify each signup once, aggregate to a dense daily series, then window the numerator and denominator. Use EXISTS to create one conversion flag per signup. Join to a calendar series so one row always means one day. Sum daily counts over the current day and six preceding rows.

First define the entity grain and what 'consecutive' means—calendar days, business days, sequence numbers, or a time threshold. Then either subtract ROW_NUMBER from an ordered value to create a constant island key, or use LAG to mark breaks and a running sum to number islands. Deduplicate input and define timestamp ties before grouping.

Use ROW_NUMBER() OVER (PARTITION BY entity_id ORDER BY event_time DESC, event_id DESC), then keep row number one. The unique tiebreaker makes the result deterministic when timestamps tie. Engine-specific shortcuts can be useful, but the window pattern is portable and explicit. For high-frequency reads, consider whether the model should maintain current state separately.

One row per user. Deduplicate activity days, derive an island key from date minus row number, then rank streaks. Reduce many events to one row per user-date. Consecutive dates share date minus row-number days. Count islands and select each user's longest deterministic winner.

One row per event with a user-local session number. Detect session boundaries with LAG, then cumulatively sum the boundary flags per user. LAG within each user finds the prior event. First events and gaps over 30 minutes receive 1. A running sum converts boundary markers into session numbers.

One row per provider-event ID. Partition by the provider's idempotency key and explicitly encode which duplicate survives. provider_event_id identifies repeated delivery of the same event. Order newest-first, then use callback_id to resolve equal receipt times. Keep only row one for each business key.

NULL is not an ordinary value, so comparing it with = or <> yields UNKNOWN, even against another NULL. WHERE keeps only TRUE, which removes UNKNOWN rows. Use IS NULL and IS NOT NULL for null tests, and use an engine's null-safe equality only when that dialect-specific semantic is intended.

x NOT IN (subquery) is effectively a conjunction of x <> each returned value. If one value is NULL, that comparison is UNKNOWN, so the conjunction is not TRUE and WHERE removes the row. Use NOT EXISTS with a correlated equality for the usual anti-join intent, or prove and enforce that the subquery column cannot be NULL.

COUNT(column), SUM, AVG, MIN, and MAX ignore NULL inputs, while COUNT(*) counts rows; an aggregate over no non-NULL inputs is often NULL, not zero. Equality joins do not match NULL to NULL. Apply COALESCE only when the business meaning of missing is truly the chosen replacement—turning unknown amount into zero can change semantics.

A natural key comes from the domain and is valuable when it is compact, stable, and truly unique. A surrogate key gives the row an implementation-controlled identity and avoids propagating a wide or mutable business key. Many designs use both: a surrogate primary key plus a UNIQUE constraint on the real business identifier. The constraint matters; adding an id must not permit duplicate domain entities.

A composite key fits a relationship such as (order_id, product_id) when each pair may occur once and that combination is the row's identity. It enforces the invariant directly. Costs include wider foreign keys and indexes, awkward references, and difficulty when the business later permits multiple lines for the same pair. Choose from domain identity and evolution, not a universal single-column rule.

Put the foreign key on the many side for one-to-many. A one-to-one is usually a foreign key with a UNIQUE constraint, often used to split optional or security-sensitive data. A many-to-many needs a junction table with foreign keys to both parents and a key or uniqueness rule for the relationship. Add relationship attributes to that junction row, not to either parent.

Normalization stores each fact under a clear key and reduces update, insert, and delete anomalies. Denormalization can reduce joins or precompute a high-value read path, but creates duplicate state, refresh logic, storage, and repair work. Start normalized for correctness; denormalize when a measured workload needs it and document the source of truth, update mechanism, staleness budget, and reconciliation path.

Use users, products, orders, and order_items. The junction row owns quantity and the price/currency captured at purchase time so product-price changes do not rewrite history. Orders reference the customer and carry lifecycle fields; order_items reference both order and product with positive-quantity checks. Decide whether repeated product lines are allowed, enforce that rule, and keep payment records separate from order state.

A B-tree keeps key values ordered in a balanced search structure. Equality predicates descend to a narrow key range; range and ordered queries can then scan adjacent leaf entries instead of the whole table. Entries identify rows or, in some engines, contain the clustered row. The benefit depends on selectivity and avoided work; base-row lookups, random I/O, and maintenance are part of the cost.

An index is another data structure to store, cache, update, log, replicate, back up, and maintain. Extra indexes slow inserts and relevant updates, increase storage and memory pressure, lengthen maintenance, and can overlap without serving a distinct workload. Design the smallest useful set from recurring query patterns and observe usage across a representative business cycle before removal.

Start with the recurring query shape. Equality predicates often precede the first range predicate; following keys may support ORDER BY, GROUP BY, or coverage. 'Most selective first' is not universal, and newer engines may use skip-scan behavior in some cases. Compare important workload queries, data distribution, write cost, and the actual plan before replacing useful single-column indexes.

A covering index contains the search keys and every column the query needs, allowing an index-only access path when engine visibility and storage rules permit. It can remove many base-table lookups for a hot selective query. Wider entries reduce fan-out, use more cache and storage, and amplify writes, so coverage should serve a proven workload rather than every SELECT list.

An index on a value with few distinct options—such as a balanced boolean—may still point to half the table. Traversing the index and fetching many scattered rows can cost more than a sequential scan. Distribution matters more than the raw distinct count: a partial index for a rare status or a composite index matching a selective workload can still be valuable.

The table may be small, the predicate may return many rows, the index prefix may not match, an expression or implicit cast may block a search condition, or base-row lookups may be expensive. Statistics, skew, parameters, and stale metadata can also mislead the optimizer. Inspect estimated and actual rows, bytes/pages read, and the predicate before forcing an access path.

A partial or filtered index targets rows satisfying a stable predicate, such as open orders; an expression index stores a computed search key, such as normalized email. They can be smaller and more selective, but predicate matching, expression syntax, determinism rules, and maintenance differ by engine. Use them for a proven recurring shape and document the dialect boundary.

The engine parses SQL, resolves names and types, builds a logical relational expression, rewrites equivalent forms, estimates candidate physical plans, and chooses access paths, join algorithms, ordering, and aggregation operators. The executor runs that plan and streams or materializes results. Caches, statistics, parameters, memory, and concurrency influence the chosen and observed behavior.

The optimizer uses table sizes, distinct counts, histograms or similar distributions, null fractions, indexes, and correlation assumptions to estimate rows flowing through each operator. Those estimates drive scan, join, order, memory, and parallelism choices. Statistics are summaries, not truth; skew, correlated columns, stale data, and parameter values can create large estimate errors.

A sequential scan reads the table in storage order and can be cheaper when the table is small, most rows qualify, or index hits would trigger many scattered lookups. An index scan wins when it can avoid enough work or provide useful ordering. Do not treat every scan as a bug; compare actual rows, pages/bytes, cache state, and concurrency impact.

Nested loops work well when the outer input is small and the inner side has an efficient lookup. Hash joins build a hash table for equality joins and suit larger unsorted inputs if memory is adequate. Merge joins advance through compatible ordered inputs and can exploit existing order. Estimates, spills, parallelism, and engine implementation matter more than declaring one algorithm best.

Look for the first large estimate error, repeated loops, rows removed, scans, join shape, sorts, spills, memory, and logical/physical I/O. Estimated cost is an internal comparison unit, not milliseconds. Actual-plan facilities may execute the statement, so use care with writes and production load. The most visually expensive node is not automatically the root cause.

Production may have different row counts, value skew, statistics freshness, indexes, parameter distributions, cached plans, engine versions, memory, parallelism settings, or storage costs. Capture the exact SQL and bind values plus both plans and metadata. Reproducing only the schema is not environment parity; representative distribution is usually the missing evidence.

A diagnosis and verification plan, not a rewritten result set. A large early estimate error compounds upward and can make a nested loop that looked cheap execute an inner lookup 120,000 times. Start at leaf nodes; the filter is wrong before the join is chosen. The optimizer expects 100 outer rows and prices a nested loop accordingly. Inspect stale or insufficient statistics, correlated predicates, skew, and parameters. Refresh or extend statistics, retest representative binds, then consider indexes or query changes.

Capture the current plan and runtime metrics, compare the old plan if available, and quantify returned versus examined rows, pages/bytes, sort or hash spills, lock waits, cache state, and parameter values. Check statistics and index alignment with the current query shape. Then change one cause, test representative values and concurrency, canary, and verify latency plus write overhead.

A sargable predicate exposes a searchable key or range, such as ordered_at >= start AND ordered_at < end, rather than applying a function to the indexed column for every row. Implicit casts, arithmetic, leading wildcards, and collation mismatches can also block useful access. Expression indexes may help a stable transformed lookup, with dialect-specific rules.

DISTINCT is correct when the result truly asks for a set of unique projected values. It is suspicious when added after a join to remove duplicates created by an incomplete key or one-to-many relationship. Define the output grain, repair the join or pre-aggregate/select the child row, then use DISTINCT only if unique projection is still the requirement.

A B-tree is ordered from the beginning of the indexed value, so an unknown leading prefix often prevents a narrow range lookup. Reversing data solves only suffix search; expression, trigram, full-text, or external search indexes serve different semantics and vary by engine. Clarify case, language, tokenization, ranking, freshness, and scale before choosing the search path.

OFFSET/LIMIT supports arbitrary page numbers but the engine may still process and discard the preceding rows, and concurrent changes can create duplicates or gaps. Keyset pagination filters after the last composite sort key, making deep pages proportional to page size when an index matches. It needs a total deterministic order and cannot jump to arbitrary pages cheaply.

SELECT * can read, transfer, allocate, and serialize columns the caller does not use; wide values make this especially costly. It also couples consumers to schema order and additions and may prevent an index-only plan. It is fine for ad hoc inspection, but stable application queries should name the contract and still measure the actual bottleneck.

If the engine compiles or caches a plan using one parameter distribution, that access path may not fit another. Capture slow and fast bind values, their cardinalities and plans, and the cache/compile behavior. Remedies differ by engine and may include better statistics, query reshaping, selective recompilation, or separate paths; forcing one plan can simply move the regression.

One row per published post in the next page. Turn the last row's complete ordering key into a range predicate instead of rescanning and discarding OFFSET rows. post_id breaks equal published_at values. Use less-than comparisons that mirror descending order. Lead with the equality filter, then both ordering columns.

One row per matching order, newest first. Move time transformation to constants, define case-insensitive identity deliberately, and align a narrow index with the equality-plus-range access path. Use a half-open timestamp range so created_at remains directly searchable. Use an explicit normalized email expression or stored canonical column. Put email equality before the timestamp range and verify ordering needs.

Atomicity commits all operations or none; consistency means a transaction preserves declared invariants; isolation controls what concurrent transactions can observe; durability means committed effects survive the database's promised failures. ACID does not define every business rule or make distributed workflows atomic. The application must choose boundaries, constraints, isolation, retries, and external side-effect handling deliberately.

Both changes belong in one local transaction if balance and ledger must agree. If the insert fails, roll back the balance update. Enforce supporting constraints and use a stable operation/idempotency key for retries. A remote notification or payment call should not be held inside the database transaction; use an outbox or another explicit delivery boundary.

In the standard model, Read Uncommitted can expose dirty data; Read Committed prevents dirty reads but may allow changing and phantom results; Repeatable Read protects repeated reads more strongly; Serializable makes committed outcomes equivalent to some serial order. PostgreSQL, MySQL, and SQL Server implement these with different locking and versioning semantics. Choose from the business invariant and test contention and retry behavior.

Serializable is justified when concurrent transactions must preserve a cross-row or predicate invariant and a simpler atomic statement, constraint, or explicit lock cannot express it safely. It can reduce concurrency or abort transactions that must be retried, depending on engine. Keep transactions short, make retry semantics explicit, and apply the level narrowly rather than upgrading the entire application by habit.

A lost update occurs when two transactions read the same value, compute changes, and the later write overwrites the earlier one. Prefer one atomic UPDATE when possible. Otherwise include a version or original value in the predicate—UPDATE ... WHERE id = ? AND version = ?—then treat zero updated rows as a conflict to merge, retry, or report.

A long transaction may hold locks, prevent version cleanup, retain old snapshots, grow logs or undo/version stores, delay replication or vacuum-like maintenance, and enlarge rollback work. Keep user interaction and remote calls outside it, batch large changes, set timeouts, and monitor transaction age as well as statement time. Exact consequences vary by engine.

A deadlock or serialization failure invalidates the transaction, so retry from its beginning, not the last statement. Bound attempts, add jitter where useful, preserve deadlines, and instrument retry rate. Use idempotency keys or unique constraints for repeated commands and keep emails, payments, and other external effects behind an outbox or confirmed post-commit step.

One row per SKU. Preserve inventory rows and subtract a conditional sum of reservations that are active at one consistent timestamp. Start from inventory and left-join live reservations. Filter active, unexpired rows in the join. Return both reserved and available so negative stock is visible.

Conceptually, shared locks allow compatible reads while exclusive locks protect modifications from conflicting access. Engines also use update, intent, range, metadata, and other modes. Row locks improve concurrency but create more lock-management work; page or table locks reduce overhead but block more work. Granularity and escalation behavior are engine-specific and should not be forced without evidence.

Blocking is waiting for an incompatible lock and may be normal if the owner commits promptly. A deadlock is a cycle: each transaction waits for a resource held by another in the cycle, so the database breaks it by aborting a victim. Timeouts limit waiting but do not diagnose the cycle. Investigate owners, waiters, statements, resources, and transaction duration.

Capture the engine's deadlock graph or log, including statements, resources, indexes, transaction boundaries, and call paths. Reconstruct the two acquisition sequences. Fix recurring cycles with consistent row/table order, shorter transactions, narrower predicates and useful indexes, and no remote work under locks. Keep bounded victim retries, then load-test the same interleaving and monitor recurrence.

MVCC keeps enough version information for a transaction or statement to decide which row version is visible in its snapshot. A writer can create a new version while readers continue using an older committed one, so ordinary reads need not block that write. Writers can still conflict with writers, and snapshots do not automatically protect every business invariant.

Versioning shifts some blocking cost into storage, visibility checks, cleanup, and write amplification. Long-lived transactions or snapshots can prevent old-version removal, increasing table/undo/version-store pressure and recovery work. PostgreSQL vacuum, InnoDB purge/undo, and SQL Server version stores differ, so monitor the engine's transaction age, retained versions, cleanup lag, and storage headroom.

Application validation can explain errors early and enforce workflow-specific rules. Database constraints protect invariants under concurrency and across every writer, including scripts, jobs, and future services. Use both: validate for experience, constrain for truth, map constraint failures to useful responses, and avoid duplicating rapidly changing policy as rigid DDL when it is not a stable data invariant.

A foreign key prevents child references to missing parents and controls what happens when a parent changes or is deleted. It protects against races and nonapplication writers. It also adds validation and locking work, especially for large changes, and supporting indexes are often important. Disabling it trades a visible cost for reconciliation and integrity risk that must be owned explicitly.

Use parameterized queries or prepared statements so user data is never parsed as SQL code. Escaping strings manually is not the preferred boundary. Identifiers and sort directions often cannot be bound, so select them from an allow-list rather than concatenating arbitrary input. Add least-privilege credentials, safe error handling, and security tests as defense in depth.

Runtime services should not own DDL or unrestricted administrative rights. Use separate roles for migrations, reads, writes, reporting, and operations; scope by schema/table and rotate credentials. Row-level security can add a database policy boundary where supported, but it must be tested for bypass paths and performance. Audit privileged actions and plan break-glass access.

Stored routines can reduce round trips, execute near data, centralize permissions, and keep multi-step operations atomic. Triggers can enforce or capture cross-writer behavior. Costs include vendor coupling, specialized testing and deployment, hidden side effects, recursion, observability, and mixed ownership. Use them for a clear database-owned boundary, document execution, and keep failure behavior visible.

A normal view is a named query whose underlying data is read when referenced, subject to optimizer transformations. A materialized view persists a computed result and trades storage and refresh complexity for cheaper reads. Engine support, indexing, incremental refresh, consistency, locking, and permissions vary, so the design must state freshness and failure behavior.

It may help when many reads reuse the same expensive aggregation and the product accepts a defined freshness window. Quantify dimensions, filters, update rate, refresh duration, storage, failure recovery, and whether incremental updates are possible. Alternatives include summary tables, streaming projections, caches, or an analytical store. Verify that refresh work does not overload the OLTP system.

Range partitioning groups ordered intervals and is useful for time retention and range scans. List partitioning maps explicit categories such as regions. Hash partitioning spreads keys more evenly but weakens range locality. Partitioning helps only when queries prune partitions or operations benefit from boundaries; too many partitions, skew, global uniqueness, and cross-partition work add cost.

Table partitioning is usually managed by one database instance or cluster and preserves one query/transaction system while splitting storage into partitions. Sharding routes key ranges or buckets to independently owned database units, adding distributed transactions, rebalancing, failures, topology, and operational tooling. Partition before sharding only when it serves the workload; neither is a growth ritual.

A read routed to an asynchronous replica can observe a state before the just-committed primary write. Remedies depend on the product requirement: read that entity from the primary for a bounded session, use a consistency token/high-water mark, wait for replica position, or make the UI tolerate staleness. Define the required operation, lag budget, and failure fallback.

Options include primary reads after a user's write for a time window, routing by entity/session, attaching a commit position and waiting for a replica to reach it, or returning the written representation directly. Each adds latency, primary load, state, or failover complexity. State whether the guarantee is per user, entity, region, or globally ordered.

Read scaling can use indexes, caching, replicas, projections, or analytical copies, each with freshness semantics. Write scaling starts by reducing unnecessary writes, batching, and buying simple headroom; partitioned ownership may then distribute write load but adds routing, cross-partition invariants, rebalancing, and failure modes. Identify the actual bottleneck before proposing sharding.

Move analytical work when large scans, wide aggregations, retention, or concurrency compete with latency-sensitive transactions, or when columnar storage and independent scaling materially help. Define ingestion and transformation ownership, freshness, correctness, backfills, security, cost, and reconciliation. A warehouse is not an excuse to abandon source-of-truth semantics or data contracts.

ORMs reduce mapping boilerplate, centralize conventions, and make ordinary CRUD and relationships productive. They can hide N+1 queries, generate poor joins, blur transaction scope, and make advanced analytics or engine features awkward. Keep generated SQL observable. Use raw SQL for performance-critical, set-based, reporting, bulk, or dialect-specific work when the explicit query is clearer and testable.

N+1 occurs when loading N parents causes N additional relationship queries. Detect it with query counts and traces, not only a slow-query log. Fix with a join, explicit eager loading, batch IN lookup, or data-loader pattern while preserving pagination and avoiding a huge Cartesian result. Verify query count, rows/bytes, memory, and latency under realistic N.

A pool leases established connections to callers and queues when none are available. Size it from database capacity, query latency, application instances, and workload isolation—not per-process intuition. An oversized fleet can raise runnable queries, lock contention, context switching, memory, and tail latency. Measure acquisition wait, usage time, saturation, leaks, and database active-session capacity.

Place the transaction at the application boundary that owns the local database invariant, and pass repositories the same unit of work or connection. Avoid accidental nested/autocommit behavior and broad annotations that hold a transaction across network calls, user think time, or large computation. Make propagation rules, rollback conditions, timeouts, and post-commit side effects testable.

Prepared statements separate code from values and can reduce repeated parse/compile work depending on driver and engine. Batching combines writes or lookups to reduce round trips and commit overhead. Very large batches increase memory, lock duration, log spikes, rollback cost, and parameter limits, so choose a bounded size and preserve per-row error and idempotency semantics.

First check the engine/version because metadata-only defaults, online validation, and locks differ. A common sequence is add a nullable column, deploy writers that populate it, backfill in resumable rate-limited batches, validate missing rows and replicas, then enforce the constraint with the least-blocking supported mechanism. Keep old readers compatible, monitor lock/log/lag impact, and define abort and rollback boundaries.

Verify the query need and estimate build time, storage, temporary space, I/O, log generation, replica lag, and lock phases. Use the engine's online or concurrent option if its limitations fit, schedule against headroom, set timeouts, and monitor abortability. Afterward validate index validity, plan adoption, write overhead, and whether an overlapping index can be removed safely.

Confirm saturation and user impact, compare traffic and deployments, and rank queries by total CPU—not only slowest single call. Inspect frequency, scans, joins, sorts, parsing, parallelism, retries, and maintenance. Mitigate with rollback, rate limits, workload isolation, or a proven query/index change; then verify CPU per request, throughput, tail latency, and downstream effects.

Measure pool wait, active/idle/leaked leases, transaction age, query latency, blocked sessions, instance count, and recent traffic or deployment changes. Long queries and lock waits can occupy every connection without a code leak. Protect users with admission control or rollback, fix ownership and timeouts, and size the whole fleet against database capacity rather than raising every pool.

Stop automatic retries until the exact committed DDL and data state is known; transactional DDL behavior differs by engine. Verify application compatibility, locks, replicas, and partial backfill progress. Prefer an idempotent forward repair when rollback would destroy new writes; otherwise execute the rehearsed rollback. Record phase state, validate invariants, and fix the migration's resumability and abort criteria.

Start with per-team/query attribution, SLOs, connections, CPU/I/O, memory, locks, storage growth, and incident history. Add ownership, query and migration budgets, admission controls, pool limits, slow-query review, safe indexes, and workload scheduling. Separate analytical or untrusted workloads first when evidence shows incompatible SLOs. Any cluster split needs cost, migration, on-call, failover, and developer-productivity ownership.