Database Operations
Last reviewed: August 2026
Overview
Section titled “Overview”A managed cloud DB automates infrastructure operations (patching, backup, HA), but data design and query performance don’t automatically improve. This document covers the key topics you need to know for the operational phase after you’ve chosen a DB.
RDB scaling patterns
Section titled “RDB scaling patterns”Unlike NoSQL, horizontal scaling (sharding) is difficult for an RDB. Most scaling is achieved through read distribution.
| Step | Method | Effect | Example |
|---|---|---|---|
| 1. Vertical scaling | Upgrade the instance type (increase CPU/memory) | Simplest. Has a limit | db.r5.large → db.r5.4xlarge |
| 2. Read replicas | Distribute read traffic to replicas | Effective for workloads that are 80%+ reads | Serving product-list queries from a replica |
| 3. Cache layer | Store frequently read data in a cache (Redis/Valkey) | Dramatically reduces DB load. Millisecond responses | Caching sessions, popular products, config values |
| 4. CQRS | Separate the write (command) and read (query) DBs | Each can be optimized independently | Order writes go to the RDB; search/dashboards use a separate read DB |
| 5. Sharding | Distribute data across multiple DBs | Last resort. Sharply increases app complexity | Splitting the DB by user ID |
Query performance management
Section titled “Query performance management”| Problem | Symptom | Response |
|---|---|---|
| Indiscriminate JOINs | Joining many tables makes responses take several seconds or more | Split off read replicas, denormalize, move analytical queries to a DW |
| Missing indexes | Full table scans. Gets progressively slower as data grows | Check the execution plan (EXPLAIN), add indexes matching query patterns |
| Ignoring slow queries | A specific query degrades performance for the entire DB | Enable the slow query log, review it regularly, refactor queries |
| Using the DB as storage | Unbounded accumulation of logs/events in the RDB | Move time-series data to object storage or a time-series DB |
Index design basics
Section titled “Index design basics”Cardinality: the number of unique values in a column. A user ID (millions of values) has high cardinality, while gender (2 values) has low cardinality. Indexing high-cardinality columns has the greatest effect.
| Principle | Description |
|---|---|
| Base it on the WHERE clause | Index columns you filter on frequently |
| Check cardinality | Columns with many unique values benefit most from an index. Low-cardinality columns like booleans see little benefit |
| Compound index order | Place the most selective (highest-cardinality) column first |
| Covering index | Including the SELECT columns in the index lets queries respond without touching the table |
| Beware excessive indexing | Too many indexes degrade write performance. Consider your read/write ratio |
High availability (HA)
Section titled “High availability (HA)”| Method | Behavior | RPO | RTO | Cost | Suitable for |
|---|---|---|---|---|---|
| Multi-AZ synchronous replication | Synchronous replication across multiple AZs within the same region. Automatic failover | 0 | Tens of seconds to a few minutes | Medium (standby instance cost) | Production default |
| Read replicas | Asynchronous replication. Read distribution + manual promotion for DR | A few seconds (replication lag) | A few minutes (manual promotion) | Low | Read-load distribution + lightweight DR |
| Cross-region replication | Asynchronous replication to another region. For regional-outage protection | A few seconds to a few minutes | A few minutes (manual promotion) | High (cross-region transfer) | Regional-outage DR |
HA services by vendor
Section titled “HA services by vendor”| Feature | AWS | Azure | Google Cloud | OCI |
|---|---|---|---|---|
| Multi-AZ synchronous | RDS Multi-AZ, Aurora storage replication | Zone-redundant HA | Cloud SQL HA | ADB automatic HA |
| Read replica | Aurora Read Replica | Read Replica | Cloud SQL Read Replica | ADB Read-only Replica |
| Cross-region | Aurora Global Database | Geo-replication | Cross-Region Replica | Autonomous Data Guard |
Backup and PITR
Section titled “Backup and PITR”| Vendor | Backup retention | PITR |
|---|---|---|
| AWS RDS / Aurora | Up to 35 days | Second-level recovery |
| Azure SQL Database | Up to 35 days | Second-level recovery |
| Google Cloud Cloud SQL / AlloyDB | Up to 365 days | Second-level recovery |
| OCI Autonomous Database | Up to 60 days | Second-level recovery |
Connection pool management
Section titled “Connection pool management”DB connections are a finite resource. In serverless/auto-scaling environments especially, connections can explode when instances spike.
| Problem | Response |
|---|---|
| Connection exhaustion | Use a connection pooler (RDS Proxy, Cloud SQL Auth Proxy, PgBouncer) |
| Serverless function concurrency spikes | Limit with Reserved Concurrency + a connection pooler is essential |
| Idle connections holding resources | Set idle timeouts, reuse connections |
NoSQL key design anti-patterns
Section titled “NoSQL key design anti-patterns”Unlike an RDB, NoSQL requires you to define query patterns first and design keys accordingly. Symptoms commonly seen in operations (hot partitions, throttling) originate from key design mistakes made at the design stage.
Cache operations
Section titled “Cache operations”Cache is a key tool for reducing DB load, but it has its own operational considerations.
Cache operations considerations
Section titled “Cache operations considerations”- Cache stampede — Hundreds of requests hit the DB simultaneously when a TTL expires. Prevent with random TTL jitter or lock-based refresh
- Cache warm-up — Right after a deploy/restart, the cache is empty and DB load spikes. A pre-warming script is needed
- Memory management — Check the eviction policy (LRU, LFU) for when cache memory is exceeded. Make sure important data isn’t evicted
- Don’t use the cache as permanent storage — Design assuming the cache can disappear at any time. The source of truth must always be the DB
- Set a TTL on every key — Caching without a TTL leaves data around forever, causing inconsistency with the DB
Common mistakes
Section titled “Common mistakes”- Adding indexes without EXPLAIN — Adding an index without checking the execution plan can degrade write performance with no read improvement.
- Operating in a serverless environment without a connection pooler — When Lambda/Functions concurrency spikes, DB connections get exhausted. Always use a connection pooler such as RDS Proxy.
- Backups configured but recovery never tested — Even with backups in place, if the recovery procedure isn’t validated, recovery can fail during an actual incident.
Checklist
Section titled “Checklist”- Is the slow query log enabled, and do you have a process to review it regularly?
- Is high availability configured via multi-AZ or read replicas?
- Have you actually tested PITR (point-in-time recovery)?
Related Documents
Section titled “Related Documents”- Managed RDB — DB selection guide
- NoSQL — NoSQL selection guide
- Cache and In-Memory — cache selection guide
References
Section titled “References”- Azure Database for PostgreSQL documentation
- Azure SQL Database high availability
- Azure SQL Database read scale-out (read replicas)