Joins on Redis: serving multi-field queries from memory

Ask an engineer what Redis is for and the answer is usually "fast lookups by key". Give it a user ID, get the user back in about a millisecond. That is what Redis, an in-memory data store, is best at, and where most caching projects start and stop.

The trouble is that most screens in a real application are not a lookup by key. They are lists: orders for this customer in this date range, open tickets assigned to this team, products in this category that are in stock. Those reads need a filter across several fields, or a join across two or more tables, and a key-value store has no native way to answer them.

Where the database quietly takes the load back

So teams draw a line. Single-key reads go to Redis; anything with a filter (a WHERE clause, in database terms) or a join goes to the database. The cache hit rate (the share of reads answered from memory rather than disk) looks healthy on the dashboard, because the single-key reads are numerous. But the list screens are the ones users sit on all day, and every one of them is still a database query.

The database ends up carrying the reads that cost the most, while the cache absorbs the reads that were cheap anyway. That is the opposite of what a cache is for. It is also why teams that "already have Redis" often still see the database at high load during peaks.

Why joins are hard in a cache

The reason teams stop at single keys is not laziness. Serving a join from memory means the cache has to hold complete, current copies of every table involved, in a shape that can be filtered and combined without going back to disk. If any row in any of those tables changes, the cached answer to every query that touched it may now be wrong.

With a hand-written cache, that quickly becomes unmanageable. Either the team caches whole query results and accepts that they go stale, or they try to track which cached results depend on which rows, which is a small database engine written by accident. Most give up and send the query to the real database, which is the sensible call given the tools they have.

Keeping the data itself in step

redfly takes the other route. Its sync service keeps the data itself in Redis, rather than remembered answers to past queries, and keeps it in step with the database as rows are added, changed and deleted. On SQL Server that is done with change tracking, read on a short interval; on PostgreSQL with logical replication; on MongoDB with change streams.

Once the data is current in memory, a multi-field query or a join is a question asked of data that is already there. There is no stale query result to expire, because nothing is remembering results; the data itself is what is kept current. The application asks through GetRows with its filters, and the answer comes from Redis, not the database.

The cache stops being a list of remembered answers and becomes a current copy of the data itself.

What it changes for read-heavy screens

The practical effect is that the reads that used to be excluded from the cache are the ones that now benefit most. Consider what a typical list screen does.

• It filters on two or three fields: a status, an owner, a date range.
• It joins to one or two lookup tables for display names.
• It pages through results, so it also needs a total count.
• It is opened hundreds of times a day by every user in the department.

Every one of those steps can be served from memory. GetRows handles the filtered and joined read, and GetTotalRowCount handles the count for paging. The database sees none of that traffic, and is left with writes and the genuinely complex queries it is good at.

The shape of the data is our job, not yours

Serving joins from memory requires the data in Redis to be laid out so that it can be filtered and combined quickly, and updated so that a deleted row disappears from every join that touched it. None of that is trivial, and all of it has to survive schema changes.

That work is redfly's responsibility. The application does not describe cache keys, does not decide what to precompute, and does not maintain any of the structure in memory. It calls the API with the query it wants. When the schema changes, keeping the in-memory shape correct is part of the service, not a ticket in the customer's backlog.

The honest limits

Not every query belongs in memory. Heavy analytical work across millions of rows with complex grouping is still better suited to the database or a reporting store. The point is not that Redis replaces the query engine, but that the everyday reads that make up most of an application's traffic no longer need it.

There is also a short window after a write during which a read can return the previous state, because the sync service has to notice the change and apply it. For list screens and lookups that is almost always acceptable. For the rare read that must reflect its own write immediately, that is a design conversation to have early.

redfly sits between an application and its SQL Server, MongoDB or PostgreSQL database and serves reads, including joins and multi-field queries, from a Redis cache it keeps in step. Writes go straight to the database.