70 questions / 10 random questions
Random questions, instant feedback, and review for missed questions.
Which pair should be agreed with business stakeholders first when defining database disaster-recovery requirements?
Answer: Tolerable data loss (RPO) and tolerable downtime (RTO)
RPO and RTO provide the basis for backup frequency, log retention, replication, recovery sites, and cost. Agree on them as measurable business requirements.
Daily backup jobs report success. What is the most reliable way to confirm recoverability?
Answer: Restore regularly into an isolated environment and verify
Backup success does not guarantee restore success. A restore drill validates media, keys, permissions, procedures, and dependencies while measuring achieved RTO and recovery point.
Which statement correctly describes the relationship between high availability and backups?
Answer: HA limits downtime; backups cover the recovery scope
Replication and clustering improve availability, but accidental deletes, ransomware, and logical corruption may propagate. Combine HA with independent backup and restore.
Which backup-storage design appropriately addresses ransomware and administrator mistakes?
Answer: Keep multiple immutable generations in a separate failure domain
Separate accounts, regions, or media so production credential compromise cannot immediately delete backups. Combine immutable retention with restore testing.
Which backup format is generally easier when moving selected schemas to a different database engine?
Answer: A logical export containing DDL and data
Logical exports support selective migration and transformation, though types, collations, sequences, privileges, and procedures still require mapping and validation.
What is important when backing up multiple files of a running database with storage snapshots?
Answer: Take it following the engine's consistency procedure
Verify engine support for crash consistency, atomic multi-volume capture, and required logs. Uncoordinated file copies can create an inconsistent backup.
A database must be restored to just before an accidental delete at 14:37. What does PITR require?
Answer: A base backup plus an unbroken log chain
PITR replays WAL, binary logs, transaction logs, or archived redo from a base backup. A gap prevents recovery beyond that point.
Which key-management practice is appropriate for encrypted backups stored remotely?
Answer: Manage the key in a failure domain separate from the backups
Encryption becomes unrecoverable if keys are lost. Separate access with a KMS and include old-key availability and disaster key recovery in restore drills.
Which metrics should be prioritized for cross-platform monitoring of asynchronous replication health?
Answer: Apply-position gaps, time lag, queue volume, and errors
Separate transport lag from apply lag. Monitor positions, queues, worker errors, and trends rather than relying on one seconds-behind value.
A standby is connected with low lag. What else should be checked before declaring it failover-ready?
Answer: Regularly rehearse promotion through old-primary isolation
Synchronization alone does not guarantee service recovery. A failover drill must test role change, routing, credentials, application reconnection, and fencing to measure actual RTO.
A primary site is completely lost in an asynchronous replication setup. Which risk assessment is correct?
Answer: Unapplied transactions may be lost
Asynchronous replication usually lowers latency in exchange for a possible data-loss window. Determine the last durable or applied position and communicate any gap from the declared RPO.
What is the core control for preventing split brain where both old and new primaries accept writes after a network partition?
Answer: Use quorum and fencing to stop one side's writes
Fence the old primary from storage, network, or process access before promotion to preserve a single writer. Unreachable does not necessarily mean stopped.
Which connection design minimizes application changes after failover?
Answer: Use a role-aware endpoint with reconnection
Abstract routing with a listener, cluster endpoint, proxy, or DNS, while applications safely recreate connections and retry transactions. Do not rely on DNS TTL alone.
Which preparation is appropriate before a planned primary-standby switchover?
Answer: Verify sync and switch criteria, then do it in a change window
A planned switchover verifies synchronization and dependent jobs, suppressing alerts only for the maintenance window. Validate writes, reads, jobs, backups, and monitoring afterward.
Which option can mitigate the risk of a large accidental delete immediately reaching ordinary replicas?
Answer: Keep a delayed replica or a PITR-capable log archive
A delayed replica creates a detection window for logical mistakes but is not the freshest failover target. Combine it with PITR, immutable backups, and access control.
A query sent to a read replica immediately after a write returns stale data. What is the appropriate design response?
Answer: Route reads that follow a write to the primary
Asynchronous replicas provide eventual consistency. Define tolerated staleness per workflow and use session stickiness, position waiting, or primary reads as needed.
What is the best first approach for investigating performance problems across different database products?
Answer: Compare current load and waits against baselines
Start with common signals showing what waited, when degradation began, and under which workload. Then drill into engine-specific wait events and query stores.
A query suddenly became slow. What should be checked when comparing execution plans?
Answer: Check estimated versus actual rows, join order, and changes
Use runtime evidence and change history to find cardinality-estimation or plan changes. Plan forcing can mitigate an incident but needs scope and exit criteria.
Which policy is appropriate when standardizing statistics and index maintenance across database products?
Answer: Understand each product's automation and target by measurement
Excessive maintenance increases I/O, logging, replication lag, and locking. Understand engine automation and online features, then act based on measured benefits and side effects.
Blocking sessions increase and cause application timeouts. What is the appropriate first response?
Answer: Identify the blocking chain and relieve the minimum scope
Identify the root blocker and business operation before choosing cancellation, rollback, or traffic control. Preserve evidence and fix long transactions or access ordering afterward.
Increasing application instances exhausted the database connection limit. What is an appropriate response?
Answer: Set a connection budget and tune pool limits and timeouts
Pool size multiplies by instance count. Budget against database workers, memory, and workload, using queues and admission control to protect the database during spikes.
Long-running transactions prevent MVCC cleanup or transaction-log reuse. What is an appropriate long-term fix?
Answer: Shorten transactions and add idle-in-transaction timeouts
Long transactions retain old versions, undo, WAL or logs, and locks. Redesign application boundaries and add attributable monitoring with safe timeouts.
Database latency rises while CPU remains low. What should be checked next?
Answer: Check storage I/O, log flushes, and lock waits
A database may consume little CPU while waiting on I/O, log flushes, network, or locks. Correlate host, storage, and engine waits over the same time window.
Non-data-file space is growing rapidly and disk exhaustion is near. Which cross-platform investigation is appropriate?
Answer: Break growth down by source such as logs and temp space
Space growth can signal backup failure, stopped replicas, long transactions, large sorts, or broken log rotation. Create emergency headroom while removing the cause.
Which privilege design is appropriate for an application database account?
Answer: Grant only the operations each business role needs
Separate runtime, migration, monitoring, and backup roles, using secret management, short-lived credentials, and rotation. Review and audit privilege changes.
Which break-glass process is appropriate for powerful database privileges used only during emergencies?
Answer: Issue approved, time-bounded access and record it
Break-glass is an exception when normal paths are unavailable. Require identity, reason, approval, expiry, session recording, notification, and reliable revocation afterward.
Which policy is appropriate when designing database audit logs?
Answer: Define high-risk events with tamper resistance and retention
Select events from audit objectives and threat models while minimizing sensitive values. Centralize logs with time sync, access control, tamper detection, and searchable common fields.
What preparation supports rotating database TLS certificates without downtime?
Answer: Overlap old and new trust and rotate in stages
Servers and many clients cannot always change simultaneously, so use time-bounded overlap. Canary connections must preserve hostname validation and encryption before old keys or CAs are removed.
A NOT NULL column must be added to a large table with near-zero downtime. Which migration pattern is generally safer?
Answer: Add it nullable, backfill, then enforce the constraint
Create a compatibility window between schema and application and throttle backfill. Test product-specific online DDL, locks, logging, and replication lag.
What is an appropriate change plan for adding an index to a multi-terabyte table?
Answer: Test online features and extra space, then stage the change
Even online DDL affects CPU, I/O, logs, and may take short locks. Measure with production-scale data and define traffic controls, stop conditions, and post-change plan checks.
Which compatibility areas deserve close review before migrating PostgreSQL to another engine?
Answer: Differences in data types, collation, time zones, and SQL dialect
Convertible syntax does not guarantee equivalent semantics. Test empty strings versus nulls, timestamps, decimals, sorting, booleans, generated keys, and transaction behavior.
Which general approach supports moving a large database to a different engine with short downtime?
Answer: Bulk-load, sync changes with CDC, then cut over
Separating bulk load from change data capture limits downtime to the final delta. Manage DDL, ordering, duplicates, deletes, retries, and CDC retention.
Which method appropriately validates data consistency before and after migration?
Answer: Compare counts, checksums, and business invariants in stages
One metric can hide omissions, duplicates, or conversion errors. Prioritize critical tables and business rules, align snapshot points, control load, and record reproducible differences.
What should be predefined as rollback criteria for a database migration?
Answer: Error and difference thresholds plus the last reversible point
Define go or no-go and rollback with metrics, owners, and deadlines. Once new writes reach the target, reverse synchronization may be required, so identify the point of no return.
Which preparation is appropriate before a major database-version upgrade?
Answer: Rehearse the upgrade on a production-like copy
Major upgrades can change SQL, drivers, extensions, authentication, optimizers, and parameter defaults. Inventory deprecated features and run canary and performance-regression tests.
How should configuration drift be controlled across many database instances?
Answer: Version policies and detect drift
Define common intent for encryption, logging, backups, timeouts, and similar controls, then map it to each product. Exceptions need owners, reasons, and expiry.
Which information should be prioritized in an inventory for managing database-fleet risk?
Answer: Owner, criticality, version, RPO/RTO, and dependencies
Databases without clear ownership and lifecycle fall through patching, backup, and retirement processes. Reconcile inventory with discovery and prioritize EOL or unprotected instances.
Which design supports monitoring PostgreSQL, MySQL, SQL Server, and Oracle in one platform?
Answer: Standardize on a common model, adding product adapters for detail
Compare fleet health through common service signals, then drill into product-specific waits, buffers, logs, and replication workers. Tune thresholds to SLOs and baselines.
A database restart is the leading temporary mitigation during an incident. What should be done before executing it?
Answer: Preserve session and wait evidence and communicate impact
A restart may erase evidence and in-memory state. Runbooks should define quick evidence capture without delaying recovery, followed by symptom, integrity, and recurrence checks.
How should cross-platform database runbook quality be continuously improved?
Answer: Measure it in drills and incidents and feed back improvements
Treat runbooks as executable products. Include prerequisites, stop conditions, escalation, vendor differences, validation, and rollback, and test whether another operator can complete them.
A team plans to stop restore tests because backup manifest and checksum verification succeed. What is the correct decision?
Answer: Keep checksum checks and also continue periodic restores
Checksums and manifests detect missing or altered files, but do not prove engine startup, log replay, key access, permissions, or business consistency. Restore drills also measure RTO.
A full backup is valid, but one transaction-log backup in the chain is missing. Can you perform PITR to a time after the gap?
Answer: Cannot cross the gap; start from another base backup
PITR requires an unbroken sequence of WAL, binary logs, or transaction logs from a base backup to the target time. Continuously monitor chain completeness and retention.
An offline replica keeps retaining logs until the primary WAL or binary-log volume is nearly full. What is the core control?
Answer: Bound slot lag and retention with a release procedure
Retention for an offline replica must not take down the primary. Monitor age, bytes, and disk headroom, and set bounds from RPO and reseed time.
A two-site database cluster must avoid dual primaries during a network partition. Which quorum design is appropriate?
Answer: Odd votes or an independent witness so only the majority writes
Quorum protects single-writer safety before availability. Keep witness network, power, and administration independent and exercise site-loss and partition matrices.
The former primary site returns after failover to the disaster-recovery site. What should happen first?
Answer: Keep the old primary fenced, assess divergence, and resync
The old primary may have a divergent history after failover. Fence writes, compare timelines, GTIDs, or LSNs, then reinstate it as a standby.
After a forced failover to an asynchronous replica, some clients cannot see transactions that previously returned success. What is appropriate?
Answer: Compare old and new positions and reconcile the loss window with business records
An asynchronous failover may lose acknowledged transactions not yet received by the replica. Use idempotency keys, business ledgers, and message records to control loss and duplication.
After a deployment, latency for the same SQL rose sharply and its execution plan changed. What response also helps prevent recurrence?
Answer: Compare plan history with deploy time and pin a plan temporarily
Plan regressions are hard to reconstruct without history. Correlate plan hashes, runtime, rows, waits, and deployments; treat plan forcing as temporary and reevaluate data distribution.
Parameter values change selectivity from a few rows to millions, making a reused plan unstable. What is the right approach?
Answer: Compare plans and statistics by parameter range and act
Measure whether one plan fails across selectivity ranges rather than relying on a label such as parameter sniffing. Bucket or redact sensitive parameter values.
Sort and hash operations spill to temporary storage and database latency rises. What is appropriate?
Answer: Correlate spill volume with row estimates and memory settings
Spills can indicate statistics errors, poor plans, missing indexes, oversized intermediates, or memory-grant issues. Validate global memory changes against concurrency.
Deadlock errors are being investigated exactly like ordinary long blocking. What distinction is appropriate?
Answer: Analyze deadlocks and blocking with different indicators
A deadlock is a wait cycle resolved by choosing a victim. Standardize lock order, shorten transactions, and retry only idempotently with limits and jitter.
Two transactions each saw another doctor on call, updated different rows, and left nobody on call. What is the core control?
Answer: Protect the invariant with serializable isolation or explicit locks
Write skew can violate a set-level invariant even when transactions update different rows. Test the engine's actual isolation and conflict behavior, not just its level name.
The connection pool stays at its maximum while database CPU remains low. What should be checked next?
Answer: Trace acquisition waits and connection leaks
Connection exhaustion can come from waits or application leaks without high CPU. Correlate acquisition time, hold time, queries, and transaction owners, then fix timeouts and lifecycle.
During CDC-based cross-engine synchronization, adding a source column stopped a consumer. What is the core prevention?
Answer: Version schema compatibility rules and update consumers first
DDL is an interface change in a CDC pipeline. Design additive evolution, defaults, nullability, rename and drop windows, schema registry, DLQ, and replay procedures.
During a cross-engine migration, application dual writes sometimes succeed on one database and fail on the other. What is appropriate?
Answer: Apply from the source of truth to the target idempotently via CDC
Synchronous writes to independent databases are not atomic. Derive a replayable change stream from the source commit and control loss and duplication with idempotency and reconciliation.
After data migration, the target identity or sequence is below existing IDs and new inserts collide. What should be done before cutover?
Answer: Advance the sequence past the maximum used ID
Copying rows may not migrate generator state. Include ownership, increment, cycling, caching, and post-failover behavior in the cutover checklist.
After a cross-engine migration, string uniqueness and sorting changed, and timestamps shifted by hours. What pre-migration validation is appropriate?
Answer: Map collation and time zones and scan every row
Identical text and timestamps can compare, store, and render differently across engine settings and type semantics. Produce conflict reports and agree on business conversion rules.
A missing WHERE tenant_id clause exposed another tenant's rows in a multitenant database. What is the core prevention?
Answer: Enforce the boundary in the database with row-level security
Do not rely only on every query being written correctly. Combine database policy, least privilege, pooled-context reset, negative tests, and auditing.
A team enabled TDE and concludes that TLS and column-level sensitive-data controls are unnecessary. What is correct?
Answer: TDE protects data at rest; transport and access need separate controls
Encryption controls serve different threat boundaries. Design at-rest, in-transit, data-in-use, administrator access, and key backup, rotation, and recovery separately.
Database application credentials must rotate without downtime. Which migration pattern is safe?
Answer: Enable the new credential first, then revoke the old one
Manage a short overlap window and automate pool reconnection, rollback conditions, auditing, and revocation verification. Prefer short-lived tokens or managed identity where available.
After a database incident, clock differences across database, application, and cloud audit logs prevent timeline reconstruction. What prevents recurrence?
Answer: Synchronize clocks and correlate evidence by trace ID
An accurate timeline underpins root cause, data-impact, and audit decisions. Test clock-drift alerts, tamper-resistant retention, access controls, and evidence export procedures.
In MySQL 8.4 with InnoDB, a transaction runs UPDATE followed by ALTER TABLE on a normal table. What is wrong with assuming a later ROLLBACK always undoes the UPDATE?
Answer: ALTER TABLE can implicitly commit the preceding update.
Normal ALTER TABLE causes an implicit commit. Do not generalize transactional DDL behavior across engines; check statement-specific boundaries and recovery plans.
An operator proposes running EXPLAIN ANALYZE UPDATE in production PostgreSQL. What must be understood before execution?
Answer: It executes the statement, so assess updates and other side effects.
EXPLAIN ANALYZE executes the statement. Prefer a test environment and assess locks, load, and triggers before production use. Explicit rollback does not necessarily undo every side effect.
A query uses id NOT IN (SELECT customer_id FROM excluded), where id is NOT NULL. Results disappear after a NULL enters excluded. Which fix fits?
Answer: Use correlated NOT EXISTS to test for the absence of a matching row.
A NULL in NOT IN can make a non-match unknown, which WHERE rejects. Correlated NOT EXISTS with equality checks whether the non-null customer ID has a matching excluded row.
A migration check uses COUNT(email) on a nullable email column and reports fewer rows than expected. What explains this and fixes the full-row count?
Answer: COUNT(email) skips NULL values; use COUNT(*) to count every row.
COUNT(expression) counts non-null values, whereas COUNT(*) counts rows. Compare total rows and null counts over matching scopes; equal counts alone do not prove equal contents.
An INSERT using PostgreSQL nextval is rolled back, leaving an ID gap. Why is a gap alone insufficient evidence of lost data?
Answer: Sequence values are not rolled back, so normal activity can leave gaps.
Values obtained from nextval are not reclaimed on transaction abort. Separate gapless business-number requirements from internal IDs and investigate loss using business and audit records.
One task changes a pooled connection's session time zone. The pool does not reset this setting on return. How should leakage into the next task be prevented?
Answer: Define initialization or scoped settings and test connection reuse.
Reused physical connections can retain unreset session state. Verify the engine, driver, and pool contract and establish baseline or transaction-scoped settings where supported.
A PostgreSQL Serializable transaction aborts with SQLSTATE 40001. It chose an update quantity based on inventory it read. What should a retry include?
Answer: Start a new transaction and repeat its reads, decisions, and writes.
Retry the complete transaction, including reads and decision logic, with bounded attempts. External side effects need their own duplicate-prevention design.
An unchanged table is paginated using only created_at, and rows sharing a timestamp move across page boundaries. How should a unique ordering be established?
Answer: Add a unique id to ORDER BY to resolve timestamp ties.
created_at alone leaves ties unordered. ORDER BY created_at, id resolves them. Concurrent changes require additional pagination consistency design.
A PostgreSQL table allows multiple NULLs under a default single-column UNIQUE constraint. It is moving to an ordinary SQL Server single-column UNIQUE constraint. What must be checked?
Answer: Review NULL uniqueness differences and the target data and constraint design.
PostgreSQL's default treats NULLs as distinct, while an ordinary SQL Server single-column UNIQUE allows one NULL. Consider a filtered unique index for non-null values if that matches the requirement.
The connection drops just after sending COMMIT for an order, before its result arrives. What should the client do to avoid a duplicate order?
Answer: Treat the outcome as unknown, reconcile by business key, and recover idempotently.
Missing acknowledgement does not reveal commit outcome. Reconcile using a stable business or idempotency key and an authoritative view, and enforce uniqueness so recovery remains safe even with an unresolved original attempt.