Tuning the PostgreSQL Server

The SAS Infrastructure Data Server component is supported by a PostgreSQL database server. SAS Infrastructure Data Server provides a transactional store that is used by the SAS Viya platform. The server is configured automatically during deployment. However, you have multiple options for optimizing its performance, which can have an outsize impact on overall SAS Viya platform performance.

Considerations

To support the required SAS Infrastructure Data Server component, the SAS Viya platform requires either an internal PostgreSQL instance, which is the default option and is deployed automatically, or an external instance that you configure and maintain. If you deploy with the default option, SAS provides tools to configure and maintain the deployment for you. If you instead deploy an external PostgreSQL instance, you are responsible for configuring and maintaining it.

For the internal PostgreSQL database server, SAS uses Crunchy Data PostgreSQL. Two operators manage SAS Infrastructure Data Server:

SAS Data Server Operator – Manages all PostgreSQL instances for the SAS Viya platform (both internal and external PostgreSQL).
SAS Crunchy Data PostgreSQL Operator – Provisions internal PostgreSQL instances only.
This operator is not included in your deployment by default, but when you add an overlay to the kustomization.yaml file, it creates a sas-crunchy-platform-postgres or sas-crunchy-cds-postgres cluster with default settings. You might want to tune these default settings for improved SAS Viya platform performance.

Providing your own instance enables you to restrict the ability of SAS services to access the database when a single PostgreSQL instance houses multiple databases. A README file in your deployment assets, $deploy/sas-bases/examples/postgres/README.md (Markdown format) or $deploy/sas-bases/docs/configure_postgresql.htm (for HTML format), documents the required steps to configure an external PostgreSQL instance.

Your deployment assets include example transformers to assist you in tuning PostgreSQL settings. The files and an explanatory README are located in $deploy/sas-bases/examples/crunchydata/tuning.

Database Provisioning and Maintenance

SAS Infrastructure Data Server requires persistent storage, where it retains configuration data, job instances, and more. The default PVC size that is described in Persistent Storage Volumes, PersistentVolumeClaims, and Storage Classes in System Requirements for the SAS Viya Platform reflects the typical guidance for the third-party database that is used for SAS Infrastructure Data Server and for a fairly small CAS server deployment.

SAS also requires two separate PVCs for SAS Viya backup and restore operations, and these files are another potential source of database growth. By default, these PVCs are set to require the minimum amount of storage that supports these operations. Depending on your data-retention settings for backup and restore and the size and usage of your deployment, the default sizes for these PVCs might be inadequate.

Pay close attention to the size and growth metrics for the SAS Infrastructure Data Server database. A large database size can affect SAS Viya platform performance. In addition, with the default data-retention settings for the PostgreSQL server, multiple backups can exceed 8 GB, leading to backup failures when PVC limits are exceeded.

If you think that your database size is slowing SAS Viya platform performance, tune the database retention settings. These settings are described in detail in Routine Maintenance Tasks in SAS Viya Platform: Infrastructure Servers. You can also change job-retention settings to reduce database growth. For more information, see Job Objects and Database Growth in SAS Viya Platform: Jobs and Flows. And if changing these settings does not alleviate performance issues, consider allocating additional storage for SAS Infrastructure Data Server or for platform backup and restore operations.

Tuning Memory and CPU Resources

Crunchy Data offers the following rough formula for determining memory resource levels for the PostgreSQL container:

(work_mem * avg_active_session) + (max_connections * 14mb) + shared_buffers = avgMemory

However, usage spikes might trigger the Out-Of-Memory killer in the operating system. In order to reduce the risk of launching the OOM killer, Crunchy Data recommends that the avgMemory not exceed 70% of the actual container resource limit for memory. The value for shared_buffers should be set to 25% of the container resources for Crunchy Postgres.

For more information, see https://blog.crunchydata.com/blog/deep-postgresql-thoughts-the-linux-assassin.

For CPU resource limits, you can tune the CPU requests value, which represents a minimum number of CPU cores that are allocated to a container. Crunchy Data provides the following tuning formulas that might help you determine a minimum value for CPU requests:

Minimum CPU = avg_concurrent_sessions / 2

Note: Multiply CPU by 1000 to get the value in millicores.

Minimum CPU = avg_concurrent_sessions / 2.5

This formula suggests setting a higher limit, with the trade-off that it uses more resources but is less likely to be throttled. If latency or disk contention limit performance, consider adding additional PostgreSQL instances.

SAS provides example files to help you modify these settings. For more information, see the README file: $deploy/sas-bases/examples/crunchydata/pod-resources/README.md (for Markdown format) or $deploy/sas-bases/docs/configuration_settings_for_postgresql_pod_resources.htm (for HTML format).

Recommendations

Relevant Tuning Parameters

Specialized solutions or use cases might require further configuration tuning. If you need to experiment with parameters in order to optimize system performance, the most important PostgreSQL tuning parameters are as follows:

shared_buffers

Specifies the amount of memory to be used for caching data and refers to the main in-memory cache used by PostgreSQL for table and index data. PostgreSQL also benefits from the file system cache, so shared_buffers should not be so large that they interfere with the file system cache.

For a large database, set this parameter to a value ranging from 1 GB up to 25% of the total container memory resources allocated to Crunchy Postgres.

effective_cache

Set this parameter to a value ranging from 4 GB up to 75% of the total container memory resources allocated to Crunchy Postgres. The typical value should be 4 times larger than the size that you set for shared_buffers.

work_mem

Specifies the amount of memory to be used for sorts, hashing, and materialization, before writing to temporary disk files. Several running sessions can perform operations concurrently. Therefore, the total memory that is used might be many times the value of work_mem. Keep this in mind when selecting a value for this parameter. Set this parameter to a value between 16 MB and 64 MB or more, for a specialized use case (for example, frequent, very large sorts).

max_connections

Specifies the maximum number of database user connections that can be open simultaneously. The internal PostgreSQL instance is set to 1280 connections by default. An external PostgreSQL server should support max_connections of at least 1024. SAS recommends setting this value to be the same as the value for max_prepared_transactions.

Microsoft provides guidance for setting the maximum connections on Azure Database for PostgreSQL in Limits in Azure Database for PostgreSQL – Single Server.

max_prepared_transactions

Specifies the maximum number of transactions with a status of prepared that can exist simultaneously. The default value is 0, which means that no transactions are "prepared." SAS recommends setting max_prepared_transactions to the same value as max_connections.

maintenance_work_mem

Specifies the maximum amount of memory to be used for vacuuming (reclaiming storage that is used by rows that are marked for deletion) and index builds. For a large database, set this parameter to 256 MB or more.

temp_buffers

A database session allocates temporary buffers as needed, up to the limit that is specified by this parameter. These per-session buffers facilitate access to temporary tables. This setting can be changed only within individual sessions, and only before the first use of temporary tables within those sessions.

You can set the synchronous_commit parameter to Off for faster updates, but if an outage occurs, transactions might be lost.

See the README file at $deploy/sas-bases/examples/crunchydata/tuning (for Markdown format) or at $deploy/sas-bases/docs/configuration_settings_for_postgresql_database_tuning.htm (for HTML format) for instructions about how to set or change those parameters.

Tune Wait Times

The PostgreSQL server from Crunchy Data offers a Patroni option that can be tuned to improve performance. The Patroni option is used by the SAS Crunchy Data PostgreSQL Operator to manage the performance of the sas-crunchy-platform-postgres or sas-crunchy-cds-postgres cluster.

Tuning Patroni settings is recommended in cases where the Kubernetes cluster experiences slow response times, which can occur due to resource contention or network latency. The Patroni option attempts to address performance degradation on the cluster side by increasing the wait times.

See the README file in $deploy/sas-bases/examples/crunchydata/tuning (Markdown) or see $deploy/sas-bases/docs/configuration_settings_for_postgresql_database_tuning.htm (HTML) for instructions. For more information about Patroni settings, see Crunchy Data Patroni Configuration Settings.

Tuning SAS Backup for PostgreSQL Database Backup Operations

If your PostgreSQL database is large, backup operations can take hours to complete. By default, compression is applied by the PostgreSQL utility as the backup proceeds for the PostgreSQL data source. The addition of compression means that the resulting size of the backup data is greatly reduced, but it slows the process significantly. You have the option to configure the backup job to disable compression for the PostgreSQL data source, to run the PostgreSQL dump in parallel by dumping a specified number of tables simultaneously, or both.

Note: The parallel jobs option might reduce the time that is required to perform the dump, but it also increases the load on the database server.

To reduce the overall time that is required in order to complete the backup operation, add options to the backup command using the sas-backup-job-parameters configMap. For more information, see the README file that is located at $deploy/sas-bases/examples/backup/postgresql/README.md (for Markdown format) or $deploy/sas-bases/docs/configuration_settings_for_postgresql_backup_using_the_sas_viya_backup_and_restore_utility.htm (for HTML).

Here are examples of configMap options that might improve the performance of the backup job. To run the PostgreSQL (pg_dump) backup command without the compression that is applied to the backup job by default, apply this YAML:

configMapGenerator:
- name: sas-backup-job-parameters
  behavior: merge
  literals:
  - SAS_DATA_SERVER_BACKUP_ADDITIONAL_OPTIONS=-Z0

configMapGenerator:
- name: sas-backup-job-parameters
  behavior: merge
  literals:
  - SAS_DATA_SERVER_BACKUP_ADDITIONAL_OPTIONS=-Z0

To run the PostgreSQL (pg_dump) backup command with the parallel jobs option (in this example, while dumping three tables simultaneously), apply this YAML:

configMapGenerator:
- name: sas-backup-job-parameters
  behavior: merge
  literals:
  - SAS_DATA_SERVER_BACKUP_ADDITIONAL_OPTIONS=-j3

configMapGenerator:
- name: sas-backup-job-parameters
  behavior: merge
  literals:
  - SAS_DATA_SERVER_BACKUP_ADDITIONAL_OPTIONS=-j3

To run the PostgreSQL (pg_dump) backup command with both options (that is, without compression and with the parallel jobs option), apply this YAML:

configMapGenerator:
- name: sas-backup-job-parameters
  behavior: merge
  literals:
  - SAS_DATA_SERVER_BACKUP_ADDITIONAL_OPTIONS=-Z0,-j3

configMapGenerator:
- name: sas-backup-job-parameters
  behavior: merge
  literals:
  - SAS_DATA_SERVER_BACKUP_ADDITIONAL_OPTIONS=-Z0,-j3

SAS has tested with both options. The uncompressed files were almost three times larger than the compressed file that is created by default. Although the time to complete the backup operation depended on the size of the data that was backed up, the jobs completed approximately 70% faster when 8 threads were specified for the parallel jobs option.

If you decide to disable compression for backup jobs, consider also allocating additional disk space for the files that are created.

Tuning SAS Backup for PostgreSQL Database Restore Operations

If your PostgreSQL database is large, restoring it from a backup package can take hours to complete. The time that is required to restore the database from backup is reduced when you run parallel jobs to restore the database objects. The optimal value for this option depends on the underlying hardware of the server and of the client, and it also depends on the network. For example, the number of CPU cores plays an important role. Refer to the following document for more information about parallel restore jobs using the pg_restore utility: https://www.postgresql.org/docs/12/app-pgrestore.html.

To reduce the overall time that is required in order to complete the restore operation, add options to restore command using the sas-restore-job-parameters configMap. For more information, see the README file that is located at $deploy/sas-bases/examples/restore/postgresql/README.md (for Markdown format) or $deploy/sas-bases/docs/uncommon_restore_customizations.htm (for HTML).

Here are examples of configMap options that might improve the performance of the restore job. To run the pg_restore command with the parallel jobs option (in this example, running eight jobs simultaneously), apply this YAML:

configMapGenerator:
- name: sas-restore-job-parameters
  behavior: merge
  literals:
  - SAS_DATA_SERVER_RESTORE_PARALLEL_JOB_COUNT=8

configMapGenerator:
- name: sas-restore-job-parameters
  behavior: merge
  literals:
  - SAS_DATA_SERVER_RESTORE_PARALLEL_JOB_COUNT=8

Tuning the Crunchy Data PostgreSQL pgBackRest Utility

Crunchy Data provides a backup and restore utility that operates independently from the SAS Viya platform backup service. The pgBackRest utility takes a binary backup of the Crunchy Data PostgreSQL server that provides an internal PostgreSQL instance. You can modify the default pgBackRest backup schedule for full or incremental backups. You can modify the retention policy for those backups and also change the archive policy for the PostgreSQL WAL (Write-Ahead Log) data.

For more information about the Crunchy Data pgBackRest utility, see the pgBackRest User Guide and the pgBackRest Command Reference.

Last updated: May 29, 2026