PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to Efficiently Track Latest IoT Data Timestamps for Multiple Vehicles?

I’m working on an application where I need to monitor the latest IoT data being sent from a fleet of over 3000+ vehicles. Each vehicle is equipped with an IoT device that sends data to an Iotdata table. To determine if a vehicle is actively sending data, I need to track the latest timestamp of the data it sends.

Problem: This approach is not working efficiently and is causing performance issues.

Requirement: I need a more efficient approach to achieve the following:

  • Track the latest timestamp of data sent from each vehicle.

  • Ensure minimal performance impact on the database.

  • Provide real-time or near-real-time status of each vehicle’s data transmission activity.

Questions:

  1. What is a more efficient way to track and update each vehicle’s latest IoT data timestamp?

  2. Are there any best practices or design patterns that could help optimise this process?

Any suggestions or insights would be greatly appreciated!

Current Approach:

  1. There are two columns in the Vehicles table: iot_id and timestamp. Both columns are foreign keys.

  2. I run a query every minute to fetch the latest timestamp from the Iotdata table and update the timestamp column in the Vehicles table.

I have attached the link for DDL information here: https://drive.google.com/file/d/1Ydh2Q4zJeN6x2W4p_VKW80utTr_rQ3pY/view?usp=sharing

Tags:

2

Answers


  1. 0

    I use an implementation of ULIDs (aka UUiD v7) in combination of TimeScale extension, from Nikolay Samokhvalov, based on this post: https://x.com/samokhvalov/status/1730107298369171943

    Works really well with about 5k weather stations.

    Login or Signup to reply.
  2. 0

    This query should be fast, when both columns are indexed:

    SELECT vehicle_fk
     , MAX(timestamp) AS last_message
FROM iotdata
GROUP BY vehicle_fk;

    If not, please share the results from explain(analyze, verbose, buffers) for this statement.

    About your data model, I would change it to a data warehouse schema, a star schema. But I don’t think that makes it much faster, just easier to use and maintain.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search