Optimizing PostgreSQL Configuration for 100GB Database on a 4 vCPU, 8GB RAM, 1TB SSD Server

Ensuring fast data retrieval and seamless user experiences is crucial for databases of all sizes, including a 100GB PostgreSQL database. In this guide, we’ll provide detailed instructions on configuring PostgreSQL, its cache, and sort settings to optimize performance for a 100GB database running on a server with 4 vCPUs, 8GB RAM, and a 1TB SSD.

1. PostgreSQL Configuration:

Shared Buffers:

• shared_buffers: Set to 4GB (approximately 50% of RAM) to allocate sufficient memory for shared buffers, which cache frequently accessed data.

Work Memory:

• work_mem: Set to 1GB – 2GB to provide adequate memory for sorting and joining operations.
• maintenance_work_mem: Set to 512MB – 1GB to allocate memory for background tasks like vacuuming and autovacuuming.

Background Processes:

• min_worker_processes: Set to 2 – 4 to ensure enough worker processes for concurrent connections.
• max_worker_processes: Set to 4 – 8 to handle a moderate volume of concurrent connections.

Logging:

• log_level: Set to notice or warning to reduce logging verbosity and minimize I/O impact.
• fsync: Set to off to disable synchronous writes, improving performance but increasing the risk of data loss in case of a crash.

WAL Settings:

• wal_buffers: Set to 2MB – 4MB to allocate memory for write-ahead logging buffers.
• wal_level: Set to minimal or replication to minimize WAL logging overhead.

2. Cache Settings:

• cache_size: Set to 512MB – 1GB to allocate memory for the statement cache, which stores prepared statements for faster execution.

3. Sort Settings:

• sort_work_mem: Set to 512MB – 1GB to provide adequate memory for sorting operations.
• temp_buffers: Set to 512MB – 1GB to allocate memory for temporary buffers used during sorting.

4. postgresql.conf Configuration File Content:

shared_buffers = 4GB
work_mem = 1GB
maintenance_work_mem = 512MB
min_worker_processes = 2
max_worker_processes = 4
log_level = notice
fsync = off
wal_buffers = 4MB
wal_level = minimal
cache_size = 1GB
sort_work_mem = 1GB
temp_buffers = 1GB

5. Considerations:

• Fine-tune the configuration values based on specific workload characteristics and performance requirements.
• Refer to the official PostgreSQL documentation for detailed explanations of each configuration option: https://www.postgresql.org/docs/current/
• Utilize performance analysis tools like pgBadger or pgMonitor to identify and address performance bottlenecks.