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 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.
Figure 1. PostgreSQL® Database Structure
PostgreSQL® Database Structure

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.

Use the following query to check the number of open connections:
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)