CloudNativePG Tuning

The HavenPlus reference implementation uses PostgreSQL default values. For workloads requiring better performance, you can customize database parameters in the CloudNativePG Cluster specification.

Performance Tuning Parameters

For clusters with heavier workloads, you can tune the following postgresql.parameters. These should be set in proportion to the Cluster's configured resources (CPU and memory).

Use PGTune to calculate recommended starting values by entering your Cluster's resources, workload type, and storage type.

Parameter	Example value	What it does	Why
shared_buffers	2GB	PostgreSQL's shared memory for caching table and index data	Rule of thumb: 25-50% of the memory request. Claimed at startup, leaving less headroom for connections and work_mem
effective_cache_size	4GB	Planner estimate of total cacheable memory (shared_buffers + OS cache)	Guides the planner toward index scans; typically 50-75% of total RAM, kept conservative relative to the memory limit
maintenance_work_mem	512MB	Memory allocated for maintenance operations like VACUUM, CREATE INDEX	Speeds up maintenance operations (default 64MB); used only during those operations, not per connection
work_mem	4MB	Memory per sort or hash operation per query	Keep close to default when max_connections is high (e.g., 500). Higher values multiplied by concurrent operations risk OOM
max_connections	500	Maximum number of concurrent connections to the database	Controls connection load. Higher values allow more clients but consume more memory per connection
max_worker_processes	4	Maximum number of background worker processes	Ceiling for all parallel worker settings
max_parallel_workers	4	Maximum parallel workers for parallel queries	Subset of max_worker_processes
max_parallel_workers_per_gather	2	Maximum parallel workers per single parallel query	Match to the CPU limit of the Cluster
max_parallel_maintenance_workers	2	Maximum parallel workers for maintenance operations	Match to the CPU limit of the Cluster
checkpoint_completion_target	0.9	Target fraction of checkpoint interval to complete	Spreads checkpoint I/O over time to avoid I/O spikes on shared or cloud storage
random_page_cost	1.1	Planner's estimated cost of a random page read vs. sequential	Lowered from the default 4.0 (HDD assumption) to reflect SSD or cloud-backed storage, favoring index scans
effective_io_concurrency	200	Number of concurrent I/O operations the storage can handle	High value suits SSD or cloud storage (default 1 assumes a single spinning disk)
default_statistics_target	100	Default sample size for ANALYZE statistics	Equal to the PostgreSQL default; explicitly set for documentation clarity
huge_pages	try	Enables use of OS huge pages for shared memory	Reduces memory management overhead for large shared_buffers; try avoids hard dependency on node configuration
hot_standby_feedback	on	Enables standby servers to send feedback to the primary	Prevents the primary from removing WAL files still needed by standbys; required for read replicas

caution

If shared_buffers is set equal to the memory request, total usage (buffers + work_mem × concurrent operations + overhead) can approach the memory limit under high connection load. Monitor usage and adjust the memory request, or lower shared_buffers and work_mem, if OOM restarts occur.

Configuring PostgreSQL Parameters in a CNPG Cluster Resource

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: example-pg-cluster
spec:
  instances: 3
  storage:
    size: 10Gi
  postgresql:
    parameters:
      shared_buffers: "2GB"
      effective_cache_size: "4GB"
      maintenance_work_mem: "512MB"
      work_mem: "4MB"
      max_connections: "500"
      max_worker_processes: "4"
      max_parallel_workers: "4"
      max_parallel_workers_per_gather: "2"
      max_parallel_maintenance_workers: "2"
      checkpoint_completion_target: "0.9"
      random_page_cost: "1.1"
      effective_io_concurrency: "200"
      default_statistics_target: "100"
      huge_pages: "try"
      hot_standby_feedback: "on"