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?