PostgreSQL Database Administration Quiz Questions and Answers

Which one correctly explains Phantom read?

Answer :
  • A transaction that re-executes a query, returning a set of rows that satisfy a search condition and then finds that the set of rows that have been satisfying the condition has changed due to another recently-committed transaction.

Explanation :

A transaction that re-executes a query, returning a set of rows that satisfy a search condition and then finds that the set of rows that have been satisfying the condition has changed due to another recently-committed transaction.

Which one correctly explains Non-repeatable read?

Answer :
  • A transaction that re-reads the data that it has previously read and then finds that data has already been modified by another transaction (that committed since the initial read).

Explanation :

A transaction that re-reads the data that it has previously read and then finds that data has already been modified by another transaction (that committed since the initial read)

What does Write-Ahead Logging do?

Answer :
  • The Write-Ahead Logging enhances database reliability by logging changes before any changes or updates are made to the database

Explanation :

The Write-Ahead Logging enhances database reliability by logging changes before any changes or updates are made to the database

What are indices in Postgres?

Answer :
  • Postgres has built in functions such as hash-table, B-tree, GIST indices can be used or users can define their own indices

Explanation :

Postgres has built in functions such as hash-table, B-tree, & GIST indices can be used or users can define their own indices

Which one correctly explains Dirty read?

Answer :
  • A transaction when reads data that is written by a concurrent uncommitted transaction is the dirty read.

Explanation :

A transaction when reads data that is written by a concurrent uncommitted transaction is the dirty read.

What is the use of Table Partitioning in PostgreSQL?

Answer :
  • Table partitioning in PostgreSQL is the process of splitting a large table into smaller pieces.

Explanation :

Table partitioning in PostgreSQL is the process of splitting a large table into smaller pieces

What is MVCC in PostgreSQL used for?

Answer :
  • MVCC in PostgreSQL is used to avoid unnecessary locking of the database

Explanation :

MVCC in PostgreSQL is used to avoid unnecessary locking of the database

Are Cross-database queries supported in Postgres?

Answer :

  • Both

Explanation :

contrib or dblink allows cross-database queries using function calls.

What are indexes used for in Postgres?

Answer :
  • Indexes are used by the search engine to speed up data retrieval.

Explanation :

Indexes are used by the search engine to speed up data retrieval.

How to create a PostgreSQL user?

Answer :
  • CREATE USER < user_name > AND password ‘< password_here >’ ;

Explanation :

CREATE USER < user_name > WITH password ‘< password_here >’ ;