5

I'm creating a real time traffic application. I read the AVL car data from a web service and insert the records in a PostgreSQL table.

Then a trigger calculates the closest link and if there is a previous position for the same car, I calculate the route to get the distance travelled in that time frame, then I get the average speed for that route.

The thing is, I get around 3000 points each 5 minutes, so I need to process ~600 records per minute (10 each second). I'm very close to those numbers because I filter the road network before calling the routing function and the routes are very small.

I understand PostgreSQL doesn't support multiple threads in one connection but supports multiple connections, so could use the other CPU cores.

I can make a different table for each State (25), but how can I partition the problem so each trigger creates a new connection and usse more CPU cores instead of only one?

  • 1
    What you're asking is a bit unclear to me, but PostgreSQL automatically utilizes multiple cores based on the number of connections. Are you asking how to create multiple connections? – Radar Sep 26 '13 at 20:25
9

See this help document for creating multiple connections.

This simple code will create multiple connections:

    EXEC SQL CONNECT TO testdb1 AS con1 USER testuser;
    EXEC SQL CONNECT TO testdb2 AS con2 USER testuser;
    EXEC SQL CONNECT TO testdb3 AS con3 USER testuser;

You can then choose a connection to use:

    EXEC SQL AT connection-name SELECT ...;

Finally you need to disconnect all of your connections:

    EXEC SQL DISCONNECT ALL;

This code will automatically make use of multiple threads and doesn't require you to create redundancies, such as duplicate tables.

  • 1
    And raise Shared Mem configuration value so high that all shared data (route networks) is in memory – simplexio Sep 27 '13 at 8:55
  • Yes, multiple connections is what i'm looking for. I will try it and let you know. – Juan Carlos Oropeza Sep 27 '13 at 16:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.