DEV Community

Cover image for Mastering PostgreSQL Performance: Linux Tuning and Database Optimization
Anuj Tyagi
Anuj Tyagi

Posted on

1

Mastering PostgreSQL Performance: Linux Tuning and Database Optimization

PostgreSQL is a powerful, open-source relational database that can handle massive datasets efficiently. However, its out-of-the-box configuration often requires optimization to achieve peak performance in production environments. Whether you’re running PostgreSQL on a local machine, in Docker, or on a managed service like Aiven, tuning both the Linux system and PostgreSQL configurations is essential. This blog explores key techniques for tuning Linux for PostgreSQL and optimizing database configurations.

1. Optimize Kernel Parameters

The Linux kernel can significantly impact database performance. Key settings include:

  • vm.swappiness: Set to 1 to minimize swapping.
  • vm.dirty_ratio and vm.dirty_background_ratio: Configure these to 10% and 5%, respectively, to manage how modified memory is flushed to disk.

Add these to /etc/sysctl.conf and apply changes with sudo sysctl -p.

2. Adjust Filesystem Settings

Use high-performance filesystems like ext4 or XFS, and mount them with the noatime option to reduce write operations. Example /etc/fstab entry:

/dev/sdX /data ext4 defaults,noatime 0 2
Enter fullscreen mode Exit fullscreen mode

3. Monitor System Resources

  • Memory: Tools like vmstat and htop help ensure efficient usage and avoid heavy swapping.
  • I/O: Use iostat and iotop to identify bottlenecks.
  • CPU: Track with top or pg_top.

4. Use the Right Scheduler

PostgreSQL benefits from the Completely Fair Scheduler (CFS). Verify and adjust the scheduler if necessary:

grep . /sys/block/sdX/queue/scheduler
Enter fullscreen mode Exit fullscreen mode

PostgreSQL Configuration Tuning

Key Configuration Settings

Focus on these PostgreSQL parameters for performance tuning:

  1. shared_buffers: Allocates memory for caching data. Set to 25% of total RAM.
  2. effective_cache_size: Helps the query planner estimate available cache space. Use 50-75% of RAM.
  3. work_mem: Adjust based on query complexity. Start with 4MB per connection.
  4. maintenance_work_mem: Allocate sufficient memory for VACUUM and index creation.
  5. max_connections: Optimize this based on workload and pooling tools like PgBouncer.
  6. random_page_cost and seq_page_cost: For SSDs, set both to 1 to reflect similar read costs.
  7. log_min_duration_statement: Logs slow queries for analysis.

Modify these settings in postgresql.conf or use SQL commands:

ALTER SYSTEM SET shared_buffers = '4GB';
Enter fullscreen mode Exit fullscreen mode

Beyond Configuration: Advanced Tuning Techniques

Analyze Queries with EXPLAIN

Use EXPLAIN or EXPLAIN ANALYZE to evaluate and optimize query plans.

Leverage Indexing

Indexes significantly speed up query performance. Choose the right type (B-tree, GIN, etc.) based on your workload. For example:

CREATE INDEX idx_name ON table_name(column_name);
Enter fullscreen mode Exit fullscreen mode

Use VACUUM Aggressively

Regular vacuuming prevents table bloat and maintains performance:

VACUUM ANALYZE;
Enter fullscreen mode Exit fullscreen mode

Monitor Logs for Insights

Adjust logging settings, such as log_line_prefix and log_statement, to gather meaningful performance data.


Managed Services and Their Defaults

Managed services like Aiven optimize PostgreSQL configurations differently. For example, Aiven uses a lower max_connections value and a different cache strategy to maximize throughput with limited resources.


Conclusion

Tuning Linux and PostgreSQL requires an iterative approach. Start with basic adjustments, monitor performance metrics, and gradually refine your configurations. Whether you’re managing a small-scale deployment or a large enterprise system, these optimizations will help you extract the best performance from PostgreSQL.

Stay curious, and happy tuning!

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

Heroku

Build apps, not infrastructure.

Dealing with servers, hardware, and infrastructure can take up your valuable time. Discover the benefits of Heroku, the PaaS of choice for developers since 2007.

Visit Site

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay