Messaging systems don’t change the fundamental tradeoffs involved in data loading, but they do add a lot of opinionated weight to the choices. 

First, messaging and streaming have a lot of potential meanings and implementations.  To overgeneralize, there are queues, where each message should be read once, topics, where each client should receive each message, and broadcast, where there are no guarantees about anything.

Queues and topics work as polling loops within your software.  Broadcast messaging comes directly to the machine at the network level; the abstractions are highly dependent on implementation.

For the rest of this article I’m going to cover queues.  I’ll cover topics and broadcast in future articles.

Loading Patterns with Queues

The main feature of a message queue is that you want each message to be processed exactly once.  This requires clients claim messages when taking them off the queue, notifications back to the queue when messages have been processed, and a client timeout, after which a claimed message will reappear at the head of the queue.

A client has to connect to a queue, ask for some number of messages and indicate how long it will wait for the max number.  For example, AWS’ SQS the defaults are 10 messages or 30 seconds.  The client will block until 10 messages are available, or it has been waiting for 30 seconds.

Set the message request too big and you will wait while the queue fills.  Set the timeout to long and the first few messages will get stale while the client waits.  You need to balance the time cost of polling the queue against the cost of having your workers idle.

The main concern however, is visibility timeouts.  If your client grabs 10 messages and has a 30 second timeout it absolutely must finish all 10 in under 30 seconds.  After 30 seconds the unacked messages will be released to the next waiting worker and your message will be processed twice.

Optimizing worker counts, polling settings, and timeouts requires maximizing execution consistency.  When working at scale, how long an action takes matters much less than how consistent the timing is.

The Four Patterns of Data Loading are all about tradeoffs.

If your system is drinking from a firehose, you need to push everything towards a Pre-Cache model.  Pre-Cache pushes data loading out of the critical path, which gives the most consistent timing for message processing.

Having fully Pre-Cached queue processors is rarely possible, there is too much data and it changes to often.  Read Through Caching is the practical alternative.

Link Tracking Example

Link Tracking, recording when someone clicks on a link, is a common activity for Marketing SaaS.  I’m going to use a simplified version and walk through each of the data patterns.

At a high level, we have a queue of events with 4 pieces of data that describe the click event: Customer Name, URL, Email, and Timestamp.  

We want to process each event exactly once, as quickly as possible, and we don’t care about the order messages are processed.  However, we can’t insert directly, we need to normalize Customer Name, URL, and Email into customerId, urlId, and emailId.

Lazy Load

The nice things about the Lazy Load pattern is that it starts quickly and is simple to understand.

Unfortunately, every event requires 3 trips to the database to normalize the data.  This adds load to the database and has highly variable processing time.

It’s a fine place to start, but a terrible place to stay.

Pre-Fetch

The Pre-Fetch pattern separates fetching the data into a separate step from execution.  In this example, the worker would be attempting to normalize multiple events at the same time.  Once an event has been fully normalized, it is inserted into the database.

Pre-Fetch adds a lot of complexity because the workers now have internal concurrency in addition working in parallel with each other.  This might be worth it if the data was being composed from different resources in a micro-service architecture and the three calls could be done in parallel.  In this example though, you would be pounding the database.

Pre-Cache

The great thing about a Pre-Cache model is that there are no reads against the database during processing.  It is read-only during init, and write only from the queue.  That will really help the database scale!

Pre-Cache is impractical in this use case though.  We might know the full set of customers at startup, but urls and emails are open ended.  Pure Pre-Cache setups require that all of the data be known before starting.  That can work for things like file imports and trading systems,  but not link tracking.

Read Through Cache

The Read Through Cache Pattern is likely to be the best option for our use case.  It is more flexible than the Pre-Cache Pattern and much friendlier to the database than Lazy Loading or Pre-Fetching.  It pushes complexity to the cache where there are lots of great solutions.  Most languages have internal caching mechanics, and external caches like memcached and redis are widely supported.

Conclusion

Reading from message queues is a common problem and usually requires composing data from a database.  What’s the best way to load the data?  As always, the right data loading pattern depends on a your conditions and assumptions.  

Lazy Loading is always a decent place to start because it is simple.  Performance, cost, and scaling constraints will push your design towards Read Through Caching and Pre-Caching.  Pre-Fetch models are likely to be more effort than they are worth because they add a second source of concurrency and complexity.

Remember, requirements change over time, so will the best solution to your problem!

The Four Patterns Of Data Loading are about two main trade offs: simplicity for performance, and freshness for execution consistency.

This may seem odd because the quadrants are defined by loading and caching strategies, not simplicity, performance or execution consistency.

Simple or Performant

The decision to use caching is about trading simplicity for performance.  You can simply load the data every time you need it.  If you’re using MySql on AWS, a basic query will take about 2ms to return.  The pattern is very simple and self contained: load data when needed.

Caching, saving data for reuse, improves performance by reducing the time it takes to use the data again.  In exchange, you have to think about your code and determine:

• Will I use the data again?
• Is the data likely to change in the DB while I have it cached?
• If the data does change, do I want to use the latest version or the version that the process has been using so far?
• How much server memory will I need for the cache?

Example - Adding a Tag to a Contact

Imagine a simple operation, adding a tag to a contact.  The tag is a string and the contact is represented by an email address.  You need to transform the tag and email into ids and store them in a normalized database table.  For simplicity's sake, let’s say all DB operations take 2ms.

There are 3 DB Operations

1. Load the contactId based on email
2. Load the tagId based on tag
3. Insert into contact_tags

With the On Demand access pattern, we do each action every time.  This requires 3 trips to the DB for 6ms.

Similarly, with the Pre-Load pattern, we spend 2ms pre-loading the tagId, and each operation takes 4ms.

Using a Read Through Cache, we store the tagId after the first load.  The first operation takes 6ms and each additional operation takes 4ms.

Finally, with the Pre-Cache pattern, we spend 2ms pre-loading the data and each operation takes 4ms.

	1 Tag, 1 Contact		1 Tag, 10 Contacts		10 Tags, 10 Contacts
	Init	Exec	Init	Exec	Init	Exec
On Demand	0ms	6ms	0ms	60ms	0ms	600ms
Pre-Load	2ms	4ms	20ms	40ms	200ms	400ms
Read Through Cache	0ms	6ms	0ms	42ms	0ms	420ms
Pre-Cache	2ms	4ms	2ms	40ms	20ms	400ms

Freshness or Execution Consistency

The next tradeoff to consider the value of fresh data vs execution time consistency.  This goes beyond questions of caching, it also affects whether you can use the Pre-Load strategy at all.  A big advantage of the Pre-Load and Pre-Cache strategies is that the execution time is lower and less variable.

Stock trading software is designed to pre-load as much data as possible and can spend minutes initializing so that the actual buying and selling happens in microseconds.  Similarly, internet ad networks like Google’s demand responses in 100ms or less.  Having consistent execution times in each piece of your software makes it much easier to monitor performance for signs of trouble.

Security software and reporting sit on the other end of the spectrum.  It doesn’t matter if a user had permission 5 minutes ago and everyone hates waiting for report data to update.  In these cases the variance for each response is much less important than getting the most recent data.

Some data never changes once it has been created.  In the example above of adding a tag to a contact, both tagId and contactId will never change during your program’s execution.  Generally, anything with ‘id’ in the name is safe to cache.  On the other hand counts, permissions, and timestamps change all the time.

Strategies can be good for some situations and terrible for others.  Sometimes it depends on expectations vs money.

	Ids and static data	Permissions	Counts and Reporting
On Demand	Bad	Good	Good, until it doesn’t scale
Pre-Load	Good	It depends on time elapsed	It depends on time and money
Read Through Cache	Good	It depends on time elapsed	It depends on time and money
Pre-Cache	Best	Bad	It depends on time and money

Conclusion

The “right” data loading pattern is a moving target.  Remember that in the beginning load is low and there are continuous changes.  Simplicity is always a great choice when there isn’t enough scale to justify complexity.

As software matures two trade offs emerge: Simplicity vs Complexity and Freshness vs Consistency.

You’re changing the software for a reason.  When you consider the tradeoffs it should become clear which patterns will help solve your problem.