Question 1

How do you read and interpret a PostgreSQL EXPLAIN ANALYZE output to identify query performance bottlenecks?

Accepted Answer

Reading execution plans is one of the most critical skills for a mid-level DBA focused on performance tuning. EXPLAIN ANALYZE in PostgreSQL executes the query and provides detailed timing and row count information for each step in the execution plan, allowing you to pinpoint exactly where time is being spent.

The output is a tree of plan nodes, read from the innermost node outward and from bottom to top. Each node represents an operation like a sequential scan, index scan, hash join, or sort. For each node, the plan shows the estimated cost as a range from startup cost to total cost, the es

Question 2

What role do database statistics play in query optimization, and how do you ensure they are accurate?

Accepted Answer

Database statistics are the foundation of the query optimizer's decision-making process. Without accurate statistics, even the most sophisticated optimizer will choose poor execution plans. As a mid-level DBA, ensuring statistics are current and accurate is one of the most impactful performance tuning activities.

The query optimizer uses statistics to estimate the number of rows that will be returned by each operation in a query plan. These estimates drive decisions about which indexes to use, which join algorithm to select, and in what order to join tables. When statistics are stale or ina

Question 3

What is database replication, and what are the differences between synchronous and asynchronous replication?

Accepted Answer

Database replication is the process of copying and maintaining data across multiple database servers to provide redundancy, improve availability, and distribute read workloads. Understanding replication modes is fundamental for a mid-level DBA responsible for maintaining highly available database environments.

In asynchronous replication, the primary server commits transactions and sends the changes to replicas without waiting for confirmation that the replica has received or applied them. The primary operates at full speed because it never waits for the replica. The trade-off is that there

Question 4

How do you set up streaming replication in PostgreSQL, and what are the key components involved?

Accepted Answer

Streaming replication in PostgreSQL creates a real-time copy of the primary database on one or more standby servers. It works by continuously streaming Write-Ahead Log records from the primary to the standbys, which apply them to maintain an up-to-date copy. Setting up streaming replication is a core skill for a mid-level DBA.

On the primary server, several configuration changes are needed. Set wal_level to replica or logical, which determines how much information is written to the WAL. The replica level includes enough information for physical replication. Set max_wal_senders to at least t

Question 5

What is pgBackRest, and what advantages does it offer over pg_dump and pg_basebackup for enterprise backup management?

Accepted Answer

pgBackRest is a purpose-built backup and restore solution for PostgreSQL that addresses the limitations of built-in tools for enterprise environments. Understanding why pgBackRest is preferred over pg_dump and pg_basebackup is important for a mid-level DBA managing production databases at scale.

pg_dump creates logical backups that are portable and support selective restoration but are slow for large databases because they process every row through SQL. pg_basebackup creates physical base backups by copying the data directory but only supports full backups, meaning every backup copies the e

Question 6

What is the difference between Recovery Point Objective and Recovery Time Objective, and how do they drive backup architecture decisions?

Accepted Answer

Recovery Point Objective and Recovery Time Objective are the two most important metrics that define a disaster recovery strategy. Every backup architecture decision should trace back to these business requirements. A mid-level DBA must be able to translate RPO and RTO requirements into concrete technical implementations.

RPO defines the maximum acceptable amount of data loss measured in time. An RPO of one hour means the organization can tolerate losing up to one hour of data in a worst-case scenario. An RPO of zero means no data loss is acceptable under any circumstances. RPO directly dete

Question 7

What is Transparent Data Encryption (TDE), and why is it important for database security?

Accepted Answer

Transparent Data Encryption, commonly referred to as TDE, is a technology that encrypts database files at the storage level, protecting data at rest without requiring changes to application code. It is called "transparent" because the encryption and decryption happen automatically as data is written to and read from disk, making the process invisible to applications and users.

TDE works by encrypting the database data files, log files, and backup files using a database encryption key (DEK). This DEK is itself protected by a certificate or asymmetric key stored in the master database (in SQL

Question 8

What is the principle of least privilege, and how do you apply it in database access management?

Accepted Answer

The principle of least privilege states that users and applications should be granted only the minimum permissions necessary to perform their specific tasks. In database management, this means giving each user or service account access to only the tables, views, stored procedures, and operations they actually need, rather than granting broad administrative or read-write access.

Applying least privilege in databases starts with creating specific roles for different job functions. Instead of giving every developer full database access, you create roles like read_only_reporting, app_read_write

Question 9

What are Dynamic Management Views in SQL Server, and how do you use them for database monitoring? Describe the most useful DMVs you rely on regularly.

Accepted Answer

Dynamic Management Views, commonly referred to as DMVs, are system views in SQL Server that expose internal state information about the server and database engine. They provide real-time insight into what the server is doing at any given moment, making them indispensable for monitoring, troubleshooting, and performance tuning.

DMVs fall into two broad categories: server-scoped DMVs that provide information about the entire SQL Server instance, and database-scoped DMVs that focus on a specific database. The naming convention uses the prefix sys.dm_ followed by a category and specific view na

Question 10

Explain what wait statistics are in database systems. How do you interpret common wait types to diagnose performance bottlenecks?

Accepted Answer

Wait statistics are a fundamental diagnostic tool that tells you what a database server spends its time waiting on. Every time a worker thread in SQL Server cannot proceed immediately, it records a wait event with a type and duration. By analyzing the accumulation of these waits over time, you can identify the primary bottlenecks limiting your server performance.

The concept is straightforward: if a query needs data from disk but it is not in memory, the thread waits for the IO operation and records a PAGEIOLATCH wait. If a query needs to acquire a lock held by another transaction, it recor

Database Administrator

Topics

Performance Tuning & Query Optimization

High Availability & Replication

Advanced Backup & Disaster Recovery

Database Security & Compliance

Database Monitoring & Troubleshooting

Mock Interview

Quick Stats