Database

PostgreSQL

The CDP4-COMET Datastore is built using the [[PostgreSQL|https://www.postgresql.org] relational database. PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, macOS, Solaris), and Windows. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages). It includes most SQL:2008 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP.

CDP4-COMET is supported on PostgreSQL version 9.6 and above. The prefered major version of PostgreSQL is version 16.

An enterprise class database, PostgreSQL boasts sophisticated features such as Multi-Version Concurrency Control (MVCC), point in time recovery, tablespaces, asynchronous replication, nested transactions (savepoints), online/hot backups, a sophisticated query planner/optimizer, and write ahead logging for fault tolerance. It supports international character sets, multibyte character encodings, Unicode, and it is locale-aware for sorting, case-sensitivity, and formatting. It is highly scalable both in the sheer quantity of data it can manage and in the number of concurrent users it can accommodate. There are active PostgreSQL instances in production environments that manage many terabytes of data, as well as clusters managing petabytes. Some general PostgreSQL limits are included in the table below:

Limit	Value
Maximum Database Size	Unlimited
Maximum Table Size	32 TB
Maximum Row Size	1.6 TB
Maximum Field Size	1 GB
Maximum Rows per Table	Unlimited
Maximum Columns per Table	250 - 1600 depending on column types
Maximum Indexes per Table	Unlimited

These details have been taken from the PostgreSQL Aout page. The many enterprise level features and virtually unlimited storage capability have led Starion to select this RDBMS as datastore for CDP4-COMET. Starion has relied on PostgreSQL for many years, the CDP3 relied on PostgreSQL as well.

Database Structure

The CDP4-COMET makes use of 3 databases in the PostgreSQL cluster:

• cdp4server
• cdp4serverrestore
• cdp4manage

design principles

More information coming soon

• inheritance
• value properties - HSTORE
• one-to-many
• many-to-many
• transaction safe
• caching tables
• temporal data

cdp4server

• The database that contains all the data of a CDP4-COMET database server including site data, model data and reference data.

cdp4serverrestore

• A database that is created and filled with data when data has been seeded to the CDP4-COMET Server using an Annex C3 file. this file is sent to the server using the following route: http://cdp4services-test.cdp4.org/Data/Exchange. This can only be done when this feature is enabled. Use the following command to seed a server from a file: curl --form file=@"Data.zip" http://cdp4services-test.cdp4.org/Data/Exchange
• The CDP4-COMET Server database can be restored to the state it was in when the CDP4Restore database was changed last using a specific route in the COMET WebService. Send an empty

cdp4manage

• Used as an anchor database to initially create a CDP4-COMET Server database.
• It is there as "proof" that a database is setup correctly so it can create a CDP4-COMET database.