Hey there! Let's walk through the best AWS database options for your B2B log storage scenario—since you're dealing with high daily write volumes (1M entries per customer), super infrequent reads (5-6 retrievals per month), and a 2-year retention requirement, we need solutions that balance cost, scalability, and ease of use. Let's break this down:

1. Optimizing Your Existing Postgres (If You Want to Stick With It) #

Postgres can handle your workload, but we can tweak it to better fit your "store-heavy, read-light" needs:

• Time-based partitioning: Split your log table into monthly or quarterly partitions. When customers query old logs, Postgres only scans the relevant time range instead of the entire dataset—this cuts down on query latency and resource usage.
• Hot/cold data separation: Archive logs older than, say, 6 months to AWS S3 using Postgres's foreign data wrapper (FDW). You can still query archived data directly from Postgres, but you'll save a ton on storage costs since S3 is way cheaper than EBS volumes.
• Read replicas: Offload all log retrieval queries to a read replica so your main Postgres instance doesn't get bogged down by infrequent but potentially heavy read operations.

That said, Postgres is an OLTP database at heart—long-term storage for millions of daily logs will get expensive compared to purpose-built log storage tools. Let's look at better fits.

2. Purpose-Built AWS Solutions for Your Use Case #

Option A: S3 + Athena (Top Pick for "Store More, Retrieve Less") #

This combo is tailor-made for your scenario:

• Ultra-low storage costs: Use S3's Intelligent-Tiering storage class to automatically move old logs to cheaper cold storage tiers. For 2-year retention, this is way more cost-effective than any database.
• Efficient retrieval: Store logs in columnar formats like Parquet (compressed!) and organize them by customer and time (e.g., s3://your-log-bucket/{customer_id}/2024/05/logs.parquet). Athena is a serverless SQL query tool that charges only for the data it scans—perfect for your 5-6 monthly queries.
• Easy multi-tenancy: Use S3 prefixes and IAM policies to restrict each customer to their own log paths. When a customer needs to download logs, generate a pre-signed S3 URL for them—no extra export steps needed.
• Automated lifecycle management: Set up S3 lifecycle rules to automatically move logs to Glacier Flexible Retrieval after a certain period, further slashing long-term storage costs.

Option B: Amazon Timestream #

If you want a fully managed time-series database without the hassle of managing S3/Athena setup, Timestream is a great choice:

• Automatic hot/cold tiering: Timestream automatically moves old logs to low-cost cold storage, and queries seamlessly span both tiers—you don't have to lift a finger.
• High write throughput: It's built for high-volume time-series data, so 1M daily entries per customer is no problem.
• Optimized time-range queries: It’s designed specifically for filtering data by time and customer ID, so retrieving 2-year-old logs is fast and efficient.
• Built-in multi-tenancy: Use customer_id as a dimension tag to isolate data, and query by that dimension to pull up a specific customer's logs quickly.

The tradeoff? Timestream's query costs are slightly higher than Athena, but it's a more integrated solution if you don't want to manage storage formatting or partitioning manually.

Option C: Amazon DynamoDB (Only If You Need Low-Latency Reads) #

DynamoDB works, but it's not ideal for your "store-heavy" scenario:

• Great for writes: It handles high-volume writes effortlessly, matching your 1M daily entries per customer.
• Indexing for queries: A global secondary index (GSI) on customer_id + timestamp lets you query logs by customer and time range.
• Cost caveat: Storage costs are much higher than S3, and if you're scanning large datasets for old logs, read costs can add up quickly. Stick with this only if you need sub-second latency for log retrievals (which doesn't seem to be your case).

3. CloudWatch Logs: Is It Right for Production? #

Let's address your CloudWatch question straight up: it's not the best fit for your B2B customer log use case, here's why:

• Multi-tenancy headaches: While you can use log groups/namespaces to isolate customers, setting up fine-grained access control (so each customer only sees their own logs) requires complex IAM policies. There's no native row-level isolation, which makes scaling across many customers messy.
• Higher long-term costs: CloudWatch's storage costs are significantly higher than S3 for 2-year retention—with millions of daily logs, this will add up fast.
• Limited query flexibility: CloudWatch Logs Insights uses a simplified query language, which works for basic searches but struggles with complex filtering or aggregation that your customers might need.
• Extra export steps: If customers need to download logs, you have to first export CloudWatch data to S3, then generate a download link—adding unnecessary complexity to your workflow.

CloudWatch is great for AWS service monitoring logs, but not for storing and serving customer-specific business logs in production.

Final Recommendation #

Based on your exact needs:

1. First choice: S3 + Athena — It’s the cheapest, most flexible option for your "store more, retrieve less" scenario, with easy multi-tenancy and straightforward log downloads for customers.
2. Second choice: Amazon Timestream — A fully managed, low-fuss time-series database with automatic tiering, perfect if you want to avoid manual storage management.
3. Stick with Postgres only if: You have tight integration with existing Postgres workflows, and you're willing to implement partitioning and cold data archiving to keep costs in check.

内容的提问来源于stack exchange，提问作者RHauck