PostgreSQL Fundamental Course - Day 1

What is the purpose of Write-Ahead Logging (WAL) in PostgreSQL?

• To log every SQL command executed by the server for auditing.

• To ensure data integrity by recording changes before they are written to the database files.

• To log user access and activities for security monitoring.

• To write data directly to disk, bypassing the need for shared memory.

How does PostgreSQL achieve data durability?

• By storing multiple copies of data in memory.

• Through the use of Write-Ahead Logging (WAL) and periodic checkpoints.

• By replicating the database in real-time to another server.

• Using RAID storage configurations exclusively.

What is the significance of the PostgreSQL shared buffer?

• It is used to store user passwords securely.

• It caches frequently accessed disk blocks to reduce I/O operations.

• It acts as a temporary storage for transaction logs.

• It is used exclusively for caching query results.

What is the primary purpose of the PostgreSQL sequence object?

• To generate unique identifiers for rows in a table.

• To schedule automated tasks within the database.

• To sequence the execution order of transactions.

• To maintain a sequence of logged events for debugging.

In PostgreSQL, what is the purpose of the bgwriter process?

• To write background reports about database performance.

• To manage background tasks such as writing dirty buffers to disk.

• To handle background user queries that are not time-sensitive.

• To backup the database in the background without affecting performance.

Which PostgreSQL feature ensures that only authorized users can access specific database objects?

• Nothing, PosgreSQL user can access any objects

• Configure pg_hba.conf

• Role-Based Access Control (RBAC)

• PostgreSQL TDE

Which PostgreSQL extension is used to add support for geographic objects?

• pg_stat_statements

• log_fdw

• PostGIS

• pgaudit

How can you enable an extension in a PostgreSQL database?

• By modifying the postgresql.conf file

• Using the CREATE EXTENSION SQL command

• Restarting the PostgreSQL service

• Installing additional software packages on the server

What role does the 'schema' play in organizing PostgreSQL databases?

• It acts as the primary data storage unit, replacing tables.

• It is used to group database objects, such as tables and functions, logically.

• It serves as a temporary cache for query results.

• It is a security feature that encrypts data at rest.

Which of the following is NOT a schema object in PostgreSQL?

• Table

• View

• Stored Procedure

• Trigger

• User Role