PGCon2013 - Final Release

PGCon 2013
The PostgreSQL Conference

Roland Sonnenschein
Day Talks - Day 1 - 2013-05-23
Room MRT 256
Start time 10:00
Duration 01:00
ID 548
Event type Lecture
Track Applications
Language used for presentation English

Monitoring of Machines using PostgreSQL

We have developed a versatile system to monitor machines and groups of machines. The system is well proven and used in industrial production, power supply industry, research and development.

PostgreSQL is used to store all the measurements and metadata. The system also containes some SCADA elements.

This talk explains the database related essentials of the monitoring part of the software.

Beside configuration, a monitoring system mainly consists of four main functionalities. Solutions to some important topics will be explained.

  1. Reading of measurements from devices
  2. Connection of heterogeneous datasources (PLCs, sensors, computer programs, …) implementing different protocols.
  3. Managing network problems between devices and monitoring system.
  4. Consideration of time zones.
  5. Synchronizing of timestamps between connected devices.
  6. Handling of reconfiguration.

  7. Storage in PostgreSQL

  8. Efficient database structures to avoid an inflation of tables (no EAV!).
  9. Optimizing the size of the database.
  10. Unified treatment of the simple data types: boolean, integer, floating point numbers.
  11. Working with complex data like text, images or spectra.
  12. Implementing storage strategies and pretriggers to minimize disk space.
  13. Realizing high input rates (>3 million measurements per second over weeks).
  14. Handling large amounts of data.
  15. Preparing the database structure for fast retrieval and statistical evaluations.
  16. Avoiding disk overflow.

  17. Fast retrieval

  18. Minimizing dataflow by using dynamic aggregations.
  19. Processing of statistical analytics.
  20. Generating charts and reports.

  21. Alarming

We see our solution as a new way to implement the basic ideas of a Round-Robin Database.