PostgreSQL

Optimize PostgreSQL 16 Performance on Ubuntu 24.04 VPS for Low RAM Environments

Philip Jensen

— 2 min read

Optimize PostgreSQL 16 Performance on Ubuntu 24.04 VPS for Low RAM Environments

At ByteHosting, we understand that running a high-performance database like PostgreSQL 16 on a VPS with limited RAM can be challenging. Whether you're hosting small-scale applications or testing environments, tuning PostgreSQL for low-resource systems is essential to ensure responsiveness and stability. In this guide, we’ll walk you through practical steps to optimize PostgreSQL 16 on Ubuntu 24.04 VPS, helping you get the most out of your server without upgrading hardware.

Prerequisites: PostgreSQL 16 Installed and Basic Understanding of Database Tuning

Before diving into configuration tweaks, ensure that PostgreSQL 16 is installed on your Ubuntu 24.04 VPS. You should also have a basic understanding of database concepts such as shared buffers, work memory, and autovacuum. Familiarity with command-line operations will make the tuning process smoother.

Review Default PostgreSQL Configuration and System Resources

Start by examining your current PostgreSQL settings and system resources. You can check the default configuration file located at /etc/postgresql/16/main/postgresql.conf. Use a text editor or grep to review key parameters:

sudo grep -E 'shared_buffers|work_mem|effective_cache_size' /etc/postgresql/16/main/postgresql.conf

Additionally, check your system's total RAM with:

free -h

On low RAM VPS, default settings are often too high, leading to swapping and degraded performance. Adjustments are necessary to align PostgreSQL's memory usage with your available resources.

Adjust Shared Buffers, Work Mem, and Effective Cache Size for Low RAM

These three parameters are critical for PostgreSQL performance. Proper tuning ensures efficient memory utilization without overcommitting, which can cause swapping.

Shared Buffers

This setting determines how much memory PostgreSQL uses for caching data. On low RAM systems, allocate a conservative amount—typically 15-25% of total RAM.

shared_buffers = 256MB  # For a 1GB RAM VPS

Adjust this value based on your server's total memory.

Work Mem

This parameter controls the amount of memory used for internal sort operations and hash tables before writing to disk. Set it to a modest value to prevent excessive memory consumption during complex queries.

work_mem = 16MB

Effective Cache Size

This setting estimates how much memory is available for disk caching by the operating system. For low RAM, set it to about 50-75% of total RAM.

effective_cache_size = 512MB  # For a 1GB RAM VPS

Remember to restart PostgreSQL after making changes:

sudo systemctl restart postgresql

Enable and Configure Autovacuum for Performance

Autovacuum is vital for maintaining database health, especially on systems with limited resources. Proper configuration prevents table bloat and ensures efficient query execution.

Check Autovacuum Status

SHOW autovacuum;

If disabled, enable it in postgresql.conf:

autovacuum = on

Tune Autovacuum Settings

Adjust parameters like autovacuum_vacuum_cost_limit and autovacuum_vacuum_threshold to balance maintenance and performance. For low RAM, increase thresholds to reduce the frequency of autovacuum runs:

autovacuum_vacuum_threshold = 50
autovacuum_vacuum_cost_limit = 200

This helps prevent autovacuum from consuming too much memory during operation.

Monitor PostgreSQL Performance and Resource Usage

Regular monitoring is essential to identify bottlenecks and verify that your tuning efforts are effective. Use tools like pg_stat_activity and pg_stat_database to gather insights:

psql -c "SELECT * FROM pg_stat_activity;"

Additionally, consider setting up monitoring solutions like pgAdmin or third-party tools to visualize performance metrics.

Troubleshooting Common Performance Issues and Misconfigurations

If you experience slow queries, high CPU usage, or excessive swapping, revisit your configuration. Common issues include:

  • Overly high shared_buffers or work_mem settings for low RAM systems
  • Autovacuum disabled or misconfigured
  • Insufficient disk I/O capacity

Adjust settings accordingly and restart PostgreSQL. Also, ensure your VPS's disk performance is adequate for your workload.

Conclusion

Optimizing PostgreSQL 16 on Ubuntu 24.04 VPS with low RAM requires careful tuning of memory parameters, autovacuum, and ongoing monitoring. By following our practical guide, you can significantly improve database responsiveness and stability without investing in more hardware. At ByteHosting, we’re committed to providing reliable, affordable VPS solutions that support your development and production needs. If you’re looking for a cost-effective environment to run your PostgreSQL databases, consider our VPS plans in Frankfurt, Germany, and start optimizing today!

Read more