CSE Roadmap

Common tier · about 3 hours · 8 parts

Part 3 of 8 · 3 min read

Reference plus blob

Keeping large objects out of the database and a pointer to them in.

Think of a library catalogue. The catalogue card says where the book is on the shelf. The card is small, and thousands fit in a drawer. The books themselves stay on the shelves. Gluing a book to each card would make the drawer impossible to carry.

The book "Understanding Distributed Systems" describes this pattern. Large binary objects, which it calls blobs, such as images, audio and video, are hard to move around. Message brokers limit the size of a message. It names an Azure queue limit of 64 KB and an AWS Kinesis limit of 1 MB. The way round is to upload the blob to an object storage service, then send only its URL in the message. The pattern is sometimes called queue plus blob.

The same idea applies to databases. Instead of storing a blob inside the database, store a row of metadata that holds a reference to the blob in object storage.

Metadata

Metadata is data about data: the name, size, type, owner and location of a file, not its content.

Example: {id: 91, owner: 7, name: "holiday.mp4", size: 180000000, object_key: "u7/91"}.

The book lists the benefits.

  • Less data moves between the application and the database, which improves performance and cuts bandwidth.
  • The cost per byte of an object store, built for large and rarely changing objects, is lower than that of a general database.

It also lists the costs.

  • Two systems must now be managed, not one.
  • The blob can no longer be updated in one transaction with its metadata and other records. A new row with an image is visible only after the transaction finishes, but the image in the object store is visible at once.
  • When the row is deleted, the image is not deleted automatically. The application must remove it, or it stays forever as waste.

A worked example. A photo site has 5 million photos of 2 MB.

  • In the database: 5,000,000 × 2 MB = 10 TB inside the tables. Backups, copies for reads (Level 4) and index builds all handle 10 TB.
  • Reference plus blob: 5,000,000 rows of about 200 bytes = 1 GB in the database, and 10 TB in object storage.
  • A copy of the database now moves 1 GB, not 10 TB. At 100 MB per second, copying takes 10 seconds against 100,000 seconds (about 28 hours).

A safe order of steps for an upload keeps the two stores from drifting apart:

  1. Upload the object to the store under a new key. If this fails, nothing else has happened.
  2. Insert the metadata row that points to the key. If this fails, the object is an orphan.
  3. A periodic clean-up job lists objects with no row, and deletes the ones older than some hours.

For a delete, remove the row first, then the object. A failed object delete leaves an orphan, which the clean-up job removes. The other order could leave a row pointing at nothing, which a user would see as a broken link.

Common mistake

Writing the row first and then the object. If the upload fails, users see a record whose file does not exist. Write the object first.

Check yourself:

  • What does the database hold under reference plus blob?
  • Why is it safer to upload the object before the row is written?