Why the retention decision cannot wait
Location and telemetry data accumulate constantly, because devices report cyclically. The heartbeat table grows without limit: 73.9 million rows and 6.2 GB after two years of operation. That is roughly 37 million rows a year on average, tens of millions.
A retention mechanism exists but is switched off until the client decides how long history should be kept. This is a reasonable default, because the system cannot decide on its own how long the plant has the right or the duty to keep data. That decision belongs to the client, however, and if nobody makes it, the data will keep growing.
Who takes part in the decision
The decision is prepared by the IT department and reviewed by the data protection officer, if one is appointed at the plant, together with the process owner: health and safety, logistics or security. Each of them knows a different requirement: IT costs and performance, the officer the data minimisation principle, the process owner the need for evidence.
It is worth adopting the principle that the retention period follows from the purpose for which the data was collected. If route data serves delivery settlements, the period follows from settlement needs. If it serves the investigation of incidents, it follows from claim deadlines and internal procedures.
Growth of the heartbeat table at a constant rate (example calculation)
Five recommendations
The recommendations below are meant to help draft a retention policy without the risk that the disk runs out after a year or that someone deletes data needed to explain an incident.
1. Separate heartbeats from route points
A heartbeat is a technical signal that a device is alive, and a short history is usually enough. Route points show where a vehicle or worker has been, so their period follows the business purpose. Two different tables can have two different periods.
2. Start with a short period and extend it on a justified request
It is easier to extend a period when someone reports a need than to explain why data was kept for years and nobody looked at it. Set the initial period after talking to the process owners and record it in the policy.
3. Plan a maintenance window for the first clean-up
The first deletion of a large batch of rows from a table with tens of millions of records loads the database. Run it in a maintenance window, in batches, with a backup taken before the start and a measurement of the size afterwards.
4. Record the policy and its review date
A retention policy without a review date ages together with the installation. Enter in it the date of the annual review and the person responsible for keeping it up to date.
5. Calculate the storage cost from the installation's figures
At 6.2 GB after two years, the growth is about 3.1 GB a year for one table. Multiply this by the number of installations and add indexes and backups. The result allows you to compare the cost of disks with the value of a longer history.
Sample growth calculation
Based on the values from an installation after two years, the annual growth can be estimated. This is a linear estimate: it does not account for the scaling of the device fleet, which we discuss in the implementation notes on indexes and heartbeat traffic.
- Rows after 2 years: 73.9 million, or about 36.95 million a year.
- Size after 2 years: 6.2 GB, or about 3.1 GB a year.
- Average row size: about 84 bytes including indexes (6.2 GB divided by 73.9 million).
What to watch out for
Deleting data is irreversible, so make a backup and verify it by restoring before the first clean-up. Tables with location data are sometimes required in a disputed matter or in the analysis of an accident, and a decision to shorten the period cannot be undone.
The second trap is confusing retention with anonymisation. Aggregated traffic statistics may remain longer than raw data if they do not allow a person to be identified. Establishing whether the data is truly anonymised belongs to the data protection officer, not to the database administrator.
Yearly growth of the heartbeat table
| Parameter | Value |
|---|---|
| Rows after 2 years | 73.9 million |
| Size after 2 years | 6.2 GB |
| Growth rate | constant linear |
Result: approx. 36.95 million rows and 3.1 GB a year
Note: Example calculation: does not account for a change in the number of devices in the fleet or in the frequency of reports.
Video
Frequently asked questions
Does the system delete old heartbeats by itself?
A retention mechanism exists but is switched off until the client decides how long history should be kept. Until then the table grows without limit.
Who should set the retention period?
The client, with the IT department, the data protection officer and the process owner. The period follows from the purpose of collecting the data.
How large is the data growth?
At the installation our data comes from, the table held 73.9 million rows and 6.2 GB after two years. Scaling the fleet will increase the growth.
Will cleaning the database stop the system?
A large deletion loads the database, so it is run in a maintenance window, in batches, after a backup.
Sources
Do you need a retention policy for location data?
We will help prepare the decision for IT and the data protection officer based on the figures from your installation. Book a conversation.
Let's talk