8. Performance DB Tunning (rosetta version: greater than equal 1.2.6)

Introduction

Our current postgres parameters are set to Postgres 14.11 bullseye recommendations:

PostgreSQL Tuning (PostgreSQL Defaults)

DB_POSTGRES_MAX_CONNECTIONS=100
DB_POSTGRES_SHARED_BUFFERS=128MB
DB_POSTGRES_EFFECTIVE_CACHE_SIZE=4GB
DB_POSTGRES_WORK_MEM=4MB
DB_POSTGRES_MAINTENANCE_WORK_MEM=64MB
DB_POSTGRES_WAL_BUFFERS=16MB
DB_POSTGRES_CHECKPOINT_COMPLETION_TARGET=0.5
DB_POSTGRES_RANDOM_PAGE_COST=4.0

A brief description of parameters is below (from postgresql.conf):

Default PostgreSQL 14.x Values:

• max_connections: 100 (default number of concurrent connections PostgreSQL can handle).
• shared_buffers: 128MB (default memory allocated for caching database blocks).
• effective_cache_size: 4GB (default cache hint for the PostgreSQL query planner).
• work_mem: 4MB (default memory allocated for each query operation).
• maintenance_work_mem: 64MB (default memory used for maintenance operations like vacuum, index creation).
• wal_buffers: 16MB (default memory buffers for write-ahead logging).
• checkpoint_completion_target: 0.5 (default time spread for checkpoint operations).
• random_page_cost: 4.0 (default cost of a non-sequential page read).

Rosetta Docker Parameter Descriptions (e.g. in .env.docker-compose file):

• DB_POSTGRES_MAX_CONNECTIONS: Description: The maximum number of concurrent connections PostgreSQL will allow. Default: 100 Recommendation: Adjust based on the anticipated load and number of concurrent clients.
• DB_POSTGRES_SHARED_BUFFERS: Description: The amount of memory PostgreSQL will allocate for caching data blocks. This significantly impacts the performance of data access operations. Default: 128MB Recommendation: Typically set to 25% of total system RAM for production systems, but depends on available system resources.
• DB_POSTGRES_EFFECTIVE_CACHE_SIZE: Description: A rough estimate of how much memory is available for caching data in the operating system and PostgreSQL. It helps PostgreSQL to optimize query plans. Default: 4GB Recommendation: Set to around 50-75% of system RAM for systems with large datasets.
• DB_POSTGRES_WORK_MEM: Description: The amount of memory allocated for each individual query operation (like sorting and hashing). This is per operation, so high values can increase memory usage for complex queries. Default: 4MB Recommendation: Can be increased for complex queries, but be cautious with system memory limitations.
• DB_POSTGRES_MAINTENANCE_WORK_MEM: Description: The memory PostgreSQL uses for maintenance operations such as VACUUM, CREATE INDEX, and ALTER TABLE. Default: 64MB Recommendation: Can be increased for faster maintenance operations, especially on large tables.
• DB_POSTGRES_WAL_BUFFERS: Description: The amount of memory allocated to the write-ahead log (WAL) buffers, affecting the performance of transaction logging. Default: 16MB Recommendation: Larger values can improve write-heavy workloads, but higher values should be used cautiously to prevent excessive memory use.
• DB_POSTGRES_CHECKPOINT_COMPLETION_TARGET: Description: Controls the spread of checkpoints over time, aiming to reduce I/O spikes. Higher values reduce the frequency of checkpoints, but increase WAL file size. Default: 0.5 Recommendation: Set to 0.9 to reduce I/O spikes for write-heavy applications.
• DB_POSTGRES_RANDOM_PAGE_COST: Description: Used by PostgreSQL’s query planner to estimate the cost of non-sequential page accesses (e.g., index scans). A lower value favors index scans, while a higher value favors sequential scans. Default: 4.0 Recommendation: Can be set to 1.1 for SSD/NVMe systems to favor index scans over sequential scans, as these devices have lower random access costs.

PostgreSQL Tuning Based on Machine Configuration

Configuration ID	CPU Core Count	RAM	Storage Type	Virtualized	IOPS Type	DB_POSTGRES_MAX_CONNECTIONS	DB_POSTGRES_SHARED_BUFFERS	DB_POSTGRES_EFFECTIVE_CACHE_SIZE	DB_POSTGRES_WORK_MEM	DB_POSTGRES_MAINTENANCE_WORK_MEM	DB_POSTGRES_WAL_BUFFERS	DB_POSTGRES_CHECKPOINT_COMPLETION_TARGET	DB_POSTGRES_RANDOM_PAGE_COST
1	4	8GB	SSD	Yes	Low	100	2GB	3GB	4MB	64MB	16MB	0.7	4.0
2	4	8GB	SSD	No	High	150	4GB	6GB	8MB	128MB	32MB	0.9	4.0
3	8	32GB	SSD	Yes	Low	200	8GB	16GB	16MB	512MB	64MB	0.8	4.0
4	8	32GB	SSD	No	High	300	16GB	24GB	32MB	1GB	128MB	0.9	4.0
5	16	64GB	NVMe	Yes	Low	400	32GB	48GB	64MB	2GB	128MB	0.9	1.1
6	16	64GB	NVMe	No	High	500	64GB	96GB	128MB	4GB	256MB	0.9	1.1
7	32	128GB	NVMe	Yes	Low	600	128GB	128GB	256MB	8GB	512MB	0.9	1.1
8	32	128GB	NVMe	No	High	800	256GB	256GB	512MB	16GB	1GB	0.9	1.1
9	64	256GB	NVMe	Yes	Low	1000	512GB	512GB	1GB	32GB	2GB	0.9	1.1
10	64	256GB	NVMe	No	High	1500	1TB	1TB	2GB	64GB	4GB	0.9	1.1

---

Key Parameters Explained:

1. DB_POSTGRES_MAX_CONNECTIONS: The maximum number of connections PostgreSQL will allow.
2. DB_POSTGRES_SHARED_BUFFERS: Memory allocated for caching data blocks in PostgreSQL.
3. DB_POSTGRES_EFFECTIVE_CACHE_SIZE: A hint to the query planner about the effective size of memory caches.
4. DB_POSTGRES_WORK_MEM: Memory allocated for each query operation (sort, hash, etc.).
5. DB_POSTGRES_MAINTENANCE_WORK_MEM: Memory allocated for maintenance operations (VACUUM, CREATE INDEX, etc.).
6. DB_POSTGRES_WAL_BUFFERS: Memory allocated for write-ahead log (WAL) buffers.
7. DB_POSTGRES_CHECKPOINT_COMPLETION_TARGET: Controls how spread out checkpoints are, reducing I/O spikes.
8. DB_POSTGRES_RANDOM_PAGE_COST: The cost estimate for random page access, influencing the choice between sequential and index scans.

Configuration Details:

• CPU Core Count: Affects the processing power for query execution and parallel operations.
• RAM: Influences the caching behavior, work memory, and overall performance.
• Storage Type: NVMe offers much better IOPS and latency compared to SSD, which can have a significant effect on write-heavy workloads.
• Virtualized: Virtualized environments may have shared resources and overhead, which can affect the maximum values for some parameters.
• IOPS Type: Systems with high IOPS (like NVMe drives) can handle more intensive workloads with fewer bottlenecks in terms of data retrieval and write operations.

Recommendation:

• Low IOPS & SSD: Tuning is more conservative. Focus on optimizing shared buffers, work memory, and connection count for moderate workloads.
• High IOPS & NVMe: Can handle more aggressive tuning, especially for workloads that involve heavy writes and large data sets. Increase shared buffers, effective cache size, and connection limits as needed.

---

Explanation:

• Configuration ID: Sequential ID to identify different machine sizes.
• CPU Core Count: The number of CPU cores for the machine.
• RAM: Amount of RAM available, affecting cache sizes and work memory.
• Storage Type: The type of storage being used (SSD or NVMe).
• Virtualized: Indicates whether the system is virtualized or running on real hardware.
• IOPS Type: This column distinguishes between low IOPS and high IOPS setups. High IOPS typically refers to systems using NVMe storage with low latency and high throughput.
• Parameters: Different PostgreSQL parameters are tuned based on the configuration, focusing on concurrent connections, memory allocation, and write performance.