PostgreSQL® Schema
For versions 3.20 and higher, the default index for the EVENT table has been changed from BRIN to BTREE for various reasons, particularly with regard to better reading performance.
Note: The PostgreSQL® schema was changed from BRIN
indices to BTREE indices in version 3.20. BRIN indices in the PostgreSQL® schema are
therefore obsolete. You can request a tool/script for converting an existing PostgreSQL®
database from BRIN to BTREE from WinCC OA support.
Please select a segment size between 15 and 50GB, as larger segments can lead to a reduction in performance in the long term (degradation).
The PostgreSQL® database schema contains several tables:
- systems – contains information of the systems of the WinCC OA project.
- elements – contains information of the data point elements that must read/write/archive their values.
- archive_groups – contains archive groups of the backend.
- elements_to_archive_groups – defines relations (many-to-many) between "archive groups" and "elements". This table allows storing crossing for multiple archive groups.
- segments – stores segments of archive groups.
- _event_%segment_id%_a – stores simple (non-dyn) EVENTS values.
- _events_%segment_id%_d – stores events for dyn values.
- _alert_%segment_id%_a – stores ALARM values.
- _alert_%segment_id%_add – stores additional values for ALARMS.
- configuration – is used as a key-value storage of internal parameters, which are defined when the DB is created.
- scheduler_tasks – contains information about the last successful periodic tasks execution and its execution period in seconds.
The database provides the following VIEWS:
- view_events – the view contains the union of all EVENTS segment tables with statuses ONLINE, CURRENT, ONLINE AND BACKUPED and RESTORED.
- view_alarms – the view contains the union of all ALARMS segment tables with statuses ONLINE, CURRENT, ONLINE AND BACKUPED and RESTORED.
