I am working on a Django project using a PostgreSQL database in which I need to run a sometimes complex query on a user profile table with ~1M records, and the dataset returned can be anywhere from 0 to all 1M records. My issue is that once I grab all of the records, I want to be able to filter them further to run analytics on these profiles. The analytics cannot be completed in the same request/response loop, as this will time out for all but the smallest querysets. So I am using async javascript to shoot off new requests for each type of analytics I want.
For example, I will grab all of the profiles in the initial request and then i will send subsequent requests to take those profiles and return to me the % of genders or % of users who have a certain job title, etc. The issue is that every subsequent request has to run the original query again. What I would love to do is somehow cache that query and then run my query on this subset of the profile table without having to execute the original, long-running query.
I have tried to use a cache function to cache the queryset itself, but this actually slows down performance a bit, I assume because the queryset has to be pickled or unpickled and then it still has to run? I also tried to cache a list of ids from the parent long query (this is potentially a VERY long list, up to 1M integers) and that grinds my system to a completely halt for anything more than like 44k records.
Has anyone dealt with this kind of issue before? I know that I could set up a worker/queue system, which is on my roadmap, but it would be lovely if there was a simple solution to this that utilizes the built-in capabilities of Django.
Some sample code:
def get_analytics(request):
data_type = request.POST.get('data_type')
query_params = request.POST.get('query_params') # a crazy filter with lots of Q objects
profile_ids = get_profiles(query_params) # I WANT TO CACHE THIS
profiles = Profile.objects.filter(id__in=profile_ids).distinct()
if data_type == 'overview':
return profiles.count()
else if data_type == 'gender':
gender_breakdown = profiles.filter(a_filter_for_gender).values('gender').annotate(Count('gender', distinct=True))
return gender_breakdown
def cache_function(length):
"""
A variant of the snippet posted by Jeff Wheeler at
http://www.djangosnippets.org/snippets/109/
Caches a function, using the function and its arguments as the key, and the return
value as the value saved. It passes all arguments on to the function, as
it should.
The decorator itself takes a length argument, which is the number of
seconds the cache will keep the result around.
"""
def decorator(func):
def inner_func(*args, **kwargs):
if hasattr(settings, 'IS_IN_UNITTEST'):
return func(*args, **kwargs)
key = get_cache_key(func.__name__, func.__module__, args, kwargs)
value = cache.get(key)
if key in cache:
return value
else:
result = func(*args, **kwargs)
cache.set(key, result, length)
return result
return inner_func
return decorator
@cache_function(60*2)
def get_profiles(query_params):
return Profile.objects.filter(query_params).values_list('id')
Why does caching the ids slow my system down? Is there a better way to accomplish this?