ChatGPT Prompts for Database Administrators: Performance, Backup, Security

Published: July 2026 | 9 min read

Database administrators guard the most critical asset in any organization: the data. You manage performance, backups, security, migrations, and the 3 AM calls when replication breaks. AI can help you write optimization queries, plan maintenance windows, design backup strategies, and troubleshoot issues — if your prompts understand database internals.

Performance Optimization

1. Slow Query Analysis

Role: Senior DBA analyzing a slow query
Database: [PostgreSQL / MySQL / SQL Server / Oracle]
Query: [Paste the slow query]
Execution plan: [Paste EXPLAIN ANALYZE output or execution plan]
Table stats: [Row counts, index list, recent statistics update]
Analysis:
1. What is the query trying to do? (explain the intent)
2. Where is the bottleneck? (sequential scan, missing index, bad join order, statistics stale?)
3. Index recommendations:
   - Which index to add (columns, type: btree/GIN/partial/covering)
   - Expected impact (will it use the index? how much faster?)
   - Trade-offs (write performance impact, storage, maintenance)
4. Query rewrites:
   - CTE vs subquery vs JOIN (which is most efficient?)
   - Window function alternatives
   - Materialized view candidate?
   - Partition pruning opportunity?
5. Statistics: Are stats up to date? Could stale stats cause bad plans?
6. Configuration: Are any DB parameters relevant? (work_mem, shared_buffers, etc.)
Output:
1. Root cause of slowness
2. Recommended fix (index + query rewrite + config change)
3. Expected improvement
4. Implementation plan (test first, then prod)
Format: Query optimization report for the development team.

2. Index Optimization Strategy

Role: Database performance consultant
Task: Create an index optimization strategy for [database/schema].
Current indexes: [List all indexes with table, columns, type, size]
Slow queries: [Top 10 slowest queries from pg_stat_statations / slow query log]
Unused indexes: [From pg_stat_user_indexes / sys.dm_db_index_usage_stats]
Analysis:
1. Missing indexes (which queries need indexes that do not exist?)
   - For each: index definition, query it helps, expected improvement
2. Redundant indexes (which indexes overlap or are never used?)
   - For each: why it is redundant, safe to drop? any dependencies?
3. Over-indexed tables (too many indexes hurting write performance?)
   - Write/read ratio for each table
   - Which indexes to keep vs drop based on usage
4. Index maintenance:
   - Bloat assessment (which indexes are bloated? need REINDEX?)
   - Statistics update schedule (ANALYZE / UPDATE STATISTICS)
   - Auto-clean / vacuum impact on indexes
Output:
1. Index audit report (add, remove, modify)
2. Prioritized action list (by impact on query performance)
3. Implementation plan (off-peak, monitor, rollback if needed)
4. Ongoing monitoring plan (how to keep indexes optimal)
Format: Index strategy document for DBA team.

3. Database Capacity Planning

Role: Database capacity planning analyst
Task: Create a capacity plan for [database(s)].
Current state:
- Database size: [GB/TB, growth rate per month]
- Table sizes: [Top 10 tables by size]
- Connection count: [average, peak]
- Query volume: [queries per second, per minute]
- Disk I/O: [IOPS, throughput, latency]
- Memory usage: [buffer pool hit ratio, total allocated]
Projection (12 months):
1. Data growth:
   - Projected size (based on growth rate + planned features)
   - When do we hit 70% disk capacity? 80%? 90%?
   - Table-specific growth (which tables are growing fastest?)
2. Performance:
   - Query volume projection (will current hardware handle it?)
   - Connection pool sizing (do we need more connections? bigger pool?)
   - Memory requirements (will working set fit in memory?)
3. Storage:
   - Disk space timeline (when do we need more storage?)
   - IOPS requirements (will current storage keep up?)
   - Archive/purge opportunities (what can we move/delete?)
4. Recommendations:
   - Hardware upgrades needed (when, what, estimated cost)
   - Data archival strategy (what to archive, where, how)
   - Partitioning opportunities (which tables would benefit?)
   - Read replica needs (can we offload read traffic?)
Output: 12-month capacity plan with quarterly milestones.

Backup, Recovery & High Availability

4. Backup Strategy Design

Role: DBA designing a backup strategy
Database: [PostgreSQL / MySQL / SQL Server / Oracle]
Size: [GB/TB], Criticality: [mission-critical / important / standard]
RPO target: [how much data can we lose? 0 / 5 min / 1 hour / 1 day]
RTO target: [how fast must we recover? 5 min / 30 min / 4 hours]
Strategy:
1. Backup types:
   - Full (frequency: daily/weekly, method: pg_dump/mysqldump/snapshot/RMAN)
   - Incremental (frequency: hourly, method: WAL/WAL-G/binlog/backups)
   - Continuous (streaming replication / log shipping)
2. Retention:
   - Daily backups: keep [X] days
   - Weekly backups: keep [X] weeks
   - Monthly backups: keep [X] months
   - Archive logs: keep [X] days (enough for point-in-time recovery)
3. Storage:
   - Primary (local disk / NAS)
   - Secondary (offsite replication / cloud storage)
   - Encryption (at rest, key management)
   - Compression (method, ratio, CPU impact)
4. Verification:
   - Restore test frequency (weekly? monthly?)
   - What to test (full restore? point-in-time? table-level?)
   - Automated validation (checksum, row count, referential integrity)
5. Point-in-time recovery:
   - Can we recover to any point in the retention window?
   - How long does PITR take for a 1-hour-ago recovery?
6. Runbook:
   - Restore procedure (step by step, for each scenario)
   - Verification steps (how to confirm recovery is correct)
   - Communication plan (who to notify, when)
Output: Backup policy document + runbook for each restore scenario.

5. High Availability Architecture

Role: Database HA architect
Task: Design a high availability architecture for [database].
Database: [Engine, version, size, workload pattern]
RTO: [recovery time objective]
RPO: [recovery point objective — 0 means zero data loss]
Availability target: [99.9% / 99.95% / 99.99%]
Options to evaluate:
1. Streaming replication + failover (PostgreSQL: Patroni + pgBouncer, MySQL: Orchestrator/MHA)
2. Cluster (SQL Server Always On, Oracle RAC)
3. Cloud managed HA (AWS RDS Multi-AZ, Cloud SQL HA)
4. Shared storage (SAN-based failover)
5. Database sharding (for horizontal scale + HA)
For the recommended option:
1. Architecture diagram (text description)
2. Components (primary, replicas, load balancer, health checker, DNS)
3. Failover process (automatic? manual? time to failover?)
4. Split-brain prevention (fencing, quorum, witness)
5. Connection routing (how do clients connect? failover handling?)
6. Data replication (sync vs async? lag monitoring?)
7. Monitoring (what to monitor, alert thresholds)
8. Testing (chaos testing, failover drills, frequency)
9. Cost (hardware, software licenses, network, cloud)
10. Operational complexity (what does the DBA team need to manage?)
Output: HA architecture document for review and implementation.

6. Disaster Recovery Plan

Role: DBA creating a DR plan
Database: [Engine, HA setup, data center location]
DR scenarios:
1. Primary site failure (total data center loss)
2. Storage failure (disk array failure, data corruption)
3. Replication failure (replica is broken, behind, or inconsistent)
4. Human error (accidental DROP TABLE, DELETE without WHERE)
5. Ransomware/malicious deletion
For each scenario:
1. Detection (how do we know this happened?)
2. Decision authority (who declares DR?)
3. Recovery procedure (step by step):
   - Which backup to use?
   - Which recovery point? (latest? specific time?)
   - How to failover to DR site?
   - How to verify data integrity?
   - How to switch application connections?
4. RTO (how long to recover?)
5. RPO (how much data is lost?)
6. Post-recovery:
   - Re-establish replication
   - Rebuild primary if needed
   - Root cause investigation
   - Documentation update
7. Testing:
   - DR drill frequency (quarterly? annually?)
   - What to test (failover, restore, application connectivity)
   - Success criteria
Output: DR plan document with runbooks for each scenario.

Security & Compliance

7. Database Security Hardening

Role: Database security DBA
Task: Create a security hardening checklist for [database engine].
Checklist:
1. Authentication:
   - Password policy (complexity, expiry, lockout)
   - Multi-factor auth (if supported)
   - Service accounts (not shared, not default, least privilege)
   - Remove default accounts (sa, postgres, root — rename or disable)
2. Authorization:
   - Role-based access (no direct table grants to users)
   - Least privilege (read-only for reporting, no DDL for apps)
   - Schema separation (app schema vs admin schema)
   - Audit which roles have superuser/admin
3. Network:
   - Bind to localhost or private network (not 0.0.0.0)
   - Firewall rules (only app servers can connect)
   - SSL/TLS for connections (force, not optional)
   - Connection limits per user
4. Encryption:
   - At rest (TDE, LUKS, cloud encryption)
   - In transit (SSL/TLS, certificate management)
   - Column-level (for PII fields)
   - Key management (KMS, vault — not in config files)
5. Auditing:
   - Enable audit logging (who did what, when)
   - Log failed login attempts
   - Log DDL changes (CREATE, ALTER, DROP)
   - Log privilege escalations
   - Log data exports/copies
6. Patching:
   - Security patches applied within [X] days
   - Version is still supported (not EOL)
7. Configuration:
   - Disable unnecessary extensions/features
   - Remove sample databases
   - Restrict COPY/pg_dump to authorized roles
For each item: check command, expected secure state, remediation
Generate 30-40 items.
Format: Database hardening checklist ready for audit.

8. Data Masking Strategy

Role: Data privacy DBA
Task: Design a data masking strategy for [database/environment].
Purpose: [Dev/test data, analytics, offshore development, demo]
Regulations: [GDPR / HIPAA / CCPA / PCI / industry-specific]
Tables with sensitive data: [List with columns and data types]
Masking techniques (per data type):
1. PII (names, emails, phones): Replacement with synthetic data, format-preserving
2. Financial (SSN, credit card): Tokenization or partial mask (last 4 only)
3. Health data: De-identification per HIPAA safe harbor
4. Free text: Regex-based redaction of patterns (emails, phones, SSNs)
Strategy:
1. Static masking (for dev/test — one-time masking, irreversible)
2. Dynamic masking (for analytics — mask at query time, views)
3. Format-preserving encryption (for apps that need format validity)
Implementation:
1. Tool recommendation (Delphix, DataVeil, Informatica, custom scripts)
2. Masking rules (column-level mapping)
3. Non-maskable columns (what to leave alone and why)
4. Validation (how to verify masking is complete and correct)
5. Refresh process (how often to re-mask, trigger, automation)
6. Audit trail (who masked what, when, approved by whom)
Output: Data masking specification ready for implementation.

Maintenance & Migration

9. Database Migration Plan

Role: Database migration DBA
Task: Create a migration plan for [source] to [destination].
Source: [Engine, version, size, schema, special features used]
Destination: [Engine, version, size, features available]
Migration type: [homogeneous (same engine, version upgrade) / heterogeneous (different engine)]
Plan:
1. Pre-migration:
   - Schema compatibility assessment (what works, what needs conversion?)
   - Feature parity check (stored procedures, triggers, functions, extensions)
   - Data type mapping (any types that do not exist in destination?)
   - Application compatibility (queries, connection strings, ORM config)
2. Migration strategy:
   - Full dump and restore (for small DBs or maintenance windows)
   - Logical replication (for minimal downtime)
   - Physical replication (for same-engine migrations)
   - CDC / change data capture (for ongoing sync during transition)
3. Execution:
   - Step 1: Schema migration (DDL, indexes, constraints)
   - Step 2: Data migration (bulk load, verify counts, checksums)
   - Step 3: Object migration (views, functions, procedures, triggers)
   - Step 4: Application cutover (connection strings, test, verify)
4. Validation:
   - Row count comparison (source vs destination)
   - Checksum comparison (full or sample)
   - Query result comparison (run same queries on both, compare)
   - Performance comparison (same query plans? same speed?)
5. Rollback:
   - Cutover criteria (what constitutes success?)
   - Rollback plan (if migration fails, how do we revert?)
   - Dual-run period (how long do we keep both running?)
6. Timeline with milestones, owners, and dependencies
Output: Migration runbook ready for execution.

10. Maintenance Window Planner

Role: DBA scheduling maintenance
Task: Plan a maintenance window for [maintenance task].
Task: [Version upgrade / index rebuild / schema change / vacuum / partition management]
Database: [Engine, size, criticality, user impact]
Window: [Date, start time, end time, duration]
Plan:
1. Pre-maintenance:
   - Notifications (who, when, channel)
   - Backup verification (is the latest backup valid? restore-tested?)
   - Dry run (can we test in staging? when?)
   - Rollback preparation (what is the rollback plan? tested?)
   - Stakeholder sign-off (who approved the window?)
2. During maintenance:
   - Step-by-step procedure (exact commands, in order)
   - Time estimate per step
   - Verification points (what to check between steps)
   - Communication (status updates to stakeholders at intervals)
   - Decision points (at what point do we abort and rollback?)
3. Post-maintenance:
   - Verification (queries work? performance OK? data correct?)
   - Monitoring (watch for issues for [X] hours after)
   - Close notification (all clear message)
   - Post-mortem (if any issues occurred)
   - Documentation update (what changed, what to watch for)
Output: Maintenance runbook with timeline, commands, and checkpoints.

11. Database Health Check Report

Role: DBA performing a database health check
Database: [Engine, version, environment]
Health check categories:
1. Configuration:
   - Key parameters (shared_buffers, work_mem, max_connections, wal settings)
   - Are they optimal for the workload and hardware?
2. Performance:
   - Top 10 slowest queries (from stats)
   - Cache hit ratios (buffer cache, procedure cache)
   - Connection metrics (active, idle, max)
   - Lock waits (are queries waiting on locks? which ones?)
3. Storage:
   - Database/table sizes and growth trends
   - Index bloat (which indexes need REINDEX?)
   - Table bloat (which tables need VACUUM/compaction?)
   - Disk space (how much free? growth rate?)
4. Replication:
   - Replica status (is it connected? lag time?)
   - WAL/binlog generation rate
   - Replication slot health
5. Security:
   - Failed login attempts (brute force?)
   - Superuser/admin role audit
   - Password age for critical accounts
   - SSL connection enforcement
6. Backups:
   - Last backup date and type
   - Last successful restore test
   - Backup size and duration trend
7. Maintenance:
   - Last ANALYZE/VACUUM date per table
   - Long-running transactions (any stuck?)
   - Stale replication slots
   - Orphaned objects
For each category: green/yellow/red status, specific findings, recommendations
Format: Health check report ready for DBA team and management.

12. Query Monitoring Dashboard Spec

Role: Database monitoring specialist
Task: Design a query monitoring dashboard for [database(s)].
Dashboard sections:
1. Overview:
   - QPS (queries per second, by type)
   - Active connections (with breakdown by database/user)
   - Average/p95/p99 query latency
   - Error rate (per second, per type)
2. Slow queries:
   - Top 10 slowest (with query text, avg time, call count)
   - Slow query trend (getting worse?)
   - New slow queries (queries that were not slow before)
3. Resource usage:
   - CPU (per database if possible)
   - Memory (buffer pool, cache hit ratio)
   - Disk I/O (reads, writes, latency)
   - Network (bytes in/out, connections)
4. Locks and waits:
   - Blocking locks (who is blocking whom?)
   - Wait events (what are queries waiting on?)
   - Deadlock count (trending up?)
5. Replication:
   - Replica lag (seconds behind primary)
   - WAL/binlog size and generation rate
   - Replication connection status
6. Maintenance:
   - Long-running queries (> X seconds)
   - Idle in transaction (connections holding locks while idle)
   - Autovacuum/checkpoint progress
   - Bloat estimates (table and index)
Alert rules:
   - p95 latency > X ms for Y minutes -> Warning
   - Connection count > 80% of max -> Warning, > 95% -> Critical
   - Replica lag > X seconds -> Warning
   - Cache hit ratio < 95% -> Warning, < 90% -> Critical
   - Disk space < 20% free -> Warning, < 10% -> Critical
For each panel: metric, source, visualization, threshold, alert
Output: Dashboard specification + alert rule definitions.

Best Practices

For more data content, see our AI prompts for data analysts and AI prompts for system administrators.