vehicle tracking system in postgresql performance

Question

I am developing the vehicle tracking system using postgresql.

When the data comes from device to my DB server, I am calling the following functions after inserting data into log table.

1. Distance Update -- picking up the last inserted row (latitude, longitude) of the specific device from log table and calculating distance with newly inserted row... updating the distance in the same table

2. Trip function -- gets the list of all locations'(stations) latitude, longitude, radius and again calculating the distance [by using this I am able to get the details of trip start date and trip end date and inserting those details into anotehr table]

3. Geofence functions

4. Overspeed function

5. some other functions

I will get almost 2000 rows per minute.

Will this hit my performance ... otherwise do I need to write the trigger for this...

What happens if the data increases in the table more than 10,00,000... because we are picking the last inserted data based on the device everytime?

Do i get any db schema for basic vehicle tracking?

Thanks in advance, AJ

Answer 1

This won't scale, that's how I started 7 years ago with such an application doing vehicle tracking.

I have 3500 of those assets now, imagine them inserting 1 record at the same time. This was a nightmare, especially when GPRS comms failed at provider level and all those updates where waiting, every device has 50 records to send....

We now keep all data in sqlite files per imei, and it is not served with an http servers (although it speaks http) but with a custom TCP server. The reason is:

• scalability together with haproxy
• data overhead over GPRS costs money. So every server header we needlessly send back will end up on our GSM bill.

You should seperate your logic (e.g. customers, devices) and everything that drives your site away from where the events of the trackers end up.

Here's a basic schema that scales (sqlite file PER unique imei):

CREATE TABLE Event_customerx_all (
  record_id INTEGER PRIMARY KEY,
  com_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  gps_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  imei bigint(20) NOT NULL,
  switch unsigned int(3) NOT NULL,
  event_id unsigned int(5) NOT NULL,
  latitude unsigned int(10) NOT NULL,
  longitude unsigned int(10) NOT NULL,
  IO unsigned int(3) NOT NULL,
  raw_data BLOB NOT NULL,
  sys_parsed int(3) DEFAULT NULL,
  UNIQUE (imei,raw_data) ON CONFLICT IGNORE
);

And it's simple in design. raw_data is 33 bytes of our events, this is a custom compresion, every package is that large, max of 42 events can be sent (the device uses GET method).

Using a custom class you can extract more information like gps sats in view, or fixed sats, distance, speed .... etc but you won't really need to 'unpack' it all when it comes in. The other data (event_id, switch, gps_date etc) is actually also extractable in raw_data. You unpack when you need it.

now why is this good? well, this scales, imagine you invent a new var, now you need to change the schema when you 'extract' them all. But in this case, your software takes care of it, as your class will be able to extract that event detail.

You application will basically always want to do in (general) for the most part:

• Select either the latest of information
• Select a date range (e.g. trip/track).

Most will fall under these 2. So you also keep your latest position (and perhaps latest interesting event like start/stop, as I do ...) separately when it comes in.

In this app, switch and event_id will make you able to figure out what type of raw_data you have, as some events don't have coordinates, you'll want to filter those when drawing a map, perhaps not if you are creating a report....

Do not write a trigger for this... Very bad idea. What I explain above has replaced such a design. And all those 3500 clients are being received by single a 8Gb Linode VPS.

There's much more, but not making this mistake will gain you years if this is a serious application we are talking about. You're welcome ;-)

Answer 2

You going need to do some indexing on the device identifier attribute and latlng attribute as scanning the table will become slower with large quantity of rows. Why not use a auxiliary table to hold the last device inserted row? Postgresql offers several kinds of index to solve slow speed cases.

Also large trips may be slower to compute due to great number of locations your function will need to locate. Well I did not know your data but you should must have those things in mind and a large memory will help postgres find things quicker without doing disk writes.