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.