Managing Long-Running SQL Regression Test Suites

A SQL test takes 90 seconds in CI. Instrumentation attributes 68 seconds to fixture loading, 8 seconds to SQL execution, 10 seconds to result comparison, and 4 seconds to cleanup.

Which investigation should receive priority if the goal is to reduce this test's total runtime?

• AInspect the query plan for the statement with the highest estimated cost.
• BProfile fixture loading, including inserts, indexes, and commit frequency.
• CIncrease query execution memory to reduce possible temporary-file spills.
• DReplace exact result comparison with a checksum before measuring further.

A join becomes slow after a fixture grows from 100,000 to 5 million rows. Its actual plan shows an inner scan executed 80,000 times, while the planner estimated only 12 outer rows.

Which investigations are justified by this evidence? Select all that apply.

• Check whether statistics represent the enlarged fixture and its value distribution.
• Standardize the suite on hash joins, treating repeated inner scans as evidence against nested loops.
• Compare estimated and actual row counts upstream of the repeatedly executed scan.
• Inspect correlations or skew in the predicates that produced the outer rows.

Four tests have different evidence: Accounts spends 40 seconds waiting for a lock, Orders spills its sort to disk, Customers estimates 10 rows but returns 200,000, and Export finishes its database statement quickly but spends 50 seconds transferring and comparing rows.

Match each test to the most directly supported next investigation.

A read-only test takes 25 seconds on its first run and 2 seconds on immediate repeats. The plan is unchanged, and physical reads fall sharply after the first run.

Before crediting a code change for the speedup, investigate ___ as the explanation for the repeat-run improvement.

A developer runs EXPLAIN ANALYZE on an UPDATE against a shared regression database, believing the command only displays the proposed plan.

Assertion: This diagnostic can change database rows unless execution is contained or rolled back appropriately. Reason: EXPLAIN ANALYZE executes the statement to collect actual execution measurements.

• ABoth A and R are true and R is the correct explanation of A
• BBoth A and R are true but R is NOT the correct explanation of A
• CA is true but R is false
• DA is false but R is true

A rewritten query has a lower estimated plan cost, but one CI run takes 14 seconds instead of the previous 10. The two runs used different workers and no wait or resource telemetry was collected.

Which conclusions or next steps are defensible? Select all that apply.

• Reject the rewrite because a 40% measured slowdown is large enough to establish a regression.
• Repeat both versions under comparable conditions and record execution and wait data.
• Accept the rewrite because estimated plan cost is less sensitive to worker load than elapsed time.
• Check whether I/O, locks, or worker load explain the observed runtime difference.

An actual PostgreSQL plan reports an inner node with actual time 0.010..0.020 milliseconds, 1 row, and 100,000 loops. A reviewer dismisses the node because its displayed time is tiny.

How should the reviewer interpret this evidence?

• AThe inner node contributes only 0.020 milliseconds because the reported time is cumulative.
• BThe loop count is already reflected in the displayed timing; use row count to assess repeated work.
• CThe node's contribution should be measured by adding its displayed time to every ancestor's displayed time.
• DThe per-loop time can accumulate substantially; inspect repeated work without summing inclusive parent times.

A production failure involved a join between highly skewed customer and order tables. The team wants a CI fixture that can reproduce the plan problem without copying sensitive production records.

Which fixture properties or validation steps are appropriate? Select all that apply.

• Preserve relevant skew and cross-table correlations using synthetic or approved transformed data.
• Check whether the fixture reproduces the important estimates, plan behavior, and failure condition.
• Prefer uniform random values because equal row counts preserve optimizer behavior.
• Retain enough cardinality to exercise the problematic behavior within the test's resource budget.

Each test is wrapped in a transaction and rolled back, but the application opens another connection and commits an audit row. Later tests sometimes find those audit rows.

Which isolation change best addresses this failure mode?

• AGive each test a disposable database or equivalent isolated environment used by all its connections.
• BAdd a savepoint around each application call so the outer rollback also covers its audit writes.
• CUse serializable isolation on the harness connection so its rollback governs concurrent audit writes.
• DDelete the audit rows inside the outer transaction immediately before rolling that transaction back.

Four tests need different environments: Ledger performs writes on one connection without committing, Migration must issue real commits and database-level DDL, Checkout uses several connections, and Tax reads a large immutable reference dataset.

Match each test to an isolation strategy suited to its stated behavior.

An optimizer bug appears only when a million-row orders table contains many customers with no orders and a small number with thousands. The proposed replacement fixture has the same row count but distributes orders evenly.

The replacement should be rejected unless it preserves the relevant data ___, not merely the table size.

A team proposes using approved, sanitized production snapshots for every SQL test. The suite includes both tiny constraint checks and large optimizer regressions.

Assertion: Small purpose-built fixtures can be more appropriate than production snapshots for some tests in this suite. Reason: Sanitizing a production snapshot guarantees that its data distribution and query plans remain unchanged.

• ABoth A and R are true and R is the correct explanation of A
• BBoth A and R are true but R is NOT the correct explanation of A
• CA is true but R is false
• DA is false but R is true

A baseline database takes 12 minutes to build. The storage platform offers copy-on-write clones, and each test worker must perform independent writes.

Which safeguards make cloning a sound strategy? Select all that apply.

• Reuse the baseline for worker writes, separating rows by worker ID and deleting them after each run.
• Version and keep the baseline immutable while assigning a separate writable clone to each worker.
• Measure clone startup, first-write overhead, and storage growth under realistic concurrency.
• Size worker concurrency from single-clone timings, treating separate clones as independent storage workloads.

A test inserts and rolls back an invoice row, then expects the next test's generated invoice ID to be 1. The database's sequence advances are not rolled back.

What is the most appropriate correction when the test only needs to verify invoice relationships?

• AUse serializable isolation so identifier allocation follows the same rollback boundary as invoice data.
• BReplace identifier checks with matching row counts in the invoice and related tables.
• CCapture generated IDs and compare relationships instead of assuming a fixed sequence value.
• DRun the tests serially and retain fixed expected IDs to avoid concurrent identifier allocation.

Four CI workers each run 25 SQL tests. Three finish in 8 minutes, while the fourth takes 31 minutes because it contains most of the suite's slow tests.

Which scheduling change most directly addresses the imbalance?

• AKeep equal test counts but randomize assignment independently on every run.
• BAllocate more CPU to all four workers while preserving their current assignments.
• CGroup tests by feature area, giving each worker an equal number of feature groups.
• DAssign tests using recent durations, balancing estimated total time across workers.

Increasing SQL test workers from 4 to 12 makes the suite slower. All workers connect to one database instance, and telemetry shows high storage latency and frequent lock waits.

Which responses are justified? Select all that apply.

• Benchmark lower concurrency levels to identify the throughput sweet spot.
• Separate conflicting mutable state or serialize tests that require shared global resources.
• Add workers to keep useful queries running while other workers wait on storage or locks.
• Check whether the bottleneck is shared storage capacity rather than available client CPU.

Four parallelization problems appear: Refunds fails only after Currency changes a setting, Reports slows when many independent scans run together, Users has duplicate-key failures from fixed IDs, and Shard D finishes long after equally populated shards.

Match each observed problem to the most targeted remedy.

A runner will distribute tests with measured durations of 18, 11, 7, and 3 minutes. Its specified algorithm sorts longest first, then assigns each test to the currently least-loaded shard.

In what order should the runner consider these tests for assignment?

Each SQL worker now uses a different schema on the same database server. The suite still includes heavy sorts and simultaneous full-table scans.

Assertion: Separate schemas guarantee that adding workers cannot cause resource contention. Reason: Separate schemas can isolate object names while workers still share CPU, memory, and storage.

• ABoth A and R are true and R is the correct explanation of A
• BBoth A and R are true but R is NOT the correct explanation of A
• CA is true but R is false
• DA is false but R is true

A suite passes in its traditional serial order but fails under parallel execution. Engineers propose keeping serial mode permanently because it is reliable.

Which experiments can distinguish order dependence from concurrent resource or state interference? Select all that apply.

• Raise timeouts until the parallel run passes, then attribute the failures to resource contention.
• Run tests serially in reproducibly shuffled orders and record the order seed.
• Repeat the passing serial order from a clean fixture to establish that tests do not depend on predecessors.
• Run suspected tests concurrently in isolated databases and compare with shared-database runs.

A work-stealing runner balances most tests well, but two migration tests both modify a database-wide setting. They fail if they overlap, even though their tables are in separate schemas.

How should the runner schedule these tests?

• APlace them in different shards and rely on duration estimates to prevent overlap.
• BUse an explicit exclusive-resource constraint or give each a separate database instance.
• CStagger their starts by the slower test's recent average duration to avoid overlap.
• DRun them first on different workers before the remaining tests start.

A repository has a 5-minute pull-request budget and a 2-hour full SQL suite. Most changes affect a small set of stored procedures, but occasional engine upgrades affect the whole system.

Which tiering choices appropriately balance speed and coverage? Select all that apply.

• Run a small critical-path suite plus dependency-selected tests on ordinary pull requests.
• Run broader suites on a schedule and use stronger gates for database-engine upgrades.
• Track failures found only by broader tiers to evaluate gaps in fast-tier coverage.
• Use the same dependency-selected gate for engine upgrades when no application SQL files change.

A change modifies a shared SQL function used by billing, reporting, and exports. The selector currently chooses only tests whose file paths match the changed function's directory.

What is the best selection policy for this change?

• ATrust path matching because unchanged caller files imply unchanged caller behavior.
• BRun only direct unit tests because shared functions are covered most precisely in isolation.
• CExpand to known transitive consumers and use broader coverage where dependency information is incomplete.
• DRun a random sample of the normal size so the pull-request budget remains constant.

Four changes need coverage decisions: a query-comment edit with a verified no-op parse, a tax rounding fix, a database-engine major upgrade, and a rare archival workflow change whose full fixture takes 45 minutes to load.

Match each change to the most suitable coverage emphasis.

A selector has no dependency metadata for a newly added extension. The change modifies that extension's behavior, but the selector returns an empty test list.

Rather than interpreting an empty list as evidence of safety, use a ___ policy that runs broader coverage when impact is unknown.

A team moves expensive SQL cases to a nightly tier. Some of those cases use production-shaped data designed to expose cardinality-estimation errors.

Assertion: The nightly tier needs a named response owner and an explicit policy for handling failures. Reason: Production-shaped data can expose query-planning problems that tiny fixtures miss.

• ABoth A and R are true and R is the correct explanation of A
• BBoth A and R are true but R is NOT the correct explanation of A
• CA is true but R is false
• DA is false but R is true

The fast tier passes consistently, but nightly runs repeatedly find regressions in date-boundary handling. The relevant full tests are slow because they rebuild a large database, not because the boundary assertions themselves are expensive.

Which changes improve feedback without simply moving the entire nightly suite into every pull request? Select all that apply.

• Keep boundary coverage in the nightly tier and use faster failure notifications instead of changing test setup.
• Extract small deterministic date-boundary cases into the fast tier.
• Exclude recurring date-boundary failures from coverage-gap metrics so those metrics emphasize new gaps.
• Improve fixture reuse or setup isolation so focused boundary tests become cheap.

A team wants to validate a new dependency-based SQL test selector before relying on it to reduce pull-request runs.

Which evidence would support responsible adoption? Select all that apply.

• Replay historical regression-inducing changes and check whether their detecting tests would be selected.
• Measure the fraction of tests retained and use it as an estimate of retained fault detection.
• Run selection in shadow mode alongside broader coverage and inspect missed failures.
• Measure decision time and selected-suite latency alongside coverage effectiveness.

A report query has ORDER BY created_at, but several rows share the same timestamp. A baseline comparison intermittently reports those rows in different positions.

What is the best fix if the report contract requires a stable ordered result?

• AAdd a stable unique tie-breaker to the ordering and update the expected result after review.
• BSort both outputs by all returned columns before comparison, leaving the report query unchanged.
• CCompare rows as a multiset while checking separately that timestamps are in ascending order.
• DPin the expected row sequence to the current execution plan and refresh it when that plan changes.

A SQL test returns monetary totals, binary floating-point aggregates, and timestamps. CI workers use different default time zones, and parallel aggregation sometimes changes the least significant floating-point digits.

Which comparison controls are appropriate? Select all that apply.

• Apply one relative numeric tolerance across monetary totals, row counts, and floating-point aggregates.
• Set session time zone and other relevant formatting settings explicitly.
• Use justified absolute and relative tolerances for approximate numeric results.
• Keep exact comparison for values whose contract requires exact decimal equality.

Four result checks fail: Inventory contains the right rows in varying order, Payments differs by one cent in an exact decimal total, Search returns generated UUIDs, and SensorAvg varies only in tiny floating-point digits.

Match each check to the most appropriate comparison treatment.

A query's row order is irrelevant, but returning the same invoice twice is a defect. A proposed comparator converts both actual and expected rows to sets.

To ignore row order without hiding duplicate-row defects, compare the results as a ___ instead.

A query rewrite changes several golden result files. The author proposes accepting all generated files because they came from the newest implementation.

Assertion: The changed baselines require semantic review rather than automatic acceptance. Reason: A baseline produced by the implementation under test can preserve that implementation's defects.

• ABoth A and R are true and R is the correct explanation of A
• BBoth A and R are true but R is NOT the correct explanation of A
• CA is true but R is false
• DA is false but R is true

After a database upgrade, 300 SQL baselines change because of mixed formatting differences, plan-text changes, and a few changed result values.

Which baseline-management actions are responsible? Select all that apply.

• Classify differences and require justification for semantic changes before accepting them.
• Attach relevant engine, fixture, and comparison-setting versions to baseline changes.
• Approve the batch once repeated clean runs reproduce the new outputs consistently.
• Infer normalization rules from the changed outputs and apply them before reviewing semantic differences.

A comparator rounds every returned numeric column to two decimal places to eliminate noise. A probability calculation changes from 0.004 to 0.001, but the test still passes.

This comparator has created a ___: the test reports success even though the relevant output changed incorrectly.

A SQL test fails on 3% of first attempts and passes most retries. Failures show different row ordering, and the query has no ORDER BY despite sequence-based comparison.

Which actions address the problem without concealing it? Select all that apply.

• Record the first failure even when a retry passes.
• Count only failures that recur on retry, using repeatability to separate defects from harmless variation.
• Align the query and comparator with whether ordering is actually required.
• Retain failed outputs and execution context to confirm the suspected cause.

An intermittently failing SQL test blocks many unrelated changes. Its root cause is not yet known, but it covers a critical reconciliation invariant.

Which quarantine policy best preserves visibility while restoring useful CI feedback?

• ARun it only on reconciliation-related changes, relying on path selection to limit exposure until a fix is available.
• BMove it to a nonblocking lane and leave reactivation to the owning team's normal backlog prioritization.
• CKeep it blocking with three retries, publishing only the final outcome to avoid duplicate failure alerts.
• DRun it in a visible nonblocking lane with an owner, issue, deadline, and explicit risk treatment.

Four intermittent failures have clues: Expiry fails near midnight, Bootstrap queries before migrations finish, Settings fails only after another test changes search_path, and BulkLoad times out during high shared-disk activity.

Match each test to the most targeted investigation or repair.

A randomly generated SQL fixture fails once, but the next CI run generates different rows and passes. The logs contain the generator version but not the input that initialized its pseudorandom sequence.

To make the failing fixture reproducible, record the random ___ alongside the generator version and environment.

After a suspected race fix, a test passes 20 repeated runs. Its historical failure frequency was approximately one in 200 runs.

Assertion: Twenty passing runs alone provide weak evidence that this rare failure has been eliminated. Reason: A flaky test must fail at least once in every batch whose size equals the inverse of its failure probability.

• ABoth A and R are true and R is the correct explanation of A
• BBoth A and R are true but R is NOT the correct explanation of A
• CA is true but R is false
• DA is false but R is true

A quarantined SQL test was failing because two sessions raced to create the same resource. A fix introduces explicit synchronization, and the team is deciding whether to restore the gate.

Which evidence supports removing quarantine? Select all that apply.

• The full suite passes locally after the fix, with the two sessions executed sequentially.
• A reproducer fails before the fix and passes after it under the relevant concurrency.
• The test now accepts either session's resource-creation error as long as the resource eventually exists.
• Repeated targeted runs and CI evidence show the intended synchronization without new coverage loss.

A test began failing after an optimizer change. It now fails on the same fixture and assertion in every clean rerun, although it passed on the previous revision.

How should the team initially classify this evidence?

• AAs a reproducible regression candidate requiring investigation, not a flake merely because it is newly failing.
• BAs an intermittent test candidate because its pass/fail history varies across revisions, warranting quarantine first.
• CAs a performance-only plan change, with result differences provisionally attributed to test-environment variation.
• DAs a baseline-maintenance issue that should be resolved by accepting the latest output.

A pipeline spends 25 minutes provisioning a large SQL fixture before running a 2-minute syntax and migration-sanity stage. Many pull requests fail that short stage.

Which pipeline redesign most directly improves time to actionable failure?

• ARun all stages concurrently against the same writable database to avoid sequencing delays.
• BMove migration-sanity checks to nightly runs while retaining the full pull-request regression suite.
• CRun cheap independent validation first and start expensive provisioning only when warranted.
• DIncrease the final job timeout so provisioning variability does not interrupt validation.

A query benchmark varies by 15% on shared CI workers. A proposed gate fails every pull request whose single measured runtime exceeds the last successful run by 5%.

Which changes would make the performance gate more credible? Select all that apply.

• Use repeated measurements and evaluate both effect size and measurement variability.
• Compare candidate and reference under controlled, comparable resource and cache conditions.
• Keep single-run comparisons but use increased estimated plan cost to confirm timing-based failures.
• Use stable benchmark fixtures and record relevant environment versions.

Four CI failures need different evidence: PlanRegression changes a join strategy, SnapshotSetup fails restoring a fixture, LockHang times out waiting for another session, and WrongRows returns unexpected values.

Match each failure to the most useful primary artifact bundle.

A disposable-database job must restore a fixture before running tests. Failure diagnostics need the live database, teardown destroys it, and the job's final exit must reflect the saved test status even after cleanup.

Arrange these job actions in the order required by those dependencies.

CI caches restored SQL fixture images to reduce setup time. A schema migration changes, but a worker restores an older cached image and reports confusing missing-column failures.

Which controls address this cache failure? Select all that apply.

• Key the cache using fixture inputs, migration state, and compatible engine or restore-tool versions.
• Use a short cache expiration period as the validity check, rebuilding images when that period expires.
• Store provenance and verify expected schema or fixture identity after restore.
• On a cache hit, skip migration checks because the image was valid when first created.

The full SQL suite takes two hours, while useful database diagnostics disappear when the CI platform kills a job at its hard timeout. Some individual tests occasionally hang.

Which timeout design best preserves both bounded execution and diagnostic value?

• AUse only the platform timeout so every test receives the maximum possible runtime.
• BUse measured per-test or stage deadlines plus an overall budget, leaving time to capture diagnostics and clean up.
• CUse a suite-level watchdog that restarts timed-out jobs from the beginning and retains the final attempt's logs.
• DApply one timeout derived from suite-wide duration percentiles to every test, relying on normal teardown for diagnostics.

A schema change makes a benchmark's fixture restore 30 seconds slower, but the measured query still executes in 2 seconds. The gate claims a query-performance regression because its timer surrounds setup and execution together.

To distinguish query performance from fixture cost, add separate phase-level ___ rather than relying only on the job timer.

A team cuts median SQL suite runtime from 20 to 12 minutes. At the same time, the 95th-percentile runtime rises to 50 minutes and rerun-related database spending doubles.

Which conclusions and follow-up measures are justified? Select all that apply.

• The median improvement does not establish that developer feedback improved for slow runs.
• The suite is healthier overall because median runtime is the least noisy summary.
• Track tail latency, retry volume, and resource cost alongside median runtime.
• Separate first-attempt execution from retries and queue time to explain the change.

A test dashboard shows 40 failures this month versus 20 last month. The test ran 4,000 times this month and 500 times last month under comparable conditions.

What does this evidence show about the observed per-execution failure rate?

• AIt doubled, with the increase in failure count indicating a higher per-execution risk.
• BIt should be reported as unchanged until a significance test confirms a difference.
• CIt must be normalized by execution duration before the monthly rates can be estimated.
• DIt fell from 4% to 1%, despite the higher absolute failure count.

A suite dashboard shows four trends: shard imbalance is increasing, quarantine cases are aging, database provisioning dominates latency, and defects repeatedly escape dependency-selected runs.

Match each trend to the most appropriate governance response.

A dashboard mixes timings from large dedicated database workers and small shared workers. After more jobs move to the smaller pool, the overall average worsens even though timings within each pool are stable.

Before attributing the change to SQL code, segment runtime metrics by worker or environment ___ to avoid mixing unlike execution conditions.

A team is evaluating duration-based sharding through a staged rollout. Its review process requires a comparable baseline before rollout, comparable observations after rollout, and an evidence-based decision before making the change permanent.

Arrange these evaluation actions in their required order.

Management sets a goal of reducing SQL test failures by 50%. One proposal is to remove unstable tests and report only the final result after retries.

Which companion metrics would help reveal whether apparent improvement comes from hiding detection rather than improving health? Select all that apply.

• Final green-build percentage, broken down by branch and change category.
• First-attempt failures and the number of retry attempts.
• Quarantined or disabled coverage and the age of those exceptions.
• Successful assertion volume, used as a proxy for coverage retained after test removal.

An engineer can either save 10 seconds from a test run 2,000 times per week or save 5 minutes from a test run 10 times per week. The changes have similar implementation cost and risk, and neither test is on a specially weighted critical path.

Which prioritization is best supported if the immediate goal is aggregate weekly execution-time savings?

• APrioritize the frequently run test: it offers about 333 minutes saved per week versus 50 minutes.
• BPrioritize the slower test because per-run duration is the strongest measure of optimization value.
• CTreat the opportunities as equivalent after normalizing savings by each test's execution frequency.
• DPrioritize whichever test has the larger percentage speedup after measuring their original durations.