Guidelines for using your PostgreSQL Database with Clarify

  • Updated

This article provides recommended best practices for setting up PostgreSQL with Clarify. Understanding how to configure and optimize your PostgreSQL database is essential for ensuring performance, reliability, and seamless replication. 

Please note: Installation and configuration of a supported PostgreSQL database must be completed before installing the Clarify server. The Clarify server installer will require specific database connection details, including location, port configuration, and assignment of the database user and password. See these related topics for more information.

Where to install the PostgreSQL database?

The PostgreSQL database can be installed on the same server as the Clarify installation or separate server. Ultimately, the decision will depend on your organization's architecture, performance, and maintainability preferences. Here are some things to consider:

(Option 1) Installing on the same server as Clarify

✅ Pros

  • Simpler setup for small or development environments.
  • Easier configuration and fewer network concerns (localhost connection).

❌ Cons

  • Shared resources (CPU, RAM, disk I/O) could affect performance if Clarify and PostgreSQL are both under heavy load.
  • Not ideal for production-scale or high-availability setups.

✅ Recommended for: Development, test, or small-scale production environments.

(Option 2) Installing on a separate Server for PostgreSQL

✅ Pros

  • Better performance and resource isolation.
  • Easier to scale and monitor independently.
  • Aligns with best practices for enterprise or production deployments.

❌ Cons

Slightly more complex setup (network configuration, firewall rules, DB security, backups across systems).

✅ Recommended for: Production environments or any system where performance, scalability, or high availability is a concern.

How to download and install PostgreSQL

To get started, download and install PostgreSQL for your Clarify environment using the link below:

Please note version requirements:

  • PostgreSQL 16.9 (Recommended)
  • PostgreSQL 13.12
  • PostgreSQL 14.9
  • PostgreSQL 15.4

Configuring the Local Test Server

General Requirements

  • Password complexity must meet the requirements as defined by the operating system hosting the database or this installation will fail.
  • Ensure that the firewall on the machine that is hosting the database allows connections from all other remote computers that are part of the Server Cluster. Whichever directory the database will be created in must have the proper permissions for users to access that directory.   
  • For Windows, the database installation must be done by a domain user with admin privileges.
  • For Linux, the database installation must be done as root. 

PostgreSQL Configuration Parameters

The following settings serve as baseline recommendations from a proven configuration. They can be adjusted based on system resources or to achieve higher performance. These values are defined in postgresql.conf, which may be located in different paths depending on the operating system:

max_connections = 300 
shared_buffers = 2GB
work_mem = 64MB
maintenance_work_mem = 1GB
effective_cache_size = 12GB
huge_pages = try
huge_page_size = 2048kB
 

Common postgresql.conf Locations

For Windows:

C:\Program Files\PostgreSQL\<version>\data\postgresql.conf

For RHEL (Linux):

/var/lib/pgsql/<version>/data/postgresql.conf

For macOS (Docker):

Within the container: /var/lib/postgresql/data/postgresql.conf

 
Notes:
 
  • Ensure the server has sufficient memory to support these values. Refer to System Requirements.
  • huge_pages and huge_page_size may require OS-level configuration (e.g., enabling huge pages on RHEL).

  • Consider further tuning work_mem and effective_cache_size for larger data workloads if system resources are limited.

     

     


     
     

Related articles

Comments

0 comments

Please sign in to leave a comment.