Tuning PostgreSQL Configuration Parameters

FortiSIEM uses PostgreSQL database for many purposes. Some examples are:

• Store FortiSIEM configuration information
• Store CMDB information – devices, users, etc.
• Store Incidents and Cases and keeping them up to date
• Store External Threat Intelligence data
• Serve GUI users – provide information to display and store user changes
• Serve FortiSIEM Agents, Collectors and Workers with information needed to function
• Support Agent and Collector upgrades
• Integrate with help desk systems like ServiceNow
• Support public REST API

PostgreSQL performance is critical to FortiSIEM operations. This document describes how to tune important PostgreSQL configuration parameters for optimal performance. Note that the following are guidelines only and adjustments can be made based on the specific hardware, workload, and usage patterns to ensure the system runs efficiently while avoiding potential bottlenecks.

The following external reference may be helpful: Comprehensive Guide on How to Tune Database Parameters and Configuration in PostgreSQL

PostgreSQL configuration parameters are stored in /cmdb/data/postgresql.conf on the Supervisor or DB Server Node.

Important Configuration Parameters

1. effective_cache_size:
   • Definition: Specifies the available memory for PostgreSQL to assume for data caching.
   • Recommended value: 50%-75% of system memory, with a conservative approach of 50%.
   • FortiSIEM settings: FortiSIEM default is 4GB for All-In-One Supervisor Node. For FortiSIEM DB Node with 24GB memory, it can be increased to 12GB.
2. maintenance_work_mem:
   • Definition: Memory allocated for maintenance tasks such as VACUUM, CREATE INDEX, etc.
   • FortiSIEM settings: Default value: 64MB, can be adjusted as needed during maintenance tasks. Example of setting: SET maintenance_work_mem TO '128MB';
3. max_connections:
   • Definition: Specifies the maximum number of client connections.
   • Recommended value: Align with project needs, with a default value of 100.
   • FortiSIEM settings: FortiSIEM's default for All-In-One Supervisor Node is 300, and for FortiSIEM DB Node, it's set to 500. Ensure that max_connections > max-pool-size defined in Glassfish (max-pool-size=160 for DB connection pool) to leave room for other processes to access PostgreSQL.
4. max_wal_size:
   • Definition: Determines the threshold at which a checkpoint will be triggered.
   • Recommended value: Between 1GB and 2GB.
   • FortiSIEM settings: 2GB
5. shared_buffers:
   • Definition: Determines memory allocated to PostgreSQL for data caching.
   • Recommended value: 25% of total memory, up to a maximum of 8GB for FortiSIEM DB Node.
   • FortiSIEM settings: For FortiSIEM All-In-One Supervisor Node, adjust based on available memory. For example, allocate 2GB for PostgreSQL, and set shared_buffers to 512MB (1/4 of the 2GB).
6. temp_buffers:
   • Definition: Specifies the amount of memory for temporary buffers within each database session. Default value: 8MB.
   • FortiSIEM settings: For FortiSIEM DB Node, it is increased to 64MB for systems with 16GB allocated to PostgreSQL (on a total of 24GB system memory).
7. work_mem:
   • Definition: Determines memory usage for internal operations like sorting and hash tables.
   • Recommended value: Between 10MB and 50MB, depending on the number of connections.
   • FortiSIEM settings: FortiSIEM default is 16MB for All-In-One Supervisor Node and 32MB for FortiSIEM DB Node, but can be increased for faster sorting (be cautious of excessive values leading to memory bottlenecks).

Additional Configuration Parameters

8. autovacuum_analyze_scale_factor:
   • Definition: Controls the threshold for automatic analysis of tables. Reducing this value can help improve query performance by more frequently analyzing tables for better statistics.
   • Recommended value: Default is 0.1 (10% of the table’s rows).
   • FortiSIEM settings: 0.1
9. autovacuum_vacuum_scale_factor:
   • Definition: Controls when automatic vacuuming occurs for tables. Lowering this value can help prevent table bloat by vacuuming tables more frequently.
   • Recommended value: Default is 0.2 (20% of the table’s rows).
   • FortiSIEM settings: 0.2
10. checkpoint_completion_target:
    • Definition: Controls how much of the checkpoint interval is used to complete a checkpoint. Setting it closer to 1.0 helps in spreading I/O load, minimizing performance spikes during checkpoints.
    • Recommended value: 0.9 (90% of the checkpoint time).
    • FortiSIEM settings: 0.9
11. checkpoint_timeout:
    • Definition: Determines the maximum time between automatic write-ahead logging (WAL) checkpoints. A longer interval between checkpoints can improve performance but may risk more data loss in case of a crash.
    • Recommended value: Between 5 and 10 minutes.
    • FortiSIEM settings: 5 minutes
12. effective_io_concurrency:
    • Definition: Controls the number of simultaneous disk I/O operations PostgreSQL can perform. Higher values can benefit systems with multiple disks and parallel I/O operations, improving read/write performance.
    • Recommended value: Set to the number of available disk spindles (usually 2-4 for HDDs, or higher for SSDs).
    • FortiSIEM settings: 1
13. log_min_duration_statement:
    • Definition: Logs the duration of any query that takes longer than the specified value. Helps in identifying slow queries that may need optimization
    • Recommended value: 500ms or higher.
    • FortiSIEM settings: 500ms
14. log_temp_files:
    • Definition: Logs the use of temporary files during queries. Useful for debugging performance issues related to queries spilling to disk.
    • Recommended value: -1 disables, 0 logs all temp files.
    • FortiSIEM settings: -1
15. random_page_cost:
    • Definition: Defines the cost estimate for non-sequentially accessed pages. For SSD-based storage, this value should be reduced to reflect the faster random access performance of SSDs.
    • Recommended value: Lower the value if using SSDs (e.g., 1.0) or leave at default (4.0) for HDDs.
    • FortiSIEM settings: 4.0
16. seq_page_cost:
    • Definition: Defines the cost estimate for sequentially accessed pages. Reducing this value can help PostgreSQL's query planner favor sequential scans more often.
    • Recommended value: Default is 1.0, which works well for most systems.
    • FortiSIEM settings: 1.0