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.
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 datapoint 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.
event*_a
Contains a detailed description of the PostgreSQL® event*_a table.
| Column | Type | Description |
|---|---|---|
| element_id | bigint | DP element ID. |
| ts | bigint | Event creation timestamp. |
| value_number | double precision | Value of numeric type. |
| status | bigint | Status bits. |
| corrstatus | bigint | Correction status bits. |
| manager | integer | The manager that set the value. |
| corrmanager | integer | The manager that set the correction value. |
| user_ | integer | The logged-in user when the value was set. |
| corruser_ | integer | The logged-in user when the correction value was set. |
| corrvalue_number | double precision | Correction value of numeric type. |
| value_timestamp | bigint | Value of timestamp type. |
| corrvalue_timestamp | bigint | Correction value of timestamp type. |
| value_string | text | Value of string type. |
| corrvalue_string | text | Correction value of string type. |
event*_d
Contains a detailed description of the PostgreSQL® event*_d table.
| Column | Type | Description |
|---|---|---|
| element_id | bigint | DP element ID. |
| ts | bigint | Event creation timestamp. |
| position | integer | Position within the dyn value. |
| valtype | smallint | Type of stored value. |
| value_dynnumber | double precision | Value of the dynnumber type. |
| value_dyntimestamp | bigint | Value of the dyntimestamp type. |
| value_dynstring | text | Value of the dynstring type. |
alert*_a
Contains a detailed description of the PostgreSQL® alert*_a table.
| Column | Type | Description |
|---|---|---|
| element_id | bigint | DP element ID. |
| ts | bigint | Alert creation timestamp (e.g., by a driver). |
| ack_time | bigint | Acknowledgement time. |
| system_time | bigint | Time when the alert was received by the WinCC OA system. |
| value_timestamp | bigint | Value of type timestamp. |
| partner | bigint | Alert time for the partner (the partner of CAME is WENT). |
| ack_state | integer | Identifies how the alert was acknowledged. |
| ack_type | integer | Type of acknowledgement. |
| partn_idx | integer | Alert index for the alert partner. |
| dest | integer | Current alert range. |
| detail | integer | Details of the alert. |
| state | integer | Status bits. |
| ack_user | integer | User who acknowledged the alert. |
| value_status | bigint | Status of the value that triggered the alert. |
| value_number | double precision | Value of type integer, float, or boolean. |
| ackable | boolean | Is the alert acknowledgeable? |
| visible | boolean | Is the alert visible (in the alert panel)? |
| direction | boolean | True for CAME, false for WENT. |
| inact_ack | boolean | Alerts that are no longer current must be acknowledged. |
| abbr | text | Abbreviation for the priority of an alarm. |
| panel | text | Panel for cross-selection. |
| prior | integer | Priority of the alert range. |
| single_ack | boolean | Did a single acknowledgement occur? |
| alert_id | text | Used for identification of a multi-instance alarm. |
| alert_color | text | Background color. |
| class | text | Alert class of the range. |
| comment | text | User comment on an alert. |
| dest_text | text | Text for the current alert range. |
| text | text | Status text for the range. |
| value_string | text | Value of type string. |
| alert_fore_color | text | Foreground color. |
| alert_font_style | text | Font style of the alert. |
More details on some of these parameters can be found in the online documentation: _alert_hdl.
alert*_add
Contains a detailed description of the PostgreSQL® alert*_add table.
| Column | Type | Description |
|---|---|---|
| element_id | bigint | DP element ID. |
| ts | bigint | Alert creation timestamp. |
| valtype | bigint | Type of stored value. |
| add_value_timestamp | bigint | Value of the timestamp type. |
| detail | integer | Details of the alert. |
| position | integer | Position within the dyn value. |
| add_value_number | double precision | Value of the numeric type. |
| add_value_string | text | Value of the string type. |
view_events
Contains a detailed description of the PostgreSQL® view events.
This view combines columns from event*_a and event*_d tables.
| Column | Type | Description |
|---|---|---|
| element_id | bigint | DP element ID. |
| element_name | text | DP element name. |
| sys_id | bigint | System ID. |
| ts | bigint | Event creation timestamp. |
| position | integer | Position within the dyn value. |
| value_number | double precision | Value of numeric type. |
| status | bigint | Status bits. |
| corrstatus | bigint | Correction status bits. |
| manager | integer | The manager that set the value. |
| corrmanager | integer | The manager that set the correction value. |
| user_ | integer | The logged-in user when the value was set. |
| corruser_ | integer | The logged-in user when the correction value was set. |
| corrvalue_number | double precision | Correction value of numeric type. |
| value_timestamp | bigint | Value of timestamp type. |
| corrvalue_timestamp | bigint | Correction value of timestamp type. |
| value_string | text | Value of string type. |
| corrvalue_string | text | Correction value of string type. |
| valtype | smallint | Type of stored value. |
| value_dynnumber | double precision | Value of dynnumber type. |
| value_dyntimestamp | bigint | Value of dyntimestamp type. |
| value_dynstring | text | Value of dynstring type. |
view_alarms
Contains a detailed description of the PostgreSQL® view alarms.
This view combines columns from the alert*_a and alert*_add tables.
| Column | Type | Description |
|---|---|---|
| element_name | text | DP element name. |
| element_id | bigint | DP element ID. |
| sys_id | bigint | System ID. |
| ts | bigint | Alert creation time. |
| ack_time | bigint | Acknowledgement time. |
| system_time | bigint | Time the alert was received by the WinCC OA system. |
| value_timestamp | bigint | Value of timestamp type. |
| partner | bigint | Alert time for the partner (the partner of CAME is WENT). |
| ack_state | integer | Identifies how the alert was acknowledged. |
| ack_type | integer | Type of acknowledgement. |
| partn_idx | integer | Alert index for the alert partner. |
| dest | integer | Current alert range. |
| detail | integer | Details of the alert. |
| state | integer | Status bits. |
| ack_user | integer | User who acknowledged the alert. |
| value_status | bigint | Status of the value that triggered the alert. |
| value_number | double precision | Value of type integer, float, or boolean. |
| ackable | boolean | Is the alert acknowledgeable? |
| visible | boolean | Is the alert visible (in the alert panel)? |
| direction | boolean | True for CAME, false for WENT. |
| inact_ack | boolean | Alerts that are no longer current must be acknowledged. |
| abbr | text | Short sign for the priority of an alarm. |
| panel | text | Panel for cross-selection. |
| prior | integer | Priority of the alert range. |
| single_ack | boolean | Did a single acknowledgement occur? |
| alert_id | text | Used for identification of a multiinstance alarm. |
| alert_color | text | Background color. |
| class | text | Alert class of the range. |
| comment | text | User comment for an alert. |
| dest_text | text | Text for the current alert range. |
| text | text | Status text for the range. |
| value_string | text | Value of string type. |
| alert_fore_color | text | Foreground color. |
| alert_font_style | text | Font style of the alert. |
| valtype | bigint | Type of the stored (dyn) value. |
| position | integer | Position within the dyn value. |
| add_value_number | double precision | Value of numeric type. |
| add_value_string | text | Value of string type. |
| add_value_timestamp | bigint | Value of timestamp type. |
Maximum number of required PostgreSQL® connections in a distributed system
If a schema is shared by multiple distributed systems, each system establishes as many connections as the backend it is connected to requires. In addition, each manager that performs direct reads has its own independent read connections.
When all these connections are added together, the total number can be considerable. Estimating the number of connections required depends heavily on the specific use case. For most users, the default value of 100 connections should be sufficient. However, in the case of a shared schema, multiple distributed systems and many simultaneous operations, this may not be sufficient.
To determine the appropriate number of connections when multiple clients perform queries in parallel, multiple distributed systems perform queries from the same database, or multiple clients use direct read access.
SELECT COUNT(*) FROM pg_stat_activity;You can change the number of connections in the file:
Project_name/db/wincc_oa/localdb/postgresql/16/pgdata/postgresql.conf
via the entry max_connections = 100 # (change requires restart)
