CompTIA DataSys+ (DS0-001)Database DeploymentMedium
A database administrator is planning to deploy a new PostgreSQL database server on a Linux host. To ensure proper resource allocation and prevent the database from consuming all system memory, the administrator needs to configure the maximum amount of memory PostgreSQL can use for caching data and indexes. Which parameter in `postgresql.conf` controls this setting?
- Aeffective_cache_size
- Bmaintenance_work_mem
- Cwork_mem
- Dshared_buffers
Show answer & explanationAnswer & explanation
Correct answer: D. shared_buffers
`shared_buffers` in `postgresql.conf` controls the amount of memory PostgreSQL dedicates to caching data pages, which is crucial for overall database performance and preventing excessive memory consumption.
Why the other options are wrong
- A. `effective_cache_size` is an optimizer hint to estimate available OS cache, not an actual memory allocation parameter.
- B. `maintenance_work_mem` is used for maintenance operations like VACUUM, CREATE INDEX, and ALTER TABLE, not the general data cache.
- C. `work_mem` defines the memory used by internal sort operations and hash tables per query operation, not the main data cache.
PostgreSQL shared_buffers
A core PostgreSQL configuration parameter that sets the amount of shared memory used by the database server for caching data blocks and indexes.
- Crucial for database performance; higher values generally mean more data can be cached.
- Should typically be set to 25% of total system RAM, up to a certain limit.
- Too high a value can lead to excessive swapping or OOM errors.
Memory trick: Shared buffers for shared data blocks.