Optimizing PostgreSQL chevron_right Key postgresql.conf Parameters Affecting Query Performance

Key postgresql.conf Parameters Affecting Query Performance

calendar_today schedule 7 min read

Learning Objectives: By the end of this lesson, you will be able to:

  • Identify and explain the most important PostgreSQL configuration parameters for query performance
  • Adjust key memory and planner-related settings and observe their impact
  • Analyze query plans to understand the effect of configuration changes

1. Welcome and Introduction (5 minutes)

Icebreaker Activity

Have you ever run a query that was unexpectedly slow, even though the database server seemed powerful? What do you think controls how much memory or resources a query can use?

Lesson Overview

  • Duration: 90 minutes
  • Prerequisites: Basic familiarity with PostgreSQL, ability to run queries, basic understanding of EXPLAIN plans
  • Materials Needed: PostgreSQL instance (local or cloud), access to postgresql.conf or ability to use SET commands, psql or GUI client

2. Learning Objectives and Session Outline (5 minutes)

What We’ll Cover Today:

  1. Core PostgreSQL configuration parameters for performance
  2. How these parameters influence the query planner and execution
  3. Practical demonstration: adjusting settings and observing effects
  4. Hands-on exercises
  5. Common mistakes and troubleshooting

Success Criteria:

By the end of this session, you should be able to confidently adjust and test key configuration parameters to optimize query performance for different workloads.

3. Foundation Knowledge (15-20 minutes)

Core Concepts

What is postgresql.conf?

postgresql.conf is the main configuration file for PostgreSQL. It controls server-wide settings, including memory usage, planner behavior, and more.

Real-World Analogy: Think of postgresql.conf as the control panel for your car’s engine—adjusting these settings can make your database run faster, smoother, or more efficiently, depending on your needs.

Why This Matters: The default settings are often conservative. Tuning them for your hardware and workload can yield dramatic performance improvements, especially for complex queries or large datasets.

Key Parameters for Query Performance

  • shared_buffers: Memory allocated for caching data blocks. Affects how much data can be kept in memory, reducing disk reads.
  • work_mem: Memory available for operations like sorts and hashes per query. Too low = disk-based operations (slow); too high = risk of out-of-memory errors.
  • effective_cache_size: An estimate of how much memory is available for disk caching by the OS and PostgreSQL. Influences planner’s cost estimates.
  • maintenance_work_mem: Memory for maintenance tasks (e.g., VACUUM, CREATE INDEX). Not directly for queries, but impacts overall performance.
  • random_page_cost: Planner’s estimate of the cost of reading a random page from disk. Lowering this can make index scans more attractive.
  • seq_page_cost: Cost estimate for sequential page reads. Usually lower than random_page_cost.

Key Terminology

  • Buffer: A chunk of memory used to cache data blocks from disk.
  • Planner: The PostgreSQL component that decides how to execute a query.
  • Cost: An abstract unit used by the planner to compare different execution strategies.

4. Detailed Explanation and Examples (25-30 minutes)

Understanding Key Parameters

shared_buffers

  • Default: Often 128MB. Recommended: 15-25% of system RAM for dedicated servers.
  • Too low: Frequent disk reads, slow queries.
  • Too high: Starves OS of memory for its own caching.

work_mem

  • Used for sorts, hashes, and materializations per operation (not per query!).
  • Example: A query with 3 sorts and 2 hashes could use 5x work_mem.
  • Set too low: Disk-based sorts (see “Sort Method: external merge” in EXPLAIN ANALYZE).
  • Set too high: Risk of running out of memory if many queries run concurrently.

effective_cache_size

  • Tells the planner how much data is likely cached (by PostgreSQL + OS).
  • Higher value = planner more likely to choose index scans.
  • Does not allocate memory, just influences planner decisions.

maintenance_work_mem

  • Used for maintenance tasks (VACUUM, CREATE INDEX, etc.).
  • Not used for regular queries, but important for overall system health.

random_page_cost & seq_page_cost

  • random_page_cost: Default 4.0 (assumes random disk I/O is 4x slower than sequential). On SSDs, can be set lower (e.g., 1.1-1.5).
  • seq_page_cost: Default 1.0. Rarely changed.
  • Lowering random_page_cost makes index scans more attractive.

Example: How work_mem Affects Query Plans

Suppose you have a query that sorts a large table. If work_mem is too low, PostgreSQL will spill to disk:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM big_table ORDER BY some_column;

If you see Sort Method: external merge Disk: in the plan, try increasing work_mem:

SET LOCAL work_mem = '128MB';
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM big_table ORDER BY some_column;

Now you may see Sort Method: quicksort Memory:—the sort happened in memory!

Practical Applications

  • Tuning these parameters can speed up reporting queries, batch jobs, and OLAP workloads.
  • For OLTP (many small queries), careful tuning prevents memory exhaustion.

Common Misconceptions

  1. Misconception: “Bigger is always better for memory settings.” Reality: Setting memory too high can cause the server to run out of RAM and crash, especially with many concurrent queries.
  2. Misconception: “Changing effective_cache_size allocates memory.” Reality: It only informs the planner; it does not reserve memory.

5. Live Coding Demonstration (20-25 minutes)

Example 1: Adjusting work_mem for a Sort

-- Default work_mem (e.g., 4MB)
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM big_table ORDER BY some_column;

-- Increase work_mem for this session
SET LOCAL work_mem = '64MB';
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM big_table ORDER BY some_column;

Explanation:

  • The first plan may show a disk-based sort if the table is large.
  • The second plan, with higher work_mem, may perform the sort in memory, reducing execution time.

Example 2: Tuning random_page_cost for SSDs

-- Default value (4.0) may favor sequential scans
SET random_page_cost = 1.2;
EXPLAIN (ANALYZE) SELECT * FROM big_table WHERE indexed_column = 42;

Key Points:

  • Lowering random_page_cost makes index scans more attractive, which is appropriate for SSDs.
  • Always test changes with real queries and data.

Example 3: shared_buffers and effective_cache_size

# In postgresql.conf
shared_buffers = 2GB
# For a server with 8GB RAM

effective_cache_size = 6GB

Explanation:

  • shared_buffers is the memory PostgreSQL uses for its own cache.
  • effective_cache_size tells the planner how much data is likely cached (including OS cache).

Debugging Demonstration

Show a common mistake:

-- Setting work_mem too high globally
SET work_mem = '2GB';
-- If 50 queries run at once, could use 100GB+ RAM and crash!

Corrected version:

-- Use SET LOCAL for a single query, or set a reasonable default (e.g., 8-64MB)
SET LOCAL work_mem = '32MB';

6. Hands-On Practice (30-40 minutes)

Exercise 1: Basic Practice (10 minutes)

Objective: Observe the effect of work_mem on sort operations

Instructions:

  1. Run a query that sorts a large table and note the sort method in the EXPLAIN ANALYZE output.
  2. Increase work_mem using SET LOCAL and rerun the query.
  3. Compare the execution time and sort method.

Starter Code:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM big_table ORDER BY some_column;
SET LOCAL work_mem = '64MB';
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM big_table ORDER BY some_column;

Solution:

-- You should see the sort method change from external merge (disk) to quicksort (memory) if the table fits in work_mem.

Exercise 2: Intermediate Challenge (15 minutes)

Objective: Tune random_page_cost and observe planner changes

Requirements:

  • Set random_page_cost to a lower value
  • Run a query with a WHERE clause on an indexed column
  • Observe if the plan switches from Seq Scan to Index Scan

Hints:

  • Use EXPLAIN to see the plan
  • Try different values (e.g., 4.0, 1.5, 1.1)

Exercise 3: Creative Application (15 minutes)

Objective: Design a configuration for a reporting workload

Challenge: Given a server with 16GB RAM and mostly reporting queries, propose values for shared_buffers, work_mem, and effective_cache_size. Justify your choices.

Extension Ideas:

  • Research how these settings differ for OLTP vs. OLAP workloads
  • Test your configuration with pgbench or real queries

7. Common Pitfalls and Troubleshooting (10 minutes)

Frequent Mistakes

  1. Problem: Setting work_mem too high globally Cause: Each operation can use work_mem, so many queries can exhaust RAM Solution: Use SET LOCAL for large queries, keep default moderate

  2. Problem: Not adjusting random_page_cost for SSDs Cause: Default assumes spinning disks Solution: Lower for SSDs, but test with your workload

Debugging Tips

  • Always use EXPLAIN (ANALYZE, BUFFERS) to see the real impact
  • Monitor memory usage with system tools (htop, free, Activity Monitor)
  • Read PostgreSQL logs for out-of-memory errors

8. Best Practices and Professional Tips (5 minutes)

Industry Standards

  • Tune shared_buffers to 15-25% of RAM for dedicated servers
  • Use SET LOCAL for large, one-off queries
  • Document all changes and test with real workloads

Next Steps in Your Learning Journey

  • Learn about autovacuum and bloat management
  • Explore connection pooling and partitioning
  • Read the official PostgreSQL documentation on performance tuning

9. Summary and Review (5 minutes)

Key Takeaways

  1. Key configuration parameters can dramatically affect query performance
  2. Memory settings must be balanced for your workload and hardware
  3. Always test changes and monitor their effects

Quick Review Questions

  1. What does shared_buffers control?
  2. Why is setting work_mem too high risky?
  3. How does random_page_cost affect the planner’s decisions?

For Instructors

Teaching Tips

  • Use real-world analogies (e.g., car engine tuning)
  • Encourage students to experiment in a safe environment
  • Discuss the trade-offs of each setting

Assessment Ideas

  • Have students tune a test server and report on performance changes
  • Quiz on parameter effects and safe tuning practices
  • Peer review of configuration proposals

Adaptation Notes

  • For beginners: Focus on shared_buffers and work_mem, less on planner cost parameters
  • For advanced: Dive deeper into planner internals and OS-level caching
  • Online: Use cloud PostgreSQL instances or Docker for safe experimentation