Skip to content

S3 vs Database

1. Decision

Choose Amazon S3 (Object Storage) for high-capacity, low-cost, immutable storage of unstructured binary blobs, large media files, database backups, and analytical data lake files; choose a Database (SQL/NoSQL) for structured entity storage, sub-millisecond point queries, dynamic row indexing, complex search filtering, and transactional ACID mutations.

2. 30-Second Answer

Amazon S3 is an object store accessed via HTTP REST APIs, designed for storing flat, unstructured data objects (up to 5TB per object) with infinite scale, low cost (\(0.023/GB/mo), and 11 9s durability. Databases (Relational SQL, Document, Key-Value) store structured or semi-structured data records in indexed data structures (B-Trees, LSM-Trees), providing low-latency (\)<5\text{ms}$) random point access, ACID transactions, secondary index filtering, and field-level updates. Storing large binary files (e.g., 50MB videos or PDFs) directly inside a database causes table bloat, RAM cache eviction, and severe backup thrashing. Modern architectures decouple binary content (stored in S3) from metadata (stored in a Database).

3. Decision Matrix

Option Best For Avoid When Latency Scalability Consistency Operational Complexity
AWS S3 Video/audio uploads, PDFs, images, database backups, Parquet analytical data lakes High-frequency random point updates, microsecond query latency, complex relational joins Medium (20-50ms TTFB over HTTP) Virtually Unlimited Strong Read-After-Write Very Low
Database (SQL/NoSQL) Structured domain records, user accounts, transactional ledgers, rapid indexed queries Storing large binary files (>1MB blobs), long-term cold file archival Low (<5ms point lookup) Scale-up; Read Replicas; Sharding clusters Strict ACID (SQL); Eventual/Strong (NoSQL) Medium to High

4. Selection Rules

Requirement Best Choice Rationale / Example
Large Unstructured File Storage (>1MB) AWS S3 S3 handles multi-terabyte assets cleanly without polluting DB memory buffers
High Frequency Field-Level Updates Database Modify specific fields (UPDATE user SET status = X) without rewriting entire payload
Cost-Effective Cold Data Archival AWS S3 Automatically transition old files to S3 Glacier ($0.00099/GB/month)
Sub-Millisecond Multi-Attribute Indexing Database B-Tree and GIN indexes allow filtering across millions of structured rows
Presigned URL Direct Client Uploads AWS S3 Mobile clients upload videos directly to S3, bypassing backend API servers
Financial ACID Double-Entry Bookkeeping Database Multi-row transactional guarantees prevent double-spending

5. Tradeoffs

  1. Storage Cost Economics vs Microsecond Query Speed
  2. Gain (S3): Up to 100x cheaper per gigabyte than SSD database storage.
  3. Lose (S3): Higher latency (20-50ms) per HTTP object fetch compared to indexed DB disk page reads.
  4. Infinite Elasticity vs Indexing & Query Flexibility
  5. Gain (S3): Unlimited capacity without managing disk volume resizing or partition sharding.
  6. Lose (S3): Cannot query contents inside objects without external engines (S3 Select, Athena) or metadata DBs.
  7. Decoupled Architecture Complexity vs Single Data Store
  8. Gain (S3 + DB): Keeps DB lean and fast; offloads heavy I/O bandwidth to AWS S3 infrastructure.
  9. Lose (S3 + DB): Risk of orphan files in S3 if database row creation fails during file upload.
  10. Immutable Replacement vs In-Place Modification
  11. Gain (S3): Simplifies distributed replication, versioning, and object locking.
  12. Lose (S3): Modifying 1 byte in a 100MB file requires re-uploading the entire 100MB object.
  13. Direct Client Upload (Presigned URLs) vs Backend Proxy Ingestion
  14. Gain (S3): Offloads video/image streaming bandwidth completely away from API gateway compute nodes.
  15. Lose (S3): Requires asynchronous webhook validation (S3 Event Notifications) to verify uploaded content.

6. Interview Counter Questions

  1. What happens to database performance (Postgres/MySQL) when you store 10MB image blobs directly in a BLOB column?
  2. How do S3 Presigned URLs work for secure direct client uploads without passing data through backend servers?
  3. How do you resolve orphan files in S3 if an application uploads a file to S3 but crashes before writing the DB row?
  4. How do S3 Event Notifications trigger serverless processing pipelines (AWS Lambda)?
  5. What is S3 Multipart Upload and when is it mandatory for large files?
  6. How does S3 Object Versioning protect against accidental file deletion or ransomware attacks?
  7. What is S3 Transfer Acceleration and how does it leverage AWS CloudFront edge locations?
  8. How does Amazon Athena query Parquet files directly on S3 without loading them into a database?
  9. Why is DELETE operation in S3 fast, but batch deleting 100 million S3 objects complex?
  10. How do you handle strict atomic transactions across an S3 file write and a PostgreSQL database insert?

7. Gold Standard Answers

  1. Storing 10MB blobs in a database pollutes the buffer pool (RAM cache), evicting index pages. It causes severe table bloat, slows down full table scans, increases Write-Ahead Log (WAL) volume, and makes database backups thrashes.
  2. The backend application authenticates the user, generates a cryptographically signed S3 Presigned URL with an expiration TTL, and returns it to the client. The client issues an HTTP PUT directly to S3 using the signed URL.
  3. Use a 2-step async pattern: Client uploads to S3 under a /tmp prefix. After successful DB row insertion, moving the file to /permanent occurs, OR run a daily background cleanup job deleting un-referenced /tmp objects older than 24 hours.
  4. S3 emits events (s3:ObjectCreated:*) to AWS SNS, SQS, or EventBridge, which trigger AWS Lambda functions asynchronously to perform image thumbnailing, virus scanning, or metadata extraction.
  5. Multipart Upload splits files larger than 100MB into smaller chunks (5MB to 5GB), uploading them in parallel. It is mandatory for objects over 5GB and improves throughput and network fault resilience.
  6. Object Versioning keeps historical copies of objects under unique version IDs upon deletion or overwrite. A DELETE request adds a "Delete Marker" without destroying underlying data, allowing easy rollback.
  7. S3 Transfer Acceleration routes client uploads over geographically distributed AWS CloudFront Edge Locations, carrying traffic over AWS's optimized global fiber backbone to the target S3 bucket.
  8. Athena uses a distributed Presto/Trino query engine. It reads column footers (min/max stats) in Parquet files on S3 to skip irrelevant data blocks and executes vectorized queries directly in memory.
  9. S3 object deletion is fast per object, but deleting 100M objects requires issuing thousands of DeleteObjects batch API calls (max 1,000 keys per call) or using S3 Batch Operations with S3 Inventory.
  10. Achieving true atomic transactions across S3 and DB requires a 2-Phase Commit or Saga pattern: write DB row with status = PENDING, upload S3 file, then commit status = ACTIVE in DB. If upload fails, mark DB row FAILED.

8. Common Mistakes

  • Storing base64-encoded image payloads directly inside PostgreSQL or MongoDB documents.
  • Proxying multi-gigabyte client file uploads through backend API containers instead of using S3 Presigned URLs.
  • Failing to configure S3 Lifecycle Rules on temporary client upload buckets.
  • Expecting instant sub-millisecond point read latency when fetching small files from S3 over HTTP.
  • Hardcoding AWS IAM long-lived credentials inside client applications for S3 uploads instead of generating temporary Presigned URLs.

9. Keywords

S3, Database, Object Storage, Presigned URL, BLOB Column, Buffer Pool Pollution, S3 Event Notifications, S3 Multipart Upload, Object Versioning, S3 Transfer Acceleration, Amazon Athena, Orphan Files, Saga Pattern.

10. Cheat Sheet

  • Core Rule: Large unstructured assets (>1MB) \(\rightarrow\) S3; Structured entity state & transactional indexes \(\rightarrow\) Database.
  • Direct Uploads: Always generate S3 Presigned URLs on backend and let mobile/web clients upload directly to S3.
  • DB Health: Never store binary files in DB BLOB columns; it pollutes memory buffer pools and ruins DB performance.
  • Cost: S3 is ~100x cheaper per GB than database SSD disk storage; use Glacier for long-term archiving.
  • Orphan File Handling: Store uploads in /tmp/ prefix; promote to /permanent/ only after DB transaction commits.
  • Analytics: Use Parquet files on S3 queried via Amazon Athena (Presto) for cheap analytical data lakes.

11. Related Decisions