Work_mem and effective_cache_size tuning in PostgreSQL - Mini Project: Build & Apply

Work_mem and effective_cache_size Tuning in PostgreSQL

📖 Scenario: You are a database administrator for a small online bookstore. The database is running PostgreSQL, and you want to improve query performance by tuning memory settings.Two important settings are work_mem and effective_cache_size. These control how much memory PostgreSQL uses for sorting and caching data during queries.

🎯 Goal: Learn how to check current PostgreSQL memory settings, set new values for work_mem and effective_cache_size, and verify the changes.You will write SQL commands to view and update these settings step-by-step.

📋 What You'll Learn

Use the SHOW command to check current values of work_mem and effective_cache_size

Use the SET command to change work_mem and effective_cache_size for the current session

Use the ALTER SYSTEM SET command to change these settings permanently

Use the SELECT pg_reload_conf() command to reload configuration after permanent changes

💡 Why This Matters

🌍 Real World

Database administrators often tune memory settings to improve query speed and resource usage in PostgreSQL databases.

💼 Career

Knowing how to configure work_mem and effective_cache_size is important for roles like DBA, backend developer, and system administrator working with PostgreSQL.

1

Check current memory settings

Write two SQL commands to check the current values of work_mem and effective_cache_size using the SHOW command. Use exactly SHOW work_mem; and SHOW effective_cache_size;.

PostgreSQL

# Write SQL commands to show current work_mem and effective_cache_size
# Your code here

Need a hint?

Use the SHOW command followed by the setting name and a semicolon.

2

Set new memory values for current session

Write two SQL commands to set work_mem to 64MB and effective_cache_size to 2GB for the current session using the SET command. Use exactly SET work_mem = '64MB'; and SET effective_cache_size = '2GB';.

PostgreSQL

SHOW work_mem;
SHOW effective_cache_size;
# Write SQL commands to set work_mem and effective_cache_size for this session
# Your code here

Need a hint?

Use the SET command with the setting name, equals sign, value in quotes, and semicolon.

3

Make memory changes permanent

Write two SQL commands to permanently set work_mem to 64MB and effective_cache_size to 2GB using the ALTER SYSTEM SET command. Use exactly ALTER SYSTEM SET work_mem = '64MB'; and ALTER SYSTEM SET effective_cache_size = '2GB';.

PostgreSQL

SHOW work_mem;
SHOW effective_cache_size;
SET work_mem = '64MB';
SET effective_cache_size = '2GB';
# Write SQL commands to permanently set work_mem and effective_cache_size
# Your code here

Need a hint?

Use ALTER SYSTEM SET followed by the setting name, equals sign, value in quotes, and semicolon.

4

Reload configuration to apply permanent changes

Write the SQL command to reload the PostgreSQL configuration so the permanent changes take effect. Use exactly SELECT pg_reload_conf();.

PostgreSQL

SHOW work_mem;
SHOW effective_cache_size;
SET work_mem = '64MB';
SET effective_cache_size = '2GB';
ALTER SYSTEM SET work_mem = '64MB';
ALTER SYSTEM SET effective_cache_size = '2GB';
# Write SQL command to reload configuration
# Your code here

Need a hint?

Use SELECT pg_reload_conf(); to reload the config.