DEV Community

Cover image for The main tasks of a Database Administrator (DBA) by 2025
Digital Pollution profile image Leandro Nuñez
Leandro Nuñez for Digital Pollution

Posted on

The main tasks of a Database Administrator (DBA) by 2025

#database #dba #sql

TL;DR

The Database Administrator (DBA) role has evolved far beyond backups and index rebuilds. In 2025, DBAs are data reliability engineers who ensure databases are secure, recoverable, performant, and aligned with business goals.

This guide explores:

  • Core DBA responsibilities.
  • Common mistakes (and how to prevent them).
  • SQL Server vs. PostgreSQL practices.
  • The evolving role of DBAs in cloud and DevOps environments.

Table of Contents

  1. Introduction: Why DBAs Still Matter in 2025
  • 1.1. The Origins of the DBA Role
  • 1.2. The Cloud and the “Death of the DBA” Myth
  • 1.3. SQL Server and PostgreSQL DBA Context

  • 1.4. Why DBAs Still Matter in 2025

    1. Core Responsibilities of a DBA

  • 2.1. Database Installation and Upgrades

  • 2.2. Security and User Management

  • 2.3. Backup and Recovery Strategies

  • 2.4. Performance Monitoring and Tuning

  • 2.5. Schema and Data Modeling Support

    • Clustered vs. Nonclustered Indexes
    • Filtered Indexes
    • Computed Columns and Indexing Them
    • Indexed Views
    • Enforcing Data Integrity
    • Denormalization: When to Break the Rules
    • SQL Server vs. PostgreSQL Schema Design Philosophies

  • 2.6. High Availability and Disaster Recovery

    • SQL Server HA/DR Options
    • PostgreSQL HA/DR Options
    • HA vs. DR in Practice
    • Designing HA/DR Strategies with RPO/RTO
    • Comparing SQL Server and PostgreSQL HA/DR Philosophies
    • Real-World Scenario: Disaster Simulation and Recovery
    • Cost vs. Complexity Trade-Offs in HA/DR

  • 2.7. Automation and Scripting

  • 2.8. Supporting Developers & Query Optimization

    1. Common Mistakes DBAs See (and How to Avoid Them)
    2. The Evolving Role of DBAs: Cloud, DevOps, and Intelligent Databases

  • 4.1. The Cloud DBA

  • 4.2. The DevOps DBA

  • 4.3. The Intelligent DBA

  • 4.4. Real-World Examples of Role Evolution

    1. Conclusion: The DBA’s Real Mission
    2. References & Further Reading

1. Introduction: Why DBAs Still Matter in 2025

For decades, the Database Administrator (DBA) has been a central figure in IT organizations. Yet with the rise of cloud services, automation, and “self-healing databases,” the DBA role has been declared obsolete more times than we can count.

The truth? DBAs are not disappearing — they are evolving.

1.1. The Origins of the DBA Role

The DBA role began in the 1970s and 1980s, with early relational databases like IBM DB2 and Oracle. Back then, DBAs:

  • Tuned disk layouts by hand.
  • Managed tiny amounts of extremely expensive storage.
  • Performed backups on tape, often spending nights and weekends ensuring jobs completed.

In the 1990s and 2000s, as SQL Server, Oracle, and MySQL became mainstream, DBAs became the gatekeepers of enterprise data. They designed schemas, wrote stored procedures, maintained indexes, and handled recovery when things broke.

If data was the crown jewel, DBAs were the knights guarding the vault.

1.2. The Cloud and the “Death of the DBA” Myth

The 2010s brought cloud adoption. AWS RDS, Azure SQL Database, and Google Cloud SQL marketed themselves as “DBA-free” solutions:

  • Backups run automatically.
  • Patching happens in the background.
  • Failover is built in.

For many managers, this meant: “We don’t need DBAs anymore.”

But what cloud actually removes are the infrastructure tasks. It does not:

  • Validate that backups can be restored.
  • Ensure queries scale under concurrency.
  • Implement business-specific compliance policies (GDPR, HIPAA, PCI-DSS).
  • Educate developers about writing SARGable queries.

The idea that cloud replaces DBAs is a myth. Instead, it has shifted their responsibilities.

1.3. SQL Server and PostgreSQL DBA Context

The DBA’s role also depends on the database platform:

  • SQL Server DBAs often work in enterprises with high regulatory requirements and mission-critical OLTP workloads. They handle Always On Availability Groups, Query Store, policy enforcement, and licensing decisions that directly affect business cost.

  • PostgreSQL DBAs are more common in startups, SaaS businesses, and cost-sensitive enterprises. They tune autovacuum, manage streaming/logical replication, and support modern workloads with JSONB, extensions, and cloud-native tools like Patroni or pgBackRest.

Different ecosystems, same core mission: make data reliable.

1.4. Why DBAs Still Matter in 2025

In 2025, DBAs:

  • Spend less time on manual maintenance.
  • Spend more time on automation, cloud strategy, and DevOps pipelines.
  • Act as bridges between developers, operations, and business stakeholders.

The DBA’s mission is not about maintaining servers — it is about ensuring trust in data.

When disaster strikes, executives don’t ask about query plans or index fill factors. They ask:

  • “Can we get the data back?”
  • “How fast can we be online again?”
  • “Is our data safe from breach or corruption?”

The DBA is the person who can confidently answer yes.

2. Core Responsibilities of a DBA

The day-to-day work of a DBA is a mix of routine maintenance, proactive tuning, and emergency firefighting. But while tasks vary by organization and technology stack, the core responsibilities remain consistent across SQL Server, PostgreSQL, and other relational platforms.

Let’s explore them one by one.

2.1. Database Installation and Upgrades

The DBA’s work often begins before the first row of data is ever inserted: installing, configuring, and upgrading database systems.

SQL Server

In SQL Server, installation is deceptively simple. The wizard lets you click through defaults, but DBAs know better:

  • Data and log files should live on separate storage volumes for performance and recovery.
  • TempDB should be pre-sized with multiple files to avoid contention.
  • Service accounts should follow the principle of least privilege, not run as LocalSystem.

Upgrades require planning. In-place upgrades (e.g., SQL Server 2017 → 2019) are faster but riskier. Many enterprises prefer side-by-side migrations, allowing rollback if issues appear.

Example: Checking SQL Server version post-upgrade

SELECT @@VERSION;
Enter fullscreen mode Exit fullscreen mode

PostgreSQL

PostgreSQL upgrades are a different challenge: in-place upgrades are not supported between major versions. Instead, DBAs must:

  • Use pg_upgrade for efficient migration.
  • Or rely on logical replication to migrate with minimal downtime.

Example: Upgrading with pg_upgrade

pg_upgrade -b /usr/lib/postgresql/14/bin -B /usr/lib/postgresql/15/bin \
-d /var/lib/postgresql/14/main -D /var/lib/postgresql/15/main
Enter fullscreen mode Exit fullscreen mode

Why It Matters

Poorly planned installations or upgrades create problems that last for years. The DBA ensures the foundation is solid, avoiding “default config debt.”

📖 SQL Server Install/Upgrade Docs
📖 PostgreSQL Upgrade Docs

2.2. Security and User Management

Data is often an organization’s most valuable asset, and DBAs are its gatekeepers.

SQL Server

SQL Server uses a mix of Windows Authentication and SQL Authentication. Best practices include:

  • Enabling Windows Authentication whenever possible.
  • Disabling the sa account or renaming it.
  • Enforcing password policies.

Example: Granting least-privilege role

CREATE LOGIN reporting_user WITH PASSWORD = 'StrongPass!23';
USE Sales;
CREATE USER reporting_user FOR LOGIN reporting_user;
ALTER ROLE db_datareader ADD MEMBER reporting_user;
Enter fullscreen mode Exit fullscreen mode

PostgreSQL

PostgreSQL relies on roles with flexible permission structures. Authentication is managed through pg_hba.conf.

Example: Creating a read-only reporting role

CREATE ROLE reporting_user LOGIN PASSWORD 'StrongPass!23';
GRANT CONNECT ON DATABASE salesdb TO reporting_user;
GRANT USAGE ON SCHEMA public TO reporting_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_user;
Enter fullscreen mode Exit fullscreen mode

Why It Matters

One of the most common mistakes is granting developers sysadmin or superuser privileges. DBAs enforce least privilege to prevent accidents and meet compliance standards.

📖 SQL Server Security Best Practices
📖 PostgreSQL Role Management

2.3. Backup and Recovery Strategies

Backups are meaningless unless they can be restored. DBAs design and test recovery strategies that align with business SLAs.

SQL Server

SQL Server offers:

  • Full backups (entire database).
  • Differential backups (changes since last full).
  • Transaction log backups (point-in-time recovery).

Example: Full + diff + log strategy

-- Full backup
BACKUP DATABASE Sales TO DISK = 'D:\Backups\Sales_full.bak' WITH COMPRESSION;

-- Differential backup
BACKUP DATABASE Sales TO DISK = 'D:\Backups\Sales_diff.bak' WITH DIFFERENTIAL;

-- Log backup
BACKUP LOG Sales TO DISK = 'D:\Backups\Sales_log.trn' WITH INIT;
Enter fullscreen mode Exit fullscreen mode

Advanced features:

  • Backup compression (smaller & faster).
  • Striped backups across multiple disks.
  • Encrypted backups for compliance.
  • Azure Blob Storage backups for offsite retention.

PostgreSQL

PostgreSQL provides:

  • Logical backups with pg_dump.
  • Physical backups with pg_basebackup.
  • WAL archiving for point-in-time recovery.

Example: WAL archiving

archive_mode = on
archive_command = 'cp %p /mnt/backups/wal/%f'
Enter fullscreen mode Exit fullscreen mode

Real-World Case Study

A bank thought replication = backup. When corruption hit the primary, it was instantly copied to all replicas. Without valid backups, they lost hours of data. Afterward, DBAs enforced independent backups + restore tests.

📖 SQL Server Backup Docs
📖 PostgreSQL Backup Docs

2.4. Performance Monitoring and Tuning

Performance issues often surface as vague complaints: “the app is slow.” DBAs translate this into measurable diagnostics.

SQL Server

DBAs rely on:

  • Dynamic Management Views (DMVs) to identify slow queries.
  • Query Store to track plan regressions.
  • Extended Events to capture deadlocks.

Example: Top resource-consuming queries

SELECT TOP 10
  qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time,
  qs.execution_count, qt.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY avg_elapsed_time DESC;
Enter fullscreen mode Exit fullscreen mode

SQL Server 2019+ adds:

  • Adaptive Joins.
  • Automatic Plan Correction.
  • Row mode/batch mode execution.

PostgreSQL

PostgreSQL DBAs use:

  • pg_stat_activity to view current queries.
  • pg_stat_statements for aggregated query performance.
  • EXPLAIN (ANALYZE) to examine execution plans.

Example: Long-running queries

SELECT pid, query, state, now() - query_start AS runtime
FROM pg_stat_activity
WHERE state != 'idle' AND now() - query_start > interval '5 minutes';
Enter fullscreen mode Exit fullscreen mode

Why It Matters

A DBA’s goal is not to make one query fast — it’s to keep the entire system predictable under load.

📖 SQL Server Performance Docs
📖 PostgreSQL Performance Tips

2.5. Schema and Data Modeling Support

Databases are only as good as their schema. A poorly designed schema results in queries that never scale, indexes that don’t help, and data integrity problems that cost businesses millions.

DBAs help design schemas that balance performance, integrity, and maintainability.

2.5.1. Clustered vs. Nonclustered Indexes (SQL Server)

SQL Server requires every table to have one clustered index (or be a heap).

  • Clustered index: Defines the physical order of rows (often the primary key).
  • Nonclustered indexes: Pointers back to the clustered index, optimized for searches.

Example:

-- Clustered index on OrderID
CREATE CLUSTERED INDEX idx_orders_orderid ON Orders(OrderID);

-- Nonclustered index on CustomerID
CREATE NONCLUSTERED INDEX idx_orders_customerid ON Orders(CustomerID);
Enter fullscreen mode Exit fullscreen mode

DBAs often choose narrow, unique, ever-increasing keys for clustered indexes (e.g., identity columns). Choosing poorly (like a random GUID) causes fragmentation and wasted I/O.

2.5.2. Filtered Indexes

Filtered indexes improve performance for queries on sparse data.

Example: Only index active orders:

CREATE NONCLUSTERED INDEX idx_orders_active
ON Orders(Status)
WHERE Status = 'Active';
Enter fullscreen mode Exit fullscreen mode

Filtered indexes are powerful in SQL Server — but PostgreSQL requires partial indexes for the same purpose.

PostgreSQL Example:

CREATE INDEX idx_orders_active ON orders(status) WHERE status = 'Active';
Enter fullscreen mode Exit fullscreen mode

2.5.3. Computed Columns and Indexing Them

SQL Server allows computed columns that can be persisted and indexed.

Example: Avoid non-SARGable queries on YEAR():

ALTER TABLE Orders
ADD OrderYear AS YEAR(OrderDate) PERSISTED;

CREATE INDEX idx_orders_orderyear ON Orders(OrderYear);
Enter fullscreen mode Exit fullscreen mode

This allows developers to query WHERE OrderYear = 2025 without breaking index usage.

PostgreSQL does not have persisted computed columns by default, but DBAs can simulate this using generated columns:

ALTER TABLE orders
ADD COLUMN orderyear INT GENERATED ALWAYS AS (EXTRACT(YEAR FROM orderdate)) STORED;

CREATE INDEX idx_orders_orderyear ON orders(orderyear);
Enter fullscreen mode Exit fullscreen mode

2.5.4. Indexed Views

SQL Server DBAs often use indexed views to pre-aggregate data.

Example: Sales totals per customer:

CREATE VIEW v_customer_sales
WITH SCHEMABINDING
AS
SELECT CustomerID, SUM(Amount) AS TotalAmount
FROM dbo.Orders
GROUP BY CustomerID;

CREATE UNIQUE CLUSTERED INDEX idx_v_customer_sales ON v_customer_sales(CustomerID);
Enter fullscreen mode Exit fullscreen mode

In PostgreSQL, materialized views provide similar functionality but require manual refresh (or automation via cron/pgAgent).

CREATE MATERIALIZED VIEW customer_sales AS
SELECT customerid, SUM(amount) AS totalamount
FROM orders
GROUP BY customerid;

REFRESH MATERIALIZED VIEW customer_sales;
Enter fullscreen mode Exit fullscreen mode

2.5.5. Enforcing Data Integrity

DBAs must ensure data is consistent, even when applications misbehave.

SQL Server Example: Foreign keys and check constraints

ALTER TABLE Orders
ADD CONSTRAINT fk_orders_customers FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID);

ALTER TABLE Orders
ADD CONSTRAINT chk_amount_positive CHECK (Amount > 0);
Enter fullscreen mode Exit fullscreen mode

PostgreSQL Example: Same constraints

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customerid)
REFERENCES customers(customerid);

ALTER TABLE orders
ADD CONSTRAINT chk_amount_positive CHECK (amount > 0);
Enter fullscreen mode Exit fullscreen mode

Constraints are often skipped by developers for “speed,” but DBAs know that long-term consistency is more valuable than short-term gains.

2.5.6. Denormalization: When to Break the Rules

Normalization prevents redundancy but can harm performance in read-heavy systems. DBAs decide when denormalization is appropriate.

Case Study:
An analytics platform constantly joined Orders and Customers on CustomerID. After analyzing workload, the DBA added a redundant CustomerName column in Orders. Query performance improved 5x, storage increased only 2%.

DBAs understand when to break normalization — and how to do it safely.

2.5.7. SQL Server vs. PostgreSQL Schema Design Philosophies

  • SQL Server: Optimized for enterprise workloads, rich indexing options (filtered indexes, indexed views, computed columns). Designed for DBAs who want tight control over query performance.
  • PostgreSQL: Flexible, extensible, favors standards. Offers partial indexes, generated columns, materialized views, but often relies on DBAs to tune autovacuum and manage bloat.

In practice:

  • SQL Server DBAs spend time choosing index strategies and leveraging Query Store.
  • PostgreSQL DBAs spend time balancing schema design with vacuum tuning and monitoring table bloat.

📖 SQL Server Index Design Guide
📖 PostgreSQL Indexes

2.6. High Availability and Disaster Recovery

High Availability (HA) and Disaster Recovery (DR) are often confused, but they solve different problems:

  • High Availability (HA): Keep the database online during failures (hardware, OS, network).
  • Disaster Recovery (DR): Restore service after catastrophic events (datacenter outage, ransomware, corruption).

A strong DBA strategy blends both.

2.6.1. SQL Server HA/DR Options

SQL Server provides multiple built-in HA/DR technologies. Each has trade-offs:

Failover Cluster Instances (FCI)

  • Instance-level protection.
  • Requires Windows Server Failover Clustering (WSFC) and shared storage. 
# Check failover cluster nodes
Get-ClusterNode
Enter fullscreen mode Exit fullscreen mode

Pros: Instance-wide coverage, transparent failover.
Cons: Shared storage is a single point of failure.

Always On Availability Groups (AG)

  • Database-level protection.
  • Supports synchronous commit (zero data loss) and asynchronous commit (better performance).
  • Secondary replicas can be used for read-only queries. 
CREATE AVAILABILITY GROUP SalesAG
FOR DATABASE Sales
REPLICA ON 'SQLNode1' WITH (ENDPOINT_URL = 'TCP://sqlnode1:5022'),
            'SQLNode2' WITH (ENDPOINT_URL = 'TCP://sqlnode2:5022');
Enter fullscreen mode Exit fullscreen mode

Pros: No shared storage dependency, read scale-out.
Cons: Enterprise Edition required for full features, system databases not protected.

Log Shipping

  • Ships transaction log backups to secondary servers.
  • Often used for DR across regions. 
EXEC master.dbo.sp_help_log_shipping_monitor;
Enter fullscreen mode Exit fullscreen mode

Pros: Simple, proven, cost-effective.
Cons: Manual failover, potential data loss between log backups.

Database Mirroring (Deprecated)

  • Supported synchronous/asynchronous commit.
  • Replaced by AGs, but still seen in legacy systems.

2.6.2. PostgreSQL HA/DR Options

PostgreSQL’s HA/DR relies on replication and external tools.

Streaming Replication

  • Continuous WAL shipping to replicas. 
primary_conninfo = 'host=10.0.0.1 user=replicator password=secret'
Enter fullscreen mode Exit fullscreen mode

Pros: Simple, low latency.
Cons: No automatic failover without tooling.

Logical Replication

  • Replicates tables/schemas selectively.
  • Useful for migrations. 
CREATE PUBLICATION mypub FOR TABLE sales;
CREATE SUBSCRIPTION mysub CONNECTION 'host=primary dbname=mydb user=replicator password=secret' PUBLICATION mypub;
Enter fullscreen mode Exit fullscreen mode

Patroni, Pgpool-II, Stolon

  • Provide automated failover and orchestration.
  • Common in Kubernetes/cloud-native deployments.

2.6.3. HA vs. DR in Practice

Replication ≠ backup.

Case Study:
At a logistics firm, a developer executed DELETE FROM Orders without WHERE. The delete replicated instantly to all SQL Server AG secondaries and PostgreSQL replicas. Without backups, recovery was impossible.

Lesson: HA protects from hardware failures. DR protects from human error. Both are mandatory.

2.6.4. Designing HA/DR Strategies with RPO/RTO

DBAs align HA/DR with business SLAs:

  • RPO (Recovery Point Objective): Acceptable data loss.
  • RTO (Recovery Time Objective): Acceptable downtime.

Examples:

  • Trading platform: RPO = 0, RTO < 1 minute → synchronous AGs across datacenters.
  • Analytics platform: RPO = 15 min, RTO = 1 hr → log shipping with frequent backups.
  • Startup: RPO = 1 hr, RTO = 4 hrs → cloud multi-AZ + automated backups.

2.6.5. SQL Server vs. PostgreSQL HA/DR Philosophies

  • SQL Server: Built-in enterprise HA/DR (AGs, FCI, log shipping). Strong for regulated industries.
  • PostgreSQL: Modular, flexible, requires tools like Patroni. Strong for cloud-native cost-sensitive businesses.

📖 SQL Server Always On Docs
📖 PostgreSQL High Availability Docs

2.6.6. Real-World Scenario: Disaster Simulation and Recovery

Scenario: Ransomware encrypts the primary server.

  • SLA: RPO = 15 minutes, RTO = 1 hour.
  • SQL Server uses full + diff + log backups.
  • PostgreSQL uses WAL archiving + base backups.

SQL Server Recovery Steps

  1. Isolate compromised server.
  2. Restore full backup: 
RESTORE DATABASE Sales FROM DISK = 'Sales_full.bak' WITH NORECOVERY;
Enter fullscreen mode Exit fullscreen mode
  1. Restore differential backup.
  2. Apply log backups up to 10:15 AM: 
RESTORE LOG Sales FROM DISK = 'Sales_log.trn' WITH STOPAT = '2025-09-18T10:15:00', RECOVERY;
Enter fullscreen mode Exit fullscreen mode
  1. Run DBCC CHECKDB for integrity.

Outcome: Downtime = 45 min, Data loss = <10 min.

PostgreSQL Recovery Steps

  1. Promote standby if available: 
pg_ctl promote -D /var/lib/postgresql/standby
Enter fullscreen mode Exit fullscreen mode
  1. Restore base backup: 
pg_basebackup -D /var/lib/postgresql/recovery -R -X stream
Enter fullscreen mode Exit fullscreen mode
  1. Replay WAL up to 10:15 AM: 
restore_command = 'cp /backups/wal/%f %p'
recovery_target_time = '2025-09-18 10:15:00'
Enter fullscreen mode Exit fullscreen mode

Outcome: Downtime = 50 min, Data loss = ~10 min.

2.6.7. Cost vs. Complexity Trade-Offs

Technology Platform Cost Complexity RPO RTO Best Use Case
Failover Cluster Instance (FCI) SQL Server High High 0 Min Enterprise datacenter instance protection
Availability Groups (AGs) SQL Server Very High High 0–sec Sec–Min Mission-critical OLTP
Log Shipping SQL Server Low Medium Min 15–60m Budget DR
Streaming Replication PostgreSQL Low Medium Sec Manual General HA
Logical Replication PostgreSQL Low Medium Sec–Min Manual Selective migration
Patroni/Pgpool/Stolon PostgreSQL Medium High Sec Sec–Min Cloud-native HA
Cloud Multi-AZ Failover Both Med–High Low Sec Sec Cloud-first orgs

Key insight: A cheap, well-tested DR plan is safer than an expensive, untested HA solution.

2.7. Automation and Scripting

A DBA without automation quickly becomes overwhelmed. Manual backups, manual index rebuilds, and ad-hoc monitoring don’t scale — and they often lead to human error.

Automation is how DBAs scale themselves, reduce mistakes, and prove value to the business.

2.7.1. SQL Server Automation

SQL Server Agent Jobs

SQL Server Agent is the native job scheduler for SQL Server.

  • Automates backups.
  • Rebuilds or reorganizes indexes.
  • Sends alerts on job failures.

Example: Nightly index maintenance job

ALTER INDEX ALL ON Orders
REBUILD WITH (ONLINE = ON, FILLFACTOR = 90);
Enter fullscreen mode Exit fullscreen mode

This can be scheduled to run during off-peak hours, improving performance without manual effort.

PowerShell Integration

PowerShell provides powerful scripting across multiple servers.

Example: Automating backups with PowerShell

$server = "SQLNODE1"
$database = "Sales"
$backupFile = "D:\Backups\Sales_$(Get-Date -Format 'yyyyMMdd_HHmm').bak"

Invoke-Sqlcmd -ServerInstance $server -Database master `
-Query "BACKUP DATABASE [$database] TO DISK = N'$backupFile' WITH INIT, COMPRESSION;"
Enter fullscreen mode Exit fullscreen mode

DBAs often use PowerShell for:

  • Automated restores during DR drills.
  • Bulk login/user provisioning.
  • Schema deployment in CI/CD pipelines.

Policy-Based Management (PBM)

SQL Server’s PBM enforces rules automatically.

Examples:

  • All databases must have CHECKSUM page verification enabled.
  • No database can run in FULL recovery mode without log backups.
  • No unauthorized accounts can own objects.

This ensures compliance at scale.

Extended Events and DMVs

SQL Server DBAs can automate monitoring with XEvents + DMVs.

Example: Deadlock capture

CREATE EVENT SESSION DeadlockMonitor
ON SERVER
ADD EVENT sqlserver.lock_deadlock
ADD TARGET package0.event_file (SET filename='C:\XEvents\Deadlocks.xel');
ALTER EVENT SESSION DeadlockMonitor ON SERVER STATE = START;
Enter fullscreen mode Exit fullscreen mode

Scripts then parse .xel files and send alerts automatically.

2.7.2. PostgreSQL Automation

Unlike SQL Server, PostgreSQL lacks a built-in scheduler like SQL Agent. DBAs typically use:

  • cron jobs (Linux).
  • pgAgent (lightweight PostgreSQL job scheduler).
  • Ansible / Puppet / Chef for configuration automation.

Example: VACUUM via cron

0 3 * * * psql -d salesdb -c "VACUUM ANALYZE;"
Enter fullscreen mode Exit fullscreen mode

Autovacuum Tuning

PostgreSQL’s autovacuum prevents table bloat. DBAs tune thresholds to match workloads:

autovacuum_naptime = 30s
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
Enter fullscreen mode Exit fullscreen mode

Improperly tuned autovacuum is a common cause of bloat in production.

pgBackRest Automation

For enterprise-grade backups, many PostgreSQL DBAs use pgBackRest.

Example: Full backup

pgbackrest --stanza=prod --type=full backup
Enter fullscreen mode Exit fullscreen mode

With cron or Ansible, this becomes part of an automated backup/restore strategy.

📖 pgBackRest Documentation

2.7.3. Database Automation in CI/CD

Modern DBAs embed database management into CI/CD pipelines. Schema changes are version-controlled and deployed automatically.

  • Flyway (SQL migrations as versioned scripts).
  • Liquibase (YAML/XML/SQL migrations).
  • DBUp (.NET schema migration).

Example: Flyway migration

-- V2025.09.18__AddIndexOnOrders.sql
CREATE INDEX idx_orders_customerid ON Orders(CustomerID);
Enter fullscreen mode Exit fullscreen mode

This ensures changes are applied consistently across dev, staging, and prod.

2.7.4. Real-World Case Study

At a retail company, DBAs manually rebuilt indexes monthly. One night, a DBA mistakenly ran the script on a 2TB reporting database during peak hours, causing hours of blocking and downtime.

After the incident, they implemented:

  • SQL Server Agent jobs for scheduled index maintenance.
  • PowerShell scripts to validate job completion.
  • PBM policies to prevent configuration drift.

Result: DBA errors dropped to zero, and monthly maintenance stopped being a fire drill.

Why Automation Matters

Automation is not about replacing DBAs. It’s about:

  • Consistency: Tasks run the same way every time.
  • Efficiency: Free DBAs from repetitive work.
  • Safety: Reduce human mistakes during critical operations.

A DBA without automation is a bottleneck. A DBA with automation is a force multiplier.

📖 SQL Server Agent Docs
📖 PostgreSQL Autovacuum Docs

2.8. Supporting Developers & Query Optimization

Developers write queries; DBAs make sure those queries don’t bring production to its knees. This collaboration is where the DBA’s role becomes most visible — and sometimes most contentious.

DBAs don’t just fix slow queries. They educate, guide, and create a culture where performance is part of development, not an afterthought.

2.8.1. Understanding the Query Optimizer

Both SQL Server and PostgreSQL rely on cost-based optimizers:

  • SQL Server uses statistics and cardinality estimators.
  • PostgreSQL uses its planner to compare join strategies, index usage, and sequential scans.

A query is not executed exactly as written — the optimizer rewrites it into what it believes is the most efficient execution plan. DBAs help developers write queries the optimizer can optimize well.

2.8.2. SARGability

SARGability (Search ARGument Ability) means queries can leverage indexes efficiently. Non-SARGable queries force scans, even if indexes exist.

Bad SQL Server Example:

SELECT * FROM Orders WHERE YEAR(OrderDate) = 2025;
Enter fullscreen mode Exit fullscreen mode

This breaks index usage.

Optimized:

SELECT * FROM Orders
WHERE OrderDate >= '2025-01-01'
  AND OrderDate < '2026-01-01';
Enter fullscreen mode Exit fullscreen mode

DBAs often fix this permanently with computed columns:

ALTER TABLE Orders
ADD OrderYear AS YEAR(OrderDate) PERSISTED;

CREATE INDEX idx_orders_orderyear ON Orders(OrderYear);
Enter fullscreen mode Exit fullscreen mode

PostgreSQL equivalent:

ALTER TABLE orders
ADD COLUMN orderyear INT GENERATED ALWAYS AS (EXTRACT(YEAR FROM orderdate)) STORED;

CREATE INDEX idx_orders_orderyear ON orders(orderyear);
Enter fullscreen mode Exit fullscreen mode

2.8.3. Parameter Sniffing in SQL Server

SQL Server caches execution plans. This helps most of the time but can cause problems when workloads vary by parameter.

Example:

CREATE PROCEDURE GetOrdersByCustomer @CustomerID INT
AS
SELECT * FROM Orders WHERE CustomerID = @CustomerID;
Enter fullscreen mode Exit fullscreen mode
  • If CustomerID = 1 returns millions of rows, SQL chooses a scan.
  • If CustomerID = 9999 returns 10 rows, SQL chooses a seek.

If the “big customer” plan is cached, all executions slow down.

Fixes:

  • OPTION (RECOMPILE) for sensitive queries.
  • OPTIMIZE FOR UNKNOWN.
  • Query Store to force stable plans. 
ALTER DATABASE Sales SET QUERY_STORE = ON;
EXEC sp_query_store_force_plan @query_id = 123, @plan_id = 456;
Enter fullscreen mode Exit fullscreen mode

📖 SQL Server Parameter Sniffing

2.8.4. PostgreSQL Query Optimization Patterns

PostgreSQL queries often suffer from misuse of indexes and poor filtering.

Bad Example:

SELECT * FROM Customers WHERE email LIKE '%gmail.com';
Enter fullscreen mode Exit fullscreen mode

This forces a full scan.

Optimized with trigram index:

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_customers_email_trgm
  ON Customers USING gin (email gin_trgm_ops);

SELECT * FROM Customers WHERE email LIKE '%gmail.com';
Enter fullscreen mode Exit fullscreen mode

The pg_trgm extension enables efficient text search.

📖 PostgreSQL Performance Tips

2.8.5. Locking, Blocking, and Concurrency

Performance isn’t only about speed — it’s about scalability under load.

SQL Server Example: Deadlocks

Two transactions hold locks and wait on each other:

BEGIN TRAN;
UPDATE Orders SET Amount = Amount + 1 WHERE OrderID = 1;
Enter fullscreen mode Exit fullscreen mode

… while another transaction updates OrderID = 2 then tries OrderID = 1.

Fixes:

  • Use READ COMMITTED SNAPSHOT to reduce blocking.
  • Design better indexes to avoid lock escalation.
  • Monitor deadlocks with Extended Events.

PostgreSQL Example: Idle-in-transaction sessions

PostgreSQL uses MVCC. Long idle transactions prevent cleanup, causing bloat.

Fix: Enforce timeouts:

statement_timeout = 60000
idle_in_transaction_session_timeout = 300000
Enter fullscreen mode Exit fullscreen mode

2.8.6. Real-World Case Study: Query Gone Wild

At a retail company, a developer ran:

SELECT * FROM Orders WHERE CAST(OrderDate AS VARCHAR(10)) = '2025-09-18';
Enter fullscreen mode Exit fullscreen mode

This broke index usage. The query ran thousands of times per minute, each scan touching 200M rows.

DBA Fix:

  1. Rewrote query to be SARGable.
  2. Added computed column OrderDateText.
  3. Created supporting index.

Query time dropped from 12 seconds to <50ms.

2.8.7. DBAs as Developer Partners

The best DBAs are not gatekeepers — they’re partners. Developers should feel comfortable asking:

  • “Is this query efficient?”
  • “Should we add an index?”
  • “Is denormalization safe here?”

DBAs who educate developers prevent problems before they reach production. DBAs who only fix after-the-fact become bottlenecks.

Why Supporting Developers Matters

Queries are the bridge between applications and data.

  • If queries are slow, apps are slow.
  • If queries deadlock, apps fail.
  • If queries are written well, everything scales smoothly.

By supporting developers, DBAs scale themselves. They prevent issues instead of reacting to them.

📖 SQL Server Performance Guide
📖 PostgreSQL EXPLAIN

3. Common Mistakes DBAs See (and How to Avoid Them)

Every DBA, regardless of experience, has stories of catastrophic failures that could have been avoided. The most frustrating part? These mistakes are predictable, repeatable, and preventable.

Here are the most common pitfalls — and how DBAs protect organizations from them.

3.1. Giving Developers Sysadmin or Superuser Privileges

The Mistake: Developers are granted unrestricted access (sysadmin in SQL Server, superuser in PostgreSQL) “just to get things done.”

Impact:

  • Accidental DROP TABLE in production.
  • Schema drift with unapproved changes.
  • Compliance violations (SOX, GDPR, HIPAA).

Example:

-- SQL Server: Dangerous!
ALTER SERVER ROLE sysadmin ADD MEMBER DevUser;

-- PostgreSQL: Just as bad
ALTER ROLE devuser WITH SUPERUSER;
Enter fullscreen mode Exit fullscreen mode

Prevention:

  • Implement role-based access control.
  • Separate dev, test, and prod accounts.
  • Enforce the principle of least privilege.

📖 SQL Server Security Best Practices
📖 PostgreSQL Role Management

3.2. Confusing Replication with Backup

The Mistake: Assuming replication = backup.

Impact:

  • A DELETE without WHERE replicates instantly.
  • Ransomware or corruption spreads to all replicas.

Example Detection:

  • SQL Server: Check AG replica health 
SELECT ag.name, ar.replica_server_name, drs.synchronization_state_desc
FROM sys.dm_hadr_availability_replica_states drs
JOIN sys.availability_groups ag ON drs.group_id = ag.group_id;
Enter fullscreen mode Exit fullscreen mode
  • PostgreSQL: Monitor replication lag 
SELECT client_addr, state, write_lsn, replay_lsn, 
       pg_wal_lsn_diff(write_lsn, replay_lsn) AS lag_bytes
FROM pg_stat_replication;
Enter fullscreen mode Exit fullscreen mode

Prevention:

  • Always maintain independent backups.
  • Perform regular restore drills.

📖 SQL Server Backup vs. HA
📖 PostgreSQL High Availability

3.3. Over-Indexing Tables

The Mistake: Creating an index for every column.

Impact:

  • Sluggish inserts/updates (all indexes must update).
  • Increased storage costs.
  • Confusing optimizer with too many options.

Example Detection:

  • SQL Server: Unused indexes 
SELECT OBJECT_NAME(i.object_id) AS TableName, i.name AS IndexName
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s
  ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE s.index_id IS NULL AND i.is_primary_key = 0 AND i.is_unique_constraint = 0;
Enter fullscreen mode Exit fullscreen mode
  • PostgreSQL: Same idea 
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;
Enter fullscreen mode Exit fullscreen mode

Prevention:

  • Favor covering indexes.
  • Drop unused indexes.
  • Monitor index usage trends.

📖 SQL Server Index Design Guide
📖 PostgreSQL Indexes

3.4. Skipping Statistics Updates

The Mistake: Assuming indexes alone guarantee performance.

Impact:

  • Poor cardinality estimation.
  • Wrong join strategies.
  • Plan regressions.

Fixes:

  • SQL Server: 
EXEC sp_updatestats;
Enter fullscreen mode Exit fullscreen mode
  • PostgreSQL: 
ANALYZE orders;
Enter fullscreen mode Exit fullscreen mode

Detection:

  • SQL Server: Query Store for regressions.
  • PostgreSQL: pg_stat_all_tables for outdated autovacuum.

📖 SQL Server Statistics
📖 PostgreSQL ANALYZE

3.5. Ignoring Alerts and Monitoring

The Mistake: DBAs rely on user complaints instead of proactive monitoring.

Impact:

  • Disk fills without warning.
  • Blocking chains grow unnoticed.
  • Backups silently fail.

Prevention:

  • SQL Server: Database Mail + Agent Alerts.
  • PostgreSQL: Prometheus + Grafana dashboards.

📖 SQL Server Monitoring
📖 PostgreSQL Monitoring

3.6. Not Testing Recovery

The Mistake: Backups exist, but restores are never tested.

Impact:

  • Corrupted .bak or missing WAL files.
  • Outage during real recovery.

Fix:

  • Automate weekly restore drills.
  • SQL Server: DBCC CHECKDB post-restore.
  • PostgreSQL: pg_restore --list to verify dumps.

3.7. Schema Design Anti-Patterns

The Mistake:

  • Storing everything in JSON.
  • Using VARCHAR(8000) for every field.
  • Ignoring normalization.

Impact:

  • Queries that can’t use indexes.
  • Data integrity violations.

Prevention:

  • Balance normalization vs. denormalization.
  • Use appropriate data types.
  • Apply constraints.

3.8. Parameter Sniffing (SQL Server)

The Mistake: Cached execution plans hurt performance for varied parameters.

Fixes:

  • Use OPTIMIZE FOR UNKNOWN.
  • Force stable plans with Query Store.

📖 SQL Server Parameter Sniffing

3.9. Autovacuum Disabled (PostgreSQL)

The Mistake: Autovacuum disabled to “reduce overhead.”

Impact:

  • Dead rows accumulate.
  • Queries slow down.

Detection:

SELECT relname, n_dead_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 100000;
Enter fullscreen mode Exit fullscreen mode

Fix:

  • Tune autovacuum.
  • Run manual VACUUM FULL if needed.

📖 PostgreSQL Autovacuum

3.10. Transaction Log Mismanagement (SQL Server)

The Mistake: FULL recovery mode without log backups.

Impact:

  • Log file grows until disk is full.
  • Inserts/updates blocked.

Detection:

DBCC SQLPERF(LOGSPACE);
Enter fullscreen mode Exit fullscreen mode

Fix:

  • Pair FULL recovery with log backup schedule.
  • Monitor log size growth.

📖 SQL Server Transaction Log

3.11. Mixing OLTP and OLAP Workloads

The Mistake: Running analytics on production OLTP.

Impact:

  • Blocking.
  • Deadlocks.
  • Latency for customers.

Fix:

  • Offload with read replicas (AG secondaries, PostgreSQL logical replication).
  • Build a data warehouse for analytics.

Real-World "DBA War Stories"

  1. The Log Explosion: A bank’s SQL Server log filled because no log backups were configured. Transactions froze during peak hours, costing millions in lost trades.
  2. Autovacuum Disabled: A SaaS platform disabled autovacuum. Query latency grew from 50ms to 30s due to bloat. A single DBA spent 2 days manually vacuuming.
  3. Index Explosion: A retail Orders table had 40 indexes. Inserts took 5 seconds each. After dropping 30 unused indexes, inserts dropped to 300ms.

Why These Mistakes Persist

Most DBA mistakes are cultural, not technical. They happen because:

  • Developers optimize for speed of delivery, not long-term reliability.
  • Managers undervalue disaster testing until it’s too late.
  • DBAs are brought in after problems appear.

The solution: education, automation, and regular testing.

4. The Evolving Role of DBAs: Cloud, DevOps, and Intelligent Databases

The DBA role has never been static. In the 1980s, DBAs managed tape backups and tuned disk layouts. In the 2000s, they rebuilt indexes and handled schema changes. In 2025, they are far more strategic — working in cloud-native environments, embedding databases into DevOps pipelines, and supervising intelligent optimizers that make automatic decisions.

The myth of the “obsolete DBA” resurfaces every decade. But instead of disappearing, DBAs reinvent themselves with every technological shift.

4.1. The Cloud DBA

When managed services arrived, many executives declared DBAs unnecessary. After all, AWS RDS, Azure SQL Database, and Google Cloud SQL promise:

  • Automated patching.
  • Automated backups.
  • Automatic failover.

So, what’s left for DBAs?

Plenty.

Cloud providers handle infrastructure plumbing — but DBAs handle architecture, governance, and business alignment.

  • SQL Server Example (Azure SQL Database): A finance company assumed geo-replication protected them. But when ransomware deleted critical rows, the deletes replicated instantly. The DBA had to perform a point-in-time restore and replay transactions. Without DBA oversight, the “self-healing” feature made things worse.

  • PostgreSQL Example (AWS RDS): A SaaS startup used Multi-AZ failover. During peak load, failover revealed a 30-second replication lag. Their SLA required <10s RPO. The DBA redesigned replication using Aurora PostgreSQL parallel replication, cutting lag to <5s.

The Cloud DBA ensures businesses don’t confuse vendor promises with actual recoverability.

📖 Azure SQL Database Backups
📖 AWS RDS PostgreSQL

4.2. The DevOps DBA

Traditional DBAs were gatekeepers. Every schema change required approval, slowing down development. In fast-moving companies, this model collapsed.

Enter the DevOps DBA — a partner, not a blocker.

  • SQL Server Example (Flyway CI/CD): A fintech company’s schema changes were applied manually in prod. A developer accidentally dropped and recreated a table, erasing data. The DBA introduced Flyway migrations in Azure DevOps pipelines, with versioned SQL scripts and automated rollback tests. Failures were caught early, and releases became safe.

  • PostgreSQL Example (Liquibase + GitHub Actions): A SaaS platform gave developers superuser access. One migration dropped an index mid-day, causing downtime. The DBA implemented Liquibase migrations, where schema changes ran through CI pipelines before deployment. Developers still moved fast — but with safety rails.

The DevOps DBA’s role is cultural as much as technical:

  • Teaching developers to think in SARGable queries.
  • Embedding schema validation into pipelines.
  • Ensuring database changes move at the same speed as application code.

📖 Flyway Docs
📖 Liquibase Docs

4.3. The Intelligent DBA

Modern databases ship with self-optimizing features:

  • SQL Server Intelligent Query Processing (IQP): Adaptive joins, batch mode on rowstore, automatic plan correction.
  • PostgreSQL: Parallel query execution, JIT compilation, evolving cost-based optimizer.

At first glance, this looks like the end of query tuning. But automation requires oversight.

  • SQL Server Example: A logistics firm upgraded to SQL Server 2022. An ETL job slowed from 20 min → 2 hrs because adaptive joins misjudged skewed data. Query Store forced the stable plan, restoring performance. The DBA’s role wasn’t tuning the query manually — it was guiding automation safely.

  • PostgreSQL Example: A healthcare company noticed a query ignoring an index and choosing sequential scans. The root cause: skewed statistics. The DBA ran ANALYZE, created a partial index, and cut query time from 20s to 200ms.

Automation didn’t remove the DBA — it gave them smarter tools. But someone must still validate, override, and ensure the business isn’t hurt by misjudgments.

📖 SQL Server Intelligent Query Processing
📖 PostgreSQL Parallel Query

4.4. Real-World Examples of Role Evolution

The evolution of DBAs is best told through stories:

  • Cloud Transition Gone Wrong (SQL Server): A manufacturer moved to Azure Managed Instance. They thought backups were compliant by default. Auditors rejected them — long-term retention and encryption were missing. The DBA implemented Azure Blob + Key Vault encryption, saving millions in fines.

  • DevOps Culture Shift (PostgreSQL): A SaaS team deployed migrations directly to prod. After a botched alter table, downtime lasted 3 hrs. The DBA integrated schema tests into Liquibase + CI/CD. Outages dropped to zero, releases accelerated.

  • Intelligent Features in Action (SQL Server): A retail system saw queries slow after an upgrade. Automatic Plan Correction fixed regressions instantly. The DBA validated and enforced the correction, ensuring the business trusted automation.

  • Planner Surprise (PostgreSQL): An analytics query scanned billions of rows instead of using indexes. The DBA spotted skewed data distribution, refreshed statistics, and applied a partial index. Query time dropped 100x.

The New DBA Identity

By 2025, DBAs are no longer “server babysitters.” They are:

  • Cloud strategists balancing vendor-managed features with compliance and cost.
  • DevOps partners ensuring schema changes flow safely through pipelines.
  • Intelligent overseers supervising automated optimizers and AI-powered features.

The DBA has become a data reliability engineer — the guardian of trust in modern data systems.

5. Conclusion: The DBA’s Real Mission

From mainframes in the 1970s to Kubernetes in 2025, the database has remained the heart of enterprise IT. And wherever data lives, someone must ensure it is safe, performant, and available.

That person is the DBA.

5.1. The Invisible Success

When DBAs do their jobs well, no one notices.

  • Queries just run.
  • Backups complete.
  • Failovers happen smoothly.

But when DBAs fail, everyone notices.

  • An untested backup won’t restore.
  • An unoptimized query locks production tables.
  • A forgotten autovacuum setting bloats tables until the system crawls.

The paradox of the DBA role is that success is invisible, but failure is public.

DBAs don’t seek the spotlight — they prevent the disasters that would bring it.

5.2. Reliability as the Core Mission

Every task a DBA performs — backups, monitoring, indexing, automation, HA/DR — serves one goal: reliability.

Reliability means:

  • Data safety: Even if ransomware hits, data can be recovered.
  • Predictable performance: Queries stay within SLA targets, even as data grows.
  • Continuous availability: Outages are rare, and recovery aligns with RPO/RTO.
  • Compliance enforcement: Regulations are met without slowing down development.

DBAs are the custodians of reliability — the engineers who turn uncertainty into trust.

5.3. The Human Side of the DBA Role

Technology is only part of the mission. DBAs are also:

  • Educators — teaching developers about query design and SARGability.
  • Negotiators — balancing cost, risk, and performance with business leaders.
  • Translators — converting vague complaints (“the app is slow”) into actionable diagnostics.
  • Guardians — ensuring compliance without blocking agility.

Great DBAs succeed not just through technical skill but through communication, trust, and influence.

5.4. The DBA of the Future

Looking ahead, the DBA role will evolve again:

  • Cloud-native DBAs will design hybrid systems spanning on-prem, cloud, and edge.
  • DevOps DBAs will embed database safety into continuous delivery pipelines.
  • Intelligent DBAs will supervise AI-driven optimizers, validating their decisions.
  • Data governance DBAs will enforce global compliance (GDPR, HIPAA, PCI-DSS) across distributed data.

Automation will keep taking tasks off DBA plates — but it will also raise expectations. Businesses will expect DBAs to focus less on “button-pushing” and more on strategy, architecture, and reliability engineering.

5.5. A Final Word

If you are a DBA today, or aspire to be one: your job title may change, your tools may evolve, but your mission will remain constant:

Keep data safe. Keep data fast. Keep data available.

Organizations may not always understand what DBAs do day to day, but they will always feel the absence of a DBA when things go wrong.

The DBA of 2025 is not obsolete. The DBA is the reason modern businesses can trust their data at all.

6. References & Further Reading

Here’s a curated list of official documentation and resources for DBAs who want to go deeper into the topics covered in this guide.

SQL Server

PostgreSQL

Automation & DevOps

Cloud Services

Top comments (0)