Role-Based Guides

WhoDB for Database Administrators

As a database administrator, you manage complex database infrastructure supporting multiple applications and teams. WhoDB provides DBA-specific capabilities for schema exploration, multi-database management, data integrity verification, and administrative maintenance tasks.

Tip

This guide covers schema management, multi-database workflows, data integrity checks, and monitoring patterns essential for effective database administration.

DBA Administration Capabilities

Schema Management

Explore, understand, and document database schemas across your infrastructure

Multi-Database Access

Manage multiple database instances, environments, and replica servers

Data Integrity

Verify data consistency, check constraints, and identify orphaned records

Maintenance Tasks

Execute administrative queries and maintenance operations

Multi-Database Management

Managing Multiple Connection Profiles

Create Environment-Specific Profiles

Set up separate connection profiles for your database infrastructure:

Typical DBA environment structure:

Production: Primary production database (read-only access recommended)
Production Replica: Read-only replica for analysis and backups
Staging: Pre-production environment mirroring production
Development: Shared team development database
Local: Local development database for quick testing

Warning

Configure production connections with read-only credentials to prevent accidental modifications.

Switch Between Databases

Quickly switch connection context using the database selector:

Sidebar Database Selector

This lets you compare schemas or data across environments without reconnecting.

Organize by Environment

Use consistent naming for profiles:

prod-primary, prod-replica-1, prod-replica-2
staging-main, staging-mirror
dev-main, dev-local

Clear naming prevents dangerous mistakes when connecting to production.

Cross-Database Schema Comparison

Schema Exploration & Documentation

Understanding Your Database Structure

Navigate Table Metadata

Open any table and review its structure:

Explore Table Metadata

Review:

Column names, types, and nullability
Primary keys and their data types
Constraints (UNIQUE, NOT NULL, CHECK)
Indexes and their coverage

Identify Primary Keys

Verify that each table has appropriate primary key definition:

Primary Key Column

For effective DBA work, document:

What is the primary key (usually ID or UUID)?
Is it auto-incrementing or application-generated?
Are there any surrogate keys vs natural keys?
How are distributed systems handling ID generation?

Verify Foreign Key Relationships

Check how tables relate to each other:

Foreign Key Columns

Document:

Which tables reference which
Referential integrity constraints
Foreign key index presence
Cascading behavior on deletes/updates

Document Index Strategy

Review indexes to ensure optimal query performance:

Explore Table With Indexes

Check:

Are heavily-queried columns indexed?
Are all foreign keys indexed?
Are there redundant or unused indexes?
Is index maintenance scheduled appropriately?

Visualizing Schema Topology

Open Graph Visualization

Access the schema graph to visualize your entire database structure:

Graph View Schema Topology

The graph displays:

All tables and their relationships
Foreign key connections
Data flow between entities

Analyze Relationships

Use the graph to understand your data model:

Graph View With Controls

Identify:

Central tables that many others depend on
Orphaned or isolated tables
Highly interconnected subsystems
Potential data redundancy or denormalization

This visual helps you understand the impact of schema changes.

Review Node Cards

Click nodes to load columns when needed:

Graph Node Card

Node cards render metadata and loaded columns directly. Use the visible Data button to open the object's data page.

Data Integrity & Verification

Integrity Checking Queries

Open Scratchpad for Queries

Use the Scratchpad to write data integrity verification queries:

Scratchpad Main View

Create a standard DBA query library for regular integrity checks.

Check Referential Integrity

Verify that foreign keys reference valid data:

sql

-- Find orphaned orders (no matching customer)
SELECT o.order_id, o.customer_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

-- Find dangling order items (no matching order)
SELECT oi.item_id, oi.order_id
FROM order_items oi
LEFT JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_id IS NULL;

These queries identify data inconsistencies that violate referential integrity.

Verify Constraint Compliance

Check that data respects defined constraints:

sql

-- Find rows violating NOT NULL constraints
SELECT * FROM users WHERE email IS NULL;

-- Find rows violating UNIQUE constraints
SELECT email, COUNT(*) as count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Find rows violating business logic constraints
SELECT * FROM orders
WHERE total_amount <= 0
  OR created_at > NOW()
  OR status NOT IN ('pending', 'completed', 'cancelled');

Regular constraint verification catches data quality issues early.

Monitor Table Health

Track table statistics for capacity planning:

sql

-- Table size and row count analysis
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,
  (SELECT COUNT(*) FROM schemaname||'.'||tablename) as row_count
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

This helps you understand table growth and storage requirements.

Check for Gaps in Sequences

For auto-increment fields, verify no gaps or anomalies:

sql

-- Find missing user IDs (gaps in sequence)
WITH sequence AS (
  SELECT generate_series(MIN(id), MAX(id)) as id
  FROM users
)
SELECT s.id
FROM sequence s
LEFT JOIN users u ON s.id = u.id
WHERE u.id IS NULL
ORDER BY s.id;

Gaps sometimes indicate deleted records or application issues.

Data Quality Audits

Execute Quality Check Query

Write comprehensive data quality queries:

Scratchpad Query Results

sql

-- Comprehensive data quality audit
SELECT
  CASE
    WHEN email IS NULL THEN 'Missing email'
    WHEN email NOT LIKE '%@%.%' THEN 'Invalid email format'
    WHEN phone IS NULL THEN 'Missing phone'
    WHEN created_at > NOW() THEN 'Future creation date'
    WHEN age < 0 OR age > 150 THEN 'Invalid age'
    WHEN DATEDIFF(NOW(), created_at) > 365*100 THEN 'Account older than 100 years'
    ELSE NULL
  END as quality_issue,
  COUNT(*) as affected_records
FROM users
WHERE (email IS NULL
  OR email NOT LIKE '%@%.%'
  OR phone IS NULL
  OR created_at > NOW()
  OR age < 0 OR age > 150
  OR DATEDIFF(NOW(), created_at) > 365*100)
GROUP BY quality_issue
ORDER BY affected_records DESC;

This aggregates quality issues for reporting and prioritization.

Review Results

Examine the quality report:

Scratchpad Query Error

Document:

How many records have issues
What types of issues are most common
Which tables are most affected
Severity of each issue type

Plan Remediation

Based on the audit:

Identify root causes of quality issues
Plan data cleanup procedures
Implement validation rules to prevent recurrence
Document findings for stakeholders

Administrative Maintenance Tasks

Schema Modification Verification

Before and after schema changes, verify your modifications:

sql

-- Verify new column was added correctly
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'orders'
ORDER BY ordinal_position;

-- Check index creation
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders';

-- Verify constraint creation
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'orders';

Tip

Always verify schema changes after applying them. Document the modifications for audit trails.

Statistics & Performance Monitoring

Monitor database performance over time:

sql

-- Monitor slow queries
SELECT
  query,
  calls,
  total_time,
  mean_time,
  max_time
FROM pg_stat_statements
WHERE mean_time > 100
ORDER BY mean_time DESC
LIMIT 20;

-- Check table bloat (dead tuples)
SELECT
  schemaname,
  tablename,
  ROUND(dead_tuples::float / live_tuples * 100, 2) as dead_percent
FROM pg_stat_user_tables
WHERE live_tuples > 0
  AND ROUND(dead_tuples::float / live_tuples * 100, 2) > 10
ORDER BY dead_percent DESC;

Regular monitoring helps you maintain optimal database performance.

Cleanup & Maintenance

Execute administrative cleanup tasks safely:

sql

-- Delete old archive records (with backup first)
DELETE FROM audit_logs
WHERE created_at < NOW() - INTERVAL '2 years'
AND archived = true;

-- Mark records for deletion rather than immediate delete
UPDATE orders
SET is_deleted = true, deleted_at = NOW()
WHERE created_at < NOW() - INTERVAL '5 years'
  AND status = 'cancelled';

Warning

Always have backups before executing destructive operations. Test cleanup queries on non-production first.

DBA Best Practices

Document Everything

Maintain documentation of your schema, constraints, relationships, and standard procedures.

Use Read-Only Access

Configure production connections as read-only to prevent accidents.

Regular Integrity Checks

Schedule routine data integrity verifications to catch issues early.

Version Control Schemas

Track schema changes and have a rollback plan for each modification.

Monitor Performance

Regularly check slow queries, indexes, and table statistics.

Test First

Test all queries and procedures on development/staging before production.

Query Repository for DBAs

Common DBA Queries

Database Health Check

sql

-- Overall database health summary
SELECT
  datname,
  numbackends as active_connections,
  xact_commit as transactions_committed,
  xact_rollback as transactions_rolled_back,
  tup_returned as tuples_read,
  tup_fetched as tuples_fetched,
  tup_inserted as tuples_inserted,
  tup_updated as tuples_updated,
  tup_deleted as tuples_deleted
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1', 'postgres');

Missing Indexes on Foreign Keys

sql

-- Identify foreign keys without corresponding indexes
SELECT
  tc.table_name,
  kcu.column_name,
  'MISSING INDEX' as status
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND NOT EXISTS (
    SELECT 1 FROM information_schema.statistics s
    WHERE s.table_name = tc.table_name
    AND s.column_name = kcu.column_name
  );

Connection Analysis

sql

-- Who is connected and what are they doing?
SELECT
  usename,
  application_name,
  state,
  query,
  state_change,
  query_start
FROM pg_stat_activity
WHERE datname = current_database()
  AND usename != 'postgres'
ORDER BY query_start DESC;

Troubleshooting Common DBA Issues

Next Steps

Enhance your DBA capabilities:

Database Exploration

Master schema exploration and documentation

Query Writing

Learn advanced SQL for administrative tasks

Data Export

Export schema documentation and reports

Where Conditions

Master filtering for targeted administrative queries

Check

With these DBA tools and patterns, you can effectively manage your database infrastructure, verify data integrity, track schema changes, and maintain optimal performance across multiple database instances. WhoDB provides the administrative capabilities essential for professional database administration.

WhoDB for Data Analysts

WhoDB for QA Engineers & Testers