Availability & Reliability
These terms describe keeping the database running even if something breaks.
Availability & Reliability
- Failover: Automatically switching to a backup server when the primary one fails.
- Failback: Moving the system back to the original primary server once it has been repaired.
- Heartbeat: A signal sent between servers to confirm they are still "alive" and functioning.
- Quorum: A voting system where a majority of servers must agree on the cluster's state to prevent data conflicts.
- High Availability (HA): A system design that aims for maximum uptime (often measured in "nines," like 99.99%).
- Disaster Recovery (DR): The broad plan for restoring data and services after a major site failure or catastrophe.
Scaling & Traffic Management
These terms describe how to handle "too much traffic" by adding resources.
- Vertical Scaling (Scale-Up): Adding more power (CPU, RAM) to a single existing server.
- Horizontal Scaling (Scale-Out): Adding more servers to a group to share the workload.
- Load Balancing: Distributing incoming requests across multiple database servers so no single one is overwhelmed.
- Read Replicas: Copying data to secondary "read-only" servers to offload traffic from the main database.
- Sharding: Splitting a large database into smaller, faster pieces called "shards" distributed across different servers.
- Caching: Storing frequently accessed data in a very fast, temporary memory area (like Redis) to avoid hitting the database at all.
Data Consistency
When you have multiple databases, you have to decide how they stay in sync.
- Replication: The process of copying data from one server to another.
- Synchronous Replication: The primary waits for the secondary to confirm it saved the data before finishing. (Safest, but slower).
- Asynchronous Replication: The primary saves data and continues immediately; the secondary catches up a moment later. (Faster, but risk of "Eventual Consistency").
- CAP Theorem: A principle stating a distributed system can only provide two of three guarantees: Consistency, Availability, and Partition Tolerance.
Performance Metrics
How you measure if the traffic management is actually working.
- Latency: The time it takes for a single request to travel to the database and back.
- Throughput: The total amount of work or number of queries the database handles in a given time (e.g., queries per second).
- RPO (Recovery Point Objective): How much data you can afford to lose (e.g., "we can lose up to 15 minutes of data").
- RTO (Recovery Time Objective): How quickly you need to be back online after a failure.
Data Modeling & Schema Design
- Conceptual, logical, and physical data modeling
- Table design principles
- Table normalization (1NF → 3NF, BCNF)
- When to denormalize for performance
- Primary keys, foreign keys, and constraints
- Data types selection (including handling long text, JSON, spatial, etc.)
- Designing audit tables & change-tracking strategies
- Designing for soft deletes vs hard deletes
- Designing for multi-tenant systems (shared vs isolated schemas)
Performance-Oriented Schema Considerations
- Indexing strategy (clustered, non-clustered, filtered, covering indexes)
- Partitioning large tables
- Archival strategies (e.g., cold storage like AWS Glacier)
- Data warehouse schema design (star schema, snowflake schema)
- Materialized views / indexed views
- Handling large objects (LOBs) efficiently
- Avoiding unnecessary wide tables
Stored Logic & Processing
- Stored procedures vs middle-tier processing
- Stored procedure optimization techniques
- Functions: scalar vs table-valued (performance differences)
- What to avoid:
- Overuse of scalar functions in SELECT
- Recursive functions unless necessary
- Triggers for business logic (use sparingly)
- Batch processing vs real-time processing
- ETL/ELT design patterns
Query Optimization & Monitoring
- Query execution plans
- Identifying slow queries
- SQL monitoring tools (native + third-party)
- Queries to inspect running processes
- Identifying blocking and deadlocks
- Killing long-running sessions safely
- Statistics maintenance (update stats, auto-stats)
- Index maintenance (rebuild/reorganize)
Front-End & Application Layer Considerations
- Grid design: how much data to load into UI
- Pagination vs infinite scroll
- Caching strategies (client-side, server-side, distributed cache)
- Avoiding SELECT *
- Minimizing round trips to the database
- Using parameterized queries to prevent SQL injection
- Connection pooling considerations
- Implement a progress indicator for operations that take a long time to complete.
Reporting & Analytics
- One-time report generation vs on-demand
- Pre-aggregated tables for heavy analytics
- Data warehouse vs OLTP separation
- Using OLAP cubes or columnar storage
- Scheduling vs real-time dashboards
Security & Compliance
- Role-based access control (RBAC)
- Row-level security
- Encryption at rest and in transit
- Auditing and logging access
- Secure handling of credentials
- Least-privilege principle
- Data masking / anonymization for non-prod environments
- SQL injection prevention
Technology Choices & Alternatives
- SQL vs NoSQL — when each is faster or more appropriate
- Document stores vs relational tables
- Flat-file search (Lucene, ElasticSearch)
- In-memory databases (Redis, Memcached)
- Cloud-native database services (RDS, DynamoDB, Cosmos DB)
Maintenance & Operations
- Backup and restore strategy
- Disaster recovery (RPO/RTO planning)
- High availability (replication, clustering, failover)
- Schema migration/versioning (Flyway, Liquibase)
- Capacity planning and storage forecasting
- Monitoring disk I/O, CPU, memory, tempdb usage
Additional Topics Commonly Missed
- Concurrency control (optimistic vs pessimistic locking)
- Transaction isolation levels
- Handling long-running transactions
- Logging strategy (minimal logging where appropriate)
- API design for data access
- Testing database performance under load
- Data governance & lifecycle management