Database Management · Advanced level

Advanced level · Module 2: Performance Tuning · Lesson 4 of 4

Configuration and Connection Management

Tune memory settings and manage connections with pooling, based on measurements.

In this lesson you will

  • Adjust key memory settings sensibly
  • Use connection pooling to protect the server

Key concepts

shared_buffers and work_memconnection limitsconnection pooling (PgBouncer)measure, change, measure

The lesson

A few settings matter most: shared_buffers (memory for cached data), work_mem (memory per sort or hash operation) and effective_cache_size (a hint about available OS cache). Defaults are cautious; adjust them to the server's memory, carefully.

Each connection uses memory and processing. Hundreds of idle application connections can exhaust a server. A connection pooler such as PgBouncer lets many clients share a small number of server connections.

Change one setting at a time and measure with the same workload. Record what you changed and why.

Step by step

  1. Review the server's memory settings.
  2. Measure a sort-heavy query with two work_mem values.
  3. Set up PgBouncer in front of the server.
  4. Record the results in the tuning report.

Practice activity

Tune the health programme server's key settings and add connection pooling, documenting measurements.

Expected result: Documented, measured configuration changes and a working connection pooler.

Check your understanding

  1. What does a connection pooler do?

    Show answer

    Lets many clients share a small number of database connections.

  2. Why change one setting at a time?

    Show answer

    So the effect of each change can be measured and understood.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.