AlloyDB Omni performance tuning parameters on a VM

This document describes performance tuning parameters that can improve AlloyDB Omni performance on a VM. These suggested tuning parameters are based on the results of running a set of workloads that exhibit different read and write patterns, caching behaviors, and index access patterns.

Most default PostgreSQL parameter values remain unchanged, because the changes are limited to parameters that benefit the majority of applications. The suggested values of these parameters should generally improve performance on modern hardware.

Database parameter AlloyDB Omni default Suggested setting
random_page_cost 4.0 1.1
temp_buffers 8 MB 128 MB
max_wal_size 1 GB 20 GB
min_wal_size 80 MB 10 GB
default_toast_compression pglz lz4
maintenance_work_mem 64 MB 1 GB for smaller machines 2 GB for larger machines
max_worker_processes 64 max(64, # vCPUs)
max_parallel_workers 8 max(8, # vCPUs)
max_parallel_workers_per_gather 2 max(2, # vCPUs / 2)
work_mem 4 MB 128 to 512 MB, depending on workload and the amount of memory available
effective_cache_size 40% * DRAM 80% * DRAM