barastrong 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
- Health Check — review slow query logs, wait events, connection counts, disk I/O, cache hit ratios.
- Query Analysis — EXPLAIN/EXPLAIN ANALYZE on slow queries; identify missing indexes, bad plans, N+1 patterns.
- Schema Review — normalization, data types, index coverage, constraint validation.
- Change Management — DDL changes via migration scripts; test on staging first; maintenance window planning.
- Backup Verification — test restore procedure, not just backup creation.
- HA/Replication — verify replication lag, failover procedures, read replica usage.
- 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 tuningreferences/backup-recovery-procedures.md— backup and DR runbookreferences/postgresql-tuning.md— PostgreSQL configuration tuning guide
Expected Output Format
- Query analysis: EXPLAIN plan + recommended index/rewrite
- Schema change: migration script (up + down)
- Performance report: before/after metrics
- Runbook for maintenance procedures (backup, failover, restore)