Database Management with AI
Master database schema design, write safe migrations, optimise slow queries, and manage production data — with AI as your DBA partner.
Schema design principles
A good database schema is like a good building foundation — get it right and everything built on top is solid. Get it wrong and you spend years working around the problems. Ask Claude Code: Create a PostgreSQL database schema for a project management application. The application tracks organizations, users, projects, tasks, comments, and activity logs. Design the schema following these principles: normalise to third normal form (no repeated data), use UUIDs as primary keys (avoid sequential IDs that leak information about your user count), add created_at and updated_at timestamps to every table, use appropriate column types (TEXT not VARCHAR for strings in PostgreSQL, TIMESTAMPTZ not TIMESTAMP for times), add NOT NULL constraints on all required fields, and define foreign keys with appropriate ON DELETE behaviour (CASCADE for dependent data, SET NULL for optional relationships, RESTRICT for data that should not be orphaned). Save the schema as schema.sql. Claude Code should produce 7 to 8 tables with clear relationships. Ask Claude Code: Review this schema for common design mistakes. Check for: missing indexes on foreign key columns (PostgreSQL does not auto-index foreign keys), missing unique constraints where business logic requires uniqueness (like one membership per user per organization), columns that should be enums instead of free text, and missing indexes on columns that will be frequently filtered or sorted. Apply the fixes and explain each one. Then ask: Add a soft delete pattern to the tasks and projects tables using a deleted_at column instead of actually removing rows. Update the foreign key relationships to account for soft deletes.
Migration workflows that do not break production
Schema changes in production are risky — a bad migration can cause downtime or data loss. Ask Claude Code: Set up a migration system using a dedicated migrations folder. Create a migration runner script that tracks applied migrations in a schema_migrations table, runs pending migrations in order, wraps each migration in a transaction so failures roll back cleanly, and supports both up (apply) and down (revert) directions. Create the initial migration that builds the schema from the previous section. Then create a second migration that adds a tags table and a task_tags junction table. Run both migrations and verify the schema is correct. The migration runner should output which migrations were applied and how long each took. Ask Claude Code: Create a migration for a tricky scenario — renaming a column from name to full_name in the users table. This is dangerous because application code references the old column name. Show me the safe way to do this: first add the new column, then backfill data from the old column, then update application code to use the new column, then remove the old column. This is a multi-step migration that should be split across multiple deployments. Create each step as a separate migration with clear comments. Ask Claude Code: Add migration safety checks. Before running a migration, analyse its SQL for dangerous operations: DROP TABLE without a backup step, ALTER TABLE that locks the table for a long time (like adding a column with a default value on a large table in older PostgreSQL), DELETE without a WHERE clause, and modifying columns that are referenced by application code. Flag these with warnings and require explicit confirmation. Create a pre-migration checklist script that connects to the production database, checks the table sizes affected by the migration, estimates the lock duration, and recommends a maintenance window if the migration will take more than 30 seconds.
Query optimisation with EXPLAIN ANALYSE
Slow queries are the most common database performance problem. Ask Claude Code: Create a set of sample queries for the project management schema that demonstrate different performance characteristics. Include a simple query that is already fast, a query that does a full table scan because it is missing an index, a query with an N+1 problem (fetching tasks and then their comments one by one), a query that joins 4 tables and could benefit from better join order, and a complex aggregation that calculates project statistics. For each query, show me the EXPLAIN ANALYSE output and explain what it means. EXPLAIN ANALYSE runs the query and shows the execution plan — how PostgreSQL decides to execute it. Key metrics: Seq Scan means reading every row in a table (slow for large tables), Index Scan means using an index (fast), Nested Loop means iterating through one table for each row in another (fine for small tables, deadly for large ones), Hash Join means building a hash table for efficient matching (good for large joins). Ask Claude Code: Fix each slow query. For the full table scan, add the appropriate index. For the N+1 problem, rewrite as a single query with a JOIN. For the multi-table join, rewrite with explicit join hints or restructure to help the query planner. For the aggregation, add a materialised view that pre-computes the result. Show the EXPLAIN ANALYSE before and after each fix with the improvement in execution time. Ask Claude Code: Create a query monitoring script that logs all queries taking more than 100 milliseconds. For each slow query, capture the SQL, the execution time, the EXPLAIN plan, and the calling context. Store in a slow_queries log table. Create a dashboard showing the top 10 slowest queries this week with their frequency and average duration.
Indexing strategy and performance tuning
Indexes make reads faster but writes slower. The art is choosing the right indexes. Ask Claude Code: Analyse the project management schema and recommend an indexing strategy. For each table, identify which columns need indexes based on common query patterns: primary keys (already indexed), foreign keys (need explicit indexes in PostgreSQL), columns used in WHERE clauses (filtering), columns used in ORDER BY (sorting), and columns used in JOIN conditions. Create the indexes as a migration. For each index, explain the type: B-tree (default, good for equality and range queries), GIN (good for array and full-text search), and partial indexes (index only rows matching a condition, like WHERE deleted_at IS NULL). Ask Claude Code: Demonstrate the impact of indexes with benchmarks. Create a script that inserts 100000 sample tasks. Run the slow queries from the previous section before and after adding indexes. Show the execution time difference. A well-placed index can turn a 2-second query into a 2-millisecond query — a 1000x improvement. Ask Claude Code: Add composite indexes where multiple columns are frequently queried together. For example, tasks are often filtered by project_id AND status, so a composite index on (project_id, status) is more efficient than separate indexes on each column. Explain index column ordering — the column with higher selectivity (more unique values) should usually come first. Ask Claude Code: Create an index maintenance script. Over time, indexes can become bloated as rows are inserted, updated, and deleted. The script should check index bloat using pg_stat_user_indexes, identify unused indexes that slow down writes without benefiting reads, find duplicate indexes where one index covers another, and recommend REINDEX for bloated indexes. Run this monthly to keep the database performing well.
Data backup and disaster recovery
Database loss is an extinction-level event for a business. Ask Claude Code: Create a comprehensive backup system for the PostgreSQL database. Build a backup script that runs pg_dump to create a compressed SQL backup file. Name the file with a timestamp: backup-2024-01-15T10-30-00.sql.gz. Upload the backup to an S3-compatible storage bucket (like Cloudflare R2) using the AWS SDK. Keep the last 7 daily backups, 4 weekly backups, and 3 monthly backups — delete older ones automatically. Log each backup with the filename, size, duration, and success or failure status. Create a cron schedule that runs the backup daily at 3am when traffic is lowest. Ask Claude Code: Create a restore procedure and test it. Build a restore script that downloads a backup from storage, creates a temporary database, restores the backup into it, runs basic integrity checks (row counts match expected ranges, foreign keys are valid, critical tables exist), and if all checks pass, optionally swaps the temporary database with the production database. Test the restore by backing up, making some changes, restoring, and verifying the changes are gone. Regular restore testing is essential — a backup you cannot restore is not a backup. Ask Claude Code: Add point-in-time recovery using WAL (Write-Ahead Log) archiving. Configure PostgreSQL to archive WAL files to S3. This allows restoring the database to any point in time, not just the last backup. Explain the recovery process: restore the base backup, then replay WAL files up to the desired timestamp. This is critical for recovering from accidental data deletion — you can restore to one minute before the DELETE was run. Ask Claude Code: Create a disaster recovery runbook as a markdown document. Include step-by-step procedures for: restoring from a backup, recovering from accidental data deletion, handling database corruption, failing over to a replica, and communicating with customers during an outage. This runbook should be reviewed and updated quarterly.
Database monitoring and alerting
You need to know when something goes wrong before users tell you. Ask Claude Code: Create a database monitoring system. Build a health check endpoint at /api/db-health that checks: connection pool status (active connections, idle connections, waiting connections), replication lag if using replicas, oldest running transaction (long transactions can cause problems), table bloat for the top 10 largest tables, cache hit ratio (should be above 99 percent), and deadlock count. Return the results as JSON with a status of healthy, degraded, or critical based on thresholds. Ask Claude Code: Add alerting rules. Create a monitoring script that runs every 5 minutes and checks the health endpoint. Send an email alert when: connection pool utilisation exceeds 80 percent, any query takes longer than 5 seconds, cache hit ratio drops below 95 percent, disk usage exceeds 80 percent, or replication lag exceeds 30 seconds. Each alert should include the current value, the threshold, and a recommended action. Implement alert deduplication — do not send the same alert repeatedly if the condition persists. Send a resolution notification when the condition clears. Ask Claude Code: Create a performance dashboard that shows: queries per second over the last 24 hours, average query duration by table, connection pool usage over time, storage growth trend, and the slowest queries from the last hour. Use the pg_stat_statements extension which tracks query statistics automatically. This dashboard gives you at-a-glance visibility into database health. Review it weekly and investigate any trends: is storage growing faster than expected? Are certain queries getting slower as data grows? Is the connection pool approaching capacity? Proactive monitoring catches problems while they are small — before they become outages.
Production data management patterns
Managing data in production requires care — one wrong query can delete customer data. Ask Claude Code: Create a set of safe data management procedures. First, build a read-only database connection for all analytical and reporting queries. This connection uses a read replica (or a read-only transaction in a single-database setup) that physically cannot modify data. Use this connection for dashboards, reports, and data exploration. Second, create a data modification workflow: every production data change should be written as a SQL script, reviewed by another person, tested on a staging database, run inside a transaction, and logged with who ran it, when, what changed, and a rollback script. Build a tool that enforces this workflow — it takes a SQL script, runs it in a transaction, shows the affected rows, asks for confirmation, and logs the result. Ask Claude Code: Create a data anonymisation tool for development environments. Build a script that copies production data to a development database, then anonymises sensitive fields: replace real names with generated names, replace emails with fake emails, replace addresses with fake addresses, and hash or remove payment information. Keep the data realistic in structure while removing personally identifiable information. This lets developers work with realistic data without privacy risks. Ask Claude Code: Add a data retention system. GDPR requires deleting personal data when it is no longer needed. Create a retention policy configuration that specifies how long each type of data should be kept: user activity logs for 90 days, closed support tickets for 1 year, deleted accounts for 30 days after deletion request. Build a script that enforces the policy by permanently deleting expired data. Run it weekly. Log every deletion for compliance audit purposes. The database is the most valuable part of your application — it contains your users, their data, and your business history. Managing it with the care and discipline described in this guide protects both your business and your customers.
Data Architecture
This guide is hands-on and practical. The full curriculum covers the conceptual foundations in depth with structured lessons and quizzes.
Go to lesson