PostgreSQL® Schema

Note: When using NextGen Archiver with PostgreSQL® or MS SQL®, data point elements of type ulong, long or bit64 are limited to 53 bit precision.

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

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)