Work_mem and effective_cache_size tuning in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to set work_mem to 16MB for the current session.

PostgreSQL

SET work_mem = [1];

Drag options to blanks, or click blank then click option'

A'16GB'

B'16MB'

C'160MB'

D'1MB'

Attempts:

3 left

💡 Hint

Common Mistakes

Using too large a value like '16GB' which can cause memory issues.

Forgetting to include quotes around the value.

Setting work_mem to '16MB' allocates 16 megabytes of memory for query operations in the current session.

2fill in blank

medium

Complete the code to set effective_cache_size to 4GB in the postgresql.conf file.

PostgreSQL

effective_cache_size = [1]

Drag options to blanks, or click blank then click option'

A'4GB'

B'4096MB'

C'4000MB'

D'4MB'

Attempts:

3 left

💡 Hint

Common Mistakes

Using '4MB' which is too small.

Using '4000MB' which is less precise than 4096MB.

Setting effective_cache_size to '4GB' means PostgreSQL estimates 4GB of OS disk cache available for query planning.

3fill in blank

hard

Fix the error in the command to set work_mem to 32MB for the current session.

PostgreSQL

SET work_mem = [1]

Drag options to blanks, or click blank then click option'

A'32'

B32MB

C32

D'32MB'

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting quotes around the memory size.

Using just a number without units.

The value must be a string with quotes, like '32MB', to be valid in the SET command.

4fill in blank

hard

Fill both blanks to create a query that shows current work_mem and effective_cache_size settings.

PostgreSQL

SHOW [1]; SHOW [2];

Drag options to blanks, or click blank then click option'

Awork_mem

Beffective_cache_size

Cmax_connections

Dshared_buffers

Attempts:

3 left

💡 Hint

Common Mistakes

Using incorrect parameter names.

Trying to show parameters not related to memory tuning.

The SHOW command displays the current value of a configuration parameter. Here, we want to see work_mem and effective_cache_size.

5fill in blank

hard

Fill all three blanks to write a SQL command that sets work_mem to 8MB, effective_cache_size to 2GB, and then shows both settings.

PostgreSQL

SET work_mem = [1]; SET effective_cache_size = [2]; SHOW [3];

Drag options to blanks, or click blank then click option'

A'8MB'

B'2GB'

Ceffective_cache_size

D'16MB'

Attempts:

3 left

💡 Hint

Common Mistakes

Not quoting the memory sizes.

Showing work_mem instead of effective_cache_size in the last command.

This sequence sets work_mem to 8MB, effective_cache_size to 2GB, and then shows the current value of effective_cache_size.