Almost all developers, at some point in their careers, will eventually find themselves needing to store pictures and decide to put them in a relational database for one very relatable reason: convenience.
The siren song of hitching your wagon to whatever storage mechanism you chose during your app's infancy is strong.
- You already have a database.
- You already have code for reading and writing to it.
- Your ORM of choice (e.g., Entity Framework) can map a
byte[]directly to a binary column. - And with minimal effort, your images automatically inherit the same backup, access-control, replication, and synchronization tech as the rest of your app data.
Sounds great! What could possibly go wrong?
From humble beginnings
The first web app I ever made, a membership app for my high school robotics team - Team 1138, eventually reached this same critical juncture when the need arose to store member profile pictures.
My first implementation stored each picture directly on the member row, alongside the member's data.
I was young. I was reckless.
The model was about as simple as you would expect:
using System.ComponentModel.DataAnnotations;
namespace Membership.Data
{
/// <summary>
/// Stores a member and the member's profile picture.
/// </summary>
public sealed class Member
{
[Key]
public int Id { get; set; }
//Other member properties omitted.
[MaxLength(100)]
public string PictureContentType { get; set; } = string.Empty;
public byte[] PictureData { get; set; } = Array.Empty<byte>();
}
}
Save the member. Save the bytes. Load the member. Load the bytes. Every time.
That was the first problem - I had stored the picture directly on the member row.
Because the profile picture was stored directly on the member row, loading a member also meant loading the picture data. It didn't matter whether the app needed to display the picture. The ORM would load the byte[] along with the member's ordinary data, just in case it's needed for an operation later down the line. (I learned later this can be disabled)
Relational databases are not really designed for this
Relational databases are great at filtering, joining, aggregating, and safely updating structured related data.
A picture does not need any of that.
You are storing a large sequence of bytes so you can retrieve the same sequence later - a file.
Many relational databases provide binary column types or special large-object features. Those features make storing pictures possible. They do not mean the database was designed as a file server.
File servers and database servers are trying to solve different goals - keep them separate.
Databases are expensive
The problems usually arrive slowly. A few members and pictures are totally fine, but when 10s become 100s become 1000s, the requests begin to pile up along with something most devs don't think about until the end of the month.
By the end of the month, the database was suspiciously large. When I finally checked which table was consuming the most space after sending Microsoft their check, I expected my log table.
NOPE.
It was the member table.
The profile pictures stored alongside the member data had quietly turned it into the largest table in the database. Worse, ordinary member queries were pulling those bytes even when the app wasn't going to use them.
As queries became slower and slower, desperation increased.
Buying some time
The first fix was quick and dirty. Give every picture response a fixed cache expiration so the browser could reuse it without calling the server until that expiration elapsed.
The tradeoff was that users wouldn't see changes for... a while...
However, this did make the app more responsive, the bandwidth bill lower, and my wallet happier.
The next improvement was to move the picture into its own table:
using System.ComponentModel.DataAnnotations;
namespace Membership.Data
{
/// <summary>
/// Stores a member picture and its database-managed version.
/// </summary>
public sealed class MemberPicture
{
[Key]
public int Id { get; set; }
public int MemberId { get; set; }
[MaxLength(100)]
public string ContentType { get; set; } = string.Empty;
public byte[] PictureData { get; set; } = Array.Empty<byte>();
[Timestamp]
public byte[] Version { get; set; } = Array.Empty<byte>();
}
}
Separating the picture from the member prevented normal member queries from pulling the bytes. It also gave the picture its own row and its own version, which changed whenever the picture changed.
Once we were tracking the picture in its own table, we moved from expiration-only caching to ETag-based revalidation. The ETag was based on the picture row's version. When the picture had not changed, the server could return 304 Not Modified after retrieving only the small version value instead of loading and returning the binary data, thus saving on the bandwidth a smidge.
In later implementations, we combined a short fixed cache lifetime with ETag revalidation:
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
namespace Membership.Web.Controllers
{
/// <summary>
/// Serves member pictures.
/// </summary>
[ApiController]
[Route("pictures")]
public sealed class PictureController : ControllerBase
{
private const string CACHE_CONTROL = "private, max-age=300";
private readonly MembershipDbContext _dbContext;
/// <summary>
/// Initializes a new instance of the <see cref="PictureController"/> class.
/// </summary>
/// <param name="dbContext">The membership database context.</param>
public PictureController(MembershipDbContext dbContext)
{
_dbContext = dbContext;
}
/// <summary>
/// Gets a member picture.
/// </summary>
/// <param name="id">The picture identifier.</param>
/// <param name="cancellationToken">The request cancellation token.</param>
/// <returns>The picture, a not-modified response, or a not-found response.</returns>
[HttpGet("{id:int}")]
public async Task<IActionResult> GetAsync(
int id,
CancellationToken cancellationToken)
{
var metadata = await _dbContext.MemberPictures
.AsNoTracking()
.Where(p => p.Id == id)
.Select(p => new
{
p.ContentType,
p.Version
})
.SingleOrDefaultAsync(cancellationToken);
if (metadata is null)
{
return NotFound();
}
var etag = $"\"{Convert.ToHexString(metadata.Version)}\"";
Response.Headers.ETag = etag;
Response.Headers.CacheControl = CACHE_CONTROL;
if (Request.Headers.IfNoneMatch.Contains(etag))
{
return StatusCode(StatusCodes.Status304NotModified);
}
var pictureData = await _dbContext.MemberPictures
.AsNoTracking()
.Where(p => p.Id == id)
.Select(p => p.PictureData)
.SingleAsync(cancellationToken);
return File(pictureData, metadata.ContentType);
}
}
}
The browser could reuse the picture without contacting the server during the fixed cache period. After that period expired, it could revalidate the cached picture using its ETag. If the version had not changed, the application returned 304 Not Modified after a small metadata query. If it had changed, the application retrieved and returned the new bytes.
This helped.
But caching did not change the basic architecture. When the picture was needed, the request still looked like this:
- The database read the binary data.
- The database sent it to the web application.
- The application allocated a
byte[]. - The application sent the same bytes to the browser.
We had made the expensive path happen less often. We had not removed the expensive path.
Database storage is expensive storage
This matters even more when the database runs in the cloud.
Database storage is generally priced as a premium service because it includes features pictures do not need: transactions, database replication, point-in-time recovery, database backups, transaction logs, and high-performance query processing.
When pictures live in the database, they may increase the size or workload of:
- The primary database
- Backups
- Transaction logs
- Restore operations
- Network transfers
- Database I/O
The picture itself may be small. The database infrastructure probably isn't.
Generic file storage is also not free, but it is built and priced for storing files. Keeping pictures in the database means paying database prices for file-storage work.
Store the location, not the picture
The better design is to keep the relationship in the relational database, and the file in a file system somewhere (who would have guessed files belong in the file system 😁)
The database still answers useful questions:
- Which member owns this picture?
- What is its content type?
- Where is it stored?
- Which version is current?
It does not need to contain the picture itself.
using System.ComponentModel.DataAnnotations;
namespace Membership.Data
{
/// <summary>
/// Describes a stored member picture.
/// </summary>
public sealed class MemberPicture
{
[Key]
public int Id { get; set; }
public int MemberId { get; set; }
[Required]
[MaxLength(512)]
public string FileLocation { get; set; } = string.Empty;
[Required]
[MaxLength(100)]
public string ContentType { get; set; } = string.Empty;
public long ByteLength { get; set; }
[Required]
[MaxLength(128)]
public string Version { get; set; } = string.Empty;
}
}
The app writes the file to configured storage and saves only its relative location in the database:
using System.Globalization;
using System.Security.Cryptography;
namespace Membership.Storage
{
/// <summary>
/// Describes a stored picture.
/// </summary>
public sealed record StoredPicture(
string FileLocation,
long ByteLength,
string Version);
/// <summary>
/// Stores pictures in a file system.
/// </summary>
public sealed class PictureFileStore
{
private const string PICTURE_DIRECTORY = "member-pictures";
private readonly string _rootDirectory;
/// <summary>
/// Initializes a new instance of the <see cref="PictureFileStore"/> class.
/// </summary>
/// <param name="rootDirectory">The root directory for stored files.</param>
public PictureFileStore(string rootDirectory)
{
ArgumentException.ThrowIfNullOrWhiteSpace(rootDirectory);
_rootDirectory = Path.GetFullPath(rootDirectory);
}
/// <summary>
/// Saves a member picture.
/// </summary>
/// <param name="memberId">The member identifier.</param>
/// <param name="pictureStream">The picture data stream.</param>
/// <param name="extension">The picture file extension.</param>
/// <param name="cancellationToken">The operation cancellation token.</param>
/// <returns>Metadata describing the stored picture.</returns>
public async Task<StoredPicture> SaveAsync(
int memberId,
Stream pictureStream,
string extension,
CancellationToken cancellationToken)
{
ArgumentNullException.ThrowIfNull(pictureStream);
ArgumentException.ThrowIfNullOrWhiteSpace(extension);
var safeExtension = extension.TrimStart('.').ToLowerInvariant();
if (safeExtension is not ("png" or "jpg" or "jpeg" or "webp"))
{
throw new ArgumentException(
"The picture extension is not supported.",
nameof(extension));
}
var relativeDirectory = Path.Combine(
PICTURE_DIRECTORY,
memberId.ToString(CultureInfo.InvariantCulture));
var fileName = $"{Guid.NewGuid():N}.{safeExtension}";
var relativePath = Path.Combine(relativeDirectory, fileName);
var fullPath = Path.Combine(_rootDirectory, relativePath);
Directory.CreateDirectory(Path.GetDirectoryName(fullPath)!);
await using var outputStream = new FileStream(
fullPath,
FileMode.CreateNew,
FileAccess.Write,
FileShare.None,
bufferSize: 81920,
FileOptions.Asynchronous | FileOptions.SequentialScan);
using var hash = IncrementalHash.CreateHash(HashAlgorithmName.SHA256);
var buffer = new byte[81920];
long byteLength = 0;
while (true)
{
var bytesRead = await pictureStream.ReadAsync(
buffer,
cancellationToken);
if (bytesRead == 0)
{
break;
}
await outputStream.WriteAsync(
buffer.AsMemory(0, bytesRead),
cancellationToken);
hash.AppendData(buffer, 0, bytesRead);
byteLength += bytesRead;
}
var version = Convert.ToHexString(hash.GetHashAndReset());
return new StoredPicture(
relativePath.Replace(Path.DirectorySeparatorChar, '/'),
byteLength,
version);
}
}
}
This also lets the application stream the file rather than loading the entire image into a byte[]. Depending on the environment, the web server or another file-serving layer may be able to handle the request without involving the application or database at all.
There is one tradeoff: the file system and relational database do not share a transaction. A practical save process is:
- Write the new file using a unique name.
- Save its location in the database.
- Delete the old file after the database update succeeds.
- Periodically clean up files that were written but never referenced.
Let the database do database things
Putting pictures in the database is convenient. It works for a bit, and for a small enough app could probably work forever.
Caching makes it better. Resizing pictures makes it better. Keeping binary data out of frequently queried rows improves performance.
The real fix is still simpler:
Store the picture's location in the database. Store the actual picture file in a file system somewhere.
Your database will perform less I/O. Your application will allocate less memory. Your hosting will generally cost less.
Most importantly, your relational database can get back to managing relationships instead of pretending to be a very expensive folder.
AI use disclosure: AI tools assisted with editorial review and technical feedback of this article. The author retained full editorial control and responsibility for the final text.