Postgresql performance and Django

Question

My question is something in general as I am looking for a suggestion on the best way to work with a query on PostgreSQL with the characteristics:

• Interval from client side of 5 seconds
• More than 5000 records on each query result
• Result set is four joined tables
• Result set will be displayed on google map

Most likely a view could be better instead of using simple SQL or maybe a store procedure. At the moment I am using a common AJAX request, and my AJAX code is:

def request_positions(request):
    try:
        vehicles = Vehicle.objects.filter(registration__in = simplejson.loads(request.GET['vehicles']))
    except MultiValueDictKeyError:
        vehicles = Vehicle.objects.all()
    points = []

    for vehicle in vehicles:
        point = {
                'vehicle' : vehicle.registration, 
                'imei': None,
                'latitude': None, 
                'longitude':None,
                'course': None,
                'speed': None,
                'mileage': None,
                'gps_signal': None,
                'gsm_signal': None,
                'alarm_status': None,
                'gps_satus': None,
                'vehicle_status': None,
                'alarm_over_speed': None,
                'other': None,
                'date_time_process': None,
           }
        try:
            vehicledevice = vehicle.vehicledevice_set.get(is_joined__exact = True)
            try:
                track = vehicledevice.device.devicetrack_set.exclude(date_time_process__isnull = True).latest('date_time_process')
                point['imei'] = vehicledevice.device.imei
                point['latitude'] = track.latitude 
                point['longitude'] =track.longitude
                point['course'] = track.course
                point['speed'] = track.speed
                point['mileage'] = track.mileage
                point['gps_signal'] = track.gps_signal
                point['gsm_signal'] = track.gsm_signal
                point['alarm_status'] = track.alarm_status
                point['gps_status'] = track.gps_status
                point['vehicle_status'] = track.vehicle_status
                point['other'] = track.other
                point['date_time_process'] = track.date_time_process.astimezone(tz.gettz()).strftime("%Y-%m-%d %H:%M:%S")
                points.append(point)
            except ObjectDoesNotExist:
                pass

        except ObjectDoesNotExist:
            pass
    json = simplejson.dumps(points)
    return HttpResponse(json, mimetype='application/json')

My app is for a tracker which stores data each 10 seconds, and about 5000 trackers using a twisted daemon. Then, I need a way to get fast answers with no extra loading on the client side, as you could see, I am using common django definitions, but I am also looking to try and optimize this query in order to reduce request and answer time.Also, a charge of network and server, I mean, the best way in order to get the best process.

Answer 1

I wouldn't do this in PostgreSQL at all (and I'm a HUGE PG fan). This data changes so frequently I would store it in something like Redis by vehicle ID. You can still store it in PG for long term reporting purposes, but for this particular view you're going to have a hard time making it perform terribly well.

If you must stay using only PG, these things will help:

• Restrict your Vechile QuerySet using only() to grab ONLY the fields you need here.
• Instead of creating a big dictionary with lots of Nones and then resetting them change your code to instead populate the 'point' dictionary with track.get('', None). Also makes your code a bit shorter/clearer.
• Make sure you have an index on the 'date_time_process' field.
• If you're on Python 2.7 don't use simplejson, the stdlib json is actually a bit faster now and Django drops support for simplejson in Django 1.5
• Use select_related() and/or prefetch_related() for your relations to grab them as part of your Vehicle queryset.
• Definitely consider using a View or Stored Procedure to make this much much quicker.