MCQs on Database Performance Tuning | PostgreSQL Database

200+ Free PostgreSQL MCQs Quiz | Advanced| MCQs on PostgreSQL Database MCQs on Database Performance Tuning | PostgreSQL Database

PostgreSQL performance tuning involves optimizing configuration settings, managing memory, monitoring activities, and maintaining database health. This guide covers key aspects like configuration parameters, memory management, and vacuuming for optimal performance.

MCQs on Database Performance Tuning in PostgreSQL

1. Tuning Configuration Parameters (postgresql.conf)

The postgresql.conf file is used to configure:
- A) System-level settings
- B) Database-level configurations
- C) SQL queries
- D) Operating system parameters
Which parameter in postgresql.conf controls the maximum number of concurrent database connections?
- A) max_connections
- B) shared_buffers
- C) work_mem
- D) max_wal_size
What does the log_min_duration_statement parameter in postgresql.conf do?
- A) Defines the maximum log file size
- B) Sets the minimum execution time for logging queries
- C) Configures the duration of backups
- D) Controls the maximum statement duration for queries
Which parameter in postgresql.conf helps to control the checkpoint interval?
- A) checkpoint_timeout
- B) checkpoint_segments
- C) log_checkpoints
- D) max_wal_size
In the postgresql.conf, the work_mem parameter controls:
- A) Maximum amount of memory used by each query operation
- B) The size of shared buffers
- C) Disk space used for temporary tables
- D) Network buffer size
To set the maximum amount of memory for shared buffers in PostgreSQL, which parameter would you configure?
- A) shared_buffers
- B) max_connections
- C) temp_buffers
- D) wal_buffers
What is the default value of max_connections in PostgreSQL?
- A) 10
- B) 100
- C) 500
- D) 200
Which parameter is responsible for controlling the level of information logged for SQL statements in PostgreSQL?
- A) log_statement
- B) log_min_duration_statement
- C) log_connections
- D) log_temp_files

2. Memory Management (Work Memory, Shared Buffers)

The work_mem setting in PostgreSQL refers to:
- A) Memory allocated for each database connection
- B) The total amount of memory used for all queries
- C) The memory allocated for each sorting or hash operation
- D) Cache memory for frequently accessed data
What is the recommended approach when setting work_mem in a system with multiple concurrent queries?
- A) Set it to a very high value to maximize query performance
- B) Set it to a low value to avoid memory overload
- C) Set it based on the average query size
- D) Set it high only for sequential scans
Which of the following is the correct way to calculate the shared_buffers parameter?
- A) 10% of the system RAM
- B) 50% of the system RAM
- C) 20% of the system RAM
- D) 1% of the system RAM
The effective_cache_size parameter in PostgreSQL is used to:
- A) Control the number of database connections
- B) Estimate the amount of memory available for caching disk blocks
- C) Allocate memory for sorting operations
- D) Set the limit for temporary files
To avoid excessive swapping and paging, the work_mem parameter should be:
- A) Set to a very large value for complex queries
- B) Set based on available system memory and number of connections
- C) Set to the same value as shared_buffers
- D) Left to the default value only
The maintenance_work_mem parameter affects which operation?
- A) Sorting large datasets
- B) Index creation and vacuum operations
- C) Connection pooling
- D) WAL file management
What is the primary purpose of the temp_buffers parameter?
- A) Define temporary space for sorting
- B) Control memory usage for temporary tables
- C) Set the cache for index data
- D) Limit memory usage for complex queries
Which memory setting helps PostgreSQL optimize buffer access for large datasets?
- A) shared_buffers
- B) work_mem
- C) maintenance_work_mem
- D) temp_buffers

3. Monitoring Tools (pg_stat_activity, pg_stat_user_tables)

The pg_stat_activity view shows:
- A) The status of database transactions
- B) Current activity of database sessions
- C) Details about the database schema
- D) Query performance statistics
Which of the following information is available in pg_stat_activity?
- A) User roles
- B) Active queries
- C) Index statistics
- D) Disk space usage
The pg_stat_user_tables view provides information about:
- A) User-specific connection statistics
- B) Number of rows and index statistics for tables
- C) Active queries and their execution time
- D) Table locking and blocking
The pg_stat_user_tables view helps to identify:
- A) Query optimization issues
- B) Tables with frequent updates or inserts
- C) Disk space usage by tables
- D) Table access patterns
To monitor the size of temporary files in PostgreSQL, which view would you use?
- A) pg_stat_statements
- B) pg_stat_temp
- C) pg_stat_activity
- D) pg_stat_user_tables
The pg_stat_user_indexes view is used to monitor:
- A) Index creation time
- B) Index size and effectiveness
- C) Active user queries
- D) Table fragmentation
The pg_stat_bgwriter view provides insights into:
- A) Background writer activity and buffer flushing
- B) Query execution times
- C) Database schema changes
- D) Index utilization
Which PostgreSQL tool allows real-time monitoring of database activity?
- A) pg_stat_activity
- B) pgAdmin
- C) pg_stat_statements
- D) pg_stat_bgwriter
What does the pg_stat_statements extension track?
- A) Execution statistics for SQL queries
- B) Database backup details
- C) Table row-level changes
- D) WAL file creation times
Which of the following is a common use of the pg_stat_activity view?
- A) Identifying locked or blocked queries
- B) Viewing database configuration settings
- C) Monitoring disk I/O
- D) Generating query execution plans

4. Vacuuming and Autovacuuming

What is the purpose of the VACUUM command in PostgreSQL?
- A) Delete obsolete data
- B) Reclaim storage and optimize performance
- C) Backup data
- D) Compress the database
Autovacuum in PostgreSQL helps to:
- A) Automatically optimize queries
- B) Prevent table bloat by cleaning up dead tuples
- C) Schedule backups
- D) Monitor active sessions
Which of the following happens during the vacuuming process?
- A) Temporary tables are deleted
- B) Dead tuples are removed and disk space is reclaimed
- C) Indexes are rebuilt automatically
- D) Query execution plans are optimized
The autovacuum_vacuum_threshold parameter controls:
- A) The number of dead tuples that trigger autovacuum
- B) The maximum number of vacuum processes that can run simultaneously
- C) The duration of each vacuum process
- D) The level of logging for vacuum processes

Answer Key

Qno	Answer (Option with Text)
1	B) Database-level configurations
2	A) max_connections
3	B) Sets the minimum execution time for logging queries
4	A) checkpoint_timeout
5	A) Maximum amount of memory used by each query operation
6	A) shared_buffers
7	B) 100
8	A) log_statement
9	C) The memory allocated for each sorting or hash operation
10	B) Set it based on the average query size
11	C) 20% of the system RAM
12	B) Estimate the amount of memory available for caching disk blocks
13	B) Set based on available system memory and number of connections
14	B) Index creation and vacuum operations
15	B) Control memory usage for temporary tables
16	A) shared_buffers
17	B) Current activity of database sessions
18	B) Active queries
19	B) Number of rows and index statistics for tables
20	B) Tables with frequent updates or inserts
21	B) pg_stat_temp
22	B) Index size and effectiveness
23	A) Background writer activity and buffer flushing
24	B) pgAdmin
25	A) Execution statistics for SQL queries
26	A) Identifying locked or blocked queries
27	B) Reclaim storage and optimize performance
28	B) Prevent table bloat by cleaning up dead tuples
29	B) Dead tuples are removed and disk space is reclaimed
30	A) The number of dead tuples that trigger autovacuum

Post Views: 48

Previous Lesson

Back to Course

Next Lesson