What a Senior } should know

Applies algorithmic expertise to database internals: query optimizer algorithm understanding, index selection algorithms for workload optimization, partitioning algorithms for data distribution. Designs efficient data migration algorithms minimizing downtime and lock contention in production databases.

Designs code quality standards for database code: stored procedure conventions, migration script structure, schema change review processes. Refactors complex SQL procedures for performance and readability. Implements automated quality checks for query plans, index usage, and naming consistency.

Selects optimal data structures for database internals: B-tree vs hash index strategies, covering indices for query optimization, partitioning schemes for time-series and archival data. Optimizes table structures considering storage engine specifics (InnoDB page layout, PostgreSQL TOAST). Designs efficient schema structures for high-throughput OLTP and analytical workloads.

Architects Cassandra clusters for high-availability across multiple datacenters. Designs replication and sharding strategies for petabyte-scale systems. Leads capacity planning, disaster recovery procedures, and performance tuning at scale.

Designs enterprise-grade backup architectures for high-load multi-database environments. Implements automated DR failover with sub-minute RTO targets. Optimizes backup strategies for petabyte-scale databases using parallel backup, compression, and deduplication. Leads DR exercises.

Designs ClickHouse clusters: ReplicatedMergeTree, distributed tables, sharding. Optimizes schema design for DBA analytics: query logs, slow query analysis, performance trends. Configures backup and disaster recovery.

Designs database architecture with CockroachDB for high-load systems. Optimizes performance, configures replication and sharding.

Designs data models for high-load systems: sharding with data distribution awareness, temporal tables, polymorphic associations. Defines schema evolution strategy: backward compatible changes, expand-and-contract pattern.

Designs indexing strategy for high-load: partial indexes, expression indexes, GIN/GiST for specialized types. Performs online index creation without locks. Balances read performance vs write overhead.

Designs zero-downtime migrations: online DDL, pt-online-schema-change/gh-ost for MySQL, logical replication for PostgreSQL. Plans rollback strategy for each migration. Automates migration testing in staging.

Designs database architecture with DynamoDB for high-load systems. Optimizes performance, configures replication and sharding.

Designs MongoDB database architecture for high-load production systems with complex access patterns. Optimizes performance through advanced indexing, query profiling, and write concern tuning. Configures multi-node replication topologies and implements sharding strategies based on data distribution analysis.

Designs MySQL architecture for high-load: InnoDB cluster, Group Replication, ProxySQL for connection pooling and read/write splitting. Performs online DDL, pt-online-schema-change. Configures PITR and automated failover.

Designs database architecture with Neo4j for high-load systems. Optimizes performance, configures replication and sharding.

Designs PostgreSQL HA: streaming replication, Patroni for automated failover, logical replication for zero-downtime migrations. Optimizes: partitioning, partial indexes, parallel queries. Configures PITR via WAL archiving.

Designs query optimization workflow: automated slow query detection, query plan regression testing, performance baselines. Optimizes at the architectural level: materialized views, denormalization, read replicas for offloading.

Designs enterprise replication architectures: multi-region active-active setups, conflict resolution strategies, and zero-downtime migration plans. Optimizes replication for high-throughput OLTP and mixed workloads. Architects disaster recovery with automated failover, RPO/RTO guarantees, and regular DR drills.

Designs transaction strategies for high-load distributed database systems with cross-shard consistency requirements. Implements advanced concurrency control mechanisms including MVCC tuning, serializable snapshot isolation, and distributed transactions. Optimizes transaction log performance for write-heavy workloads. Mentors team on transaction design.

Designs database architecture with Vitess for high-load systems. Optimizes performance, configures replication and sharding.

Designs AWS database architecture: Aurora Global Database for multi-region, DynamoDB for specific workloads, cross-account database access. Configures AWS Backup, Database Migration Service. Automates via CDK/Lambda.

Designs container strategy for database workloads: stateful containers vs bare-metal, storage drivers for I/O-intensive loads. Optimizes Docker networking for replication. Determines when containers are suitable for production DBs.

Designs IaC for the database platform: reusable modules for different DBMSes (MySQL, PostgreSQL, ClickHouse), composition patterns. Automates database infrastructure drift detection. Implements blue-green for database upgrades via Terraform.

Designs CI/CD platform for multiple projects. Creates reusable workflows/templates. Optimizes build time at the organizational level. Configures security scanning in pipelines. Implements deployment strategies (blue-green, canary). Integrates with ArgoCD/FluxCD.

Designs secrets management for the database tier: Vault database engine for all DBMSes, automated credential rotation, emergency revocation procedures. Configures audit logging for database access via Vault.

Effectively uses AI tools for complex DBA tasks: query optimization, partition strategy generation, writing backup/recovery scripts. Defines AI applicability boundaries for database administration.

Designs capacity planning for the database tier: data growth forecasting per table, connection pool sizing, memory and IOPS planning. Builds capacity models considering seasonal patterns and business growth.

Designs DR for the database tier: multi-region replication, automated failover (Patroni, MHA), backup verification through automated restores. Defines RPO/RTO for different tiers. Conducts DR drills.

Designs observability strategy with business metrics. Implements distributed tracing. Defines SLI/SLO. Conducts post-mortems.

Designs database log analytics on ELK: centralized log collection from all DBMSes, correlation between query logs and system events. Configures alerting on anomalies: unusual error rates, replication breaks, auth failures.

Designs on-call processes for the DBA team: alert routing by severity, escalation policies, runbook automation. Mentors junior DBAs in incident response. Implements automated remediation for common database issues.

Designs database monitoring platform on Prometheus/Grafana: multi-database monitoring, custom exporters for specific metrics, recording rules for aggregation. Implements SLI-based alerting for database services.

Designs SLI/SLO framework for the database tier: multi-tier SLO (critical vs standard databases), SLI by operation type (read vs write latency). Implements error budget policies and automated remediation on SLO breach.

Designs structured logging standard for the database tier: schema for different operations (DDL, DML audit, replication events), contextual fields (query_hash, execution_plan). Integrates with the observability platform.

Conducts deep database architecture review: evaluating data model decisions, partitioning strategy, replication impact. Mentors through review: explains normalization trade-offs, index design. Automates SQL review checks.

Designs Git workflow for database changes: trunk-based development for migrations, feature branches for schema experiments. Automates: pre-commit hooks for SQL validation, automated changelog generation.

Creates comprehensive database documentation: architecture decision records for DBMS selection, capacity planning documents, disaster recovery plans. Documents replication topology and failover procedures.

Designs runbook framework for the DBA team: standardized templates, automated runbooks via scripts, monitoring alert integration. Implements executable runbooks for automating common database operations.

Designs enterprise-level database benchmarking strategies for capacity planning and architecture decisions. Creates custom workload simulations reflecting production traffic patterns with realistic data distributions. Establishes benchmarking standards and automated regression detection. Mentors team on performance analysis methodology.

Designs CPU performance strategy for the database tier: workload classification (OLTP vs OLAP), CPU affinity for critical databases, connection pool tuning for optimal CPU usage. Implements automated query throttling.

Designs comprehensive database performance architectures including partitioning schemes, tiered storage strategies, and advanced replication topologies. Masters buffer pool internals, page eviction policies, and OS-level tuning for I/O-bound workloads. Implements proactive performance anomaly detection, capacity planning models, and conducts root cause analysis for complex multi-node performance degradations.

Designs I/O profiling strategies for production database clusters on SSD, NVMe, and networked storage. Implements monitoring of read/write amplification, checkpoint I/O spikes, and replication lag from disk contention. Mentors DBAs on correlating storage metrics with query performance degradation.