Azure Table Storage vs Cosmos DB Design

Azure Table Storage is a NoSQL key-value store that is part of Azure Storage. It offers a schema-less design where each entity is a set of properties (up to 255) with two mandatory keys: PartitionKey and RowKey. It provides high scalability (up to 20 TB per table, 500 TB per storage account) and is cost-effective for large volumes of data accessed by known keys. Table Storage is a fully managed service with automatic failover, geo-redundancy options, and a simple REST API. However, it has limited indexing (only on PartitionKey and RowKey), no secondary indexes, and eventual consistency by default.

Azure Cosmos DB is a globally distributed, multi-model database service that supports document, key-value, graph, and column-family data models via APIs like SQL, MongoDB, Cassandra, Gremlin, and Table. The Table API in Cosmos DB provides a drop-in replacement for Azure Table Storage with significant enhancements: single-digit millisecond latency at any scale, automatic secondary indexing on all properties, multiple consistency levels (strong, bounded staleness, session, consistent prefix, eventual), and global distribution with multi-region writes. Cosmos DB is throughput-provisioned (Request Units per second) or serverless, and can handle up to 10 TB per container (with partitioning) and unlimited storage via partition splits.

Azure Table Storage uses a flat namespace of tables within a storage account. Each table is partitioned by PartitionKey, which determines the physical server (partition server) that hosts the data. All entities with the same PartitionKey are stored together, enabling efficient point queries (PartitionKey + RowKey). The service automatically load-balances partitions across servers based on size and throughput. Writes are first committed to a primary region and then asynchronously replicated to secondary regions (if geo-replication is enabled). Reads from the primary are strongly consistent; reads from secondary are eventually consistent. There is no built-in support for automatic failover across regions; manual failover via PowerShell or CLI is required.

Cosmos DB uses a hash-based partitioning scheme. Each container (table) has a partition key; data is distributed across physical partitions based on the hash of the partition key value. Each physical partition is a set of replicas (at least 3) that use a replicated state machine for high durability. Writes are committed to a quorum of replicas within a region before acknowledgment. For multi-region writes, Cosmos DB uses a multi-master replication protocol, allowing any region to accept writes. The consistency level is applied per request; strong consistency uses synchronous replication across regions, while others use asynchronous replication with bounded staleness. Indexing is automatic and comprehensive: every property is indexed by default, and you can customize indexing policies (include/exclude paths, composite indexes).

Azure Table Storage: - Maximum entity size: 1 MB (including all properties). - Maximum table size: 500 TB per storage account. - Maximum storage account size: 500 TB total across all services. - PartitionKey length: 1-1024 characters (string). - RowKey length: 1-1024 characters (string). - Default consistency: Eventual (for read operations from secondary). - Throughput target: Up to 20,000 IOPS per partition (for point queries). - Pricing: ~$0.045 per GB/month for LRS; $0.10 per GB/month for GRS.

Cosmos DB Table API: - Maximum item size: 2 MB (for SQL API; 1 MB for Table API). - Maximum container size: Unlimited (up to 10 TB per container, then partitions can split). - Default consistency: Session (if not specified). - Throughput: Provisioned from 400 RU/s to unlimited; autoscale from 400 RU/s max. - Latency: <10 ms for reads and writes at P99 within same region. - Pricing: Provisioned throughput (~$0.008 per RU/hour) + storage (~$0.25 per GB/month). - Global distribution: Up to 50+ regions; multi-region writes available.

Azure Table Storage CLI commands:

# Create table
az storage table create --account-name mystorageaccount --name mytable

# Insert entity
az storage entity insert --account-name mystorageaccount --table-name mytable --entity PartitionKey=pk RowKey=rk Name=John Age=30

# Query entity
az storage entity query --account-name mystorageaccount --table-name mytable --partition-key pk --row-key rk

# List tables
az storage table list --account-name mystorageaccount

Cosmos DB Table API CLI commands (using az cosmosdb):

# Create Cosmos DB account
az cosmosdb create --name mycosmosaccount --resource-group myrg --kind GlobalDocumentDB --capabilities EnableTable

# Create table
az cosmosdb table create --account-name mycosmosaccount --resource-group myrg --name mytable --throughput 400

# Insert item (via REST or SDK, not CLI directly)
# Use Azure Portal or SDK to insert

Both services integrate with Azure Functions, Logic Apps, and Event Grid for event-driven architectures. Cosmos DB's change feed (via SQL API) enables real-time processing of inserts/updates; Table Storage does not have a native change feed but can use Azure Storage Analytics logs. Cosmos DB integrates with Azure Cognitive Search for full-text search; Table Storage requires separate indexing. Both support Azure Private Link for private endpoints. Cosmos DB provides built-in TTL (time-to-live) for automatic data expiration; Table Storage requires custom logic. Cosmos DB's multi-region writes enable active-active configurations; Table Storage is active-passive with manual failover.

A manufacturing company collects sensor data from thousands of devices every second. Each reading is small (few hundred bytes) and accessed by device ID and timestamp. Requirements: 50,000 writes/sec, sub-10 ms write latency, and occasional reads by device ID. Solution: Cosmos DB with Table API, provisioned 100,000 RU/s (autoscale), single-region writes with session consistency. Partition key: deviceId. Cost: ~$24,000/month. Alternative: Azure Table Storage would hit partition limits (20,000 IOPS per partition) and require multiple storage accounts and custom partitioning (e.g., by device group). Misconfiguration: Using Table Storage with a single partition key (e.g., all devices under one partition) would throttle writes at 20,000 ops/sec and cause 429 errors.

A global social media app stores user profiles (2 KB each) for 500 million users. Profiles are read frequently by userId, rarely updated. Need low latency reads globally (under 10 ms) and multi-region writes for disaster recovery. Solution: Cosmos DB with SQL API, partition key userId, strong consistency for writes, session for reads. Multi-region writes enabled across three regions. Cost: ~$500,000/month. Alternative: Table Storage with GRS would provide eventual consistency for reads from secondary regions, and manual failover could cause minutes of downtime. Misconfiguration: Using Table Storage with a single storage account for all users would exceed the 500 TB limit (500M * 2 KB = 1 TB, fine), but without secondary indexes, queries by email or username would require full table scans.

A financial institution needs to store 10 TB of audit logs per year, accessed only for compliance investigations by timestamp range. Latency of 1 second is acceptable. Budget is tight. Solution: Azure Table Storage with PartitionKey = date (e.g., 2025-03-17) and RowKey = timestamp. This allows efficient range queries. Cost: ~$450/month for storage + minimal transaction costs. Misconfiguration: Using Cosmos DB for this would cost ~$2,500/month for storage alone plus RU/s, overkill for the performance needs. Conversely, using Table Storage for a real-time dashboard requiring sub-second queries on multiple fields would require building a separate indexing layer (e.g., Azure Search), complicating the architecture.