Storing primary keys of objects in Redis and then querying them in Postgres through Django

Question

This is probably my second time posting in programmers. I'm usually on stackoverflow but this question is more fit for the community here.

All suggestions, advice and insight on this matter is extremely appreciated. I require you're brutal honesty as well. If you think what I'm currently doing in terms of design is really stupid, TELL ME! because I'm a self-taught back-end software engineer and I can use all the advice I can get.

I use Django, Redis & PostgreSQL. Let's say for example I have a User, Post & Location model.

#models.py
from django.db import models
from django.contrib.auth.models import User

class Post(models.Model):
        user = models.ForeignKey(User)
        media_url = models.CharField(max_length=255, blank=True)
        related_location = models.ForeignKey('Location')

class Location(models.Model):
        name = models.CharField(max_length=200, blank=True)
        address = models.CharField(max_length=255, blank=True)
        city = models.CharField(max_length=140, blank=True)

Great, now that's out of the way, let's say users wanted to see all the posts tagged at a certain location. This location has an ID of 1812. The normal way of doing this is to make a simple query and return posts where the related_location has an ID of 1812. I would paginate the results so that user will see 10 objects at a time. If they want to see more, they need to make another request.

I used to do things this way, until recently I've had the scare of databases growing potentially large and queries becoming really slow. So I decided to create indexes on Redis for each Location object. For example, let's say posts 7, 33, 18, 12, 89, 56500, 34000, 30 we're all tagged at location 1812. I would create a Redis List called 'location:1812' and in that list, it would contain the IDs of all the posts that we're tagged there, in this case redislist = [7, 33, 18, 12, 89, 56500, 34000, 30].

I would then take this list and query against it:

queryset = Post.objects.filter(pk__in = redislist).order_by('-pk')

Is this a smart thing to do? Immediately I began to think of the pros and cons of the approach. For one, queries to the DB would be faster since I already know which objects I need to grab. However, in terms of design, is it worth it? For something like unique location objects, that table in postgres alone can grow infinitely large (Can have over a billion rows). Storing a list for each location object in Redis can be costly (memory running out).

So it all comes down to this. Is the DB good enough, or should I continue doing what I am doing?

Answer 1

There are many things for which Redis is the best choice available. Replacing basic RDBMS functionality is not one of them.

Not only PostgreSQL is far faster than your Python app to do this, it has a lot of debugging behind its back. It also manages consistency no matter which client code you use to access/update the data.

Keep Redis for what it does best, like a cache system, or to keep some analysis of the data that is either too slow or would demand denormalization if done directly on the datastore.

A typical example is keeping a scoreboard. Normalizing the data makes complex to calculate and might be prohibitively slow. Denormalizing on DB tables can make it slightly better, but doing it in Redis is a huge improvement in the algorithms you can apply.

In short, keep the database clean and as normalized as practical, and do extra things in Redis, not because it's "better" than the DB (it's not), but because it's different, so it applies better to the task at hand.

You'll also save a lot of headaches if you declare one storage as the "authoritative" data store, and everything else could be rebuilt from there in a single operation.