mhdi
asked on
PostgreSQL Database design for time based data
Hi,
I am trying to work out the best database design for the following application.
In a factory we have about 500 machines/sensors that send back data every minute when in operation.
Each record received has
For an average 8 hour day there would be 240,000 records and we keep records for many years.
All of the queries will have a data range as part of the search. We will be querying things like -
Questions
Thanks
I am trying to work out the best database design for the following application.
In a factory we have about 500 machines/sensors that send back data every minute when in operation.
Each record received has
Date Time
Machine ID
Event Type - High / Low / Normal / Startup / Shutdown etc.
Various small data fields
For an average 8 hour day there would be 240,000 records and we keep records for many years.
All of the queries will have a data range as part of the search. We will be querying things like -
Records between Date1 and Date2 WHERE MachineID = X
Records between Date1 and Date2 WHERE EventType = Low
Records between Date1 and Date2 WHERE MachineID = X and EventType = 5
Latest record WHERE MachineID = X and EventType = 1
Questions
Should each machineID have its own table?
Should by primary key be a composite of DateTime MachineID and EventType? or should it be a 'surrogate' key?
What sort of index should I create?
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I would do use one table with the following indexes:
DateTime, MachineId, EventType
MachineId,EventType,DateTi
EventType ,DateTime,MachineId
DateTime, MachineId, EventType
MachineId,EventType,DateTi
EventType ,DateTime,MachineId
@Zberteoc, could you please explain your reasoning for that choice of indexes? I don't understand why you've suggested those, and having extra columns in an index for a table containing an enormous quantity of data may have a performance cost.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry, I removed a comment meant for other question. :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER