# Database Administrator

> Use when managing database systems, optimizing queries, designing schemas, handling backups/recovery, replication, or database performance tuning. Trigger phrases: "DBA", "database administration", "query optimization", "slow query", "index optimization", "database backup", "replication", "failover", "PostgreSQL tuning", "MySQL administration", "Oracle DBA", "database migration", "tablespace", "connection pooling", "database security", "EXPLAIN ANALYZE", "vacuum", "RDBMS performance".

- Skill: `barastrong/database-administrator` (Agent Skill, multi-file: 5 files)
- Install (CLI): `npx skillmds@latest add barastrong/database-administrator`
- Raw SKILL.md: https://api.skillmd.com/api/skills/barastrong/database-administrator/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Security
- Author: barastrong (https://skillmd.com/u/barastrong)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/barastrong/database-administrator

---


# Database Administrator (DBA)

## Role Summary

A DBA ensures databases are performant, available, secure, and recoverable.
The role covers installation, configuration, monitoring, tuning, backup/
recovery, security, and capacity planning for relational (and sometimes NoSQL)
database systems.

## Core Responsibilities

- Install, configure, and upgrade database engines (PostgreSQL, MySQL, Oracle)
- Design schemas, indexes, and partitioning strategies
- Monitor and tune query performance (EXPLAIN plans, index analysis)
- Implement backup and disaster recovery procedures
- Configure high availability and replication (primary/replica, clustering)
- Manage user access, roles, and data security
- Capacity planning and storage management
- Automate routine maintenance (VACUUM, ANALYZE, statistics update)
- Support application teams with data modeling and query review

## Standard Workflow

1. **Health Check** — review slow query logs, wait events, connection counts,
   disk I/O, cache hit ratios.
2. **Query Analysis** — EXPLAIN/EXPLAIN ANALYZE on slow queries; identify
   missing indexes, bad plans, N+1 patterns.
3. **Schema Review** — normalization, data types, index coverage,
   constraint validation.
4. **Change Management** — DDL changes via migration scripts; test on staging
   first; maintenance window planning.
5. **Backup Verification** — test restore procedure, not just backup creation.
6. **HA/Replication** — verify replication lag, failover procedures,
   read replica usage.
7. **Security Audit** — least privilege, encrypted connections (TLS),
   audit logging, PII data access review.

## Technology Stack

| Layer | Tools |
|-------|-------|
| RDBMS | PostgreSQL, MySQL/MariaDB, Oracle, SQL Server, SQLite |
| NoSQL | MongoDB, Redis, Cassandra, DynamoDB |
| HA/Replication | Patroni, pg_auto_failover, MySQL Group Replication, Galera |
| Monitoring | pgBadger, pg_activity, PMM (Percona), Datadog, pganalyze |
| Backup | pgBackRest, Barman, mysqldump, xtrabackup, WAL-G |
| Migrations | Flyway, Liquibase, Alembic, sqitch |
| Pooling | PgBouncer, ProxySQL, RDS Proxy |
| Cloud | RDS, Cloud SQL, Azure Database, Aurora |

## Best Practices

- Index on columns in WHERE, JOIN, ORDER BY — check usage with `pg_stat_user_indexes`.
- Partial indexes for filtered queries.
- Covering indexes to avoid table heap lookups.
- VACUUM ANALYZE scheduled; autovacuum tuned per table write frequency.
- Always test restores — backup without restore test is not a backup.
- Replication lag alerting (alert > 30s for transactional replicas).
- Connection pooling mandatory in production (PgBouncer/ProxySQL).
- Encrypted connections (TLS) and encrypted at-rest for sensitive data.
- Least privilege: app user should not have DDL privileges.
- Foreign keys enforced at DB level, not just application level.

## Anti-Patterns to Avoid

- Indexes on every column (write overhead, bloat).
- SELECT * in ORM queries on wide tables.
- Long-running transactions holding locks.
- Schema changes directly on production without migration process.
- No monitoring on replication lag.
- Using the database superuser account for application connections.
- Ignoring bloat — tables and indexes need regular maintenance.

## References

- `references/query-optimization-guide.md` — EXPLAIN plan reading and tuning
- `references/backup-recovery-procedures.md` — backup and DR runbook
- `references/postgresql-tuning.md` — PostgreSQL configuration tuning guide

## Expected Output Format

1. Query analysis: EXPLAIN plan + recommended index/rewrite
2. Schema change: migration script (up + down)
3. Performance report: before/after metrics
4. Runbook for maintenance procedures (backup, failover, restore)